Introduction to Structured Query Language - Database Processing: Fundamentals, Design, and Implementation - page 78

For the data in Figure 2-5, the SKU values for items in Water Sports are 100100, 100200,
101100, and 101200. Knowing those values, we can obtain the sum of their ExtendedPrice with
the following SQL query:

/* *** SQL-Query-CH02-46 *** */
SELECT SUM(ExtendedPrice) AS Revenue

FROM ORDER_ITEM

WHERE SKU IN (100100, 100200, 101100, 101200);

The result is:

But, in general, we do not know the necessary SKU values ahead of time. However, we do
have a way to obtain them from an SQL query on the data in the SKU_DATA table. To obtain
the SKU values for the Water Sports department, we use the SQL statement:

/* *** SQL-Query-CH02-47 *** */
SELECT SKU
FROM SKU_DATA

WHERE Department='Water Sports'

The result of this SQL statement is:

which is, indeed, the desired list of SKU values.
Now we need only combine the last two SQL statements to obtain the result we want.
We replace the list of values in the WHERE clause of the first SQL query with the second SQL
statement as follows:

/* *** SQL-Query-CH02-48 *** */
SELECT SUM(ExtendedPrice) AS Revenue

FROM ORDER_ITEM

WHERE SKU IN

(SELECT SKU

FROM SKU_DATA

WHERE Department='Water Sports');

The result of the query is:

which is the same result we obtained before when we knew which values of SKU to use.
In the preceding SQL query, the second SELECT statement, the one enclosed in parenthe-
ses, is called a subquery . We can use multiple subqueries to process three or even more tables.
For example, suppose we want to know the names of the buyers who manage any product
purchased in January 2013. First, note that Buyer data is stored in the SKU_DATA table and
OrderMonth and OrderYear data are stored in the RETAIL_ORDER table.