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

WHERE SPNUM > 200
AND COMMPERCT=10;
which yields the result of salesperson number 204, as shown.
Actually, this is a very interesting 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 commission 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. No memory
is 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);
you would find every salesperson with any salesperson number whose commission
percentage is equal to the lowest commission percentage of the salespersons with
salesperson numbers greater than 20. (Of course, if for some reason you do want
to find all of the salespersons, regardless of 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 20, then this
last SELECT statement is exactly what you should write!)
A Strategy for Writing SQL SELECT Commands
Before we go on to some more examples, it will be helpful to think about developing
a strategy for writing SQL SELECT statements. The following is an ordered list of
steps.
1. Determine what the result of the query is to be and write the needed attributes
and functions in the SELECT clause. This may seem an obvious instruction, but
it will really pay to think this through carefully before going on. In fact, it is
at this very first step that you must determine whether the query will require a
GROUP BY clause or a subquery. If either of these is required, you should start
outlining the overall SELECT statement by writing the GROUP BY clause or
the nested SELECT for the subquery further down the page (or screen).
2. Determine which tables of the database will be needed for the query and write
their names in the FROM clause. Include only those tables that are really
necessary for the query. Sometime this can be tricky. For example, you might
need an attribute that is the primary key of a table and you might be tempted
immediately to include that table in the FROM clause. However, it could be that
the attribute in question is a foreign key in another table that is already in the
FROM clause for other reasons. It is then unnecessary to include the table in
which it is the primary key unless, of course, other attributes from that table are
needed, too.