Exercise 5
CS122, Spring 2004
Thursday, April 22
Use Student.mdb:
1. (3pt each) Chapter 9, Ex 1, 2, 3, 5, and 8.
Use Books.mdb:
2. (3pt) 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. (3pt) For each author, list their name and the number of books they
published.
Discussion
[Query 1.1] SELECT c.course_name, COUNT(p.prereq) FROM Course c, Prereq
p WHERE c.course_number = p.course_number GROUP BY c.course_name ORDER
BY COUNT(p.prereq);
[Query 1.2] SELECT COUNT(*) FROM Student WHERE class = 3;
[Query 1.3] SELECT class, COUNT(stno) FROM Student WHERE major = 'MATH'
GROUP BY class HAVING COUNT(stno) >= 2;
[Query 1.5] SELECT course_num, COUNT(section_id) FROM Section GROUP BY
course_num ORDER BY COUNT(section_id) DESC;
[Query 1.8] SELECT s.instructor, COUNT(g.grade) FROM Section s,
Grade_report g WHERE g.section_id = s.section_id AND g.grade = 'A'
GROUP BY s.instructor ORDER BY COUNT(g.grade);
[Query 2] SELECT type FROM Titles GROUP BY type ORDER BY SUM(sales)
DESC;
[Query 3] SELECT a.au_fname, a.au_lname, count(ta.title_id) FROM
Authors a, Title_authors ta WHERE a.au_id = ta.au_id GROUP BY
a.au_fname, a.au_lname ORDER BY count(ta.title_id) DESC;