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.

Lesson 13

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;
Category: Lesson 13

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;
Category: Lesson 13

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;
Category: Lesson 13

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;
Category: Lesson 13

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;
Category: Lesson 13

Load More