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

Courses
1

I’m working with two tables in my database. The first is Subjects (subject_id, instructor_id*) and the second is Grade_Records (subject_id*, student_id*, grade). One instructor can teach multiple subjects. Each subject can have many grade records.

The italic fields are primary keys. Fields with * are foreign keys. For example, student_id* is a foreign key for the student.

I’m trying to find the instructor_id for instructors whose subjects all have an average grade above 80. I tried this query:

SELECT instructor_id FROM subjects WHERE EXISTS
 (SELECT AVG(grade) FROM grade_records NATURAL JOIN subjects
 GROUP BY subject_id HAVING AVG(grade) > 80);

But it’s not working right. It’s giving me too many results. Any ideas on how to fix this? I’m pretty new to SQL and could use some help figuring out the right approach. Thanks!

2

Hey there Nate_45Guitar! :guitar:

I can see why you’re struggling with this query - it’s a tricky one! Have you considered using a Common Table Expression (CTE) to simplify things? Here’s an idea:

WITH high_grade_subjects AS (
  SELECT subject_id
  FROM grade_records
  GROUP BY subject_id
  HAVING AVG(grade) > 80
)
SELECT DISTINCT instructor_id
FROM subjects
WHERE instructor_id NOT IN (
  SELECT instructor_id
  FROM subjects
  WHERE subject_id NOT IN (SELECT subject_id FROM high_grade_subjects)
);

This approach first identifies all the subjects with an average grade above 80, then selects instructors who don’t teach any subjects below that threshold. It’s a bit more straightforward than some of the other solutions.

What do you think about this method? Have you tried anything similar? I’m curious to hear if this works better for your dataset.

Also, how large is your database? Sometimes query performance can vary depending on the size of the data you’re working with. Let me know if you need any more help or want to discuss this further!

3

Your approach is on the right track, but there’s a more efficient way to solve this. Here’s a query that should work:

WITH high_grade_subjects AS (
  SELECT subject_id
  FROM grade_records
  GROUP BY subject_id
  HAVING AVG(grade) > 80
)
SELECT DISTINCT s.instructor_id
FROM subjects s
WHERE NOT EXISTS (
  SELECT 1
  FROM subjects s2
  WHERE s2.instructor_id = s.instructor_id
    AND s2.subject_id NOT IN (SELECT subject_id FROM high_grade_subjects)
);

This query first identifies all subjects with an average grade above 80, then selects instructors who don’t have any subjects below this threshold. It’s more efficient and accurate than your original approach. Remember to test it with your actual data to ensure it works as expected in your specific database environment.

4

hey mate, try:

WITH hgs AS (
  SELECT subject_id FROM grade_records GROUP BY subject_id HAVING AVG(grade)>80
)
SELECT s.instructor_id FROM subjects s
WHERE s.subject_id IN (SELECT subject_id FROM hgs)
  AND s.instructor_id NOT IN (
    SELECT s2.instructor_id FROM subjects s2
    WHERE s2.subject_id NOT IN (SELECT subject_id FROM hgs)
  );

thx.