Example: EXISTS with Correlated Subqueries - Teradata Vantage

Select all student names who have registered in at least one class offered by a department.

   SELECT SName, SNo 
   FROM student s 
   WHERE EXISTS 
    (SELECT * 
     FROM department d 
     WHERE 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));

The content of the student table is as follows.

The content of the department table is as follows.

The content of the course table is as follows.

The content of the class table is as follows.

The content of the registration table is as follows.

The following rows are returned:

SName              SNo
-----------        ---
Helen Chu1           *
Alice Clark          2
Kathy Kim            3