Sams Teach Yourself SQL in 10 Minutes (Fifth Edition) includes challenges at the end of some lessons. Solutions to the challenges are presented here. Just keep in mind that there is rarely one solution to a SQL challenge, so if your solutions look different but produce the desired result, that’s ok.
Write a SQL statement using an INNER JOIN to retrieve customer name (cust_name in Customers) and all order numbers (order_num in Orders) for each.
SELECT cust_name, order_num FROM Customers JOIN Orders ON Customers.cust_id = Orders.cust_id ORDER BY cust_name;
Modify the SQL statement you just created to list all customers, even those with no orders.
SELECT cust_name, order_num FROM Customers LEFT OUTER JOIN Orders ON Customers.cust_id = Orders.cust_id ORDER BY cust_name;
Use an OUTER JOIN to join the Products and OrderItems tables, returning a sorted list of product names (prod_name) and the order numbers (order_num) associated with each.
SELECT prod_name, order_num FROM Products LEFT OUTER JOIN OrderItems ON Products.prod_id = OrderItems.prod_id ORDER BY prod_name;
Modify the SQL statement created in the previous challenge so that it returns a total of number of orders for each item (as opposed to the order numbers).
SELECT prod_name, COUNT(order_num) AS orders FROM Products LEFT OUTER JOIN OrderItems ON Products.prod_id = OrderItems.prod_id GROUP BY prod_name ORDER BY prod_name;
Write a SQL statement to list vendors (vend_id in Vendors) and the number of products they have available, including vendors with no products. You’ll want to use an OUTER JOIN and the COUNT() aggregate function to count the number of products for each in the Products table. Pay attention, the vend_id column appears in multiple tables so any time you refer to it you’ll need to fully qualify it.
SELECT Vendors.vend_id, COUNT(prod_id) FROM Vendors LEFT OUTER JOIN Products ON Vendors.vend_id = Products.vend_id GROUP BY Vendors.vend_id;