SQL Exercises for Northwind Database

When it comes to learning SQL, many beginners struggle to gain practical experience that can eventually translate to real-world scenarios. We'll tackle this challenge by presenting Northwind database SQL exercises with solutions and explanations.

In this article, we’ll cover 16 Northwind exercises taken from our SQL practice course Basic SQL Practice: A Store. As you might have guessed, the course uses Microsoft’s classic Northwind database. It’s a small trading company database that’s designed to give SQL learners some practical experience. It includes realistic data for customers, products, suppliers, and orders, making it an ideal starting point for those looking to build foundational SQL skills.

More data is being generated daily, making databases a must for safe and organized information storage. Using SQL, or Structured Query Language, we can draw insights from the data inside these databases. SQL allows us to extract, analyze, and manipulate data for our analysis. While learning SQL may feel challenging at first, proficiency grows with practice – just like learning any language.

The Basic SQL Practice: A Store course itself offers 169 interactive exercises that cover a wide range of SQL topics. To familiarize ourselves with the type of content in the course, we’ll look at questions from each area. These exercises will help you improve your SQL skills and confidence.

Northwind Database Overview

Let's begin by taking a look at the database we’ll be using:

The tables in this database each represent a unique object and are linked to other tables via primary and foreign keys. The primary key uniquely identifies each record in a table (for example, customer_id in the Customer table), allowing us to connect tables and get relevant data by creating a bridge between them.

Let’s briefly look at each table:

• employee: This table holds data on the company's employees, which can be useful for HR and employee performance analysis.
• purchase: This table holds data for transactions between customers and the company. This information can aid in purchase analysis, stock planning, and location analysis.
• customer: This table contains data on the company's customers. This can be useful for identifying target audiences and personalizing services.
• purchase_item: This table connects purchases with products and allows us to gain information on the prices and quantities of the different products in each order.
• product: This table displays data on the products in the company’s offer. This can provide insights into popular products and their details.
• category: This table contains information about different product categories, which can help in gaining insights on popular categories.

Northwind Database Exercises

Now let’s dive into some practical SQL exercises based on this database. We are going to break down this article into six sections that each introduce different SQL skills. Keep our SQL Cheat Sheet nearby for quick reference on the functions and syntax we will be using.

1. Single Table Queries

To begin, we will start with the basics: filtering and retrieving data from a single table. In these exercises, you’ll use the SELECT and WHERE clauses to extract specific information from individual tables. Mastering these essential SQL commands will lay a strong foundation for building more complex queries later on.

Exercise 1: All Products

Display all the data present in the product table.

Solution:

SELECT *
FROM product;

Explanation:

In this query:

• SELECT * instructs SQL to select all columns from the product
• FROM product specifies the table we are using.
• This query returns all rows and columns in the product table, providing a complete view of each product’s details in the database.

Exercise 2: Products More Expensive Than 3.5

Display the names of products with a unit price greater than or equal to 3.5.

Solution:

SELECT product_name
FROM product
WHERE unit_price >= 3.5;

Explanation:

In this query:

• SELECT product_name specifies we want just the product names.
• FROM product specifies the table we are using.
• WHERE unit_price >= 5 limits the output to products priced at 3.5 or above.
• This query is useful for identifying the more expensive items in the store.

Exercise 3: Customers’ Names and Emails

Display customers’ names and emails. You'll find the name in the contact_name column and the email in the contact_email column of the customer table. Rename the columns to name and email, respectively.

Solution:

SELECT
  contact_name AS name,
  contact_email AS email
FROM customer;

Explanation:

In this query:

• SELECT contact_name AS name takes the column contact_name and renames it in the output as name. It does the same thing with contact_email AS email.
• FROM customer specifies the table we are using.
• Aliasing with AS makes the results more readable and user-friendly. This can be especially useful when sharing results with non-technical stakeholders.

You can find more examples of practical yet simple exercises in our article SQL Practice for Beginners: AdventureWorks Exercises.

2. Queries from Multiple Tables

This section focuses on combining data from different tables using JOINs; this enables us to create more complex queries.

Exercise 4: Product and Categories

