SQL allows the nesting of one query inside another, but only in the WHERE and the HAVING clauses.  In addition, SQL permits a subquery only on the right hand side of an operator.

Example 1

Find the names and IDs of all faculty members who teach a class in room ‘H221’. You could do this with 2 queries as follows:
SELECT FACID FROM CLASS WHERE ROOM = ‘H221’;
—> RESULT:  F101, F102

SELECT FACNAME, FACID FROM FACULTY WHERE FACID IN (F101, F102);

or you could combine the 2 into a nested query:
SELECT FACNAME, FACID FROM FACULTY WHERE FACID IN (SELECT FACID FROM CLASS WHERE ROOM = ‘H221’);

Note that the nested SELECT is executed first and its results are used as the argument to the outer SELECTs IN clause.

FACNAME	FACID
Adams	F101
Smith	F202

Example 2

Retrieve an alphabetical list of last names and IDs of all students in any class taught by faculty number ‘F110’.
SELECT LNAME, STUID FROM STUDENT  WHERE STUID IN (SELECT STUID FROM ENROLL WHERE COURSENUM IN (SELECT COURSENUM FROM CLASS WHERE FACID = ‘F110’)) ORDER BY LNAME;

LNAME	STUID
Burns	S1010
Chin	S1002
Rivera	S1020

The most deeply nested SELECT is done first.  Thus, after the first select you have:
SELECT LNAME, STUID FROM STUDENT WHERE STUID IN (SELECT STUID FROM ENROLL WHERE COURSENUM IN (‘MTH101B’,’MTH103C’)) ORDER BY LNAME;

Next, the next most deeply is done.
SELECT LNAME, STUID FROM STUDENT WHERE STUID IN (‘S1020′,’S1010′,’S1002’) ORDER BY LNAME;

Finally, the outer Select is executed giving the result printed above.

Example 3

Find the name and IDs of students who have less than the average number of credits.
SELECT LNAME, STUID FROM STUDENT WHERE CREDITS < (SELECT AVG(CREDITS) FROM STUDENT);

LNAME	STUID
Chin	S1002
Rivera	S1020
McCarthy	S1013