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 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;
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;
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;
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.