select ... not exists not exists query

Question

select r.index, sum(c.points)
from records r join exams e2 on r.index = e2.index
    join courses c on c.id_course = e2.id_course
where not exists ( select *
                    from required_courses rs
                    where rs.id_studie = r.id_studie
                        and not exists (select *
                                        from exams e
                                        where e.id_course = rs.id_course
                                            and r.index = e.index
                                            and score>5))
    and score>5
group by index;

I have that query. I know what it does, but don't know how.

I have relativly big database with 16 tables, but only use 4 in this query.

Used tables are:

1. RECORDS (of students) [index (pr. key), name, surname, ..., id_studie(1)(for. key)]
2. EXAMS [index(of students) (p.k.), id_course(p.k.),..., score(2), application_status]
3. COURSES [id_course(p.k.), ..., points]
4. REQUIRED_COURSES [id_studie (f.k.), id_course(f.k), ...]

(1) I don't know better word in English. When on some faculty there are Informatics, Math, Physics, etc. classes. Informatics is one studie. (2) Scores go from 5 to 10. 10 is best. At least 6 is required for passing.

Query: - Find all students that passed all required exams on studie thay studie. Print indexes and number of points.

My question: Can sameone explain how does this work in simple words?

I don't understand not exists & not exists part.

Sorry for my bad english.

Answer 1

The example is a double-nested NOT EXISTS query.

If a subquery returns any rows at all, EXISTS subquery is TRUE, and NOT EXISTS subquery is FALSE.

That is, it has a NOT EXISTS clause within a NOT EXISTS clause.

It is easier to say that a nested NOT EXISTS answers the question “is x TRUE for all y?”

--Part 3
SELECT r.index, 
       SUM(c.points) 
FROM   records r 
       join exams e2 
         ON r.index = e2.index 
       join courses c 
         ON c.id_course = e2.id_course 
WHERE  NOT EXISTS (
--Part 2 starts
                   SELECT * 
                   FROM   required_courses rs 
                   WHERE  rs.id_studie = r.id_studie 
                          AND NOT EXISTS (
--Part 1 starts                                         
                                          SELECT * 
                                          FROM   exams e 
                                          WHERE  e.id_course = rs.id_course 
                                                 AND r.index = e.index 
                                                 AND score > 5
--Part 1 ends
                                                              )
--Part 2 ends                                         
                                                               )
       AND score > 5 
GROUP  BY index; 
--Part 3 ends                                         

Part 1: Fetch all records of students from EXAMS table who scored more than 5. You should get all pass mark students for all courses here

Part 2: Join Part1 results with REQUIRED_COURSES on student id and fetch all the required courses where the student has not cleared the exams (where student does not have a score more than 5). You should have all the student's courses name where they are not successful

Part 3: Join Part 2 results with RECORDS table on index and also COURSES table on course id to fetch the index and sum of points. You can see this part into two pieces. First is the normal JOIN with two tables and then the NOT EXISTS with the part 2. When you apply a NOT EXISTS on part 2, you are going to get all successful student ids which would provide you the successful entries by adding another SCORE > 5 condition at the end.

Answer 2

Always analyze the query from the inside out.

Innermost query: Select the exams with score higher than 5

Middle one:

Select the required courses that don't have an exam with score higher than 5

Outer one:

Select students that don't have a required course that doesn't have the exam with score higher than 5