A consulting company has assigned each of its employees to a specialist group (e.g., database management). Each specialist group has a team leader. When employees join the company, they are assigned a mentor for the first year. One person might mentor several employees, but an employee has at most one mentor.
Find the name of Sophie's boss.
SELECT wrk.empfname AS Worker, boss.empfname AS Boss FROM emp wrk, emp boss WHERE wrk.empfname = 'Sophie' AND wrk.bossno = boss.empno;
In a competitive bridge competition the same pair of players play together for the entire tournament. Draw a data model to record details of all the players and the pairs of players.
Who succeeded Queen Victoria?
SELECT monname, monnum FROM monarch WHERE premonname = 'Victoria' AND premonnum = 'I';
An army is broken up into many administrative units (e.g., army, brigade, platoon). A unit can contain many other units (e.g., a regiment contains two or more battalions), and a unit can be part of a larger unit (e.g., a squad is a member of a platoon).
How many lens cleaning cloths are there in the animal photography kit?
SELECT b.proddesc, b.prodcost, quantity
FROM product a JOIN assembly ON a.prodid = assembly.prodid
JOIN product b
ON assembly.subprodid = b.prodid
WHERE b.proddesc = 'Lens cleaning cloth'
AND a.proddesc = 'Animal photography kit';
1. The group known as Asleep at the Wheel is featured on tracks 3, 4, and 7 of Swing. Record these data and write SQL to answer the following:
1a. What are the titles of the recordings on which 'Asleep at the Wheel' appear?
SELECT comptitle, grprcdrole FROM composition JOIN recording ON composition.compid = recording.compid JOIN group_recordingON recording.rcdid = group_recording.rcdid JOIN `group` ON group.grpid = group_recording.grpid WHERE grpname = 'Asleep at the Wheel';
1b. List all CDs that have any tracks featuring Asleep at the Wheel.
SELECT cdid, cdtitle FROM `group` JOIN group_recording ON group.grpid = group_recording.grpid JOIN recording ON recording.rcdid = group_recording.rcdid JOIN track ON recording.rcdid = track.rcdid JOIN cd ON cd.cdid = track.cdid WHERE grpname = 'Asleep at the Wheel';
2. Record the following facts. The music of "Sing a Song of Brown," composed in 1937, is by Count Basie and Larry Clinton and the lyric by Jon Hendricks. "Sing Moten's Swing" features music by Buster and Benny Moten, and lyric by John Hendricks was composed in 1932. Write SQL to answer the following:
2a. For what songs has John Hendricks written the lyrics?
SELECT comptitle FROM person, person_composition, composition WHERE person.psnid = person_composition.psnid AND composition.compid = person_composition.compid AND psnfname = 'John' AND psnlname = 'Hendricks' AND psncomprole = 'lyrics';
2b. Report all compositions and their composers where more than one person was involved in composing the music. Make certain you report them in the correct order.
SELECT comptitle, psnfname, psnlname, psncomprole FROM composition, person_composition, person WHERE person.psnid = person_composition.psnid AND composition.compid = person_composition.compid AND composition.compid IN (SELECT compid FROM person_composition group BY compid HAVING COUNT (*) > 1) ORDER BY comptitle, psncomporder;
3. Test your SQL skills with the following:
3a. List the tracks on which Alan Paul appears as an individual or as a member of The Manhattan Transfer.
SELECT cdtitle FROM cd, person_cd, person WHERE cd.cdid = person_cd.cdid AND person.psnid = person_cd.cdid AND psnfname = 'Alan' AND psnlname = 'Paul' UNION SELECT cdtitle FROM cd, group_cd, [group], person_group, person WHERE cd.cdid = group_cd.cdid AND group_cd.grpid = group.grpid AND group.grpid = person_group.grpid AND person_group.psnid = person.psnid AND psnfname = 'Alan' AND psnlname = 'Paul' AND grpname ='The Manhattan Transfer';
3b. List all CDs featuring a group, and report the names of the group members.
SELECT cdtitle, grpname, psnfname, psnlname FROM cd, group_cd, [group], person_group, person WHERE cd.cdid = group_cd.cdid AND group_cd.grpid=group.grpid AND group.grpid = person_group.grpid AND person.psnid = person_group.psnid;
3c. Report all tracks that feature more than one group.
SELECT cdtitle, trkid FROM cd, track, recording, group_recording, `group` WHERE cd.cdid = track.cdid AND recording.rcdid = track.rcdid AND recording.rcdid = group_recording.rcdid AND group.grpid = group_recording.grpid group BY cdtitle, trkid HAVING COUNT(*) > 1;
3d. List the composers appearing on each CD.
SELECT DISTINCT cdtitle, psnfname, psnlname FROM cd, person WHERE person.psnid = person_composition.psnid AND composition.compid = person_composition.compid AND composition.compid = recording.compid AND recording.rcdid = track.rcdid AND cd.cdid = track.cdid
4. How might you extend the data model?
Maybe the database could keep a record of when each track was played. Also one could try to classify the compositions or recordings according to music types. Information on the top ten of each week could also be entered.
This page is part of the promotional and support material for Data Management (sixth edition) by Richard T. Watson