Select all student names who have registered in at least one class offered by some 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.
| Sname | SNo |
|---|---|
| Helen Chu | 1 |
| Alice Clark | 2 |
| Kathy Kim | 3 |
| Tom Brown | 4 |
The content of the department table is as follows.
| Dept | DeptName |
|---|---|
| 100 | Computer Science |
| 200 | Physic |
| 300 | Math |
| 400 | Science |
The content of course table is as follows.
| CNo | Dept |
|---|---|
| 10 | 100 |
| 11 | 100 |
| 12 | 200 |
| 13 | 200 |
| 14 | 300 |
The content of the class table is as follows.
| CNo | Sec |
|---|---|
| 10 | 1 |
| 11 | 1 |
| 12 | 1 |
| 13 | 1 |
| 14 | 1 |
The content of the registration table is as follows.
| CNo | SNo | Sec |
|---|---|---|
| 10 | 1 | 1 |
| 10 | 2 | 1 |
| 11 | 3 | 1 |
| 12 | 1 | 1 |
| 13 | 2 | 1 |
| 14 | 1 | 1 |
The following rows are returned:
SName SNo ----------- --- Helen Chu1 * Alice Clark 2 Kathy Kim 3