Select product names along with their categories. Display two columns: product_name and category_name.

Solution:

SELECT
  product_name,
  category.name AS category_name
FROM product
JOIN category
  ON product.category_id = category.category_id;

Explanation:

In this query:

• SELECT product_name, category.name AS category_name identifies the columns that are requested in our output.
• FROM product specifies the first table in our JOIN.
• JOIN category specifies the second table in our JOIN.
• ON product.category_id = category.category_id acts as a bridge between both tables and ensures that only rows with matching category IDs are included in the result set.

Exercise 5: Employees and Purchases, Part 1

Show the last and first names of the employees handling purchases along with the shipped_date of the purchase.

Solution:

SELECT
  last_name,
  first_name,
  shipped_date
FROM employee
JOIN purchase
  ON employee.employee_id = purchase.employee_id;

Explanation:

In this query:

• SELECT last_name, first_name, shipped_date identifies the columns that are shown in our output.
• FROM employee indicates the first table in our JOIN.
• JOIN purchase specifies the second table in our JOIN.
• ON employee.employee_id = purchase.employee_id joins both tables on rows where the employee ID in the employee table matches the employee ID in the purchase Only rows with matching IDs are included in the result set.

Exercise 6: Employees and Purchases, Part 2

For each employee, display their last_name, first_name, and the ID of the purchase(s) they’ve handled (if such exist). Make sure to display data for all employees, even if they haven't been involved with any purchases.

Solution:

SELECT
  last_name,
  first_name,
  purchase_id
FROM employee
LEFT JOIN purchase
  ON employee.employee_id = purchase.employee_id;

Explanation:

In this query:

• SELECT last_name, first_name, purchase_id specifies the columns to be included in the output.
• FROM employee designates the first table in our LEFT JOIN, which contains details about employees.
• LEFT JOIN purchase specifies the second table in the join, which holds data about purchases. Using LEFT JOIN ensures that all rows from the employee table are included, even if there is no matching record in the purchase
• ON employee.employee_id = purchase.employee_id connects both tables, matching records based on the employee ID. If an employee has no corresponding record in the purchase table, they will still be included in the result and the purchase fields in the result set will show as NULL.

As you can see, writing queries really builds your SQL skills! Another great way to build your SQL skills is through projects like the ones in SQL Project for Beginners: AdventureWorks Sales Dashboard.

3. ORDER BY: Sorting Your Results

The ORDER BY clause in SQL is particularly useful for organizing results in a more readable and meaningful way.  Whether you want to sort data numerically, alphabetically, or by date, this is very useful in reporting and analytics.

Exercise 7: Employees by Birth Date

Show the last names, first names, and birth dates of employees. Sort the employees by their birth date in descending order (i.e. the most recent dates come first).

Solution:

SELECT
  last_name,
  first_name,
  birth_date
FROM employee
ORDER BY birth_date DESC;

Explanation:

In this query:

• SELECT last_name, first_name, birth_date specifies the columns to include in the output.
• FROM employee indicates the table from which data is retrieved.
• ORDER BY birth_date DESC sorts the results by the birth_date column in descending order, so the employees with the most recent birth dates appear first. This overcomes the limitation of not having an age column.

Exercise 8: Products’ Names and Prices

Display the names and unit prices of all products. Show the cheapest products first.

Solution:

SELECT
  product_name,
  unit_price
FROM product
ORDER BY unit_price;

Explanation:

In this query:

• SELECT product_name, unit_price specifies the columns to be included in the output.
• FROM product identifies the table from which the data is taken.
• ORDER BY unit_price sorts the results by the unit_price column in ascending order, meaning that the products with the lowest prices will appear first.
• As you can see, we don’t need to explicitly specify ASCending; it is the default sort order.

Exercise 9: Purchase Data

Show all data for all purchases. Sort the rows by the ship city in ascending order and by the shipped date in descending order.

Solution:

SELECT *
FROM purchase
ORDER BY
  ship_city ASC,
  shipped_date DESC;

Explanation:

In this query:

