MySQL Querie Given TABLES: STUDENT ( int:student number, varchar(20):name, varch

Question

MySQL Querie

Given 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 (CRN))
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 c.name
from (
select max(a.avg_grade) max_grade
from (
select i.name, AVG(r.grade) avg_grade
from instructor i, course c, registration r
where i.instructor_ID = c.instructor_ID
and c.CRN = r.CRN
group by i.name
) a
) b,
(
select i.name, AVG(r.grade) avg_grade
from instructor i, course c, registration r
where i.instructor_ID = c.instructor_ID
and c.CRN = r.CRN
group by i.name
) c
where b.max_grade = c.avg_grade

Related Questions

Navigate

• Browse (All)
• Browse M
• Subjects

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