Unit 05 · Practice

Practice

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). Run as many queries as you want on the way to the answer. Start each join with SELECT * and check what comes back before you narrow it down. When the answer is a count of rows, don't count them by hand. The total is shown under the results table.

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 shows the name of every product and the name of the category it's in.
⌘/Ctrl + Enter
2
Write a query that shows every order's ID and date, with the city and country of the customer who placed it.
⌘/Ctrl + Enter
3
Write a query that shows every product's name, price, and unit, with the name and city of the supplier who provides it.
⌘/Ctrl + Enter
4
Write a query that shows the ID and date of every order shipped by Federal Shipping, with the shipper's name.
⌘/Ctrl + Enter
5
Write a query that shows the name and price of every product in the Seafood category, most expensive first.
⌘/Ctrl + Enter
6
Write a query that shows how many products are in each category. Show the category's name and the count.
⌘/Ctrl + Enter
7
Write a query that shows how many orders each employee has handled, busiest first. Only include employees who have handled at least one order. Show the employee's last name and the count.
⌘/Ctrl + Enter
8
The sales team wants a report with one row per order. Show the order's ID and date, the name of the customer who placed it, the first and last name of the employee who handled it, and the name and phone number of the shipper.
⌘/Ctrl + Enter

Orders has a CustomerID, an EmployeeID, and a ShipperID, so all three tables join straight to Orders. Add them one at a time and run the query after each.

9
Write a query that shows every product that costs less than $50. Show the product's name, unit, and price, then the name of its supplier and the name of its category. Sort them from lowest to highest price.
⌘/Ctrl + Enter
10
Write a query that shows every employee's last name with the number of orders they've handled. Include employees who haven't handled any, with a count of 0.
⌘/Ctrl + Enter

Start from Employees and use a LEFT JOIN, so every employee is kept. COUNT(*) gives an employee with no orders a 1, because they still have one row. Count a column from the Orders side.

Part 2 · Using queries

These are written the way somebody at Northwind would ask them. For questions 11 to 13, run whatever queries you need, then type only the name the question asks for. Question 14 asks you to pick the query that answers it, and question 15 is a query to write.

11

Northwind wants to send someone to visit its customers in Germany. Which employee has handled the most orders from German customers? Type their last name.

⌘/Ctrl + Enter

You need the customer's country and the employee's name, so join Orders to both Customers and Employees. Then filter to Germany and group by employee.

12

Purchasing wants to make sure Northwind never runs out of its best-selling product. Which product has sold the most units in total? Which country is that product's supplier in? Type the country.

⌘/Ctrl + Enter

Find the product first. Add up Quantity in OrderDetails for each product. Then look up that product's supplier, either with a second query or by joining Products and Suppliers onto the first one.

13

Marketing wants to win back customers who signed up but never bought anything. Which country has the most customers who have never placed an order?

⌘/Ctrl + Enter

A LEFT JOIN from Customers to Orders, keeping only the rows where Orders.OrderID IS NULL, gives you the customers with no orders. Then group them by country.

14

The sales team asks: how many orders included at least one seafood product? All four of these queries run. Which one answers the question? You can run them in the box to compare.

⌘/Ctrl + Enter
15
The logistics team wants a summary of each shipper. For every shipper, show the shipper's name, how many orders it has shipped, and the total number of units in those orders. Put the shipper with the most units first. Give the columns clear names. You can include other numbers if you think logistics would want them.
⌘/Ctrl + Enter

The units are in OrderDetails, so you need Shippers, Orders, and OrderDetails. Once OrderDetails is joined in, one row is one product on one order, so COUNT(*) counts too much. Count the different OrderIDs. A column like SUM(Quantity) 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.