Databases Reference
In-Depth Information
SELECT SPNAME
FROM SALESPERSON
WHERE SPNUM=361
The result, as you already know from the previous result, is:
SPNAME
Carlyle
As already stated, the preferred method (when possible) is to use a join to
retrieve data. However, this is not always possible. There is a very interesting
circumstance in which a subquery is required. This situation is best explained
with an example. Consider the following query:
Which salespersons with salesperson numbers greater than 200 have the
lowest commission percentage of any such salesperson, with salespersons
identified by salesperson number?
This appears to be a reasonable request, but turns out to be deceptively difficult
to fill. This is because the query really has two very different parts to it. First,
the system has to determine what the lowest commission percentage is for sales-
persons with a salesperson number greater than 200. Second, it has to see which
of these salespersons is assigned 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
Another possibility you might consider is:
SELECT SPNUM FROM SALESPERSON
WHERE SPNUM>200 AND COMMPERCT=MIN(COMMPERCT)
The problem is that neither of these will give you the result you want. 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 accomplish the query, and it involves subqueries. You need to
ask the system to determine the minimum commission percentage first, in a sub-
query, and then use that information in the main query to determine which sales-
persons have that value in the COMMPERCT column:
SELECT SPNUM FROM SALESPERSON
WHERE SPNUM>200 AND COMMPERCT=
(SELECT MIN(COMMPERCT)
FROM SALESPERSON WHERE SPNUM>200)
This gives you the result:
SPNUM
204
