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 14

Write a SQL statement that combines two SELECT statements that retrieve product id (prod_id) and quantity from the OrderItems table, one filtering for rows with a quantity of exactly 100, and the other filtering for products with an ID that begins with BNBG. Sort the results by product id.


SELECT prod_id, quantity FROM OrderItems
WHERE quantity = 100
UNION
SELECT prod_id, quantity FROM OrderItems
WHERE prod_id LIKE 'BNBG%'
ORDER BY prod_id;
Category: Lesson 14

Rewrite the SQL statement you just created to use a single SELECT statement.


SELECT prod_id, quantity FROM OrderItems
WHERE quantity = 100 OR prod_id LIKE 'BNBG%'
ORDER BY prod_id;
Category: Lesson 14

This one is a little nonsensical, I know, but it does reinforce a note earlier in this lesson. Write a SQL statement which returns and combines product name (prod_name) from Products and customer name (cust_name) from Customers, and sort the result by product name.


SELECT prod_name
FROM Products
UNION
SELECT cust_name
FROM Customers
ORDER BY prod_name;
Category: Lesson 14

What is wrong with the following SQL statement? (Try to figure it out without running it):

SELECT cust_name, cust_contact, cust_email
FROM Customers
WHERE cust_state  = 'MI'
ORDER BY cust_name;
UNION
SELECT cust_name, cust_contact, cust_email
FROM Customers
WHERE cust_state = 'IL'
ORDER BY cust_name;

The ; after the first SELECT statement shouldn’t be there, it is terminating the statement. Also, if sorting SELECT statements combined with UNION only one ORDER BY may be used and it must come after the last SELECT.

Category: Lesson 14

Load More