Example: NOT EXISTS with Correlated Subqueries - Teradata VantageCloud Lake

Select the names of all students who have registered in at least one class offered by each department that offers a course.

SELECT SName, SNo 
FROM student s
WHERE NOT EXISTS
 (SELECT * 
  FROM department d 
  WHERE d.Dept IN 
   (SELECT Dept 
    FROM course) AND NOT EXISTS
     (SELECT * 
      FROM course c, registration r, class cl 
      WHERE c.Dept=d.Dept 
      AND c.CNo=r.CNo 
      AND s.SNo=r.SNo
      AND r.CNo=cl.CNo 
      AND r.Sec=cl.Sec)));

With the contents of the tables as in “Example: EXISTS with Correlated Subqueries”, the following rows are returned:

SName          SNo
-----          ---
Helen Chu      1