SQL Querie Give Tables: STUDENT ( int:student number, varchar(20):name, varchar(

Question

SQL Querie

Give Tables:

STUDENT ( int:student number, varchar(20):name, varchar(20):address, primary key (student number))
INSTRUCTOR ( int:instructor ID, varchar(20):name, varchar(20):address, primary key (instructor ID))
COURSE ( int:CRN, varchar(4):subject code, int:course number, varchar(50):name, int:term, int:instructor ID,
primary key (item ID))
REGISTRATION ( int:student number, int:CRN, int:grade )

Querie:

Find the name of the instructor who gave the highest average grade (for each instructor, compute the average grades they have given, then find the maximum).

Explanation / Answer

SELECT S.Sname FROM Students AS S INNER JOIN Enrollment AS E ON S.Sno = E.Sno GROUP BY S.Sno, S.Sname HAVING COUNT(*) = (SELECT COUNT(*) FROM Courses) SELECT S.Sname FROM Students AS S INNER JOIN Enrollment AS E ON E.Sno = S.Sno GROUP BY S.Sno, S.Sname HAVING COUNT(*) > 3 SELECT C.CName, AVG(E.Grade) AS AvgGrade FROM Courses AS C INNER JOIN Enrollment AS E ON C.CNo = E.CNo GROUP BY C.Cno, C.CName

Related Questions

Navigate

• Browse (All)
• Browse S
• Subjects

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