One more, and to make things more fun we’ll mix joins, aggregates functions, and grouping, too. Ready? Back in Lesson 10 I issued you a Challenge to find all order numbers with a value of 1000 or more. Those results are useful, but what would be even more useful is the name of the customers who placed orders of at least that amount. So, write a SQL statement that uses joins to return customer name (cust_name) from the Customers table, and the total price of all orders from the OrderItems table. Here’s a hint, to join those tables you’ll also need to include the Orders table (as Customers is not related directly to OrderItems, Customers is related to Orders and Orders is related to OrderItems). Don’t forget the GROUP BY and HAVING, and sort the results by customer name. You can use simple equijoin or ANSI INNER JOIN syntax for this one. Or, if you are feeling brave, try writing it both ways.


-- Equijoin syntax
SELECT cust_name, SUM(item_price*quantity) AS total_price
FROM Customers, Orders, OrderItems
WHERE Customers.cust_id = Orders.cust_id
 AND Orders.order_num = OrderItems.order_num
GROUP BY cust_name HAVING SUM(item_price*quantity) >= 1000
ORDER BY cust_name;

-- ANSI INNER JOIN syntax
SELECT cust_name, SUM(item_price*quantity) AS total_price
FROM Customers 
 INNER JOIN Orders ON Customers.cust_id = Orders.cust_id 
 INNER JOIN OrderItems ON Orders.order_num = OrderItems.order_num
GROUP BY cust_name
HAVING SUM(item_price*quantity) >= 1000
ORDER BY cust_name;

Comment on this FAQ