Queries.
Music Agency Relational Database Schema
recordingLabel(labelID: labelName, location)
cd(cdCode, cdTitle, numberSold, year, labelID, groupCode)
song(songCode, songTitle)
musicalGroup(groupCode, groupName)
artist(artistID, firstName, lastName, yearBorn)
topCDs(cdCode_year, rating)
composedOf(cdCode_songCode, trackNumber)
topSongs(songCode_year, rating)
member(groupCode, artistID, fromDate, toDate)
writtenBy(songCode, artistID)
Queries:
1. Which songs appeared on the CD rated number one in 2003? Order the results in ascending order on track number. (songCode, songTitle; trackNumber)
2. For each group in the database, find the number of CDs rated in the top 10. Order the results in descending order of the number of top 10 CDs. (groupCode, groupName, numberOfTop10CDs)
3. What is the maximum, minimum, and average number of tracks on CDs published since the year 2000? Order the results in chronological order by year. (year; maxNumber, minNumber, avgNumber)
4. Find the total number of CDs sold by a group with a recording label. Order the results in descending order by the total number of CDs sold. (groupCode, groupName, labelID, labelName; totalNumberSold)
5. Which artists that have written a top 5 song are currently not members of any group? Order the results alphabetically by last name and first name. (artistID, firstName, lastName, yearBorn)
6. Find the names of musical groups that have recorded CDs with every recording label located in Detroit. Order the results alphabetically by the name of the group. (groupCode, groupName)