Chapter 3
Skill Builders
- Page 61
A ship has a name, registration code, gross tonnage, and a year of construction. Ships are classified as cargo or passenger. Draw a data model for a ship.
- Page 65
Create a relational database for the ship entity you modeled previously. Add some rows to the table.
CREATE TABLE ship (
regcode VARCHAR(15),
shipname VARCHAR(30),
grosston INTEGER,
yearconstruct SMALLINT,
classification VARCHAR(10),
PRIMARY KEY (regcode));
- Page 72
List those shares where the value of the holding exceeds one million.
SELECT shrfirm, shrqty, shrprice, shrqty*shrprice
AS shrvalue FROM shr
WHERE shrqty*shrprice > 1000000;
- Page 77
Find the name of the firm for which the value of the holding is greatest.
SELECT shrfirm FROM shr
WHERE shrqty*shrprice = (select MAX (shrqty*shrprice) FROM shr);
- Page 80
List the firms containing "ian" in their name.
SELECT shrfirm FROM shr WHERE shrfirm LIKE '%ian%';
- Page 84
1. Create a single table database using the data in Table 3-10.
CREATE TABLE track (
trkid INTEGER,
tracknum INTEGER,
title VARCHAR(20),
length DECIMAL(4,2),
PRIMARY KEY (trkid));
2. Justify your choice of data type for each of the attributes.
- trackid and track number should be integers since only integers will be used in these two fields. No characters and no decimals are allowed.
- title is limited to 20 characters. Most titles should have less than 20 characters.
- length should be of type decimal with two decimal places to the right.
- Page 86
1. Write SQL commands for the following queries:
- Report all tracks between 4 and 5 minutes long.
- On what track is "Naima "?
- Sort the tracks by descending length.
- What tracks are longer than the average length of tracks on the CD?
- How many tracks are less than 5 minutes?
- What tracks start with "Cou "?
-
SELECT * FROM track
WHERE length < 5 AND length > 4;
-
SELECT trkid FROM track
WHERE title = 'Naima';
-
SELECT * FROM track
ORDER BY length DESC;
-
SELECT * FROM track
WHERE length > (SELECT AVG(length) FROM track);
-
SELECT COUNT(trkid) FROM track
WHERE length < 5;
-
SELECT trkid FROM track
WHERE title = 'Cou%';
3. Write SQL to answer the following queries:
- What is the longest track on Swing?
- What tracks on Swing include the word Java?
Both queries cannot be written because no information on a CD's name is contained in the database.
4. What is the data model missing?
As one can see from the answers to question 3, some important pieces of information are missing.
-
The database does not give information on which CD a track is found.
-
The artists of the tracks are also not included. Thus, one cannot find all songs by Manhattan Transfer with this database.
This page is part of the promotional and support material for Data Management (fifth edition) by Richard T. Watson
For questions and comments please contact the author |
Date revised:
January 24, 2010