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)

Express the following queries using SQL statements with minimum number of tables and operations.

a. List the SSN and Name of every student who has no grade in at least one course.

b. List the name of every student who enrolled in at least two courses.

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

d. 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

e. List the SSN and Name of all students who do not have the grade A in any course.

Answer 1

Answer)
a.

select SSN, Name from Student where SSN in (
select distinct SSN from Enrollment where Grade is null
);

b.

select Name from Student inner join Enrollment on Student.SSN = Enrollment.SSN
group by Student.SSN having count(Enrollment.CourseNumber) > 1;

c.

select Name from Student where SSN not in (
select distinct SSN from Enrollment
);

d.

select SSN, Name from Student where SSN in (
select distinct SSN from Enrollment where Grade is null
);

e.

select SSN, Name from Student where SSN not in (
select distinct SSN from Enrollment where Grade = 'A'
);

**Please Hit Like if you appreciate my answer. For further doubts on the question or answer please drop a comment, I'll be happy to help. Thanks for posting.**