How does it work internally

Question

I have the following tables:

STUDENT (student_id, first_name, last_name, birth_date, year , domain)
PROFESSOR (professor_id, first_name, last_name, birth_date, hire_date, title, salary) 
COURSE (course_id, course_name, professor_id)
GRADE (student_id, course_id, grade, date_of_exam)

I have to display the students that failed at least at all the courses that student with id = 1 failed.

What I tried:

SELECT s.student_id,
       s.first_name,
       s.last_name,
       n.grade,
       n.course_id
FROM  student s
JOIN grade n ON n.student_id = s.student_id
WHERE n.grade <= 4;

This gets all the students that failed but I don't know how to go from this to all the students that failed at least at all the courses that student with id = 1 failed. If someone could point me in the right direction, I would be grateful!

Additional details

For example: If student with id = 1 failed at courses with ids = 2,3. And we have other two students who failed at courses with ids = 2,3 (like student 1) and they might fail at other courses too, then I want to display those two students.

I want to understand the solution from this answer on the site:

SELECT student_id
    FROM student s
    WHERE student_id <> 1
      AND NOT EXISTS (
        SELECT 1 
        FROM GRADE g1
        JOIN GRADE g2
            ON g1.course_id = g2.course_id
        WHERE g1.student_id = 1
          AND g1.grade <= 4
          AND g2.student_id = s.student_id
          AND g2.grade > 4
    );

Paul White · Answer 1 · 2023-03-11T14:54:54.057

The first part of the query is easy to understand. It produces the set of all students except the one with student_id = 1.

SELECT 
    student_id
FROM student s
WHERE 
    student_id <> 1

The tricky part is the NOT EXISTS clause:

AND NOT EXISTS 
(
    SELECT 1 
    FROM GRADE g1
    JOIN GRADE g2
        ON g1.course_id = g2.course_id
    WHERE 
        g1.student_id = 1
        AND g1.grade <= 4
        AND g2.student_id = s.student_id
        AND g2.grade > 4
);

To understand it, first look at just the parts that relate to table GRADE aliased as g1:

SELECT 1 
FROM GRADE g1
WHERE 
    g1.student_id = 1
    AND g1.grade <= 4

That identifies the failing grade entries for student #1.

There is then a join to GRADE aliased as g2:

JOIN GRADE g2
    ON g1.course_id = g2.course_id
WHERE 
    g2.student_id = s.student_id
    AND g2.grade > 4

The join clause ensures we match the same course. The grade > 4 clause identifies courses the student did not fail.

The remaining predicate is g2.student_id = s.student_id. Notice the alias name s there. This is a correlation, so the student id is provided from the outer query (the first part of the query we split up).

The crucial part of the logic is there should be no grade rows (NOT EXISTS) with a passing grade for any course student #1 failed, considering only those students who are not student #1 (as provided by the outer part of the query).

That matches your requirement:

the students that failed at least at all the courses that student with id = 1 failed.

Though it will also include students that haven't taken a course that student #1 failed. You might want to think about how you would fix the query to address that issue (db<>fiddle online demo).

The trick with these problems is to specify all the conditions very precisely. If the database will only be queried when all students have taken all the same courses, the query is fine. If not, you need to think about and address the edge cases.

You may find it helps to think of the NOT EXISTS subquery being executed once for each row returned by the outer query with the current value of student_id being substituted for s.student_id on each iteration. The database is not forced to evaluate it that way, but the result must be the same as if it did.

It is possible to write the same logical query using different syntax. Some people find correlated subqueries more natural and easier to comprehend than others.

Related Q & A: What is the name of this type of query, and what is an efficient example?

How does it work internally

1 Answers1