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.
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);
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).
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?
This page is part of the promotional and support material for Data Management (open edition) by Richard T. Watson |