Homework 4
CS422, Summer 2004

Due: Thursday, August 12

Please include all your queries in a single text file (with .txt suffix or .sql suffix), and upload it using the online turnin server. Note that the turnin server automatically rejects uploads after 11:59PM of the due date, so please make sure to turn in your solutions in time.

Although the assignment description below refers to the PostgreSQL  DB on the cs server, you may complete the queries using any DBMS of your choice.

---

[Reading] Chapter 6 of the textbook, and Chapter 4-11 of the SQL Language section in the PostgreSQL documentation.

[Queries](100pt) Lyrics is the the sample database included in the book SQL Essentials by Gary Randolph and Jeffrey Griffin. Here's the description of the database taken from the book:

Lyric Music is an e-commerce company that provides web services to music artists. Among its services are offering mp3 and Real Audio files for download, selling CDs, and developing promotional materials for artists.

• Artists - this table holds information about each artist. An artist could be a band or group. It could also be a solo artist.
• Members - this table holds information about the individuals who are members of each artist group.
• XrefArtistsMembers - An artist (band) may have several members. But also a member may be part of more than one artist group. For instance, Band X may have four members, one of whom also works as a solo artist. A cross-reference (xref) table is needed to maintain this complex relationship between artists and members. The primary key of this table is (MemberID, ArtistID), so it can match any artist with any member and vice-versa. The RespParty is a YES/NO field that identifies which of each artist's members is the person responsible for paying bills and receiving official correspondence from Lyric Music.
• Titles - this table tracks information on each CD title produced by each artist. ArtistID is a foreign key that relates this table to the Artists table.
• Tracks - each CD title can have multiple tracks. The primary key of this table is (TitleID, TrackNum). the MP3 and ReadAud fields are YES/NO fields that indicates whether that track is available in mp3 or Real Audio format.
• Genre - each title is classified according to its genre. This table is a simple look-up table that lists the valid genres.
• Salespeople - this table tracks information on the salespeople who work with each member and studio.
• Studios - this table holds information about the studios that produce each CD title.

Download the SQL script file LyricPostgres.sql, run the script file to create the tables in your PostgreSQL database, and complete the following queries:

1. List the first name, last name, and region of all members from Virginia (VA).
2. Report all the information on tracks that do not have an MP3.
3. Report the number of tracks for each TitleID
4. For each lead source, list the number of artists who came into the system through that lead source.
5. Report the last name of the member who would be reported first in alphabetical order
6. List the track titles of all titles in the "Alternative" genre.
7. List the length of the longest Real Audio track in the "metal" genre.
8. List track titles and lengths of tracks with a length longer than all tracks of the "metal" genre.
9. List each title from the Titles table along with the name of the studio where it was recorded.
10. List the studio names and the first name of each studio contact.
11. List the cities and regions that have both members and artists.
12. List each genre from the genre table and the total length in minutes of all tracks recorded for that genre if any.
13. Report the names of all artists that came from email that have not recorded a title.
14. List each artist name and a count of the number of members assigned to that artist.
15. List any sales person who supervisor is supervised by no one.
16. Each member is given his or her salesperson as a primary contact name and also the name of that saleperson's supervisor as a secondary contact name. Produce a list of member names and the primary and secondary contacts for each.
17. Report the studio name and the first name of each studio contact.
18. Report the artist name and age in years of the responsible member for each artist at teh time of that artist's entry date
19. Report all genres from the Genre table, capitalizing the first letter of each.
20. Use EXISTS to list the names of all artists who have not recorded a title.
21. Use a correlated subquery to list all genres that do not have recorded titles.
22. Report the title name, number of tracks, and total time in minutes for each title.
    
23. Salesperson Bob Bentley has agreed to turn over all his female members to salesperson Lisa Williams. Update the Members table accordingly.
24. Members Doug Finney and Terry Irving are forming a new artist to be called "Doug and Terry". Add this record to the Artists table, using ArtistID 13, the address information of Doug Finney, no web address, today's entry date, and no lead source. Don't hand-code any data for insert that can be looked up from the Members table.
25. Lyric Music has decided to set up a web page for every artist who doesn't have a web site. The web address will be www.lyricmusic.com/ followed by the artistID. Update the Artists table accordingly.