Banner

 

10 - SQL
Skill Builders

What data types would you recommend for the following?

A book's ISBN CHAR
An ISBN is a 10-character field that identifies a book. The 9 leftmost characters are digits, but the rightmost character is a check field used to detect transcription errors. This last character is sometimes an "x".
A photo of a product BLOB
A column containing the speed of light (2.9979 * 108 meters per second) FLOAT
A short description of an animal's habitat VARCHAR
The title of a Japanese book VARGRAPHIC
A legal contract CLOB
The status of an electrical switch BOOLEAN
The date and time a reservation was made TIMESTAMP
A field for storing an item's value in euros DECIMAL
The number of children in a family SMALL INTEGER

Consult the documentation for the RDBMS you are using to determine how you report a numeric value with a defined number of decimal places.

MySQL has the ROUND function

Create a table containing the average daily temperature in Tromsø, Norway, then write a function to convert Celsius to Fahrenheit (F = C*1.8 + 32), and test the function by reporting temperatures in C and F.

CREATE TABLE temp ( 
month CHAR(3), 
tempC SMALLINT, 
PRIMARY KEY(month)); 
CREATE FUNCTION c_to_f(c REAL)
RETURNS REAL
RETURN (c*1.8 + 32);
			
PostgreSQL (The PostgreSQL version is similar but not fully compatible with SQL-99)
CREATE FUNCTION c_to_f(numeric)
RETURNS NUMERIC AS 
'SELECT $1*1.8 + 32;' 
LANGUAGE 'SQL'; 
  

1. After verifying that the system you use for learning SQL supports stored procedures, create the tables for the preceding data model and enter the code for the stored procedure. Now, test the stored procedure and query the tables to verify that the procedure has worked.

CREATE TABLE account (
acctno INTEGER,
acctbalance DECIMAL(9,2),
primary key (acctno));

CREATE TABLE transaction (
transid INTEGER,
transamt DECIMAL(9,2),
transdate DATE,
PRIMARY KEY(transid));

CREATE TABLE entry (
transid INTEGER,
acctno INTEGER,
entrytype CHAR(2),
PRIMARY KEY(acctno, transid),
FOREIGN KEY(acctno) REFERENCES account(acctno),
FOREIGN KEY(transid) REFERENCES transaction(transid));

2. Write a stored procedure to add details of a gift to the donation database (see exercises in Chapter 5).

Assuming an existing donor. The YEAR function returns the year part of the current date.
DELIMITER //
CREATE PROCEDURE donation (
IN donorno INTEGER,
IN amt DECIMAL(8,0))
LANGUAGE SQL
DETERMINISTIC
BEGIN
INSERT INTO gift VALUES (amt, YEAR(CURDATE()), donorno);
END//

Why is the primary key of STOCK_LOG not the same as that of STOCK?

The primary key of stock, stkcode, is not unique within stock_log because there can be multiple updates for any particular stock.

This page is part of the promotional and support material for Data Management (sixth edition) by Richard T. Watson
For questions and comments please contact the author

Date revised: 19-Oct-2016