Question 6 of 8: SQL & relational algebra — Student/Course/Results

Question 6: SQL & relational algebra — Student/Course/Results (20 marks)

Question text not reproduced: the examination questions are © Engineers and Geoscientists BC. Open the official past paper (linked at the top of this page) to read the question, then follow the worked solution below.

Given. Student(Id, Name, Country), Course(CrsCode, CrsName, Type, Instructor), Results(Id, CrsCode, Grade), with Results.Id/CrsCode foreign keys into Student/Course.

(a) Id of students who take TTMG or SYSC courses — join Results to Course on CrsCode, filter by course Type.

SELECT DISTINCT R.Id
FROM   Results R, Course C
WHERE  R.CrsCode = C.CrsCode AND C.Type IN ('TTMG', 'SYSC');

(b) Id of students who take EVERY course — the classic universal-quantification pattern via double negation: no course exists that this student has NOT taken.

SELECT S.Id
FROM   Student S
WHERE  NOT EXISTS (
         SELECT * FROM Course C
         WHERE  NOT EXISTS (
                  SELECT * FROM Results R
                  WHERE  R.Id = S.Id AND R.CrsCode = C.CrsCode));

(c) Id of students who take every SYSC course OR take every TTMG course — the same double-negation pattern, restricted to one course Type at a time, unioned.

SELECT S.Id FROM Student S
WHERE  NOT EXISTS (
         SELECT * FROM Course C WHERE C.Type = 'SYSC'
         AND    NOT EXISTS (SELECT * FROM Results R
                             WHERE R.Id = S.Id AND R.CrsCode = C.CrsCode))
UNION
SELECT S.Id FROM Student S
WHERE  NOT EXISTS (
         SELECT * FROM Course C WHERE C.Type = 'TTMG'
         AND    NOT EXISTS (SELECT * FROM Results R
                             WHERE R.Id = S.Id AND R.CrsCode = C.CrsCode));

(d) Plain-English reading of the relational-algebra query. Working inside-out: $\sigma_{Type='SYSC'}(Course)$ is every SYSC course row; projecting $\pi_{CrsCode}$ of that keeps just their course codes. $\sigma_{Grade='D'}(Result)$ is every (Id, CrsCode) result row where the grade was a D. Natural-joining these two (on the shared CrsCode attribute) keeps only the D-grade result rows whose course is one of the SYSC courses. Natural-joining that against Student (on the shared Id attribute) attaches each surviving row's student Name (and Country). The final $\pi_{Name}$ keeps only the Name column.

In plain English: "the names of students who received a grade of D in some SYSC course."