SQL Quiz

SQL is the language for asking questions of relational databases and changing what is stored in them. This quiz covers five ideas that come up again and again: the core statements, filtering before and after grouping, joins, removing data, and what transactions can see of each other. Here is the background behind each one.

The four core statements

Most everyday SQL uses four statements, often grouped as data manipulation language (DML):

• SELECT reads rows and returns them as a result.
• INSERT adds new rows.
• UPDATE changes values in existing rows.
• DELETE removes rows.

Only one of these hands rows back to you; the other three change the data and report how many rows they touched. A typical read looks like this:

SELECT name, email
FROM users
WHERE active = true;

SELECT names the columns, FROM names the table and WHERE picks which rows. The same WHERE matters even more on UPDATE and DELETE: leave it off, and the change hits every row in the table.

Filtering before and after grouping

SQL doesn't run a query in the order you write it. The database works through the clauses in a logical order:

1. FROM and any joins: gather the rows.
2. WHERE: keep only the rows that match.
3. GROUP BY: collect the remaining rows into groups.
4. HAVING: keep only the groups that match.
5. SELECT: work out the columns to return.
6. ORDER BY: sort the result.
7. LIMIT: cut it to a number of rows.

That order explains the difference between the two filters. WHERE filters individual rows before they are grouped. HAVING filters whole groups after grouping, so it is where conditions on totals, counts and averages go. ORDER BY and LIMIT come right at the end: they sort and trim the finished result rather than filter rows by a condition.

Here is a small orders table to try them on:

id   customer_id  total
101  1            30
102  1            250
103  3            80

Count each customer's orders over 50, using WHERE to drop the small orders first:

SELECT customer_id, COUNT(*) AS n
FROM orders
WHERE total > 50
GROUP BY customer_id;

Customers 1 and 3 each have one big order. Now find customers with more than one order of any size, which is a condition on a group, so it needs HAVING:

SELECT customer_id, COUNT(*) AS n
FROM orders
GROUP BY customer_id
HAVING COUNT(*) > 1;

Only customer 1 comes back, with 2 orders. Put COUNT(*) > 1 in WHERE instead and the database refuses: at the WHERE stage the groups don't exist yet, so there is nothing to count.

Joins: matching rows across tables

Relational databases split data into separate tables, which is the heart of database normalisation, and joins put it back together. A join pairs rows from two tables using a condition, usually a shared id. The join types differ in what happens to rows that have no match.

Add a customers table to the orders above:

id  name
1   Mochi
2   Luna

Order 103 belongs to customer 3, who doesn't exist, and Luna has no orders. An INNER JOIN keeps only pairs that match on both sides:

SELECT orders.id, customers.name
FROM orders
INNER JOIN customers
  ON orders.customer_id = customers.id;

id   name
101  Mochi
102  Mochi

Order 103 and Luna both vanish. The outer joins keep unmatched rows and fill the missing side with NULL:

So customers LEFT JOIN orders would list Luna too, with an empty order id. Pick INNER JOIN when you only want complete pairs, and an outer join when 'no match' is itself useful, such as finding customers who have never ordered.

Removing data: rows, tables and structure

Four commands sound alike but work at different levels:

• DELETE removes rows. With WHERE it removes the ones you choose; without it, all of them. The table stays.
• TRUNCATE removes every row in one fast step. The table stays, empty.
• DROP TABLE removes the table itself: its rows, its columns, its indexes and its place in the schema. Afterwards, queries against it fail because it no longer exists.
• ALTER TABLE changes a table's structure, such as adding or renaming a column, and keeps the table and its data.

DELETE FROM users WHERE id = 42;  -- one row
TRUNCATE TABLE users;             -- all rows
DROP TABLE users;                 -- the table

Exactly how TRUNCATE behaves varies between databases, for example whether it resets id counters or can be rolled back, so check your database's documentation before relying on it.

Transactions and what they can see

A transaction groups several statements so they succeed or fail together. When many transactions run at once, the database has to decide how much of each other's work they can see. That is the isolation level, and each level allows or prevents three classic anomalies:

• Dirty read: seeing another transaction's changes before it commits, which may then be rolled back.
• Non-repeatable read: reading the same row twice and getting different values, because another transaction committed an update in between.
• Phantom read: running the same query twice and getting a different set of rows, because another transaction committed an insert or delete in between.

The SQL standard defines four levels, from weakest to strongest:

READ COMMITTED is the default in PostgreSQL, SQL Server and Oracle, while MySQL's InnoDB engine defaults to REPEATABLE READ. Under READ COMMITTED you never see uncommitted work, but each statement sees whatever was committed when it started. Here is how a phantom read happens there:

Stronger levels prevent more anomalies but make transactions wait for, or retry because of, each other more often. Some databases go further than the standard requires: PostgreSQL's REPEATABLE READ, for example, also prevents phantom reads.

Common mistakes

• UPDATE or DELETE without WHERE. It changes every row. Run the matching SELECT first to see what you'll hit.
• Aggregates in WHERE. Conditions on COUNT, SUM or AVG belong in HAVING.
• Expecting INNER JOIN to keep everything. Unmatched rows disappear silently; use an outer join if you need them.
• Mixing up DELETE and DROP TABLE. One empties rows; the other removes the table.
• Assuming READ COMMITTED means 'nothing changes under me'. Rows can still appear or change between two reads in the same transaction.

If you want more practice afterwards, the database quiz covers the wider database ideas around SQL.

Key takeaways

• SELECT reads rows; INSERT, UPDATE and DELETE change them.
• WHERE filters rows before grouping, and HAVING filters groups after it.
• INNER JOIN keeps only rows that match in both tables; outer joins keep the unmatched ones too.
• DROP TABLE removes the whole table from the schema, while DELETE and TRUNCATE only remove rows.
• READ COMMITTED stops dirty reads but still allows phantom reads.