Exercise 7
CS122, Spring 2004
Thursday, May 13
All exercises are adopted from the exercises in Chapter 7 of [Bagui & Earp].
Complete the following queries using Student.mdb:
1.(6pt)
(a) List names, class and course numbers of students who have earned B's in computer science courses.
(b) Modify the query in (a) to remove from the results the students who are sophomores (class = 2). Use NOT IN.
2.(3pt) List the names, grades and course numbers of students who have
earned A's in computer science or math courses. The query should be in
the form of subquery1 UNION subquery2, where subquery1 produces part of
the result set under the condition "computer science course and A
grade", and subquery2 produces the other part of the result set with
the condition "math course and A grade".
3.(3pt) Find the names and majors of students who have made a C in any
course. Make the "who have made a C in any course" a subquery for which
you use IN.
4.(6pt)
(a) List the student numbers of the students who have made A's and B's in computer science courses.
(b) List the student numbers of the students who have taken math courses.
5.(12pt) Let the result set of query 4(a) be A and the result set of query 4(b) be B.
(a) Write a query to display A-B.
(b) Write a qeury to display B-A.
(c) Verify De Morgan's law:
- List all student numbers which are not in (A UNION B).
- List all student numbers which are not in A and are not in B.
Discussion
[Query 1]
(a) SELECT st.sname, st.class, s.course_num FROM Student st,
Grade_report g, Section s WHERE st.stno = g.student_number AND
g.section_id = s.section_id AND g.grade = 'B' AND s.course_num LIKE
'COSC*';
(b) SELECT st.sname, st.class, s.course_num FROM Student st,
Grade_report g, Section s WHERE st.stno = g.student_number AND
g.section_id = s.section_id AND g.grade = 'B' AND s.course_num LIKE
'COSC*' AND st.class NOT IN (2);
[Query 2]
SELECT st.sname, s.course_num, g.grade FROM Student st, Grade_report g,
Section s WHERE st.stno = g.student_number AND g.section_id =
s.section_id AND g.grade = 'A' AND s.course_num LIKE 'COSC*'
UNION
SELECT st.sname, s.course_num, g.grade FROM Student st, Grade_report g,
Section s WHERE st.stno = g.student_number AND g.section_id =
s.section_id AND g.grade = 'A' AND s.course_num LIKE 'MATH*';
- s.course_num LIKE 'COSC*' OR s.course_num LIKE 'MATH*'
[Query 3]
SELECT sname, major FROM Student WHERE stno IN ( SELECT DISTINCT s.stno
FROM Student s, Grade_report g WHERE s.stno = g.student_number AND
g.grade = 'C');
[Query 4]
(a) SELECT DISTINCT st.stno FROM Student st, Grade_report g, Section s
WHERE st.stno = g.student_number AND g.section_id = s.section_id AND
(g.grade = 'A' OR g.grade = 'B') AND s.course_num LIKE 'COSC*';
(b) SELECT DISTINCT st.stno FROM Student st, Grade_report g, Section s
WHERE st.stno = g.student_number AND g.section_id = s.section_id AND
s.course_num LIKE 'MATH*';
[Query 5]
(a) SELECT DISTINCT st.stno FROM Student st, Grade_report g, Section s
WHERE st.stno = g.student_number AND g.section_id = s.section_id AND
(g.grade = 'A' or g.grade = 'B') AND s.course_num LIKE 'COSC*' AND st.stno NOT IN ( SELECT DISTINCT st.stno FROM Student st, Grade_report g, Section s
WHERE st.stno = g.student_number AND g.section_id = s.section_id AND
s.course_num LIKE 'MATH*' );
(b) SELECT DISTINCT st.stno FROM Student st, Grade_report g, Section s
WHERE st.stno = g.student_number AND g.section_id = s.section_id AND
s.course_num LIKE 'MATH*' AND st.stno NOT IN ( SELECT DISTINCT st.stno FROM Student st, Grade_report g, Section s
WHERE st.stno = g.student_number AND g.section_id = s.section_id AND
(g.grade = 'A' or g.grade = 'B') AND s.course_num LIKE 'COSC*' );
(c) SELECT stno FROM Student WHERE stno NOT IN (SELECT stno FROM
(
SELECT DISTINCT st.stno FROM Student st, Grade_report g, Section s
WHERE st.stno = g.student_number AND g.section_id = s.section_id AND
(g.grade = 'A' or g.grade = 'B') AND s.course_num LIKE 'COSC*'
UNION
SELECT DISTINCT st.stno FROM Student st, Grade_report g, Section s
WHERE st.stno = g.student_number AND g.section_id = s.section_id AND
s.course_num LIKE 'MATH*'
));
- NOT IN (A UNION B) => "this operation is not allowed in subqueries"
(d) SELECT stno FROM Student WHERE stno NOT IN
(
SELECT DISTINCT st.stno FROM Student st, Grade_report g, Section s
WHERE st.stno = g.student_number AND g.section_id = s.section_id AND
(g.grade = 'A' or g.grade = 'B') AND s.course_num LIKE 'COSC*'
)
AND stno NOT IN
(
SELECT DISTINCT st.stno FROM Student st, Grade_report g, Section s
WHERE st.stno = g.student_number AND g.section_id = s.section_id AND
s.course_num LIKE 'MATH*'
);