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

Does Not Work with
Microsoft access
aNSI-89 SQL

This query fails in Microsoft Access ANSI-89
SQL for the same reason previously described
on page 72.

Solution: See the solution described in the “Does Not Work with Microsoft Access
ANSI-89 SQL” box on page 72. The correct Microsoft Access ANSI-89 SQL statement
for this query is:

/* *** SQL-Query-CH02-55-Access *** */
SELECT

Buyer, Sum(ORDER_ITEM.ExtendedPrice) AS BuyerRevenue

FROM SKU_DATA, ORDER_ITEM

WHERE SKU_DATA.SKU=ORDER_ITEM.SKU

GROUP BY Buyer

ORDER BY Sum(ExtendedPrice) DESC;

We can extend this syntax to join three or more tables. For example, suppose we want to ob-
tain the Buyer and the ExtendedPrice and OrderMonth for all purchases of items managed by each
buyer. To retrieve that data, we need to join all three tables together, as shown in this SQL query:

/* *** SQL-Query-CH02-56 *** */
SELECT Buyer, ExtendedPrice, OrderMonth
FROM SKU_DATA, ORDER_ITEM, RETAIL_ORDER
WHERE SKU_DATA.SKU=ORDER_ITEM.SKU
AND ORDER_ITEM.OrderNumber=RETAIL_ORDER.OrderNumber;
The result is:

We can improve this result by sorting with the ORDER BY clause and grouping by Buyer
with the GROUP BY clause:

/* *** SQL-Query-CH02-57 *** */
SELECT Buyer, OrderMonth, SUM(ExtendedPrice) AS BuyerRevenue
FROM SKU_DATA, ORDER_ITEM, RETAIL_ORDER
WHERE SKU_DATA.SKU=ORDER_ITEM.SKU
AND ORDER_ITEM.OrderNumber=RETAIL_ORDER.OrderNumber
GROUP BY Buyer, OrderMonth
ORDER BY Buyer, OrderMonth DESC;
The result is: