Unit 02 · Practice

Practice

Use the Northwind database for all practice questions.

This is the first practice where you write queries. 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. If it doesn't, it will usually tell you what's off, like too many rows or the wrong columns.

Run as many queries as you want. It's normal to run a plain SELECT * just to look at a table before you write the real query. All of it goes in the question's history, and a messy history is fine.

The Tables panel on the right lists every table and its columns. If you don't see it, click the blue Tables button in the bottom right corner. You don't need USE northwind; here. If you type it, it just gets ignored.

Part 1 is ten queries to write. Each one is checked, and the page tells you whether your result matches.

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
Create a query to display the first name and last name of all employees from the employees table.
⌘/Ctrl + Enter
2
Write a query to display the product name, price, and price with a 30% discount from the products table.
⌘/Ctrl + Enter
3
Re-write your query for question 2 using clear column names. Name the three columns, in order: Product, Price, Sale Price.
⌘/Ctrl + Enter
4
Write a query to retrieve all products from the products table where the price is between 15 and 25.
⌘/Ctrl + Enter
5
Create a query to list all products from the products table that have 'chef' in their product name.
⌘/Ctrl + Enter
6
Write a SELECT statement to display all customers from the customers table that are located in either Germany, France, or Spain.
⌘/Ctrl + Enter
7
Write a query to show all products from the products table that have a CategoryID of 1 and a price greater than 20.
⌘/Ctrl + Enter
8
Write a query to retrieve all employee names from the employees table, ordered from youngest to oldest. Show first name, last name, and birth date.
⌘/Ctrl + Enter
9
Write a query to show all customers from the customers table, sorted first by their country in reverse alphabetical order and the customer's name in alphabetical order.
⌘/Ctrl + Enter
10
Write a query to show the three cheapest products in the products table, cheapest first. Show the product name and the price.
⌘/Ctrl + Enter
Part 2 · Using queries

For questions 12 and 13, run whatever queries you need to find the answer, then enter your final answer.

11
Northwind is analyzing their pricing structure and needs to see how each of our products would be priced if we applied a flat $2 increase and then a 12% markdown (decrease). Write a query to accomplish this. Show the product name, the current price, and the new price.
⌘/Ctrl + Enter
12

Write a query that shows only the customers in Germany. How many are there? The number of rows is shown under your result.

⌘/Ctrl + Enter
13

Find Northwind's youngest employee. How many orders have they taken?

⌘/Ctrl + Enter

This takes two queries. The first one finds the youngest employee, and you read their EmployeeID from the result. The second one uses that EmployeeID to show only their orders from the orders table. The number of rows is shown under the result.

14

A manager asks for every product that costs 40 or more, with the most expensive at the top. All four of these queries run without an error. Which one gives the manager what they asked for?

15
The marketing team is planning a promotion on Northwind's affordable seafood and dairy products. For this promotion, affordable means a price under 20, and everything that qualifies will be 20% off. Write a query that gives them the name, the current price, and the promotional price of every product that qualifies, with the cheapest first. Give your columns clear names. You can include other columns if you think marketing would want them.
⌘/Ctrl + Enter

The products table doesn't say "seafood" or "dairy" anywhere. It has a CategoryID. Look in the categories table to find the two IDs you need. If you get too many rows back, check how your AND and OR are grouped.

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.