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