A hamburger shop makes several types of hamburgers, and the same type of ingredient can be used with several types of hamburgers. This does not literally mean the same piece of lettuce is used many times, but lettuce is used with several types of hamburgers. Draw the data model for this situation. What is a good name for the associative entity?
Report all red items that have been sold. Write the query twice, once using EXISTS and once without EXISTS.
SELECT itemname, itemcolor FROM item WHERE itemcolor = 'Red' AND EXISTS (SELECT * FROMlineitem WHERE lineitem.itemno = item.itemno); SELECT itemname, itemcolor FROM item WHERE itemcolor = 'Red' AND itemno IN (SELECT itemno FROM lineitem);
Report all red items that have not been sold. Write the query twice, once using EXISTS and once without EXISTS.
SELECT itemname, itemcolor FROM item WHERE itemcolor = 'Red' AND NOT EXISTS (SELECT * FROMlineitem WHERE lineitem.itemno = item.itemno); SELECT itemname, itemcolor FROM item WHERE itemcolor = 'Red' AND itemno NOT IN (SELECT itemno FROM lineitem);
Find the brown items that have appeared in all sales.
SELECT itemname FROM item
WHERE itemcolor = 'Brown'
AND NOT EXISTS
(SELECT *
FROM sale WHERE NOT EXISTS
(SELECT * FROM lineitem
WHERE lineitem.itemno = item.itemno
AND lineitem.saleno = sale.saleno));
List the items that contain the words "Hat", "Helmet", or "Stetson" in their name.
SELECT itemname FROM item
WHERE itemname REGEXP 'Hat|hat|Helmet|helmet|Stetson|stetson';
1. What data will you enter in PERSON-CD to relate John Coltrane to the Giant Steps CD?
The entry into the table person-cd for John Coltrane's Giant Steps CD will look like this:
| psnid | cdid | psncdorder |
| 1 | 1 | 1 |
Person ID (psnid) 1 stands for John Coltrane in the Person table, cdid 1 stands for the CD Giant Steps in the cd table and person-cd-order (psncdorder) 1 stands for the fact that John Coltrane is the first (and only) artist mentioned on the CD cover.
2. Enter appropriate values in PERSON-COMPOSITION to relate John Coltrane to compositions with compid 1 through 7. You should indicate he wrote the music by entering a value of "music " for the person ' s role in the composition.
Since John Coltrane wrote the music on compositions 1-7, the entries will be as follows:
| psnid | compid | psncomprole | psncomporder |
| 1 | 1 | music | 1 |
| 1 | 2 | music | 1 |
| 1 | 3 | music | 1 |
| 1 | 4 | music | 1 |
| 1 | 5 | music | 1 |
| 1 | 6 | music | 1 |
| 1 | 7 | music | 1 |
Person ID (psnid) 1 stands for John Coltrane, composition ID (compid) 1-7 stands for the 7 compositions, person-composition-role (psncomprole) stands for the role the person played in composing this piece. Person- composition-order (psncomporder) stands for the order the person is mentioned in this part of the composition. Since John Coltrane composed the music for these 7 pieces alone, he is mentioned first.
3a. List the tracks on Swing.
SELECT trknum, comptitle FROM cd JOIN track JOIN recording ON recording.rcdid = track.rcdid JOIN composition ON composition.compid = recording.compid WHERE cdtitle = 'Swing';
3b.Who composed the music for Spiral?
SELECT psnfname, psnlname, psncomprole, psncomporder
FROM person JOIN person_composition ON person.psnid = person_composition.psnid
JOIN composition ON composition.compid = person_composition.compid
WHERE comptitle = 'Spiral';
3c. Who played which instruments for the May 4, 1959, recording of "Giant Steps"?
SELECT psnfname, psnlname, psnrcdrole FROM person JOIN person_recording ON person.psnid = person_recording.psnid JOIN recording ON recording.rcdid = person_recording.rcdid JOIN composition ON recording.compid = composition.compid WHERE comptitle = 'Giant Steps' AND RCDDATE = '1959-05-04';
3d. List the composers who write music and play the tenor sax?
SELECT psnfname, psnlname
FROM person JOIN person_recording ON person.psnid = person_recording.psnid
JOIN person_composition ON person.psnid = person_composition.psnid
WHERE psnrcdrole = 'tenor sax'
AND psncomprole = 'music';
4. What is the data model missing?
The data model is missing information on the name of bands or other groups featured on the CDs. So far there is only information on individuals.
This page is part of the promotional and support material for Data Management (open edition) by Richard T. Watson |