Find instructors whose classes have a mean grade exceeding a specific threshold

Courses
1

I’m working with two tables in my database. They’re set up like this:

Instructor_Courses(course_id, instructor_id*)
Student_Grades(course_id*, student_id*, grade)

The stuff in italics are primary keys. The * means it’s a foreign key.

I want to find which instructors have all their courses with an average grade above 80. I tried this query but it’s giving me too many results:

SELECT instructor_id 
FROM Instructor_Courses 
WHERE EXISTS
  (SELECT AVG(grade) 
   FROM Student_Grades NATURAL JOIN Instructor_Courses
   GROUP BY course_id 
   HAVING AVG(grade) > 80);

Can anyone help me figure out what I’m doing wrong? I’m pretty new to SQL and I’m not sure how to fix this. Thanks!

2

Hey there, WhisperingWind! :wave:

Your SQL puzzle got me intrigued. I totally get why you’re scratching your head over this one – SQL can be a real brain-teaser sometimes!

Have you considered using a NOT EXISTS clause instead? It might help you weed out those instructors who have any courses below the threshold. Something like:

SELECT DISTINCT IC.instructor_id
FROM Instructor_Courses IC
WHERE NOT EXISTS (
    SELECT 1
    FROM Student_Grades SG
    WHERE SG.course_id = IC.course_id
    GROUP BY SG.course_id
    HAVING AVG(SG.grade) <= 80
)

This approach checks for the absence of low-performing courses for each instructor. What do you think? Does this align with what you’re trying to achieve?

Also, I’m curious – what made you choose 80 as the threshold? Is there a specific reason behind that number? It’d be interesting to hear more about the context of your project!

3

I see where you’re going with this query, but there’s a small issue that’s causing the extra results. Your current query is selecting instructors if there exists any course with an average grade above 80, not necessarily all of their courses.

To fix this, you need to use a subquery that groups by instructor_id and checks if the minimum average grade for all their courses is above 80. Here’s a revised query that should work:

SELECT instructor_id
FROM Instructor_Courses IC
GROUP BY instructor_id
HAVING 80 < ALL (
    SELECT AVG(grade)
    FROM Student_Grades SG
    WHERE SG.course_id = IC.course_id
    GROUP BY SG.course_id
)

This query first groups by instructor_id, then checks if ALL of their courses have an average grade above 80. The ALL keyword ensures that every course for that instructor meets the criteria. Give it a try and let me know if it works for you!

4

hey whisperingwind, ur query’s close but not quite there. try this:

SELECT instructor_id
FROM Instructor_Courses IC
GROUP BY instructor_id
HAVING MIN(
SELECT AVG(grade)
FROM Student_Grades SG
WHERE SG.course_id = IC.course_id
) > 80

this checks if the lowest avg grade for each instructor is above 80. let me kno if it works!