Exercise 10
CS122, Spring 2004
Thursday, June 3
(20pt) Chapter 6, 8, and 10. Use Student.mdb:
1. Exercise 10.1. Assume we do not use DISTINCT in any of the three
queries, the result of the join query would be different from the
results of the two subquery queries, e.g. one name may appear multiple
times in the result of the join query, while the results of the
subquery queries do not have such duplicates. Explain why.
2. Exercise 10.5
3. Exercise 10.7. With the NULL valued row added, complete the following three queries:
(a) Find the names of the departments that have secretaries using IN.
(b) Change IN in query (a) to NOT IN and explain the result.
(c) Find the names of the departments that do not have secretaries using NOT EXISTS.
Discussion
[Query 1] Without DISTINCT, if a student got multiple C's, the name of
the student will appear in the result multiple times. In the two
subquery cases, the outer query iterates through each student only once.
(a) SELECT s.sname FROM Student s, Grade_report g WHERE s.stno = g.student_number AND g.grade = 'C' ORDER BY s.sname;
(b) SELECT s.sname FROM Student s WHERE s.stno IN (SELECT
g.student_number FROM Grade_report g WHERE g.grade = 'C') ORDER BY
s.sname;
(c) SELECT s.sname FROM Student s WHERE EXISTS (SELECt 1 FROM
Grade_report g WHERE g.student_no = s.stno AND g.grade = 'C') ORDER BY
s.sname;
[Query 2] SELECT a.company FROM Plants a WHERE NOT EXISTS (SELECT
b.plantlo FROM Plants b WHERE NOT EXISTS (SELECT c.company, c.plantlo
FROM Plants c WHERE c.company = a.company AND c.plantlo = b.plantlo));
[Query 3] The behavior of comparing a NULL value to a non-null value is undefined.
(a) SELECT d.dname from Department_to_major d WHERE d.dcode IN (SELECT s.dcode FROM Secretary s);
(b) SELECT d.dname from Department_to_major d WHERE d.dcode NOT IN (SELECT s.dcode FROM Secretary s);
(c) SELECT d.dname from Department_to_major d WHERE NOT EXISTS (SELECT 1 FROM Secretary s WHERE s.dcode = d.dcode);
In the final we will use Lyric2K.mdb from 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.
4. For each lead source, list the number of artists who came into the system through that lead source.
5. List the length of the longest Real Audio track in the "metal" genre.
6. List each title from the Titles table along with the name of the studio where it was recorded.
7. List the studio names and the first name of each studio contact.
8. List the cities and regions that have both members and artists.