NESTED QUERIES

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

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>