Databases Reference
In-Depth Information
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
WHERE SPNUM>200 AND COMMPERCT=10
This yields the result of salesperson number 204. Actually, this is a very inter-
esting example of a required subquery. What makes it really interesting is why
the predicate, SPNUM
200, appears in both the main query and the subquery.
Clearly it has to be in the subquery because you must first find the lowest com-
mission percentage among the salespersons with salesperson numbers greater
than 200. But then why does it have to be in the main query, too? The answer
is that the only thing that the subquery returns to the main query is a single
number, specifically a commission percentage. There is no memory passed on
to the main query of how the subquery arrived at that value. If you remove
SPNUM
200 from the main query so that it now looks like:
SELECT SPNUM FROM SALESPERSON
WHERE COMMPERCT=
(SELECT MIN(COMMPERCT) FROM SALESPERSON)
WHERE SPNUM>200)
this query returns every salesperson (without regard for salesperson number)
whose commission percentage is equal to the lowest commission percentage of the
salespersons with salesperson numbers greater than 200. Of course, if for some
reason you do want to find all of the salespersons, regardless of their salesperson
number, who have the same commission percentage as the salesperson who has
the lowest commission percentage of the salespersons with salesperson numbers
greater than 200, then this last SELECT statement is exactly what you should write.
SELF-CHECK
•
Compare and contrast joins and subqueries.
•
Explain why it is typically preferred to use a join instead of a
subquery when either will work.
•
Describe the three basic join types.
7.3 Using Batches and Scripts
You can probably already think of several situations where you will need to peri-
odically run the same statements exactly the same way. One way of automating
