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.
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));
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;
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);
List the firms containing "ian" in their name.
SELECT shrfirm FROM shr WHERE shrfirm REGEXP 'ian';
Create a single table database using the data in the preceding table.
CREATE TABLE track ( trkid INTEGER, tracknum INTEGER, title VARCHAR(20), length DECIMAL(4,2), PRIMARY KEY (trkid));
Justify your choice of data type for each of the attributes.
1. Write SQL commands for the following queries:
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 REGEXP '^Cou';
3. Write SQL to answer the following queries:
4. What is the data model missing?
This page is part of the promotional and support material for Data Management (open edition) by Richard T. Watson