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 10

The OrderItems table contains the individual items for each order. Write a SQL statement that returns the number of lines (as order_lines) for each order number (order_num) and sort the results buy order_lines.

SELECT order_num, COUNT(*) as order_lines
FROM OrderItems
GROUP BY order_num
ORDER BY order_lines;
Category: Lesson 10

Write a SQL statement that returns a field named cheapest_item which contains the lowest cost item for each vendor (using prod_price in the Products table), and sort the results from lowest to highest cost.

SELECT vend_id, MIN(prod_price) AS cheapest_item
FROM Products
GROUP BY vend_id
ORDER BY cheapest_item;
Category: Lesson 10

It’s important to identify the best customers, so write a SQL statement to return the order number (order_num in OrderItems table) for all orders of at least 100 items.

SELECT order_num
FROM OrderItems
GROUP BY order_num
HAVING SUM(quantity) >= 100
ORDER BY order_num;
Category: Lesson 10

Another way to determine the best customers is by how much they have spent. Write a SQL statement to return the order number (order_num in OrderItems table) for all orders with a total price of at least 1000. Hint, for this one you’ll need to calculate and sum the total (item_price multiplied by quantity). Sort the results by order number.

SELECT order_num, SUM(item_price*quantity) AS total_price
FROM OrderItems
GROUP BY order_num
HAVING SUM(item_price*quantity) >= 1000
ORDER BY order_num;
Category: Lesson 10

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

SELECT order_num, COUNT() AS items
FROM OrderItems
GROUP BY items
HAVING COUNT() >= 3
ORDER BY items, order_num;

GROUP BY items is incorrect. GROUP BY must be an actual column, not the one being used to perform the aggregate calculations. GROUP BY order_num would be allowed.

Category: Lesson 10