Queries can be nested up to six levels. For example, retrieve all supplier names -- for suppliers who supply at least one red part -- can be written as the following two level nested query:
SELECT sname
FROM supplier
WHERE sno IN
(SELECT sno
FROM partsupp
WHERE pno IN
(SELECT pno
FROM part
WHERE color = "RED"))
Note that the parentheses around the subqueries are optional. The system first executes the lowest level query to find the part numbers of red parts. The next higher level query is then executed, which finds the supplier numbers for those red parts. Finally, the outer level query (or main query) retrieves the names of the red part suppliers.