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: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 has no grade in at least two of her/his courses.

b: List the name and SSN of every student who has completed the highest total number of units

Explanation / Answer

a: List the SSN and the name of every student who has no grade in at least two of her/his courses.

In the above my sub query find where grade is null then it counts the number of SSN and find count(ssn)>=2 and return that ssn number to the parent query which is used to display the SSN and name of those student from student table because name is in the student table.

Count >= 2 and grade is null is used for student who has no grade in at least two of her/his courses.

Answer)

select ssn,name from student where ssn in (select ssn from enrollment where grade is null group by ssn having count(*) =2);

b: List the name and SSN of every student who has completed the highest total number of units.

Here I have used subquery to find the maximum of totalunitcompleted and then return the value to the main query to find the name and SSN.

Answer)

select name,ssn from student where totalunitcompleted=(select max(totalunitcompleted) from student);

Still if you have any doubt then write me on comments.

Related Questions

Navigate

• Browse (All)
• Browse G
• Subjects

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