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

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:00-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,SSN, and TotalUnitCompleted of every student.

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

Explanation / Answer

SQL Query for a:

select Name,SSN,TotalUnitCompleted from student;

SQL Query for b:

select S.name from student S join enrollment E
on S.SSN=E.SSN join course C
on E.CourseNumber=C.CourseNumber
where E.grade='A';

SQL Query for c:

select distinct S.name FROM student S join enrollment
join Course C
where C.RoomNumber=9 and S.ssn not in(select ssn from enrollment);

Related Questions

Navigate

• Browse (All)
• Browse G
• Subjects

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