• SELECT * specifies that all columns from the table should be included in the output.
• FROM purchase identifies the table from which data is retrieved.
• ORDER BY ship_city ASC, shipped_date DESC sorts the results first by ship_city in ascending order (so that the cities are sorted alphabetically). Then within each city, the results are further sorted by shipped_date in descending order (so the most recent shipment dates appear first for each city).

If you want to find more exercises with ORDER BY, check out our article 10 Beginner SQL Practice Exercises with Solutions.

4. GROUP BY and Aggregate Functions

The next section helps build the skill of summarizing data through grouping and aggregation. Using aggregate functions, we can gain insights by combining rows and conducting calculations within each group.

Exercise 10: Number of Categories

Count the number of categories present in the category table. Name the column number_of_categories.

Solution:

SELECT
  COUNT(category_id) AS number_of_categories
FROM category;

Explanation:

In this query:

• SELECT COUNT(category_id) AS number_of_categories counts the number of rows that do not have NULLs in the category_id It displays the resulting column as number_of_categories.
• FROM category specifies the table from which the data is retrieved.

Exercise 11: Customer Purchases

Count the number of purchases made by each customer. Display the customer_id, contact_name, and purchases_number. Ignore any customers that aren't present in the purchase table.

Solution:

SELECT
  purchase.customer_id,
  contact_name,
  COUNT(*) AS purchases_number
FROM purchase
JOIN customer
  ON purchase.customer_id = customer.customer_id
GROUP BY
  purchase.customer_id,
  contact_name;

Explanation:

In this query:

• SELECT purchase.customer_id, contact_name, COUNT(*) AS purchases_number specifies the columns to be included in the output. It retrieves each customer’s ID and contact name, along with the count of their purchases (labeled purchases_number).
• FROM purchase identifies the first table in the JOIN.
• JOIN customer specifies the second table in the JOIN.
• ON purchase.customer_id = customer.customer_id links the two tables, matching rows where the customer_id is the same in both tables and ensuring only purchases associated with existing customers are included.
• GROUP BY purchase.customer_id, contact_name groups the results by each customer’s ID and contact name, allowing the COUNT(*) function to calculate the number of purchases for each customer.

Exercise 12: Revenue for Customer and Employee Pairs

For each customer and employee, find the total price of all purchases they made to which a given employee is assigned. Display three columns: customer_id, employee_id, and the total price of purchases. Rename the third column to total_purchases_price.

Solution:

SELECT
  customer_id,
  employee_id,
  SUM(total_price) AS total_purchases_price
FROM purchase
GROUP BY
  customer_id,
  employee_id;

Explanation:

In this query:

• SELECT customer_id, employee_id, SUM(total_price) AS total_purchases_price specifies the columns to include in the output, showing each customer_id and employee_id along with the sum of total_price for purchases associated with each combination. This sum is labeled as total_purchases_price in the result.
• FROM purchase identifies the table from which data is taken.
• GROUP BY customer_id, employee_id groups the results by both customer_id and employee_id, allowing the SUM(total_price) function to calculate the total purchase price for each unique customer–employee combination.

5. Other SQL Features

Next, we'll briefly cover some more SQL features that are included in the course but are also useful in day-to-day analysis. These functions, when combined with others discussed in this article, can help you improve your SQL skills.

Exercise 13: Unknown Hire Date

Display the last and first names of employees with an unknown hire date.

Solution:

SELECT
  last_name,
  first_name
FROM employee
WHERE hire_date IS NULL;

Explanation:

In this query:

• SELECT last_name, first_name specifies the columns to include in the output.
• FROM employee indicates the table from which data is taken.
• WHERE hire_date IS NULL filters the results to only include employees whose hire_date is NULL, meaning their hire date is unknown.

Exercise 14: Average Price per Category

For each category, count the average price of its products. Display only the categories for which the average unit price is greater than the overall average unit price. Name the columns category_name and average_price.

Solution:

SELECT
  category.name AS category_name,
  AVG(unit_price) AS average_price
FROM product
JOIN category
  ON product.category_id = category.category_id
GROUP BY category.name
HAVING AVG(unit_price) > (
  SELECT AVG(unit_price)
  FROM product
);

Explanation:

In this query:

