1

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'm having a hard time understanding the question. Could you provide an example output? examples of the tables might be nice, too. for example, why is n.grade <=4 a failure? – James May 23 '18 at 19:17
  • Not clear what "At least at all the courses" means. Do you mean all students that failed any of the same courses that student ID = 1 failed? Or students who failed all of the same courses that student ID = 1 failed? – RDFozz May 23 '18 at 19:21
  • Because if is <= means the student failed the test. For example : If student with id 1 failed at courses with id's : 2,3 . And we have another 2 students who failed at courses with id's 2,3(like student 1) and they might fail at other courses to , then I want to display those 2 students. – Eduard Valentin May 23 '18 at 19:21
  • @RDFozz I mean the ones that failed at all of the same courses that student ID = 1 failed, sorry I might have explained it wrong but english is my second language :( – Eduard Valentin May 23 '18 at 19:23
  • ahh, some sort of failure-predictor? that's harder than it looks! – James May 23 '18 at 19:37
1

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.

  • Hmm, EXCEPT does not work for me :\ , 'm using Oracle SQL – Eduard Valentin May 23 '18 at 19:41
  • 2
    Try MINUS instead of EXCEPT – Lennart May 23 '18 at 19:42
  • Yes, that was it – Eduard Valentin May 23 '18 at 19:42
  • 1
    Thanks @Lennart. - Eduard: EXCEPT is the standard SQL term. I'll update the syntax and question tag to reflect Oracle – Martin Smith May 23 '18 at 19:43
  • I got it, thank you so much for the answer , it really helped me! I will select the person that posted first. – Eduard Valentin May 23 '18 at 19:47
1

If I get it right you want STUDENTS where it does not EXIST a COURSE that the STUDENT passed and STUDENT with id 1 failed:

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
);

I assumed grade > 4 means passed.

  • Thank you so much ! I got it now, I had to use this negation ! – Eduard Valentin May 23 '18 at 19:46
0

This should lump all the students who failed the same class together:

    with failboat as (select s.student_ID, c.course_id, g.Grade, 
    rank() over (partition by s.student_id order by date_of_exam) as failcount
    from STUDENT s
    inner join GRADE  on s.student_ID = g.student_ID
    inner join Course c on g.course_ID = c.course_ID
    where g.grade <=4)

    select f1.*, f2.*
    from failboat f1
    inner join failboat f2 on f1.courseID = f2.CourseID

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.