Lab 3. More Joins
CS122, Fall 2011


(30pt) Complete the following queries in SQL. Please put your queries in an SQL script file lab3.sql and upload it to CSNS at the end of the lab. Note that file uploading will be disabled automatically after 4:00pm, and late submission will not be accepted.

1. List the names of the tracks in the "alternative" genre that are recorded by The Bullets. (Use equi-join syntax)

2. List the names of the tracks in the "alternative" genre that are recorded by The Bullets. (Use inner join syntax)

3. Report the names of all artists that came from e-mail that have not recorded a title.

4. List the names of the artists and the titles of the CDs they recorded. If an artist hasn't recorded a CD yet, put "N/A" in the CD title column.

5. List any salesperson whose supervisor is supervised by no one.

6. Each member is given his or her salesperson as a primary contact name and also the name of the salesperson's supervisor as a secondary contact name. Produce a list of member names and the primary and secondary contacts for each.

7. Find the artist groups that are located in the same region as the studio "Pacific Rim".

8. Find out whether the song "25" and the song "Song 8 and 1/2" are on the same CD. The result should be either the name of the CD or empty (i.e. no result).

9. Find out whether the song "25" and the song "Song 8 and 1/2" are recorded by the same artist. The result should be either the name of the artist or empty (i.e. no result).

10. List the names of the artist groups that have at least two members. Note that the results should not contain duplicates.