Given the following relational database schema: Student = (SSN, Name, Major, Tot

ID: 3711657 • Letter: G

Question

Given the following relational database schema:

Student = (SSN, Name, Major, TotalUnitCompleted)

Course = ( CourseNumber , CourseTitle, NumberOfUnits, RoomNumber, DayTime), where DayTime is of the form MW 1:0-2:00.

Enrollment = (SSN , CourseNumber, Grade)

Use SQL to express the following queries using a minimal number of tables and operations.

a: List the name of every student who did not earn an ‘A’ in any course

b: List the name of every student who has earned the grade ’A’ in every course he or she has taken

c: List the name of every student who is not enrolled in any course that meets in RoomNumber =9

a) SELECT s.Name FROM Enrollment e JOIN Student s ON e.SSN = s.SSN WHERE e.Grade != ‘A’

EXCEPT

SELECT s.NAME FROM Enrollment e JOIN Student s ON e.SSN = s.SSN WHERE e.Grade = ‘A’

b) SELECT s.Name FROM Enrollment e JOIN Student s ON e.SSN = s.SSN WHERE e.Grade = ‘A’ EXCEPT

SELECT s.Name FROM Enrollment e JOIN Student s ON e.SSN = s.SSN WHERE e.Grade != ‘A’

c) SELECT NAME FROM Student WHERE SSN IN(SELECT SSN FROM Enrollment WHERE CourseNumber NOT IN(SELECT CourseNumber FROM Course WHERE RoomNumber = 9))

EXCEPT

SELECT NAME FROM Student WHERE SSN IN(SELECT SSN FROM Enrollment WHERE CourseNumber IN(SELECT CourseNumber FROM Course WHERE RoomNumber = 9))

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.