Exercise 4
CS122, Spring 2004

Thursday, April 15

 Use Student.mdb:

1. Chapter 4, Ex 4, 5, 6, 7, 8, and 9. For 5(a), 6(a), and 7(a), order the results by instructor name in descending order, and for 5(b), 6(b), and 7(b), order the results by course name in descending order.

Use Movies.mdb:

2. List the movies whose overall grosses are higher than that of 50 First Dates.

Use Books.mdb:

3. Find the phone number of the author of the book Kiss My Boo-Boo.

4. Write Query 4 from the last lab assignment ("Sarah Buchman's royalies") using INNER JOIN.

Discussion

[Query 1.4]  SELECT c.course_name from Course c, Prereq p WHERE c.course_number = p.course_number;

[Query 1.5]
(a)  SELECT s.instructor, c.course_name, s.semester, s.year FROM Section s INNER JOIN Course c on s.course_num = c.course_number ORDER BY s.instructor DESC;
(b) SELECT c.course_name, s.instructor, c.offering_dept FROM Section s INNER JOIN Course c on s.course_num = c.course_number ORDER BY c.course_name DESC;
[Query 1.6]
[Query 1.7]
[Query 1.8]

• INNER JOIN, LEFT JOIN, and RIGHT JOIN

[Query 2] SELECT b.title FROM Box_office a, Box_office b WHERE a.title='50 First Dates' AND b.cgross > a.cgross;

• Self join

[Query 3] SELECT a.phone FROM Authors a, Titles t, Title_authors ta WHERE t.title_name='Kiss My Boo-Boo' AND t.title_id = ta.title_id AND ta.au_id = a.au_id;

[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;

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