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)); 


INSERT INTO track VALUES (1, 1, 'Giant Steps', 4.72);
INSERT INTO track VALUES (2, 2, 'Cousin Mary', 5.75);
INSERT INTO track VALUES (3, 3, 'Countdown', 2.35);
INSERT INTO track VALUES (4, 4, 'Spiral', 5.93);
INSERT INTO track VALUES (5, 5, 'Syeeda''s song flute', 7); 
INSERT INTO track VALUES (6, 6, 'Naima', 4.35);
INSERT INTO track VALUES (7, 7, 'Mr. P.C.', 6.95);
INSERT INTO track VALUES (8, 8, 'Giant Steps', 3.67);
INSERT INTO track VALUES (9, 9, 'Naima', 4.45);
INSERT INTO track VALUES (10, 10, 'Cousin Mary', 5.9);
INSERT INTO track VALUES (11, 11, 'Countdown', 4.55);
INSERT INTO track VALUES (12, 12, 'Syeeda''s song flute', 7.03);
INSERT INTO track VALUES (13, 1, 'Stomp of King Porter', 3.2); 
INSERT INTO track VALUES (14, 2, 'Sing a Study in Brown', 2.85);
INSERT INTO track VALUES (15, 3, 'Sing Moten''s Swing', 3.6); 
INSERT INTO track VALUES (16, 4, 'A-tisket, A-tasket', 2.95); 
INSERT INTO track VALUES (17, 5, 'I Know Why', 3.57);
INSERT INTO track VALUES (18, 6, 'Sing You Sinners', 2.75); 
INSERT INTO track VALUES (19, 7, 'Java Jive', 2.85);
INSERT INTO track VALUES (20, 8, 'Down South Camp Meetin''', 3.25);
INSERT INTO track VALUES (21, 9, 'Topsy', 3.23);
INSERT INTO track VALUES (22, 10, 'Clouds', 7.2);
INSERT INTO track VALUES (23, 11, 'Skyliner', 3.18);
INSERT INTO track VALUES (24, 12, 'It''s Good Enough to Keep', 3.18);
INSERT INTO track VALUES (25, 13, 'Choo Choo Ch'' Boogie', 3);

2. Justify your choice of data type for each of the attributes.

Page 86

1. Write SQL commands for the following queries:

  1. Report all tracks between 4 and 5 minutes long.
  2. On what track is "Naima "?
  3. Sort the tracks by descending length.
  4. What tracks are longer than the average length of tracks on the CD?
  5. How many tracks are less than 5 minutes?
  6. What tracks start with "Cou "?
  1. SELECT * FROM track
    WHERE length < 5 AND length > 4;
  2. SELECT trkid FROM track
    WHERE title = 'Naima';
  3. SELECT * FROM track
    ORDER BY length DESC;
  4. SELECT * FROM track
    WHERE length > (SELECT AVG(length) FROM track);
  5. SELECT COUNT(trkid) FROM track
    WHERE length < 5;
  6. SELECT trkid FROM track
    WHERE title = 'Cou%';

3. Write SQL to answer the following queries:

  1. What is the longest track on Swing?
  2. 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.
  1. The database does not give information on which CD a track is found.
  2. 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

Revised: Wed, Aug 30, 2006