How do I identify all the students who have passed?

Suppose I have a Table student and a Table exam which tracks all the exams a student has taken, how do I find all the students who have passed or not taken any exams similar to a white-list? In this example that means students 1 and 2.

PS: pass flag U means that the status is unknown, active flag N means the exam was removed, rest of the flags are self explanatory.

Just to be very clear, the real use case is actually assessments, I need to figure out people who have no pending assessments to participate in, so that they can proceed with next set of procedures. Others would need to finish the pending items.

+-------------+------+
| student_idn | name |
+-------------+------+
| 1           | Mark |
| 2           | Jack |
| 3           | Jane |
| 4           | Jill |
+-------------+------+

+----------+-------------+------+--------+
| exam_idn | student_idn | pass | active |
+----------+-------------+------+--------+
| 1        | 1           | Y    | Y      |
| 2        | 1           | Y    | Y      |
| 3        | 1           | Y    | Y      |
| 4        | 2           | Y    | Y      |
| 5        | 2           | N    | N      |
| 6        | 3           | Y    | Y      |
| 7        | 3           | Y    | Y      |
| 8        | 3           | Y    | Y      |
| 9        | 3           | N    | Y      |
| 10       | 4           | U    | Y      |
+----------+-------------+------+--------+

I came up with this below query but is there a better way to do this? I feel that this is O(n) or O(nlogn) depending on index etc not sure (anyone knows the best possible for this) hence should be close to optimal?

SELECT
  *
FROM student
WHERE NOT EXISTS (SELECT
  *
FROM exam
WHERE exam.student_idn = student.student_idn
AND pass <> 'Y'
AND active = 'Y')

The problem you describe is known as relational division. There are basically two ways to approach it:

• Transform for all x : p(x) to not exists x : not p(x)

In other words, transform students who have passed all test to students where it does not exist a test that the student did not pass. Your query in the question is an example of this.

This transformation is often referred to as De Morgan's laws for quantifiers. See for example:

https://philosophy.stackexchange.com/questions/28324/proving-de-morgans-laws-for-quantifiers

-
Students who have passed the same amount of tests as there are tests. Something like:

SELECT student_id
 FROM tests
 WHERE passed = 'y' 
 GROUP BY student_id
 HAVING COUNT(1) = (SELECT COUNT(DISTINCT test_id) 
                    FROM tests)

I'm answering from a mobile so I can't see the question while answering, so the details of my answer is likely wrong according to your question, but hopefully you get the idea.

StackExchange Database Administrators Q#153528, answer score: 3