# 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.prodidJOIN  product bON  assembly.subprodid = b.prodidWHERE   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.