RELATIONAL DATA RETRIEVAL: SQL - Fundamentals of Database Management Systems - page 88

When a Subquery is Required There is a very interesting circumstance in which a
subquery is required . This situation is best explained with an example up front.
Consider the following query:
''Which salespersons with salesperson numbers greater than 200 have the lowest
commission percentage?'' (We'll identify salespersons by their salesperson
number.)
This seems like a perfectly reasonable request, and yet it turns out to be deceptively
difficult. The reason is that the query really has two very different parts. First, the
system has to determine what the lowest commission percentage is for salespersons
with salesperson numbers greater than 200. Then, it has to see which of these
salespersons has that lowest percentage. It's really tempting to try to satisfy this
type of query with an SQL SELECT statement like:
SELECT SPNUM, MIN(COMMPERCT)
FROM SALESPERSON
WHERE SPNUM > 200;
or, perhaps:
SELECT SPNUM
FROM SALESPERSON
WHERE SPNUM > 200
AND COMMPERCT=MIN(COMMPERCT);
But these will not work ! It's like asking SQL to perform two separate operations
and somehow apply one to the other in the correct sequence. This turns out to be
asking too much. But there is a way to do it and it involves subqueries. In fact, what
we will do is ask the system to determine the minimum commission percentage
first , in a subquery, and then use that information in the main query to determine
which salespersons have it:
SELECT SPNUM
FROM SALESPERSON
WHERE SPNUM > 200
AND COMMPERCT=
(SELECT MIN(COMMPERCT)
FROM SALESPERSON)
WHERE SPNUM > 200);
which results in:
SPNUM
204
The minimum commission percentage across all of the salespersons with
salesperson numbers greater than 200 is determined first in the subquery and the
result is 10. The main query then, in effect, looks like:
SELECT SPNUM
FROM SALESPERSON