Use the Northwind database for all practice questions.
Type your query in the box and click Run, or press Ctrl + Enter (Cmd + Enter on a Mac). The result shows up underneath, and the page tells you whether it matches. Run as many queries as you want on the way to the answer. A plain SELECT * to look at a table first is a good habit.
Part 1 is ten queries to write. Each one is checked.
Part 2 is five questions where you use queries to answer something.
Everything you run on this page is recorded and goes into your submission.
Part 1 · Writing queries
1
Write a query that returns the total number of orders.
⌘/Ctrl + Enter
2
Write a query that returns the lowest product price, the highest product price, and the average product price, in that order.
⌘/Ctrl + Enter
3
Write a query that returns the number of customers in North America (Canada, Mexico, and the USA).
⌘/Ctrl + Enter
4
Write a query that returns the total quantity of items ever ordered, using the orderdetails table.
⌘/Ctrl + Enter
5
Write a query that shows the number of customers in each country. Show the country and the count.
⌘/Ctrl + Enter
6
Write a query that shows the number of orders each shipper has handled. Show the shipper ID and the count.
⌘/Ctrl + Enter
7
Procurement wants to see how product pricing differs between suppliers. For each supplier, show the lowest price, the average price, the highest price, and the number of products they supply. Show the supplier ID first, then those four numbers in that order.
⌘/Ctrl + Enter
8
Write a query that shows the average price of the products in each category, but only for categories where the average is 25 or more. Order the results from highest to lowest average. Show the category ID and the average.
⌘/Ctrl + Enter
9
Write a query that shows the total quantity ordered of each product, using the orderdetails table. Show only products with a total of 100 or more. Show the product ID and the total.
⌘/Ctrl + Enter
10
Write a query that shows the employees who have handled more than 20 orders. Show the employee ID and the count.
⌘/Ctrl + Enter
Part 2 · Using queries
These are written the way somebody at Northwind would ask them. Run whatever queries you need, then enter your answer. For a number, type only the number. When the answer is a count of rows, don't count them by hand. The total is shown under the results table, even when the rows run to more than one page.
11
Northwind wants to renegotiate with the shipper it relies on most. Which shipper has handled the most orders? Type the ShipperID.
⌘/Ctrl + Enter
12
Marketing wants to know where Northwind's customer base is thin. How many countries have three or fewer customers?
⌘/Ctrl + Enter
Group the customers by country, keep only the groups with a count of 3 or less, and count the rows you get back. The number of rows is shown under the results table.
13
The warehouse wants to know how common big orders are before it changes how they get packed. How many orders had more than three different products on them? Each product on an order is one row in orderdetails.
⌘/Ctrl + Enter
Group orderdetails by OrderID and keep only the groups with more than three rows. The number of groups is the number of rows you get back, and that total is shown under the results table.
14
Sales asks for the number of orders each employee handled in 1997, but only employees who handled more than five, with the busiest first. Which query gives them that?
This one fails. WHERE runs before the groups exist, so there's no count for it to check. A condition on an aggregate goes in HAVING.
WHERE keeps only 1997 orders, GROUP BY makes one group per employee, HAVING keeps the groups with more than five, and ORDER BY puts the busiest first. Each clause is doing the one job it's for.
The clauses are in the wrong order. HAVING has to come before ORDER BY. This one doesn't run.
The year check belongs in WHERE, so that only 1997 orders get counted. Here every order gets counted first, and the year is checked afterwards against one row from each group, so the numbers are wrong.
15
Purchasing wants a picture of the suppliers Northwind depends on most. For every supplier with at least four products, show the supplier ID, how many products they supply, and the average price of those products. Give the columns clear names. Put the highest average price first. You can include other numbers if you think purchasing would want them.
⌘/Ctrl + Enter
Group by supplier, and use HAVING to keep only the groups with four or more rows. A column like COUNT(*) needs an AS.
Submit
When you're done, download your submission file and upload it to this unit's practice assignment in Canvas. It carries a summary of your progress and the record of every attempt you made on this page. You can submit as many times as you like; the last one counts.
Attempts are saved in this browser. To keep going on another computer, download your submission here and resume from it there.