Homework # 3

1. SELECT sid, sname
             FROM Student
        WHERE gpa <= 4.3;

2. SELECT T.sid
            FROM Transcript T, Course C
        WHERE T.cid = C.cid AND C.pcode='ITEC';

(The condition AND C.pcode was added on March 15, 2003)

4. SELECT DISTINCT C.pcode
            FROM Course C, Transcript T, Student S
        WHERE C.cid = T.cid AND T.sid = S.sid AND S.sname = 'Brown';

Query 3 from slide 22 was presented in class.

Homework # 4

Slide 5

2. SELECT S.sname
            FROM Student S
        WHERE S.sid IN (SELECT T.sid
                                            FROM Transcript T
                                        WHERE T.cid IN (SELECT C.cid
                                                                            FROM Course C
                                                                        WHERE C.pcode = 'ITEC'));

Slide 9

5. SELECT S.sid, S.sname
            FROM Student S
        WHERE NOT EXISTS (SELECT *
                                                        FROM Transcript T
                                                    WHERE T.sid = S.sid);

Slide 11

7. SELECT S.sid, S.sname
            FROM Student S
        WHERE S.gpa > ALL (SELECT S1.gpa
                                                       FROM Student S1
                                                WHERE S1.sid IN (SELECT  T.sid
                                                                                   FROM Transcript T
                                                                                 WHERE T.cid IN (SELECT C.cid
                                                                                                                           FROM Course C
                                                                                                                   WHERE C.pcode = 'COSC')));

Slide 14

9. SELECT COUNT(DISTINCT sname) AS N_of_names
           FROM Student;

10. SELECT S.sname
            FROM Student S
         WHERE S.age = (SELECT MAX(S1.age)
                                            FROM Student S1);