Oracle Relational Division – Selecting Students Who Failed All Courses Failed by Student with ID 1

join;minusoraclerelational-division

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.

Best Answer

Here is one way of getting your desired results

SELECT *
FROM   STUDENT S
WHERE  NOT EXISTS (SELECT g1.course_id
                   FROM   GRADE g1
                   WHERE  g1.student_id = 1
                          AND g1.grade <= 4
                   MINUS
                   SELECT g2.course_id
                   FROM   GRADE g2
                   WHERE  g2.student_id = S.student_id
                          AND g2.grade <= 4)

For each student it checks with MINUS whether there are any courses that student_id = 1 has failed that are not in the set of failed courses for the student under focus.

If there are no such courses then the NOT EXISTS evaluates to true and the student is returned.

student_id = 1 will be returned as they have obviously failed at least as many courses as themselves so you may want to filter that out.

Related Question