Banner

 

6 - One-to-One and Recursive Relationships
Skill Builders

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 (open edition) by Richard T. Watson
For questions and comments please contact the author
Date revised: 02-Dec-2022