Untitled Document

Page 140

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.

SELECT wrk.empfname AS Worker, boss.empfname AS Boss
FROM emp wrk, emp boss
WHERE wrk.empfname = 'Sophie'
AND wrk.bossno = boss.empno;

Page 145

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.

SELECT monname, monnum FROM monarch
	WHERE premonname = 'Victoria' AND premonnum = 'I';

Page 148

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).

Page 150

How many lens cleaning cloths are there in the animal photography kit?

SELECT b.proddesc, b.prodcost, quantity 
FROM product a, assembly, product b
WHERE  b.proddesc = 'Lens cleaning cloth'
AND a.proddesc = 'Animal photography kit'
AND a.prodid = assembly.prodid
AND assembly.subprodid = b.prodid;

Page 153

1. The members of The Manhattan Transfer are Cheryl Bentyne, Janis Siegel, Tim Hauser, and Alan Paul. Update the database with this information, and write SQL to answer the following:

Database programs will not accept a table named Group in SQL queries since GROUP is also an SQL command. One must indicate to the program that group is a table name and not a command by writing the query with brackets around group: [group].

SELECT psnfname, psnlname FROM person, [group], person_group
   WHERE person.psnid = person_group.psnid
   AND group.grpid = person_group.grpid
   AND grpname = 'The Manhattan Transfer';

SELECT cdtitle FROM cd, group_cd, [group]
   WHERE cd.cdid = group_cd.cdid
   AND group.grpid = group_cd.cdid
   AND grpname = 'The Manhattan Transfer';

1c. What CDs feature Cheryl Bentyne as an individual or member of a group?

SELECT cdtitle FROM cd, person_cd, person
   WHERE cd.cdid = person_cd.cdid
   AND person.psnid = person_cd.cdid
   AND psnfname = 'Cheryl'
   AND psnlname = 'Bentine'
UNION
SELECT cdtitle FROM cd, group_cd,grp, person_group, person
   WHERE cd.cdid = group_cd.cdid
   AND group_cd.grpid = grp.grpid
   AND grp.grpid = person_group.grpid
   AND person_group.psnid = person.psnid
   AND psnfname = 'Cheryl'
   AND psnlname = 'Bentine';

Page 153

2. 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:

2a. What are the titles of the recordings on which Asleep at the Wheel appear?

SELECT comptitle, grprcdrole
   FROM composition, recording, group_recording, [group]
      WHERE composition.compid = recording.compid
      AND recording.rcdid = group_recording.rcdid
      AND group.grpid = group_recording.grpid
      AND grpname = 'Asleep at the Wheel';

2b. List all CDs that have any tracks featuring Asleep at the Wheel.

SELECT cdid, cdtitle
   FROM [group], group_recording, recording, track, cd
      WHERE recording.rcdid = group_recording.rcdid
      AND group.grpid = group_recording.grpid
      AND recording.rcdid = track.rcdid
      AND cd.cdid = track.cdid AND grpname = 'Asleep at the Wheel';

Page 154

3. 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:

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';

3b. 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;

4a. 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';

4b. 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;

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;

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.

Chapter 6
Skill Builders

Date revised: October 24, 2009

Chapter 6 Skill Builders

Date revised: October 24, 2009

Chapter 6
Skill Builders