Let’s make the previous challenge more useful. In addition to returning the customer name and order number, add a third column named OrderTotal containing the total price of each order. There are two ways to do this, you can create the OrderTotal column using a subquery on the OrderItems table, or you can join the OrderItems table to the existing tables and use an aggregate function. Here’s a hint, watch out for where you need to use fully qualified column names.
-- Solution using subqueries SELECT cust_name, order_num, (SELECT Sum(item_price*quantity) FROM OrderItems WHERE Orders.order_num=OrderItems.order_num) AS OrderTotal FROM Customers, Orders WHERE Customers.cust_id = Orders.cust_id ORDER BY cust_name, order_num; -- Solution using joins SELECT cust_name, Orders.order_num, Sum(item_price*quantity) AS OrderTotal FROM Customers, Orders, OrderItems WHERE Customers.cust_id = Orders.cust_id AND Orders.order_num = OrderItems.order_num GROUP BY cust_name, Orders.order_num ORDER BY cust_name, order_num;