Exercise 6
CS122, Spring 2004
Thursday, April 29
Chapter 5. Use Student.mdb:
1. (3pt) Ex 3. Only show the final query, not the intermediate steps.
2. (3pt) Ex 6. Assume that each <stno,
sname> pair in the Student table is unique. e.g. if two
students have the same name, their stno
must be different, or if two students have the same stno, their names must be
different..
3. (3pt) Ex 9. only do average.
4. (12pt) Ex 10, 15, 17, 18
Use Books.mdb
5. (9pt) Find the books
- with the earliest publish date.
- with the latest publish date.
- published in the last 4 years.
Discussion
[Query 1] SELECT DISTINCT st.sname, g.grade from Student st,
Grade_report g, Section s WHERE st.stno = g.student_number AND
s.section_id = g.section_id AND s.course_num LIKE 'ACCT*' AND (g.grade
= 'A' OR g.grade = 'B');
[Query 2] SELECT COUNT(*) FROM Student s1, Student s2 WHERE s1.stno
<> s2.stno AND s1.sname = s2.sname OR s1.stno = s2.stno AND
s1.sname <> s2.sname;
- Duplicates and primary key
[Query 3(a)] SELECT AVG(capacity) FROM Room;
[Query 3(b)] SELECT AVG( NZ(capacity, 40) ) FROM Room;
[Query 4.10(a)] SELECT TOP 10 stno, sname + ', ' +
UCASE(MID(sname, LEN(sname)/2+1, 1)) + "." FROM Student ORDER BY stno;
[Query 4.10(b)] SELECT TOP 10 UCASE(stno), UCASE(sname) + ', ' +
UCASE(MID(sname, LEN(sname)/2+1, 1)) + "." FROM Student ORDER BY stno;
[Query 4.15]
SELECT sname, stno FROM Student WHERE stno BETWEEN 140 AND 149;
SELECT sname, stno FROM Student WHERE stno LIKE '14?';
[Query 4.18] SELECT DISTINCT s.sname+"*" FROM Student s,
Grade_report g WHERE s.stno = g.student_number AND g.grade = 'A' AND
(s.class = 3 OR s.class = 4);
[Query 5(a)] SELECT TOP 1 title_name FROM Titles WHERE pubdate IS NOT
NULL ORDER BY pubdate ASC;
[Query 5(b)] SELECT TOP 1 title_name FROM Titles WHERE pubdate IS NOT
NULL ORDER BY pubdate DESC;
[Query 5(c)] SELECT title_name, pubdate FROM Titles WHERE
YEAR(DATE()) - YEAR(pubdate) <= 4;
- More precise about "last 4 years"?
- More about DATE type.