A four-table relational schema worked end to end: reading the diagram, filtering and sorting, aggregating with GROUP BY and HAVING, inner versus left joins, the anti-join pattern for finding missing rows, three-valued logic with NULL, and changing data inside a transaction.
Subject: IT Support & Networking · 63 slides · code lesson
Open the interactive version of this deck · Homework for this lesson
Title
IT · Databases
Four tables, one mark scheme, and the queries that actually return the right rows
Objectives
This assignment is graded on whether a query returns the correct rows. Not on style, not on length. So every section here ends with the result set, printed out.
PostgreSQL 16 Documentation — SQL Language — the reference this deck follows
Section
Section 1
Warm-up
One minute before any syntax.
Discussion prompt
Why does a voter registration system keep precincts in a separate table instead of writing the precinct name into every voter row?
Hint: What happens when a precinct gets renamed?
Answer:
Because the name would then be stored hundreds of times, and correcting a typo would mean correcting every copy. Store it once and point at it.
The pointer is the foreign key. Almost every join you write exists because some fact was deliberately stored in exactly one place.
PostgreSQL 16 Documentation — SQL Language — Data Definition, constraints
Concept
A voter belongs to one precinct. A precinct has many voters. A ballot belongs to one voter and one election.
Each of those sentences is a foreign key, and each foreign key is a join you will eventually write.
PostgreSQL 16 Documentation — SQL Language — Data Definition
Picture it
Read this once properly and most of the assignment stops being guesswork.
Figure (svg): An entity relationship diagram with precincts, voters, ballots and elections tables joined by foreign keys
Concept
Five voters, three precincts, four ballots. Small enough to check every result by hand, which is exactly how you should test your own queries before submitting.
| voter_id | first_name | last_name | precinct_id | status |
|---|---|---|---|---|
| 101 | Ada | Reyes | 1 | active |
| 102 | Ben | Osei | 1 | active |
| 103 | Cleo | Nakamura | 2 | active |
| 104 | Dev | Patel | 2 | inactive |
| 105 | Esi | Boateng | 3 | active |
| ballot_id | voter_id | election_id | method |
|---|---|---|---|
| 1 | 101 | 7 | in-person |
| 2 | 102 | 7 | |
| 3 | 103 | 7 | |
| 4 | 101 | 8 | in-person |
Precincts: 1 is Northside, 2 is Harbor, 3 is Westgate.
Matching
Getting this wrong is the single most common reason a join returns nonsense.
Match the pairs
Why: A foreign key column holds a value that exists as a primary key somewhere else. The last one is the trap: voter_id in the voters table is the thing being pointed at, not a pointer.
Section
Section 2
Concept
A SELECT statement is written in one order and evaluated in another. Knowing the evaluation order explains most of SQL's apparent inconsistencies.
ISO/IEC 9075, Information technology — Database languages — SQL — the logical processing order defined by the standard
Picture it
SELECT is written first and runs fifth.
Figure (svg): Two columns comparing the written order of SQL clauses against the logical order in which they are evaluated, with SELECT highlighted as running fifth
Two consequences: WHERE cannot see a column alias you invented in SELECT, and ORDER BY can, because it runs last.
Worked example
The question in English: list every active voter, surname first, in alphabetical order.
Start from the table, not from the columns
Why: FROM runs first. Deciding what you are looking at before what you want from it matches the evaluation order.
Filter the rows with WHERE
Why: Status is a string, so the literal needs single quotes. Double quotes mean an identifier in standard SQL, which is a common first error.
Choose the columns, then sort
Why: ORDER BY runs after SELECT, so it can use anything the query produced.
SELECT last_name, first_name
FROM voters
WHERE status = 'active'
ORDER BY last_name;| last_name | first_name |
|---|---|
| Boateng | Esi |
| Nakamura | Cleo |
| Osei | Ben |
| Reyes | Ada |
Verify: the row count against the data
Why: Five voters, one inactive, so four rows. Patel is missing and should be. If you get five, the WHERE clause did not apply.
Picture it
The row count at the bottom is the fastest check you have.
Figure (svg): A psql session running the active voters query and printing four result rows followed by a row count
Trap
Filtering on the status column.
Annotate
Get a confusing error about a missing column
Why: The error names a column you never mentioned, which is why this one wastes so much time.
Single quotes for values, double quotes only for names that need them.
SELECT * FROM voters WHERE status = 'active';| quote | means | example |
|---|---|---|
| single | a string value | 'active' |
| double | an identifier | "select" as a column name |
| none | a number or keyword | 101 |
Remember the rule by what breaks
Why: MySQL is lenient about this and lets double quotes work as strings, which is exactly why the habit survives until it meets Postgres.
Prediction
Against the five sample voters.
Predict first
SELECT * FROM voters WHERE precinct_id = 2 AND status = 'active';
Correct: 1.
Why: Precinct 2 holds Nakamura and Patel. Patel is inactive, so the AND removes him and one row survives. Swapping the AND for an OR would give four rows instead, which is a good illustration of how much work that one keyword does.
Sorting
Each condition belongs in exactly one clause.
Sort into buckets
Where does each condition go?
Rule of thumb: if the condition mentions COUNT, SUM or AVG, it belongs in HAVING. Otherwise it belongs in WHERE, where it is also faster.
Section
Section 3
Concept
An aggregate function turns many rows into one value. GROUP BY says which rows belong together while that happens.
aggregate — A function like COUNT, SUM, AVG, MIN or MAX that reduces a set of rows to a single value.
The rule that catches everyone: every column in the SELECT list must either be aggregated or named in the GROUP BY. There is no third option.
PostgreSQL 16 Documentation — SQL Language — Aggregate Functions and GROUP BY
Worked example
The question: count the registered voters in each precinct, and show the precinct name rather than its number.
Join so the name is available
Why: The count lives in voters and the name lives in precincts, so both tables are needed.
Group by the thing you want one row per
Why: One row per precinct means GROUP BY the precinct.
Count
Why: Counting the voter id rather than star makes the intent explicit and behaves correctly under a left join.
SELECT p.name, COUNT(v.voter_id) AS voter_count
FROM precincts p
LEFT JOIN voters v ON v.precinct_id = p.precinct_id
GROUP BY p.name
ORDER BY voter_count DESC;| name | voter_count |
|---|---|
| Northside | 2 |
| Harbor | 2 |
| Westgate | 1 |
Verify: that the counts add to the number of voters
Why: Two plus two plus one is five, and there are five voters. If a group total does not add up, a join has duplicated or dropped rows.
Picture it
Identical here, and very different under a left join with no matches.
Figure (svg): Two bars comparing count star which counts rows including nulls against counting a column which skips nulls
A precinct with no voters gets one row from the left join, with NULL in every voter column. COUNT star would report one voter; counting the id correctly reports zero.
Concept
They look interchangeable and are not. WHERE runs before the grouping, so it cannot see a count. HAVING runs after, so it can.
SELECT p.name, COUNT(v.voter_id) AS voter_count
FROM precincts p
JOIN voters v ON v.precinct_id = p.precinct_id
WHERE v.status = 'active'
GROUP BY p.name
HAVING COUNT(v.voter_id) > 1;| clause | runs | sees | here it means |
|---|---|---|---|
| WHERE | before grouping | individual rows | only active voters are counted at all |
| GROUP BY | third | the surviving rows | one group per precinct name |
| HAVING | after grouping | the aggregates | only precincts with more than one active voter |
Result: Northside alone, with two. Harbor drops to one active voter once Patel is filtered out, and Westgate had one to begin with.
PostgreSQL 16 Documentation — SQL Language — the HAVING clause
Error analysis
A first attempt at counting voters per precinct. It looks reasonable and it will not run.
Annotate
The error message names the offending column, which makes this one of the friendlier SQL errors once you know what it is telling you.
Elimination
Counting voters and also showing a name.
Eliminate the wrong options
Which one runs?
Survives elimination: A
Why: Every non-aggregated column in the SELECT list has to appear in the GROUP BY. That single rule explains why the second option fails, and the evaluation order explains the fourth.
Pattern
Five questions, in this order, and the query writes itself.
SQLBolt — interactive SQL lessons — lessons 6 and 7 drill exactly this
Check
Solve it on paper before you click.
Check your understanding
You want precincts where more than one voter registered after 2015. Where does each condition go?
Answer: A
Why: The date is a property of one row, so it is judged before grouping. The count only exists once the groups are formed, so it is judged after. That is exactly the split between the two clauses.
Section
Section 4
Concept
An inner join keeps only pairs that matched. A left join keeps every row from the left table, filling the right-hand columns with NULL where nothing matched.
Which one you want depends entirely on whether an unmatched row is a row you still care about.
PostgreSQL Documentation — Table Expressions and Joined Tables — the full set of join types
Picture it
Two circles, one condition, two very different answers.
Figure (svg): Two Venn style diagrams comparing an inner join which keeps only the overlap against a left join which keeps all of the left circle
Worked example
Here an unmatched voter is not wanted, so an inner join is correct.
Name both tables and give them short aliases
Why: Aliases make the join condition readable and are required once a column name appears in both tables.
Write the join condition on the foreign key
Why: The ballots table points at voters through voter_id, so that is the condition.
Filter to the one election
Why: This is a row-level condition, so it belongs in WHERE.
SELECT v.last_name, b.method
FROM voters v
JOIN ballots b ON b.voter_id = v.voter_id
WHERE b.election_id = 7
ORDER BY v.last_name;| last_name | method |
|---|---|
| Nakamura | |
| Osei | |
| Reyes | in-person |
Verify: against the ballot rows
Why: Election 7 has ballots 1, 2 and 3, belonging to voters 101, 102 and 103. Three rows, and the surnames match. Patel and Boateng are absent because they cast no ballot.
Picture it
Follow one unmatched voter through the join.
Figure (svg): Four boxes tracing a left join, showing every voter kept, matched voters gaining ballot data, unmatched voters gaining nulls, and a filter on null isolating them
Which is fine when you want voters who voted, and disastrous when the question was who did not.
Prediction
Who did NOT cast a ballot in election 7?
Predict first
Which approach finds them?
Correct: A left join, then keep the NULL rows.
Why: An inner join has already removed the voters you are looking for, so no WHERE clause can bring them back. The left join keeps them with NULL ballot columns, and testing for NULL is what isolates them. This shape is called an anti-join and it appears on nearly every database assignment.
Worked example
Every active voter who has no ballot in election 7.
Left join so nobody is dropped
Why: The join must keep all voters, so voters goes on the left.
Put the election filter in the join condition, not in WHERE
Why: This is the subtle part. In WHERE it would run after the join and remove the NULL rows you are trying to keep.
Keep only the rows that found no match
Why: A NULL primary key on the right side can only mean the join matched nothing.
SELECT v.last_name
FROM voters v
LEFT JOIN ballots b
ON b.voter_id = v.voter_id AND b.election_id = 7
WHERE b.ballot_id IS NULL
AND v.status = 'active'
ORDER BY v.last_name;| last_name | why |
|---|---|
| Boateng | active, and no ballot in election 7 |
Verify: by listing who was excluded and why
Why: Reyes, Osei and Nakamura all have ballots in election 7. Patel has none, but is inactive so the status filter removes him. That leaves Boateng, and one row is the right answer.
Picture it
Same query, one clause moved, completely different answer.
Figure (svg): Two bars comparing a filter placed in the ON clause which returns the intended rows against the same filter in the WHERE clause which returns none
The rule: conditions on the right-hand table of a left join belong in the ON clause. Conditions on the left-hand table belong in WHERE.
Trap
The anti-join, with the election test moved.
Annotate
Get zero rows and no error
Why: The database is perfectly happy. It just quietly turned the left join back into an inner join.
Keep the right-hand condition inside the join.
LEFT JOIN ballots b
ON b.voter_id = v.voter_id AND b.election_id = 7
WHERE b.ballot_id IS NULL| voter | matched a ballot in election 7? | b.ballot_id | survives the WHERE |
|---|---|---|---|
| Reyes | yes, ballot 1 | 1 | no |
| Osei | yes, ballot 2 | 2 | no |
| Nakamura | yes, ballot 3 | 3 | no |
| Patel | no | NULL | yes |
| Boateng | no | NULL | yes |
Now the filter shapes what counts as a match
Why: Rather than filtering the result after the fact.
Test it by removing the IS NULL line
Why: You should see every voter, with NULLs for the ones who did not vote in election 7. If you do not, the join is still behaving as an inner one.
Section
Section 5
Concept
NULL is not zero and not an empty string. It is the absence of a value, and any comparison with it produces neither true nor false.
A WHERE clause keeps a row only when the test comes out true. Neither true nor false is not true, so the row goes.
PostgreSQL 16 Documentation — SQL Language — Comparison Functions and Operators, and the three-valued logic
Picture it
SQL comparisons have a third answer that most languages do not.
Figure (svg): Three boxes showing that one equals one is true, one equals two is false, and null equals null is null rather than true
Two truths and a lie
The precincts table has a county column that is NULL for one row.
Eliminate the wrong options
Which is true?
Survives elimination: A
Why: An inequality against NULL is also NULL, so the row is dropped by a test that reads as though it should keep it. This is the most common source of quietly missing rows in a report.
Fill the middle
Find every precinct whose county has not been recorded.
Fill in the blanks
SELECT name FROM precincts WHERE county IS NULL;
Why: IS NULL is dedicated syntax precisely because the equality operator cannot do this job. Its partner is IS NOT NULL, and together they are the only reliable way to test for absence.
Section
Section 6
Concept
INSERT adds rows, UPDATE changes them, DELETE removes them. All three take a WHERE clause, and two of them are catastrophic without one.
INSERT INTO voters (voter_id, first_name, last_name, precinct_id, status)
VALUES (106, 'Femi', 'Adeyemi', 3, 'active');
UPDATE voters SET status = 'inactive' WHERE voter_id = 104;
DELETE FROM ballots WHERE ballot_id = 4;| statement | rows affected here | rows affected with no WHERE |
|---|---|---|
| INSERT | 1 | not applicable |
| UPDATE | 1 | every row in the table |
| DELETE | 1 | every row in the table |
SQLite — Query Language Documentation — the syntax diagrams for all three
Picture it
Same statement, one line missing.
Figure (svg): Two bars comparing an update with a where clause affecting one row against the same update without one affecting all five
The habit that prevents it: write the statement as a SELECT first, check the row count, then change the first word.
Worked example
Mark voter 104 inactive, without risking the rest of the table.
Write it as a SELECT first
Why: Same FROM, same WHERE. You are checking which rows the statement will touch.
Read the row count
Why: One row. That is the confirmation that the WHERE clause is doing what you meant.
Only now turn it into an UPDATE
Why: The WHERE clause is already proven, so nothing about the risky version is being guessed.
-- step 1: prove the WHERE clause
SELECT * FROM voters WHERE voter_id = 104;
-- step 2: same WHERE, now as an update
UPDATE voters SET status = 'inactive' WHERE voter_id = 104;| step | what you see | what it proves |
|---|---|---|
| the SELECT | 1 row, Dev Patel | the WHERE clause matches exactly one voter |
| the UPDATE | UPDATE 1 | exactly one row changed |
| a second SELECT | status is inactive | the change landed |
Verify: by selecting the row again
Why: If the reported count is anything other than one, roll back before doing anything else.
Picture it
Six lines. The two counts are the only things you actually read.
Figure (svg): A psql session opening a transaction, running a select that returns one row, running an update that reports one row changed, and committing
If the UPDATE had said 2, the next word typed would be ROLLBACK and nothing would have happened.
Socratic
A minute on this before moving on.
Discussion prompt
An UPDATE with no WHERE clause is almost always a mistake. Why do databases run it anyway?
Hint: What legitimate reason might someone have for updating every row?
Answer:
Because it is not always a mistake. Setting a column on an entire table is a legitimate operation, and the database cannot read intent.
The protection is meant to come from elsewhere: run inside a transaction so you can roll back, and use a client configured to require confirmation.
Which means the safety is your habit, not the tool's feature.
PostgreSQL 16 Documentation — SQL Language — Transactions
Concept
Wrap a change in a transaction and nothing is permanent until you commit. If the row count surprises you, roll back and nothing happened.
BEGIN;
UPDATE voters SET status = 'inactive' WHERE precinct_id = 2;
-- reports: UPDATE 2
-- expected 1? then:
ROLLBACK;
-- happy? then:
COMMIT;| command | effect | reversible |
|---|---|---|
| BEGIN | opens a transaction | - |
| UPDATE | changes rows, visible only to you | yes |
| ROLLBACK | discards everything since BEGIN | - |
| COMMIT | makes it permanent and visible | no |
This is the single most useful habit in this whole deck, and it takes six extra keystrokes.
PostgreSQL 16 Documentation — SQL Language — Transactions
Ranking
Five steps that make a destructive statement survivable.
Put in order
Why: The transaction opens first so that everything after it is reversible, including the SELECT. Comparing the two counts is the actual check; without it the transaction is just ceremony.
Section
Section 7
Concept
A subquery runs first and hands its result to the outer query. The commonest form tests membership: is this value in that list?
Use it when you need the other table only to decide which rows to keep, not to show any of its columns.
PostgreSQL 16 Documentation — SQL Language — Subquery Expressions
Worked example
List every voter registered in Anne Arundel county. The county lives in precincts; the voters live in voters.
Write the inner query first, on its own
Why: It should return a list of precinct ids and nothing else. Run it separately before nesting it.
Check what it returns
Why: Precincts 1 and 2 are Anne Arundel; 3 is Howard.
Nest it behind IN
Why: The outer query now keeps voters whose precinct is in that list.
SELECT last_name
FROM voters
WHERE precinct_id IN (
SELECT precinct_id FROM precincts
WHERE county = 'Anne Arundel'
)
ORDER BY last_name;| step | result |
|---|---|
| inner query alone | 1, 2 |
| outer query | Nakamura, Osei, Patel, Reyes |
| excluded | Boateng, who is in Westgate |
Verify: by counting from the sample data
Why: Precincts 1 and 2 hold four of the five voters, so four rows. Only Boateng, in precinct 3, is missing.
Picture it
A join, an IN subquery and an EXISTS subquery can all answer this. They differ in what comes back.
Figure (svg): A vertical flow comparing join, IN and EXISTS as three ways of relating two tables
Join when you want the other table's columns. IN when you want a membership test against a short list. EXISTS when you only care whether a match exists.
Concept
A correlated subquery refers to the outer row. EXISTS stops as soon as it finds one match, which makes it the natural way to express 'has at least one'.
SELECT v.last_name
FROM voters v
WHERE NOT EXISTS (
SELECT 1 FROM ballots b
WHERE b.voter_id = v.voter_id
);| voter | does a matching ballot exist? | NOT EXISTS | in the result |
|---|---|---|---|
| Reyes | yes, two of them | false | no |
| Osei | yes | false | no |
| Nakamura | yes | false | no |
| Patel | no | true | yes |
| Boateng | no | true | yes |
This is the anti-join from section four, written a second way. Both are correct; teams differ on which they prefer.
Trap
The same question, written with NOT IN.
Annotate
Get an empty result that looks like a data problem
Why: Hours get lost here, because the SQL reads perfectly and the data looks fine.
Use NOT EXISTS, or add an explicit NULL guard.
SELECT last_name FROM voters v
WHERE NOT EXISTS (
SELECT 1 FROM ballots b WHERE b.voter_id = v.voter_id
);| form | with a NULL in the subquery | verdict |
|---|---|---|
| NOT IN | returns zero rows | silently wrong |
| NOT EXISTS | returns the right rows | safe |
| LEFT JOIN with IS NULL | returns the right rows | safe |
Prefer NOT EXISTS by default
Why: It behaves the same whether or not NULLs are present, which removes an entire class of bug you would otherwise have to remember.
Comparison
Fill in what each one gives you.
Comparison matrix
| form | returns the other table's columns? | safe with NULLs? |
|---|---|---|
| JOIN | yes | yes |
| IN subquery | no | yes for IN, no for NOT IN |
| EXISTS subquery | no | yes |
| LEFT JOIN with IS NULL | yes, as NULLs | yes |
If you only ever remember one thing from this section: NOT IN is the one with the trap.
Explain it
Two sentences, out loud.
Discussion prompt
When would you reach for a subquery rather than a join?
Hint: What happens to a join when one voter has two ballots?
Answer:
When the second table is only there to decide which rows to keep, and none of its columns need to appear in the answer.
A join would work too, but it can duplicate rows when the match is one-to-many, and then you need DISTINCT to clean up after yourself. A subquery never duplicates.
PostgreSQL 16 Documentation — SQL Language — Subquery Expressions
Commit first
Answer, then rate your confidence honestly.
Predict first
Voter 101 cast two ballots. How many rows does SELECT v.last_name FROM voters v JOIN ballots b ON b.voter_id = v.voter_id WHERE v.voter_id = 101 return?
Correct: 2.
Why: A join produces one row per matching pair, so a voter with two ballots appears twice. This is the duplication that makes people reach for DISTINCT, and it is the reason a subquery is sometimes the cleaner tool.
Pattern
Every question on this assignment fits this. Answer the five in order and the query is written.
| the question says | the clause it becomes |
|---|---|
| 'list', 'show', 'which' | SELECT, and the columns named |
| a noun that is a table | FROM |
| 'where', 'only', 'active', 'after 2015' | WHERE |
| 'per', 'each', 'by precinct' | GROUP BY |
| 'more than', 'at least', with a count | HAVING |
| 'who did not', 'missing', 'never' | LEFT JOIN plus IS NULL |
| 'in alphabetical order', 'highest first' | ORDER BY |
SQLBolt — interactive SQL lessons — the same progression, with an interactive database to practise on
Discrimination
Naming the shape before writing anything is most of the work.
Sort into buckets
Which clause or pattern answers each question?
Check
Solve it on paper before you click.
Check your understanding
Which query lists voters who have never cast any ballot?
Answer: A
Why: The left join keeps every voter, unmatched ones get NULL in the ballot columns, and the IS NULL test isolates exactly those. Against the sample data it returns Patel and Boateng.
Check
Solve it on paper before you click.
Check your understanding
SELECT COUNT(*) FROM voters v JOIN ballots b ON b.voter_id = v.voter_id; against the sample data returns what?
Answer: A
Why: The join produces one row per ballot that has a matching voter, and there are four ballots, all with valid voters. Voter 101 appears twice because she cast two ballots.
Exit ticket
One honest answer, and it decides where the next session starts.
Predict first
Which of these is still murkiest?
Correct: Whichever you named is where we start.
Why: All five are separable and all five are drillable against this same tiny database, which is the point of keeping it to five voters: every answer can be checked by hand in under a minute.
Connect it up
Twenty minutes, on paper. This is worth more than re-reading the notes.
Draw it
Draw the four tables and their columns from memory. Draw an arrow for every foreign key. Then write, beside each arrow, one English question that would need that join.
The arrows you could not place are the ones to look up. Bring the page.
Recap
Six sections, and the anti-join in section four is the one that shows up on every database assignment ever set.
| query | rows against the sample data |
|---|---|
| active voters, alphabetical | 4 |
| voters per precinct | 3 groups: 2, 2, 1 |
| precincts with more than one active voter | 1: Northside |
| voters in election 7 | 3 |
| active voters not in election 7 | 1: Boateng |
| voters joined to ballots | 4 |
PostgreSQL 16 Documentation — SQL Language — every statement in this deck, with the full syntax
Want this taught 1-on-1? Alexander tutors IT Support & Networking — $55/session, free consultation.