• SELECT category.name AS category_name, AVG(unit_price) AS average_price specifies the columns to include in the output.
• FROM product identifies the first table in the JOIN.
• JOIN category specifies the second table in the JOIN.
• ON product.category_id = category.category_id links the two tables, ensuring that only products with matching category IDs from both tables are included.
• GROUP BY category.name groups the results by category name, allowing the AVG(unit_price) function to calculate the average price for each category.
• HAVING AVG(unit_price) > (SELECT AVG(unit_price) FROM product) filters the grouped results to include only categories where the average price of products is greater than the overall average price of all products in the product
• The subquery (SELECT AVG(unit_price) FROM product) calculates the overall average unit price across all products.

6. Advanced SQL Features

Finally, we'll look at some more advanced SQL queries. As we do this, we’ll reference functions we’ve looked at earlier.

Exercise 15: Categories with Active Products

For each category, display the number of its products that aren't discontinued (they are continued or there is a NULL in the discontinued column). Show the columns named category_name and products_number. Show only the rows for which the number of such products is greater than 1. Also, don't show the row for the Other category.

Solution:

SELECT
  category.name AS category_name,
  COUNT(product_id) AS products_number
FROM product
JOIN category
  ON product.category_id = category.category_id
WHERE category.name <> 'Other'
  AND discontinued IS NOT TRUE
GROUP BY category.name
HAVING COUNT(product_id) > 1;

Explanation:

In this query:

• SELECT category.name AS category_name, COUNT(product_id) AS products_number specifies the columns to include in the output.
• FROM product identifies the first table in the JOIN.
• JOIN category specifies the second table in the JOIN.
• ON product.category_id = category.category_id links the two tables, ensuring that only products with matching category IDs from both tables are included.
• WHERE category.name <> 'Other' AND discontinued IS NOT TRUE filters the results to exclude categories named "Other" and to only include products that are not discontinued.
• GROUP BY category.name groups the results by category name, allowing the COUNT(product_id) function to count the number of products within each category.
• HAVING COUNT(product_id) > 1 filters the grouped results to include only categories with more than one product.

Exercise 16: Wine Statistics

All wines in the product table have a name starting with Wine. Find the:

• Number of such products in the table (products_number).
• Total number of units in stock (units_number).
• Average product price (average_price).
• Ratio of the maximum price to the minimum price (max_to_min_ratio).
• Difference between the maximum price and the average price (max_to_average).
• Difference between the average and minimum price (average_to_min).

Round the four last columns to two decimal points.

Solution:

SELECT
  COUNT(*) AS products_number,
  SUM(units_in_stock) AS units_number,
  ROUND(AVG(unit_price), 2) AS average_price,
  ROUND(MAX(unit_price) / MIN(unit_price), 2) AS max_to_min_ratio,
  ROUND(MAX(unit_price) - AVG(unit_price), 2) AS max_to_average,
  ROUND(AVG(unit_price) - MIN(unit_price), 2) AS average_to_min
FROM product
WHERE product_name LIKE 'Wine%';

Explanation:

In this query:

• First let's break down the SELECT statement:
  • products_number calculates the total number of products.
  • units_number sums the total number of units in stock.
  • average_price gets the average unit price of the products, rounded to 2 decimal places.
  • max_to_min_ratio finds the ratio of the maximum unit price to the minimum unit price, rounded to 2 decimal places.
  • max_to_average finds the difference between the maximum unit price and the average unit price, rounded to 2 decimal places.
  • average_to_min calculates the difference between the average unit price and the minimum unit price, rounded to 2 decimal places.
• FROM product identifies the table from which the data is taken.
• WHERE product_name LIKE 'Wine%' filters the results to include only products whose names start with 'Wine'.

Want More Northwind Exercises for SQL Learners?

By practicing writing queries, you can strengthen your SQL abilities. Working with real-world data, such as the Northwind Database, gives you experience accessing and evaluating information fast.

If you have found the Northwind exercises in this article interesting, be sure to check out the course Basic SQL Practice: A Store. You will find plenty more interactive exercises. We also have SQL Project for Portfolio: Northwind Store, an article on building a project based on the Northwind database. Happy learning!