How to teach SQL with hands-on exercises

Most of a beginner's SQL trouble happens before the first query. Vicki Boykis, who taught SQL to beginners, put it plainly: "The problem with SQL is that you can't just get started." A database needs a server, an account, a connection and tables with data in them, and each of those is a way to lose the first half hour.

SQLite removes most of it. The whole database is one file that students download, and the sqlite3 program opens it with no server and no password. Software Carpentry's Databases and SQL lesson works this way: everyone downloads survey.db and opens it, in sqlite3 or in DB Browser for SQLite if they would rather click than type. Ask students to install and open the file before the day, and to send you the line sqlite3 --version printed.

Keep the database small enough to understand: three or four tables, a few hundred rows, columns whose meaning fits in a sentence. A shop works well: customers, orders, products. Put in the problems real data has, on purpose:

• Empty values: an order with no shipping date, a customer with no city.
• A one-to-many link: a customer with several orders, so a join can repeat rows.
• Values that look alike: Lisbon and lisbon, a price stored as text.

Keep a copy of every answer for yourself, and run each exercise against the file students have, not your working copy.

SQL looks like an English sentence, and beginners read it that way. They write the whole query at once, it fails, and they can't tell which part is wrong. Build it in the order the clauses can be tested:

1. SELECT * FROM orders LIMIT 10; to see what the table holds.
2. SELECT a few columns by name.
3. WHERE to keep some rows.
4. ORDER BY and DISTINCT.
5. COUNT, SUM, AVG.
6. GROUP BY, then HAVING.
7. JOIN, once single tables are easy.

Software Carpentry's lesson follows close to this order: selecting, sorting, filtering, calculating, missing data, aggregation, then combining tables. Shopify's data team, who ran SQL workshops for colleagues, gave the same advice for each exercise: start by looking at the whole table, then make every new question a small change to the last query, adding one or two new ideas at a time.

Ask students to run the query after each clause they add. A query that worked a minute ago and fails now has its mistake in the line they just wrote.

"Write a query that shows the North orders" has no end. A student sees rows and can't tell whether they are the right rows. Give each exercise a question with one answer:

Open shop.db in sqlite3. Count the orders that haven't shipped yet. You're done when sqlite3 prints a number. Paste it below.

A number does three jobs. The student knows when they are done. You can tell a right answer from a wrong one at a glance. And the wrong answers say what went wrong. In this example the answer is 14. A student who gets 0 wrote shipped_on = NULL. A student who gets 200 left out the WHERE. A student who gets 186 counted the orders that did ship.

When the result can't be one number, ask for something nearly as short: the first row of an ordered list, the number of rows the query returned, or the top three names. Teach students to check their own results too: before trusting a total, count the rows another way, or look at five of them.

Research on students' SQL shows the same mistakes year after year. Ahadi, Prior, Behbood and Lister studied queries from more than 2,300 students and found that most mistakes in meaning were omissions, and that they clustered in queries that needed a JOIN, a subquery or a GROUP BY. Taipalus, Siponen and Vartiainen analysed over 33,000 student queries and listed the logical errors that recur across students, such as AND where OR was needed and joins on columns that aren't related. Miedema, Aivaloglou and Fletcher asked students to think aloud while writing queries, and traced their errors to four causes, among them other languages they knew and a wrong picture of what a query does.

So don't hope students avoid these. Give each one an exercise where it shows:

• NULL. Software Carpentry's lesson puts it in one line: "comparing things to null with = and != produces null", and WHERE keeps only rows where the test is true. So WHERE shipped_on = NULL returns nothing, and WHERE city != 'Lisbon' silently drops every customer with no city. Teach IS NULL and IS NOT NULL the first time an empty value appears.
• Joins that repeat rows. Joining customers to orders gives one row per order, not per customer. Ask "how many customers have ordered?" and watch COUNT(*) give the number of orders. Then teach COUNT(DISTINCT customer_id).
• AND and OR. "Orders from Lisbon or Porto over 100" needs parentheses: WHERE (city = 'Lisbon' OR city = 'Porto') AND total > 100. Without them, every Lisbon order comes back whatever its total.
• GROUP BY. A column in the SELECT that isn't grouped or aggregated is an error in most databases, and SQLite returns a value from one row of the group without warning. Ask for a total per city, and check that each city appears once.

The wrong results are the most useful thing in the room. When half the class pastes 0 for the shipping question, stop for one minute: "Who got 0? Here's why." Show = NULL and IS NULL side by side, run both, and move on. One minute on the mistake the room made teaches more than a slide on NULL before anyone has met one.

Keep the loop short for each exercise, about ten to fifteen minutes:

1. Show the new clause once, on a big screen, typing it as you talk.
2. Let them answer a different question with it, on their own.
3. Collect the results and talk about the wrong ones.
4. Give whoever finished early an extra question that needs one more idea, so they don't wait.

In Workshopy each exercise is a step students follow in the browser, next to their own sqlite3. They join at workshopy.io/join with the session's code and a name, without making an account. Each step can end with a Check your work block: students paste the number their query printed and press Check, and the server compares it with your answer, so the answer never reaches their screens.

```check
Paste the number your query printed.

number: 14

mistake: 0 → Your query ran, but `= NULL` is never true in SQL, so no row matched. Write `shipped_on IS NULL`.
mistake: 200 → That's every order. Check the `WHERE` clause is there, before the semicolon.
mistake: 186 → That's the orders that HAVE shipped. You want the empty ones.
hint: An empty value in a database is NULL. It needs its own test.
```

A paste that matches a mistake you wrote gets your words for it at once, so the student with 0 learns about IS NULL without waiting for you. A right answer marks the step done. For a query that returns rows rather than a number, the key can be lines:, the rows it should print. On your screen, marked Only you, you see how many have passed and the wrong answers grouped, so "who got 0?" is a fact. A teacher block in the step holds your own notes, such as where to stop the class.

Need help sends a request with a reason and a note, and the Help tab lists them oldest first. The room shows every student by name with their state, and marks students who are taking much longer than the rest without having asked. The step timer counts down on every screen when you say "ten minutes for this one". After the class, the report shows each step: who finished, who asked for help, and the wrong answers for each check, so you know which trap to spend more time on next time.