Exercise: Joins

Twelve questions about Northwind that each need more than one table, answered with inner and outer joins, a self join, sub-queries and CASE, each with the result to expect.

Journeyman · T-SQL · SQL · Updated

Real questions rarely live in one table. Who supplies each product? Which shipper delivers late? Which customers bought Chai? Each answer here needs a join, a sub-query, or both.

Get the exercise code

Open this lesson's GitHub repository, github.com/ask-codey/exercise-joins, also linked from the Exercise box beside the lesson. Click Code, then Download ZIP, and unzip it into your bootcamp folder. Or clone it with Git.

There's one .sql file per question, numbered in order. Each holds the question as a comment, and an Expect line with the right answer's shape. Nothing here changes any data.

What to do

Open each file in SSMS with Northwind chosen, write your query under the question, run it with F5, and compare with the Expect line.

Questions Practice
1–5 INNER JOIN across two, three and more tables, with WHERE, GROUP BY and ORDER BY
6 A LEFT JOIN to find what's missing
7 Joins and aggregates over three tables
8 A table joined to itself
9–10 Sub-queries for a value and for a list
11–12 CASE to count and to label

Hints

  • Sketch the path first. Which table holds what you start from, which holds the answer, and which keys link them?
  • Alias every table, and put the alias in front of every column: p.ProductName, s.CompanyName.
  • "Which ones have none?" is a LEFT JOIN followed by WHERE ... IS NULL.
  • Shippers are linked through Orders.ShipVia, which holds a ShipperId; the column names don't match, but the values do.
  • Test sub-queries on their own before you put them in parentheses.

When you're done

Every result matches its Expect line. Question 11 is worth a second look: one pass over 830 orders, joined to the shippers, gives a delivery report card for each.

Submit your exercise

The exercise is how you finish this lesson. When your work is done, put it on GitHub in a repo of your own, then submit that repo's address here.

  1. On GitHub, click +, then New repository. Name it (for example exercise-joins), choose Public, and click Create repository. Leave out the README: your folder already has one.

  2. In a terminal in your exercise folder (Git Bash on Windows), send your work to your new repo. If you downloaded the ZIP:

    git init -b main
git add .
git commit -m "My solution"
git remote add origin https://github.com/your-name/exercise-joins.git
git push -u origin main

    If you cloned the starter instead, point your copy at your own repo, then commit and push:

    git remote set-url origin https://github.com/your-name/exercise-joins.git
git add .
git commit -m "My solution"
git push -u origin main

  3. Paste your repo's address, such as https://github.com/your-name/exercise-joins, into Submit your exercise on this page. The site checks that the repo is public and holds your commits, and then the lesson is complete.

Changed something after you submitted? Push it, and submit again: your latest submission is the one that counts.

So what

You can now follow the keys through a database to answer questions no single table could. That's most of the SQL a developer writes, and exactly what your C# data access will send.

Key terms

Related