A quick SQL quiz on SELECT and the core statements, WHERE vs HAVING, INNER and outer joins, DROP TABLE vs DELETE, and phantom reads under READ COMMITTED.
Can you answer these five SQL questions? Let's find out. Question 1 Which SQL statement is used to retrieve rows from a table? (*clicking*) (*clicking*) (*clicking*) (*ding*) Question 2 Which clause is used to filter rows before grouping? (*clicking*) (*clicking*) (*clicking*) (*ding*) Question 3 Which JOIN returns only rows with matches in both tables?
(*clicking*) (*clicking*) (*clicking*) (*ding*) Question 4 Which SQL command removes a table and all its data from the database schema? (*clicking*) (*clicking*) (*clicking*) (*ding*) Question 5 In transaction isolation, which anomaly can still occur under READ COMMITTED? (*clicking*) (*clicking*) (*clicking*) (*clicking*) (*ding*) How many answers did you get right? Comment on the video. (*upbeat music*)
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, emailFROM usersWHERE active = true;
SELECT names the columns, FROM names the table and picks which rows. The same matters even more on and : leave it off, and the change hits every row in the table.
Can you answer these five SQL questions? Let's find out. Question 1 Which SQL statement is used to retrieve rows from a table? (*clicking*) (*clicking*) (*clicking*) (*ding*) Question 2 Which clause is used to filter rows before grouping? (*clicking*) (*clicking*) (*clicking*) (*ding*) Question 3 Which JOIN returns only rows with matches in both tables?
(*clicking*) (*clicking*) (*clicking*) (*ding*) Question 4 Which SQL command removes a table and all its data from the database schema? (*clicking*) (*clicking*) (*clicking*) (*ding*) Question 5 In transaction isolation, which anomaly can still occur under READ COMMITTED? (*clicking*) (*clicking*) (*clicking*) (*clicking*) (*ding*) How many answers did you get right? Comment on the video. (*upbeat music*)
SQL doesn't run a query in the order you write it. The database works through the clauses in a logical order:
FROM and any joins: gather the rows.
WHERE: keep only the rows that match.
GROUP BY: collect the remaining rows into groups.
HAVING: keep only the groups that match.
SELECT: work out the columns to return.
ORDER BY: sort the result.
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 total101 1 30102 1 250103 3 80
Count each customer's orders over 50, using WHERE to drop the small orders first:
SELECT customer_id, COUNT(*) AS nFROM ordersWHERE total > 50GROUP 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 nFROM ordersGROUP BY customer_idHAVING 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 name1 Mochi2 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.nameFROM ordersINNER JOIN customers ON orders.customer_id = customers.id;
id name101 Mochi102 Mochi
Order 103 and Luna both vanish. The outer joins keep unmatched rows and fill the missing side with NULL:
Join
Keeps
INNER JOIN
Only rows that match in both tables
LEFT JOIN
Every row from the left table
RIGHT JOIN
Every row from the right table
FULL JOIN
Every row from both tables
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 rowTRUNCATE TABLE users; -- all rowsDROP 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:
Level
Prevents
READ UNCOMMITTED
Nothing guaranteed
READ COMMITTED
Dirty reads
REPEATABLE READ
Dirty and non-repeatable reads
SERIALIZABLE
All three
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:
Drawing the diagram…
Each count was correct when it ran; the set of matching rows changed in between.Expand it to fill the screen. Zoom with the buttons or pinch, drag to pan.
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.