Database Reference
In-Depth Information
Now, we can use an SQL query with two subqueries to obtain the desired data as follows:
/* *** SQL-Query-CH02-49 *** */
SELECT Buyer
FROM
SKU_DATA
WHERE
SKU IN
(SELECT SKU
FROM
ORDER_ITEM
WHERE
OrderNumber IN
(SELECT
OrderNumber
FROM
RETAIL_ORDER
WHERE
OrderMonth='January'
AND
OrderYear=2013));
The result of this statement is:
To understand this statement, work from the bottom up. The bottom SELECT statement
obtains the list of OrderNumbers of orders sold in January 2013. The middle SELECT state-
ment obtains the SKU values for items sold in orders in January 2013. Finally, the top-level
SELECT query obtains Buyer for all of the SKUs found in the middle SELECT statement.
Any parts of the SQL language that you learned earlier in this chapter can be applied to a
table generated by a subquery, regardless of how complicated the SQL looks. For example, we
can apply the DISTINCT keyword on the results to eliminate duplicate rows. Or we can apply
the GROUP BY and ORDER BY clauses as follows:
/* *** SQL-Query-CH02-50 *** */
SELECT
Buyer, COUNT(*) AS NumberSold
FROM
SKU_DATA
WHERE
SKU IN
(SELECT SKU
FROM
ORDER_ITEM
WHERE
OrderNumber IN
(SELECT
OrderNumber
FROM
RETAIL_ORDER
WHERE
OrderMonth='January'
AND
OrderYear=2013))
GROUP BY Buyer
ORDER BY NumberSold DESC;
The result is:
Search WWH ::
Custom Search