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

Question

Given the following relational database schema:

Student = (SSN, Name, Major)

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 SSN and the name of every student who does not have a grade in any course, i.e all her or his grades are null.

b: List the name of every student who hasn’t taken any course.

Explanation / Answer

a. for this result, we must join the two relations: Student and Enrollment as the SSN and name of the student is taken from student on condition of them having NULL in their Grade value that is taken from the Enrollment. The joining parameter will be SSN as it is the common attribute in both tables. S and E are the aliases for the two tables.

Ans: SELECT S.SSN, S.Name

        FROM Student S, Enrollment E

        WHERE S.SSN = E.SSN

        AND E.Grade IS NULL;

b) In this query also we join the two tables Student and Enrollment and join them on the basis of SSN. But this time the condition will be that coursenumber is NULL:

SELECT S.Name

FROM Student S, Enrollment E

WHERE S.SSN = E.SSN

AND E.CourseNumber IS NULL;

Related Questions

Navigate

• Browse (All)
• Browse G
• Subjects

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