Exercise 3
CS122, Spring 2004

Thursday, April 8

 Use Student.mdb:

1. (3pt each) Chapter 2, Ex 7, 13, 14, 15, and 16.

Use Books.mdb:

2. (4pt) List the book categories in order of sales, most sales first. Don't list sales figures, and don't allow duplicates in the results. [Abbott]

3. (4pt) Find the titles of the books authored by Sarah Buchman.

4. (4pt) Find how much royalties Sarah Buchman received for each of her books. List the book titles and royalities with proper column headings.

Discussion

[Query 1.7] SELECT s.sname FROM Student s, Grade_report g WHERE s.stno = g.student_number AND s.class IS NULL AND g.grade = 'A';

• Join
• Table aliases
• Check for NULL values
• Zero results

[Query 1.13] SELECT COUNT(dname) FROM Department_to_major;
[Query 1.15]

• NULL values, COUNT(field), and COUNT(*)

[Query 1.14] SELECT COUNT(*) FROM (SELECT DISTINCT bldg FROM Room);

• Subquery -- query results as a table

[Query 1.16]  SELECT sname FROM Student WHERE class BETWEEN 2 AND 4;

• The class field

[Query 2]

• A much more complex query than it looks.

[Query 3]  SELECT t.title_name FROM Titles t, Authors a, Title_authors ta WHERE a.au_fname = 'Sarah' AND a.au_lname = 'Buchman' AND a.au_id = ta.au_id AND ta.title_id = t.title_id;

• A join with three tables

[Query 4]  SELECT t.title_name as [Book Titles], t.price * t.sales * r.royalty_rate as [Royalties] FROM Titles t, Authors a, Title_authors ta, Royalties r WHERE a.au_fname = 'Sarah' AND a.au_lname = 'Buchman' AND a.au_id = ta.au_id AND ta.title_id = t.title_id AND t.title_id = r.title_id;

• Royalties = Price * Sales * Royalty_Rate