Databases & SQL

Open a spreadsheet of ten thousand customer records and everything looks fine. Open it while four colleagues have it open too, let two of them edit row 3417 at the same moment, and then pull the power cord. One of those edits is gone forever, and nobody can tell you which one. A database is the machinery built specifically so that this cannot happen. Understanding what that machinery does, and what it costs, is the point of this course.

The big picture

A database is an organized collection of related data that many people can search, update, and share at the same time without corrupting it. The software that stores that data and answers questions about it is a database management system (DBMS). We need databases because plain files break down the moment data grows large, is shared by many users, or must never be lost, and this course teaches the most important kind: the relational database, queried with the language SQL.

Scale is what forces the issue. A shopping list in a text file is a database in the loose sense, and nothing bad ever happens to it. A bank with forty million accounts, ten thousand simultaneous tellers, and a legal obligation never to lose a cent needs something else entirely. Every idea in this course exists because of that second situation.

Key idea: a database is the data; a DBMS is the software that guards and serves it.

Database versus DBMS

These two words are easy to confuse, so pin them down first. Think of a warehouse: the database is the goods stored inside, and the DBMS is the staff and machinery that shelve items, fetch them on request, and stop anyone from stealing or damaging them. Popular relational DBMS products include PostgreSQL, MySQL, SQLite, Oracle, and SQL Server. When you look up a bank balance, buy a train ticket, or scroll a social feed, a DBMS is fetching the data behind the scenes.

Key idea: you design and query a database, but it is the DBMS that actually does the work.

Why not just use files?

You could keep data in a spreadsheet or a plain text file, and for a small personal list that is fine. But as soon as data is large, shared, or must never be corrupted, flat files fall apart. A DBMS solves problems that files cannot:

• Concurrent access: many users can read and write at the same time without clobbering each other's changes. Imagine two clerks editing the same text file at once; the last one to save silently erases the other's work.
• Integrity: rules (called constraints) reject bad data, such as a negative age or a duplicate ID, before it ever gets stored.
• Efficient querying: you ask what you want and the DBMS figures out how to find it fast, even across millions of rows.
• Durability and recovery: committed data survives a crash, and backups can restore it.
• Security: access can be granted per user and per table, so a cashier sees sales but not payroll.

Key idea: a DBMS gives you concurrency, integrity, speed, durability, and security that files cannot.

Watching a flat file fail

The concurrency problem is worth watching in slow motion, because the failure is completely silent. Two clerks open the same customers.csv to correct different fields on the same customer:

time   Clerk A                          Clerk B
-----  ------------------------------   ------------------------------
10:00  opens customers.csv
10:01                                   opens customers.csv
10:02  edits row 88: phone -> 555-0143
10:03                                   edits row 88: city  -> Toledo
10:04  saves the whole file
10:05                                   saves the whole file

file on disk now holds:  row 88  city=Toledo   phone=(the old value)

Clerk A's phone correction is gone. No error appeared, no log recorded it, and the file is perfectly valid; it is simply wrong. This is the lost update problem, the clearest single reason a shared file is not a database. A DBMS makes each change a small isolated unit touching only the row it needs, so Clerk B's save cannot quietly erase Clerk A's.

Key idea: two writers on one file silently destroy each other's work, which is exactly the failure a DBMS is engineered to prevent.

What happens when you run a query

A DBMS is not one program but a stack of them. When you hand it a line of SQL, four stages run in order:

1. Parser. Checks that the SQL is syntactically legal and that the tables and columns you named really exist, turning your text into a tree.
2. Optimizer (also called the query planner). Considers several different strategies that would all produce the same answer, and estimates the cost of each using statistics it keeps about your data. This is the stage that makes SQL declarative.
3. Executor. Runs the winning plan, pulling rows through the operators the optimizer chose.
4. Storage engine. Reads and writes actual pages of bytes on disk, manages the memory cache, and writes the log that makes crash recovery possible.

Concretely, you write SELECT name FROM employees WHERE id = 7;. The parser confirms employees has both name and id. The optimizer notices that id is the primary key and therefore already indexed, so rather than read four million rows it plans a single index lookup. The executor performs it, and the storage engine fetches the one page of disk holding row 7. You wrote nine words; the system did all the deciding.

Key idea: the DBMS parses your SQL, plans the cheapest way to answer it, executes that plan, and moves the bytes, which is why one short statement can beat hand-written file code.

Kinds of databases

The most common and most important family is the relational database, which organizes data into tables and is queried with SQL (Structured Query Language). This whole course is about relational databases and SQL, because they run the majority of the world's business systems. You may also hear about NoSQL databases, an umbrella term for four broad styles:

Style	Stores data as	Example use
Relational (SQL)	Tables of rows and columns linked by keys	Banking, orders, most business apps
Document	Self-contained JSON-like documents	Product catalogs with varied fields
Key-value	A giant dictionary of key to value	Caching, session storage
Graph	Nodes and the edges between them	Social networks, recommendations

NoSQL systems trade some relational guarantees for flexibility or scale. They are useful, but the relational model is the foundation every data professional needs first.

Key idea: relational is the default and the foundation; NoSQL styles trade guarantees for flexibility or scale.

Declarative, not procedural

A defining feature of SQL is that it is declarative. You describe the result you want, not the step-by-step algorithm to compute it. Ordering at a restaurant is declarative: you say "a medium steak" and the kitchen decides how to cook it. Writing out every pan, flame, and timer yourself would be procedural. In SQL you write "give me all customers in Ohio sorted by last name," and the DBMS's query optimizer chooses the fastest way to produce that. This is a big reason SQL has lasted for decades: your instructions stay simple while the engine underneath gets smarter.

Key idea: in SQL you state the goal and the optimizer picks the method.

A tiny example

Imagine a small library. Instead of one giant file, a relational database splits the information into focused tables: a table of books, a table of members, and a table of loans that records who borrowed what. Each table holds one kind of thing, and the tables are linked so the DBMS can answer "which members currently have an overdue book?" with a single query. Learning to design those tables and write those queries is exactly what the rest of this course teaches.

You can run that idea today with no setup at all. SQLite is a complete relational database that lives in one ordinary file, and the sqlite3 tool ships with Python. Here is the whole library, working:

-- SQLite dialect. Save as library.sql, then run:  sqlite3 lib.db < library.sql
CREATE TABLE members (member_id INTEGER PRIMARY KEY, name TEXT NOT NULL);
CREATE TABLE books   (book_id   INTEGER PRIMARY KEY, title TEXT NOT NULL);
CREATE TABLE loans   (loan_id   INTEGER PRIMARY KEY,
                      member_id INTEGER REFERENCES members(member_id),
                      book_id   INTEGER REFERENCES books(book_id),
                      due_date  TEXT);

INSERT INTO members VALUES (1, 'Ren'), (2, 'Sol');
INSERT INTO books   VALUES (10, 'Dune'), (11, 'Kindred');
INSERT INTO loans   VALUES (100, 1, 10, '2026-01-15'),
                           (101, 2, 11, '2026-09-01');

SELECT m.name, b.title, l.due_date
FROM loans AS l
JOIN members AS m ON l.member_id = m.member_id
JOIN books   AS b ON l.book_id   = b.book_id
WHERE l.due_date < '2026-08-03';

The result, taking today to be 3 August 2026:

name  title  due_date
----  -----  ----------
Ren   Dune   2026-01-15

One statement, three tables, and an exact answer to "who has an overdue book?" Sol's loan is not due until September, so she is correctly left out. Every piece of that query is taught in the lessons ahead.

Key idea: splitting data into linked tables lets one short query answer a question that would take a page of file-reading code.

Why SQL is still here

The relational model was proposed by Edgar F. Codd, an IBM researcher, in a 1970 paper arguing that data should be stored as plain tables and reached by describing what you want rather than by chasing pointers. IBM built a prototype called System R to test the idea, and the query language written for it, SEQUEL, was later renamed SQL. The first ANSI standard appeared in 1986, and the language has been revised every few years since.

That standard is why a SELECT you learn here works in PostgreSQL, SQLite, MySQL, Oracle, and SQL Server. It is also only a partial truth: every engine diverges in the corners. Row limiting is the classic case. The standard spells it FETCH FIRST 10 ROWS ONLY; PostgreSQL, MySQL, and SQLite accept LIMIT 10; SQL Server writes SELECT TOP 10. This course writes PostgreSQL syntax by default and flags differences where they bite.

Key idea: SQL descends from Codd's 1970 relational model by way of IBM's System R, and the shared standard is real but every engine speaks its own dialect.

Where people get stuck

• "A database is just a big spreadsheet." A spreadsheet has no enforced rules, no safe multi-user editing, and no guaranteed recovery. A DBMS provides all three.
• "Database and DBMS mean the same thing." The database is the stored data; the DBMS is the software managing it. PostgreSQL is a DBMS; your library data is a database.
• "SQL is a general programming language like Python." SQL is a declarative query language focused on describing data you want, not a general-purpose procedural language.
• "NoSQL means no SQL and is always more modern or better." NoSQL means "not only SQL"; these systems drop some guarantees on purpose, and relational databases remain the right choice for most structured, transactional data.
• "SQL is one language, so any query runs anywhere." The core is standard, but row limiting, string functions, date handling, and auto-incrementing keys all differ. Always know which dialect you are writing in.
• Confusing the DBMS with the machine it runs on. PostgreSQL is software; it can run on your laptop, in a container, or on a rented server. "The database is down" nearly always means a process or a network, not the data.
• Assuming the DBMS makes backups for you. Durability guarantees a committed change survives a crash of that server. It does not protect you from a dropped table, a wiped disk, or a fire. Backups remain a separate decision you have to make.

Recap

• A database is organized, shared, persistent data; a DBMS is the software that stores and serves it.
• Databases beat flat files for concurrency, integrity, efficient querying, durability, and security; the lost-update trace shows why a shared file fails silently.
• Running a query means parser, then optimizer, then executor, then storage engine, and the optimizer is what makes SQL declarative.
• The relational model (tables queried with SQL) is the dominant family and the focus of this course; document, key-value, and graph stores trade guarantees for flexibility or scale.
• SQL is declarative: you state the desired result and the optimizer chooses how to compute it.
• SQL grew from Codd's 1970 paper through System R to an ANSI standard, and dialect differences such as LIMIT versus TOP versus FETCH FIRST are real.

Sources

1. Codd, E. F. (1970). A relational model of data for large shared data banks. Communications of the ACM, 13(6), 377-387. dl.acm.org
2. Silberschatz, A., Korth, H. F., & Sudarshan, S. (2019). Introduction. In Database system concepts (7th ed., ch. 1). McGraw-Hill. find source ↗
3. PostgreSQL Global Development Group. (n.d.). Chapter 1. Getting started. PostgreSQL documentation. postgresql.org
4. SQLite Consortium. (n.d.). SQLite in 5 minutes or less. SQLite documentation. sqlite.org
5. SQLite Consortium. (n.d.). Appropriate uses for SQLite. SQLite documentation. sqlite.org
6. Pavlo, A. (2024). Course schedule and lecture notes. 15-445/645 Introduction to Database Systems, Carnegie Mellon University. 15445.courses.cs.cmu.edu
7. Madden, S., & Balakrishnan, H. (2010). Lecture notes. 6.830 Database Systems, MIT OpenCourseWare. ocw.mit.edu

Codd's 1970 paper proposed something that sounds too plain to be revolutionary: throw away the pointers, the hierarchies, and the hand-written navigation code, and store everything as a grid of rows and columns. Every SQL database you will ever touch is a working implementation of that one sentence. This lesson takes the grid apart and finds the surprisingly sharp rules hiding inside it.

The big picture

The relational model, introduced by Edgar F. Codd in 1970, is the theory beneath every SQL database. Its central idea is beautifully simple: store all data in tables. Each table is a grid where every row is one thing and every column is one property of that thing. Understanding this grid, and the special rules that govern it, is the foundation for everything else in the course.

Key idea: a relational database is nothing more than well-organized tables plus the rules that keep them honest.

Anatomy of a table

A table (formally a relation) is a grid. Each row (a tuple) represents one entity or fact; each column (an attribute) represents one property. Think of a class roster printed on paper: the header line names the columns, and each student gets one line below. Here is a students table:

student_id	first_name	last_name	major	gpa
1	Ada	Nguyen	Computer Science	3.8
2	Diego	Okafor	Biology	3.5
3	Priya	Santos	Computer Science	3.9

Some vocabulary that maps everyday words to the formal terms:

• A column / attribute is a named property, such as gpa. Every value in that column is the same kind of thing (all GPAs, never a name).
• A row / tuple / record is one complete entry, such as the student Ada Nguyen.
• The domain of a column is the set of allowed values and their type. Think of it as the "allowed answers" for that column: gpa is a decimal number, last_name is text.
• The number of columns is the table's degree; the number of rows is its cardinality. The table above has degree 5 and cardinality 3.

Key idea: a row is one entity, a column is one property, and a domain is the set of values that property may take.

The schema versus the data

It helps to separate two layers. The schema is the fixed blueprint: the table's name, its columns, and each column's type. It is like the printed column headers and rules on a blank form. The data (the rows) is what gets filled in and changes constantly. The schema rarely changes; the rows change all day long.

Key idea: the schema is the unchanging blueprint; the rows are the changing contents.

Data types

Every column has a data type that restricts what it can hold, acting like a labeled bin that only accepts one shape of object. Common SQL types include:

Type	Holds	Example
INTEGER	Whole numbers	42
DECIMAL(p,s) / NUMERIC	Exact fractional numbers, ideal for money	19.99
VARCHAR(n)	Variable-length text up to n characters	Nguyen
DATE	A calendar date	2026-07-11
BOOLEAN	True or false	true

Types are the first line of defense for data quality: you cannot accidentally store the word "hello" in an INTEGER column, so a whole class of mistakes is impossible.

Key idea: a column's type quietly rejects the wrong kind of value before it can ever be stored.

Types in practice, and the money trap

Watch the type system actually refuse something. In PostgreSQL:

CREATE TABLE t (id INTEGER, price NUMERIC(10,2));
INSERT INTO t VALUES (1, 19.99);      -- accepted
INSERT INTO t VALUES ('hello', 5);    -- rejected

ERROR:  invalid input syntax for type integer: "hello"
LINE 1: INSERT INTO t VALUES ('hello', 5);

That rejection is the type system earning its keep. Be aware that SQLite behaves differently: it uses type affinity rather than strict types, so a column declared INTEGER will cheerfully store the text 'hello' unless the table is declared STRICT. If you practise on SQLite, remember that this leniency is a SQLite design decision, not how SQL normally works.

The type choice that causes the most real damage is money. Floating-point columns store binary fractions, and 0.1 has no exact binary representation, so the errors are there from the first row:

-- PostgreSQL
SELECT 0.1::float8   + 0.2::float8   = 0.3::float8;     -- f  (false)
SELECT 0.1::numeric  + 0.2::numeric  = 0.3::numeric;    -- t  (true)

Store money, quantities that must reconcile, and anything a person will audit in NUMERIC or DECIMAL, which hold exact decimal digits. Reserve REAL and DOUBLE PRECISION for measurements where a relative error of one part in ten thousand billion genuinely does not matter, such as a sensor reading.

Key idea: types reject bad values at the door, SQLite's affinity rules are the loose exception, and money belongs in NUMERIC, never in floating point.

The special value NULL

Sometimes a value is unknown or does not apply. SQL represents this with NULL, a special marker meaning "no value here." A blank on a paper form is a good analogy: the blank does not mean zero and does not mean the empty word, it means "nothing was written." NULL is not the number 0 and not an empty string; it is the absence of a value. It behaves unusually: any comparison with NULL using = yields "unknown," not true, which is why SQL provides the special tests IS NULL and IS NOT NULL. You will see the consequences of NULL throughout the course, especially in filtering and joins.

Key idea: NULL means "unknown or missing," and you test for it with IS NULL, never with the equals sign.

Three-valued logic, worked out

NULL does not sit quietly in a corner; it changes the arithmetic of truth. Every SQL condition evaluates to true, false, or unknown, and a WHERE clause keeps a row only when the condition comes out true. Unknown is not the same thing as false, but in a WHERE clause it has the same effect: the row is dropped.

Here are the truth tables, with U standing for unknown:

  AND | T   F   U           OR  | T   F   U           NOT
  ----+-----------          ----+-----------          ---------
   T  | T   F   U            T  | T   T   T           NOT T = F
   F  | F   F   F            F  | T   F   U           NOT F = T
   U  | U   F   U            U  | T   U   U           NOT U = U

Two entries surprise almost everyone. F AND U is false, not unknown, because one false operand settles the answer whatever the unknown turns out to be. Symmetrically, T OR U is true.

Now make it concrete. Add a bonus column where two employees have no recorded bonus:

name	bonus
Ada	5000
Diego	NULL
Priya	2000
Mateo	NULL

Ask for everyone whose bonus is not 5000:

SELECT name FROM employees WHERE bonus <> 5000;

Most people predict Diego, Priya, and Mateo. Evaluate it row by row instead:

Ada     5000 <> 5000   -> FALSE     dropped
Diego   NULL <> 5000   -> UNKNOWN   dropped
Priya   2000 <> 5000   -> TRUE      kept
Mateo   NULL <> 5000   -> UNKNOWN   dropped

result:  Priya

One row comes back. The two employees with no bonus vanish from a query that was plainly meant to include them, and nothing warns you. This is the most common NULL bug in production SQL. The explicit fix is to say what you mean about missing values:

SELECT name FROM employees WHERE bonus <> 5000 OR bonus IS NULL;
-- Diego, Priya, Mateo

PostgreSQL also offers IS DISTINCT FROM, which compares NULL as if it were an ordinary value: WHERE bonus IS DISTINCT FROM 5000 gives the same three rows in one clause. SQLite spells it bonus IS NOT 5000; MySQL has the null-safe equality operator <=>.

Two more NULL rules worth memorising now. First, aggregates skip NULLs, so COUNT(bonus) is 2 while COUNT(*) is 4, and AVG(bonus) divides by 2, not 4. Second, GROUP BY and DISTINCT deliberately break the rule and treat all NULLs as equal to each other, so a grouped query produces exactly one NULL group. That inconsistency is genuinely in the standard, and knowing it saves hours.

Key idea: SQL logic has three values, an unknown result silently drops the row, and any comparison against NULL yields unknown unless you use IS NULL or IS DISTINCT FROM.

Sets, bags, and honest SQL

The relational model says a relation is a set, so it can never contain two identical tuples. Real SQL quietly disagrees: a SQL table is a multiset, also called a bag, and it will hold duplicate rows all day unless a key or a UNIQUE constraint stops it.

CREATE TABLE colors (name TEXT);
INSERT INTO colors VALUES ('red'), ('red');

SELECT * FROM colors;            -- 2 rows, both 'red'
SELECT DISTINCT * FROM colors;   -- 1 row

This is a pragmatic choice rather than a mistake. Removing duplicates on every query would mean sorting or hashing the whole result every time, so SQL only pays that cost when you ask for it with DISTINCT, or with UNION, which de-duplicates while UNION ALL does not. The practical consequence is important: "every row is uniquely identifiable" is a discipline you impose with a primary key, not a guarantee the engine hands you.

Key idea: a SQL table is a bag, not a set, so duplicate rows are possible until you declare a key.

Two key properties of relations

In the pure relational model, a table is a set of rows, so two properties follow:

1. Row order carries no meaning. The database is free to store rows in any order, and if you want output sorted you must ask for it explicitly with ORDER BY. Do not assume the "first" row means anything.
2. Each row should be uniquely identifiable. Because a set has no duplicates, every row needs something that tells it apart, which leads directly to the idea of a key, the subject of the next lesson.

Key idea: because a table is a set, its rows have no built-in order and each one must be distinguishable.

Where people get stuck

• "NULL is the same as 0 or an empty string." NULL is the absence of any value. Zero is a known number and the empty string is a known (empty) text; NULL is neither.
• "Rows come back in the order I inserted them." Without ORDER BY, the order is not guaranteed and can change; a relation is an unordered collection.
• "A column can hold any kind of data." Each column has one data type, and values that do not fit that type are rejected - except in SQLite, whose type affinity accepts them unless the table is STRICT.
• "You can compare to NULL with = NULL." Any = comparison with NULL yields unknown, so you must use IS NULL or IS NOT NULL.
• Forgetting that unknown drops the row. The bonus <> 5000 query above loses exactly the employees you were trying to find. Whenever a column is nullable, decide explicitly what should happen to its NULLs.
• Storing money in a floating-point column. Cents drift, totals stop reconciling, and the bug appears only after months of rows. Use NUMERIC or DECIMAL.
• Assuming a table cannot hold duplicate rows. A SQL table is a bag; without a primary key or UNIQUE constraint, two identical rows are perfectly legal and impossible to tell apart afterwards.
• Expecting NULL to behave consistently. It does not: comparisons treat NULLs as unknown, while GROUP BY and DISTINCT treat them as equal. Both behaviours are standard and you have to hold both in your head.

Recap

• The relational model stores all data in tables of rows (tuples) and columns (attributes), with degree counting columns and cardinality counting rows.
• A domain is the set of allowed values for a column; the schema is the blueprint and the rows are the data.
• Every column has a data type that blocks the wrong kind of value, and exact money belongs in NUMERIC rather than floating point.
• NULL marks a missing value, is distinct from 0 and empty text, and is tested with IS NULL.
• SQL uses three-valued logic; an unknown condition silently drops the row, which is the source of the most common NULL bug.
• A SQL table is really a multiset, so duplicate rows are possible and row order is never guaranteed; keys are what make rows uniquely identifiable.

Sources

1. Codd, E. F. (1970). A relational model of data for large shared data banks. Communications of the ACM, 13(6), 377-387. dl.acm.org
2. Silberschatz, A., Korth, H. F., & Sudarshan, S. (2019). Introduction to the relational model. In Database system concepts (7th ed., ch. 2). McGraw-Hill. find source ↗
3. PostgreSQL Global Development Group. (n.d.). Chapter 8. Data types. PostgreSQL documentation. postgresql.org
4. PostgreSQL Global Development Group. (n.d.). 9.2. Comparison functions and operators: IS NULL and IS DISTINCT FROM. PostgreSQL documentation. postgresql.org
5. PostgreSQL Global Development Group. (n.d.). 9.1. Logical operators: three-valued AND, OR, and NOT. PostgreSQL documentation. postgresql.org
6. SQLite Consortium. (n.d.). NULL handling in SQLite. SQLite documentation. sqlite.org
7. SQLite Consortium. (n.d.). Datatypes in SQLite: type affinity and STRICT tables. SQLite documentation. sqlite.org

A company once used employee email addresses as the primary key of its staff table. It worked for two years. Then someone got married, changed her name, asked for a new address, and the update touched fourteen tables, broke three reports, and orphaned a year of expense claims. Choosing keys badly is not a stylistic error; it is the kind of mistake you pay for every day afterwards.

The big picture

The power of the relational model comes from splitting data into focused tables and then linking them. Keys are what make that linking possible and reliable: a primary key uniquely names each row, and a foreign key in one table points at the primary key of another. Get keys right and your data stays consistent no matter how it grows.

Key idea: primary keys identify rows, foreign keys connect tables, and together they hold a relational database together.

Primary keys

A primary key is one or more columns whose value uniquely identifies each row in a table. Think of it as a Social Security number for a row: no two rows may share it, and it can never be blank (NULL). In the students table, student_id is the natural primary key: every student gets a distinct id, so even two students named John Smith are still told apart.

A column (or set of columns) that could serve as a primary key is a candidate key; you pick one candidate to be the primary key. Keys made of a single column are common, but a composite key spanning several columns is allowed when no single column is unique on its own (for example, a seat is identified by row plus seat number together).

Key idea: a primary key is a row's unique, never-blank identifier, chosen from among the candidate keys.

Natural keys versus surrogate keys

Candidate keys come in two flavours, and picking between them is one of the few design decisions that is genuinely hard to reverse.

• A natural key already exists in the real world and carries meaning: an ISBN, an email address, a country code, a vehicle registration.
• A surrogate key is a meaningless value the database invents purely to identify the row: an auto-incrementing integer or a UUID.

Natural keys are tempting because they save a column and read nicely. They fail for three predictable reasons. They change: an email address, a company name, or a product code gets revised, and because every foreign key stores that value, one edit ripples through every referencing table. They turn out not to be unique: two customers really do share a name and birth date, ISBNs have been reused, and a national ID number is not the guaranteed unique value people assume. And they are wide: a 60-character email copied into five million foreign-key rows costs far more space, and more index depth, than a 4-byte integer.

The professional default is therefore a surrogate primary key plus a UNIQUE constraint on the natural key, which gives you a stable identifier and still forbids duplicates:

CREATE TABLE students (
    student_id  INTEGER      GENERATED ALWAYS AS IDENTITY PRIMARY KEY,  -- surrogate
    email       VARCHAR(255) NOT NULL UNIQUE,                           -- natural key
    first_name  VARCHAR(50)  NOT NULL,
    last_name   VARCHAR(50)  NOT NULL
);

Now a change of email is a one-row UPDATE that no other table notices. PostgreSQL writes the identity column as above; MySQL uses AUTO_INCREMENT, SQLite uses INTEGER PRIMARY KEY, and older PostgreSQL code uses SERIAL.

Key idea: prefer a stable surrogate primary key and enforce the real-world identifier with a UNIQUE constraint, because natural keys change, repeat, and are wide.

Foreign keys link tables

A foreign key is a column in one table that refers to the primary key of another table. This is the glue of a relational database, like writing a friend's phone number in your contacts instead of copying their entire life story. Suppose we add an enrollments table recording which student takes which course:

enrollment_id	student_id	course_id	grade
10	1	CS340	A
11	3	CS340	A
12	2	BIO101	B

Here student_id in enrollments is a foreign key pointing at student_id in students. Row 10 means "student number 1 (Ada) is enrolled." Because the id is stored once in students and merely referenced elsewhere, the student's name lives in exactly one place. Update it there and every reference is instantly correct.

Key idea: a foreign key stores a reference, not a copy, so each fact lives in exactly one table.

Referential integrity

A foreign key enforces referential integrity: the database refuses to store an enrollments row whose student_id does not exist in students. This prevents "orphan" records that point at nothing, the database equivalent of a party invitation mailed to an address where no one lives. The DBMS can also control what happens when a referenced row is deleted:

• Restrict / no action: block the delete while dependents still reference the row.
• Cascade: automatically delete the dependent rows too.
• Set null: keep the dependent rows but set their foreign key to NULL.

Key idea: referential integrity guarantees every foreign key points at a row that really exists.

Worked example: deleting a referenced row

The three delete rules are easiest to understand by running the same delete three times. Start from these rows:

students                          enrollments
student_id  first_name            enrollment_id  student_id  course_id  grade
----------  ----------            -------------  ----------  ---------  -----
    1       Ada                        10             1       CS340       A
    2       Diego                      11             3       CS340       A
    3       Priya                      12             2       BIO101      B

Now run DELETE FROM students WHERE student_id = 1; under each rule.

ON DELETE RESTRICT (or NO ACTION, the default). The delete is refused and both tables are untouched:

ERROR:  update or delete on table "students" violates foreign key
        constraint "enrollments_student_id_fkey" on table "enrollments"
DETAIL:  Key (student_id)=(1) is still referenced from table "enrollments".

ON DELETE CASCADE. Ada's row goes, and so does every enrollment that referenced her. Enrollment 10 disappears:

students                          enrollments
    2       Diego                      11             3       CS340       A
    3       Priya                      12             2       BIO101      B

ON DELETE SET NULL. Ada's row goes, but her enrollment survives with a blank reference:

students                          enrollments
    2       Diego                      10           NULL      CS340       A
    3       Priya                      11             3       CS340       A
                                       12             2       BIO101      B

Read that last result carefully, because it is the interesting one. The database now records that somebody earned an A in CS340 and no longer knows who. That may be exactly right (an anonymised grade record) or exactly wrong (an unbillable order), which is why the rule is a design decision rather than a default you accept without thinking. SET NULL also requires the foreign-key column to be nullable, so it is unavailable when that column is part of the primary key.

Two practical notes. RESTRICT and NO ACTION differ subtly: RESTRICT checks immediately, while NO ACTION waits until the end of the statement, which lets a deferrable constraint tolerate a temporary violation inside a transaction. And in SQLite, foreign keys are not enforced at all unless you turn them on for each connection:

PRAGMA foreign_keys = ON;   -- SQLite: required, and it resets every connection

Key idea: RESTRICT blocks the delete, CASCADE removes the dependents, and SET NULL keeps them but forgets what they pointed at, so choose the rule from what the data means.

Kinds of relationships

Relationships between entities come in three shapes, and recognizing them drives good design:

• One-to-one (1:1): each row in A matches at most one row in B. Example: a person and their single passport record.
• One-to-many (1:N): one row in A matches many in B, but each B row matches one A. Example: one student has many enrollments. This is the most common shape and is implemented by putting the foreign key on the "many" side.
• Many-to-many (M:N): rows in A match many in B and vice versa. Example: students and courses, since a student takes many courses and a course has many students. SQL cannot store this directly; you create a junction table (like enrollments) that holds foreign keys to both sides, turning one M:N relationship into two 1:N relationships.

A quick way to decide: ask "can each side have many of the other?" If only one side can, it is one-to-many and the foreign key goes on the many side. If both can, it is many-to-many and you need a junction table.

Key idea: put the foreign key on the many side of a one-to-many, and use a junction table for many-to-many.

A table that points at itself

A foreign key does not have to reference a different table. A self-referencing foreign key models a hierarchy inside one table, and the reporting chain is the standard case:

CREATE TABLE employees (
    id          INTEGER PRIMARY KEY,
    name        VARCHAR(50) NOT NULL,
    manager_id  INTEGER REFERENCES employees(id)   -- points back at this table
);

id  name    manager_id
--  ------  ----------
 1  Ada         NULL       <- top of the chain, reports to nobody
 2  Diego        1
 3  Priya        1
 4  Mateo        2

To show each person beside their manager, join the table to itself and give the two copies different aliases:

SELECT e.name AS employee, m.name AS manager
FROM employees AS e
LEFT JOIN employees AS m ON e.manager_id = m.id
ORDER BY e.id;

employee  manager
--------  -------
Ada       NULL
Diego     Ada
Priya     Ada
Mateo     Diego

The LEFT JOIN is doing real work here: with a plain INNER JOIN, Ada has no manager to match and would vanish from a report of all employees. Note also that manager_id must be nullable, because the top of any hierarchy has nothing to point at.

Key idea: a self-referencing foreign key stores a hierarchy in one table, and reading it takes a self-join with two aliases plus a LEFT JOIN to keep the root.

Where people get stuck

• "A primary key must be a number." It can be any type, and can even be several columns together (a composite key); it only needs to be unique and never NULL.
• "A foreign key stores a copy of the other row's data." It stores only a reference (the referenced primary key value), so the real data stays in one place.
• "You can store a many-to-many relationship by putting a list in one cell." Lists in a cell break the model; the correct approach is a junction table.
• "A foreign key can point to any value." Referential integrity requires it to match an existing primary key, or be NULL where allowed.
• Using a meaningful business value as the primary key. Emails, product codes, and phone numbers all change, and every change has to ripple through every referencing table. Use a surrogate key and a UNIQUE constraint.
• Accepting the default delete rule without thinking. RESTRICT, CASCADE, and SET NULL produce three different databases from the same DELETE. Decide which one your data actually wants.
• Assuming foreign keys are always enforced. SQLite silently ignores them unless PRAGMA foreign_keys = ON is issued on every connection, so a schema that looks safe may be storing orphans.
• Forgetting to index the foreign-key column. Most engines index the primary key automatically but not the referencing side, which makes both joins and cascading deletes slow on large tables.

Recap

• A primary key uniquely identifies each row and is never NULL; it is chosen from the candidate keys.
• Prefer a surrogate primary key with a UNIQUE constraint on the natural key, because natural keys change, repeat, and are wide.
• A composite key uses several columns together when no single column is unique.
• A foreign key references another table's primary key, storing a link rather than a copy.
• Referential integrity blocks orphan rows; the ON DELETE rule (RESTRICT, CASCADE, or SET NULL) decides what happens to dependents.
• Relationships are 1:1, 1:N (foreign key on the many side), or M:N (needs a junction table), and a foreign key may point back at its own table to model a hierarchy.

Sources

1. Silberschatz, A., Korth, H. F., & Sudarshan, S. (2019). Intermediate SQL: integrity constraints. In Database system concepts (7th ed., ch. 4). McGraw-Hill. find source ↗
2. PostgreSQL Global Development Group. (n.d.). 5.5. Constraints: primary keys, unique constraints, and foreign keys. PostgreSQL documentation. postgresql.org
3. PostgreSQL Global Development Group. (n.d.). CREATE TABLE: REFERENCES, ON DELETE, and identity columns. PostgreSQL documentation. postgresql.org
4. SQLite Consortium. (n.d.). SQLite foreign key support: enabling enforcement and ON DELETE actions. SQLite documentation. sqlite.org
5. Codd, E. F. (1970). A relational model of data for large shared data banks. Communications of the ACM, 13(6), 377-387. dl.acm.org
6. Pavlo, A. (2024). Course schedule and lecture notes. 15-445/645 Introduction to Database Systems, Carnegie Mellon University. 15445.courses.cs.cmu.edu
7. Winand, M. (n.d.). The right column order in multi-column indexes. Use The Index, Luke! use-the-index-luke.com

Changing a schema after a system is live is one of the most expensive things a team can do. Every query, every report, every piece of application code, and every row already stored has to survive the move. An hour spent sketching entities on paper regularly saves a month of migration work, which is why professionals draw before they type.

The big picture

Before you type any SQL, you should design the database. Entity-relationship (ER) modeling is the standard way to plan a schema: you look at the real world you are modeling, pick out its entities, their attributes, and the relationships between them, then draw a picture called an ER diagram. Designing on paper first is far cheaper than rebuilding a live database later.

Key idea: ER modeling is the blueprint stage, done before any table is created.

The three building blocks

An ER model is built from just three kinds of things, and each maps cleanly to part of a relational schema:

• An entity is a thing worth storing data about, and it usually becomes a table. In a school: Student, Course, Instructor. Entities are typically nouns.
• An attribute is a property of an entity, and it usually becomes a column. A Student has a name, a birth date, a GPA. One attribute (or a few together) is the identifier that becomes the primary key.
• A relationship is an association between entities, and it becomes a foreign key or a junction table. A Student enrolls in a Course; an Instructor teaches a Course. Relationships are typically verbs.

A simple habit for a first draft: underline the nouns in the problem description to find entities, and circle the verbs that connect them to find relationships.

Key idea: nouns become entities (tables), their properties become attributes (columns), and verbs become relationships.

Four kinds of attribute, and where each one goes

"Attribute becomes column" is true for most attributes and misleading for the rest. There are four kinds, and only the first maps one-to-one:

Kind	Example	Where it goes
Simple	A student's gpa	One column.
Composite	An address made of street, city, postcode	Usually several columns, so you can filter and sort on the parts.
Multivalued	A student's several phone numbers	Its own table, one row per value. Never a comma-separated column.
Derived	Age, computed from date of birth	Normally not stored at all; compute it in the query or use a generated column.

The multivalued case is the one that trips people up, and it is worth being firm about. A column holding '555-0143, 555-0199' cannot be indexed usefully, cannot be constrained, and turns "find the customer with this number" into a fragile text search. Give it a student_phones(student_id, phone) table instead. Storing age rather than birth date is the mirror error: derived data goes stale silently, and every stored copy is a fact that can drift out of agreement with its source.

Key idea: simple attributes become columns, composite ones usually become several, multivalued ones always become their own table, and derived ones are computed rather than stored.

Cardinality on the diagram

Each relationship is labeled with its cardinality, one-to-one, one-to-many, or many-to-many, exactly the shapes from the previous lesson. A popular notation is crow's foot, where a small three-pronged "foot" on the line means "many" (it looks like a bird's footprint spreading out to touch many rows). So the line between Student and Course would show a crow's foot at both ends, marking a many-to-many relationship.

Here is a tiny ER sketch for a school, drawn as a diagram:

Crow's foot notation actually encodes two numbers at each end, a minimum and a maximum, drawn as two symbols. The inner symbol nearest the box is the maximum and the outer one is the minimum:

notation   minimum   maximum   reads as
--------   -------   -------   -------------------------
   ||         1         1      exactly one       (mandatory)
   o|         0         1      zero or one       (optional)
   |<         1       many     one or more       (mandatory)
   o<         0       many     zero or more      (optional)

That minimum is the part beginners skip, and it is the part that decides whether the eventual foreign-key column is NOT NULL. "An order must have a customer" is a mandatory one, so orders.customer_id is NOT NULL. "An employee may have a manager" is optional, so employees.manager_id is nullable. The diagram is not decoration; it is dictating your DDL.

Key idea: cardinality labels each relationship as 1:1, 1:N, or M:N, a crow's foot marks the many end, and the minimum decides whether the foreign key is NOT NULL.

Weak entities

Some things cannot be identified on their own. An invoice line number 3 means nothing until you say which invoice; a room number 214 means nothing until you say which building. These are weak entities: their identifier is only a partial key, and the full primary key is the owner's key plus that partial key.

CREATE TABLE invoices (
    invoice_id  INTEGER PRIMARY KEY,
    issued_on   DATE NOT NULL
);

CREATE TABLE invoice_lines (
    invoice_id  INTEGER NOT NULL REFERENCES invoices(invoice_id) ON DELETE CASCADE,
    line_no     INTEGER NOT NULL,              -- partial key: unique only within an invoice
    description VARCHAR(200) NOT NULL,
    amount      NUMERIC(10,2) NOT NULL CHECK (amount >= 0),
    PRIMARY KEY (invoice_id, line_no)          -- composite key: owner + partial key
);

Notice that CASCADE is the natural rule here, because a line has no meaning once its invoice is gone. That is the practical test for a weak entity: if deleting the owner should delete the dependent automatically, you are probably looking at one.

Key idea: a weak entity has no identifier of its own, so its primary key is the owner's key plus a partial key, and deleting the owner usually cascades.

From diagram to tables

Translation follows mechanical rules:

1. Each entity becomes a table; each attribute becomes a column; the identifier becomes the primary key.
2. A one-to-many relationship becomes a foreign key on the "many" side.
3. A many-to-many relationship becomes a new junction table holding a foreign key to each entity.

So Student and Course become two tables, and the many-to-many "enrolls" becomes an enrollments junction table with student_id and course_id foreign keys, plus any attributes of the relationship itself, such as the grade. Notice that grade is a property of the enrollment, not of the student or the course alone, which is exactly why it belongs on the junction table.

Key idea: entities and attributes map to tables and columns, and the relationship shape decides where the foreign key or junction table goes.

A worked mini-example

Problem: "A library has members and books. A member can borrow many books over time, and a book can be borrowed by many members over time." Underline the nouns (member, book) and the verb (borrow). Member and Book are entities, so each becomes a table. The borrowing is many-to-many (a member borrows many books; a book is borrowed by many members), so it becomes a loans junction table holding member_id, book_id, and the due_date that describes that particular loan. Doing this thinking on paper first saves you from painful redesigns later.

The same library, designed all the way to DDL

Now take that sketch seriously and finish it. Extend the requirement: "Each book has one or more authors, and an author writes many books. We need to know when each loan is due and whether it has come back."

Entities and their identifiers, from the nouns:

MEMBER (member_id, email, name, join_date)
BOOK   (book_id, isbn, title, published_year)
AUTHOR (author_id, name)

Relationships, from the verbs, each with its cardinality:

MEMBER  ---o<---  LOAN  --->o---  BOOK      a member has 0..n loans; a loan is of exactly 1 book
BOOK    ---|<---  WRITTEN_BY --->|--- AUTHOR  a book has 1..n authors; an author has 1..n books

Two decisions fall out of that. "Written by" is many-to-many with no attributes of its own, so it becomes a pure junction table. A loan is many-to-many between member and book but it does have attributes (due date, return date), and the same member can borrow the same book twice, so it earns a surrogate key of its own and becomes a full entity. Here is the whole schema:

-- PostgreSQL
CREATE TABLE members (
    member_id  INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    email      VARCHAR(255) NOT NULL UNIQUE,
    name       VARCHAR(100) NOT NULL,
    join_date  DATE NOT NULL
);

CREATE TABLE authors (
    author_id  INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    name       VARCHAR(100) NOT NULL
);

CREATE TABLE books (
    book_id        INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    isbn           CHAR(13) NOT NULL UNIQUE,
    title          VARCHAR(200) NOT NULL,
    published_year INTEGER CHECK (published_year BETWEEN 1400 AND 2100)
);

CREATE TABLE book_authors (                    -- the M:N junction
    book_id   INTEGER NOT NULL REFERENCES books(book_id)     ON DELETE CASCADE,
    author_id INTEGER NOT NULL REFERENCES authors(author_id) ON DELETE RESTRICT,
    PRIMARY KEY (book_id, author_id)           -- composite key: one row per pair
);

CREATE TABLE loans (
    loan_id     INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    member_id   INTEGER NOT NULL REFERENCES members(member_id),
    book_id     INTEGER NOT NULL REFERENCES books(book_id),
    loaned_on   DATE NOT NULL,
    due_date    DATE NOT NULL,
    returned_on DATE,                          -- NULL means "still out"
    CHECK (due_date >= loaned_on)
);

Every line traces back to a decision on the diagram. The composite primary key on book_authors is what makes it impossible to record the same author on the same book twice. member_id is NOT NULL because a loan without a borrower is meaningless, while returned_on is deliberately nullable because NULL is the honest way to say "not yet returned". The CHECK enforces a rule about time that no application code can forget. That is the payoff of designing first: the diagram writes most of the DDL for you.

Key idea: a finished ER model translates almost mechanically into CREATE TABLE, and each cardinality decision shows up as a foreign key, a NOT NULL, or a composite primary key.

Where people get stuck

• "An attribute of a relationship should live on one of the entities." A property like a grade or a due date describes the relationship itself and belongs on the junction table, not on either entity.
• "A crow's foot means one." The crow's foot marks the many end of a relationship, not the one end.
• "ER modeling is optional busywork." Designing the model first prevents costly redesigns after data and code already depend on the tables.
• "Every relationship needs its own table." Only many-to-many relationships need a junction table; one-to-many relationships just add a foreign key.
• Turning a multivalued attribute into a delimited string. Phone numbers, tags, and category lists in one cell defeat indexing and constraints. They are entities in disguise and need their own table.
• Ignoring the minimum cardinality. The difference between "may have" and "must have" is exactly the difference between a nullable and a NOT NULL foreign key, and it is far harder to add later than to declare now.
• Storing derived values. An age column is wrong the day after you write it. Store the birth date and compute the age when asked.
• Making everything an entity. If a noun has no attributes of its own and never needs to be referenced independently, it is a column, not a table. "Colour" is usually an attribute; "supplier" usually is not.

Recap

• ER modeling designs a schema before writing SQL, using entities, attributes, and relationships.
• Entities (nouns) become tables, attributes become columns, and relationships (verbs) become foreign keys or junction tables.
• Attributes come in four kinds: simple, composite, multivalued (its own table), and derived (not stored).
• Cardinality (1:1, 1:N, M:N) is labeled on each relationship, with a crow's foot marking the many end and the minimum deciding NOT NULL.
• One-to-many adds a foreign key on the many side; many-to-many needs a junction table, and a relationship with its own attributes often becomes a full entity.
• A weak entity is keyed by its owner's key plus a partial key, and normally cascades on delete.

Sources

1. Chen, P. P.-S. (1976). The entity-relationship model: Toward a unified view of data. ACM Transactions on Database Systems, 1(1), 9-36. dspace.mit.edu
2. Silberschatz, A., Korth, H. F., & Sudarshan, S. (2019). Database design using the E-R model. In Database system concepts (7th ed., ch. 6). McGraw-Hill. find source ↗
3. PostgreSQL Global Development Group. (n.d.). Chapter 5. Data definition. PostgreSQL documentation. postgresql.org
4. PostgreSQL Global Development Group. (n.d.). CREATE TABLE. PostgreSQL documentation. postgresql.org
5. SQLite Consortium. (n.d.). CREATE TABLE: composite primary keys and table constraints. SQLite documentation. sqlite.org
6. Pavlo, A. (2024). Course schedule and lecture notes. 15-445/645 Introduction to Database Systems, Carnegie Mellon University. 15445.courses.cs.cmu.edu
7. Madden, S., & Balakrishnan, H. (2010). Lecture notes. 6.830 Database Systems, MIT OpenCourseWare. ocw.mit.edu

A university database once held an advisor's office number in six hundred rows, one for every student she supervised. She moved offices. The update script matched five hundred and ninety-eight of them. For the next two years the database sincerely believed she was in two rooms at once, and every report that touched her office was quietly wrong. Nothing crashed. That is what a normalization failure looks like in real life.

The big picture

Normalization is the process of structuring tables to eliminate duplicated facts and the update problems they cause. When the same fact is repeated in many rows, the copies drift out of sync and the data starts to contradict itself. Normalization fixes this by splitting data into focused tables so that each fact is stored exactly once. Think of it as removing duplicated facts, the way you would keep a friend's address in one contact card rather than rewriting it on every letter.

Key idea: normalization stores each fact once so the data can never disagree with itself.

The anomalies we are preventing

A badly designed table repeats the same fact in many rows, which leads to three anomalies:

• Update anomaly: you change a fact in one row but forget others, so the data disagrees with itself.
• Insertion anomaly: you cannot add one fact without inventing unrelated data.
• Deletion anomaly: deleting one row accidentally erases a separate fact.

Key idea: redundancy causes update, insertion, and deletion anomalies, and normalization removes all three.

The problem, concretely

Suppose we cram everything into one table, advising:

student_id	student_name	courses	advisor	advisor_office
1	Ada	CS340, MATH200	Dr. Lee	Room 210
2	Diego	BIO101	Dr. Lee	Room 210
3	Priya	CS340	Dr. Kim	Room 305

This has three flaws: the courses cell holds a list (not a single value), Dr. Lee's office is repeated on every one of her advisees (redundant, and if she moves offices you must update every row), and if Ada leaves we might lose the only record of the advisor's office. Normalization repairs all three, one form at a time.

The three anomalies, on actual rows

Abstract warnings about redundancy do not land until you watch the table break. Run three ordinary statements against the rows above.

Update anomaly. Dr. Lee moves to Room 400. Her office lives in two rows, so a careless update reaches only one:

UPDATE advising SET advisor_office = 'Room 400' WHERE student_id = 1;

student_id  student_name  advisor   advisor_office
----------  ------------  --------  --------------
    1       Ada           Dr. Lee   Room 400
    2       Diego         Dr. Lee   Room 210     <- now contradicts row 1
    3       Priya         Dr. Kim   Room 305

The database now asserts that Dr. Lee is in two rooms. No constraint was violated and no error was raised, because the schema has no way to know those two cells are meant to be one fact.

Insertion anomaly. The department hires Dr. Ruiz and gives her Room 118. She has no advisees yet, and there is nowhere to put the fact: every row requires a student_id, so recording her office means inventing a fake student.

Deletion anomaly. Priya graduates and DELETE FROM advising WHERE student_id = 3; removes her row. Dr. Kim's office is now gone from the database entirely; it existed only as a side effect of Priya's row, so deleting one fact destroyed an unrelated one.

All three failures share a root cause: the table mixes facts about students with facts about advisors with facts about enrollments. Normalization is the systematic procedure for separating them.

Key idea: one table holding three kinds of fact produces contradictory updates, unstorable facts, and accidental deletions, and no constraint can catch any of it.

First normal form (1NF)

A table is in 1NF if every cell holds a single atomic value and there are no repeating groups. "Atomic" means indivisible for the database's purposes, one value per box. The courses cell breaks this because it stuffs two courses into one box. Fix it by giving each student-course pair its own row. While we are here, add the course title and the grade so the example has something to teach at every later step:

student_id	course_id	student_name	course_title	advisor	advisor_office	grade
1	CS340	Ada	Databases	Dr. Lee	Room 210	A
1	MATH200	Ada	Linear Algebra	Dr. Lee	Room 210	B
2	BIO101	Diego	Cell Biology	Dr. Lee	Room 210	B
3	CS340	Priya	Databases	Dr. Kim	Room 305	A

Every cell now holds exactly one value, and the primary key is the composite (student_id, course_id). The table is in 1NF and is still badly broken: "Databases" appears twice, "Ada" appears twice, and "Dr. Lee, Room 210" appears three times. 1NF fixed the shape, not the redundancy.

Before going further, write down the functional dependencies that actually hold, because they are what drive every remaining step:

FD1  student_id             -> student_name, advisor
FD2  course_id              -> course_title
FD3  advisor                -> advisor_office
FD4  (student_id, course_id) -> grade

Key idea: 1NF means one value per cell and no lists or repeating groups; it fixes the shape of the table but removes no redundancy at all.

Second normal form (2NF)

A table is in 2NF if it is in 1NF and every non-key column depends on the whole primary key, not just part of it. This only bites when the primary key is composite, as ours now is.

Look at FD1 and FD2. student_name and advisor are determined by student_id alone, half the key; course_title is determined by course_id alone, the other half. Both are partial dependencies: you could learn a course's title knowing only the course. Only FD4, the grade, genuinely needs both halves. So FD1 and FD2 each force a split, and each partially dependent group moves into a table keyed by the part it truly depends on:

students(student_id PK, student_name, advisor, advisor_office)   -- forced by FD1
courses (course_id  PK, course_title)                            -- forced by FD2
enrollments(student_id, course_id, grade)  PK (student_id, course_id)  -- FD4 only

"Databases" is now stored once, and "Ada" is stored once. But students still carries Dr. Lee's office on both of her advisees, so "Room 210" appears twice and we are not finished.

Key idea: 2NF forbids partial dependencies, where a column depends on only part of a composite key; each such dependency forces one split.

Third normal form (3NF)

A table is in 3NF if it is in 2NF and no non-key column depends on another non-key column. Such an indirect link is a transitive dependency.

FD3 is exactly that. In students, advisor_office depends on advisor, which depends on student_id; the office is only transitively tied to the student. That single dependency forces the last split:

advisors(advisor_id PK, advisor_name, office)              -- forced by FD3
students(student_id PK, student_name, advisor_id)          -- advisor_id is a foreign key

The finished design, in 3NF, with every fact stored exactly once:

advisors                       students                       courses
advisor_id  name      office   student_id  name   advisor_id  course_id  course_title
----------  --------  -------  ----------  -----  ----------  ---------  --------------
    1       Dr. Lee   Room 210      1      Ada        1        CS340      Databases
    2       Dr. Kim   Room 305      2      Diego      1        MATH200    Linear Algebra
                                    3      Priya      2        BIO101     Cell Biology

enrollments
student_id  course_id  grade
----------  ---------  -----
    1        CS340       A
    1        MATH200     B
    2        BIO101      B
    3        CS340       A

Now re-run the three anomalies. Dr. Lee's move is UPDATE advisors SET office = 'Room 400' WHERE advisor_id = 1;, one row, impossible to leave half-done. Dr. Ruiz with no advisees is one INSERT into advisors. Deleting Priya removes a student and her enrollments and leaves Dr. Kim's office untouched. All three anomalies are gone, and they are gone structurally rather than by discipline.

A useful summary of the goal, often stated as a memory aid, is that every non-key column must depend on the key, the whole key, and nothing but the key. That single sentence captures 1NF, 2NF, and 3NF together: the key (1NF gives you one), the whole key (2NF), and nothing but the key (3NF).

Key idea: 3NF removes transitive dependencies so non-key columns depend only on the key, and each normal form is driven by a specific functional dependency you can point to.

Functional dependencies

Underlying all of this is the functional dependency: we write "A determines B" to mean "if you know A, you know B." For example, knowing a course id determines its title. Design consists of finding these dependencies and making sure each one is enforced by putting B in a table whose key is A. Normalization is not about following ritual; it is about honoring the real dependencies in your data so it can never contradict itself.

Key idea: a functional dependency says one column set determines another, and good design gives each dependency its own well-keyed table.

BCNF, and the case 3NF misses

3NF is the practical target, but it has a documented gap. Boyce-Codd normal form closes it with a single stricter rule: for every functional dependency A determines B, A must be a superkey. 3NF forgives a violation when the dependent attribute happens to be part of some candidate key; BCNF does not.

Here is a table where the difference is real. A tutoring centre records which tutor a student sees for each course. The rules are: a course has several tutors, a tutor teaches exactly one course, and a student sees exactly one tutor per course.

student	course	tutor
Ada	Databases	Lee
Diego	Databases	Kim
Priya	Databases	Lee
Ada	Calculus	Ruiz

FD-a  (student, course) -> tutor      "one tutor per student per course"
FD-b  tutor             -> course     "a tutor teaches exactly one course"

candidate keys:  (student, course)  and  (student, tutor)

Every column belongs to some candidate key, so there are no non-key columns and the table is automatically in 3NF. Yet FD-b still causes trouble: tutor is not a superkey, so "Lee teaches Databases" is stored twice and you cannot record that Ruiz teaches Calculus until a student signs up with him. 3NF passed a table that still misbehaves. The BCNF decomposition splits on the offending dependency:

tutors(tutor PK, course)          attends(student, tutor)  PK (student, tutor)
------------------------          ------------------------
Lee    Databases                  Ada    Lee
Kim    Databases                  Diego  Kim
Ruiz   Calculus                   Priya  Lee
                                  Ada    Ruiz

Both tables are in BCNF and each fact is stored once. There is an honest cost: FD-a can no longer be enforced by any key, so nothing stops you inserting (Ada, Kim) and giving Ada two Databases tutors. BCNF decomposition is always lossless but not always dependency-preserving, and this is the standard example. When it happens you either stay at 3NF or enforce the lost rule with a trigger.

Key idea: BCNF demands that every determinant be a superkey, which catches redundancy 3NF allows, but the decomposition can lose a dependency you then have to enforce another way.

When denormalization is the right call

Normalization optimizes for correctness, and sometimes you trade a little of it back for speed. Deliberate, documented duplication is denormalization, legitimate under three conditions: reads vastly outnumber writes, the join or aggregate is measurably expensive, and you have a concrete mechanism keeping the duplicate correct.

The typical case is a stored counter. Rendering a busy forum page runs SELECT COUNT(*) FROM comments WHERE post_id = ? for every post in the list; caching a posts.comment_count column removes that work, at the price of a column that can drift and therefore needs a trigger or a scheduled recompute. Two other places duplication is normal: analytical warehouses use star schemas whose wide dimension tables are loaded in batches and never edited in place, and a materialized view is denormalization the database manages for you, storing a query's result and refreshing it on command.

One distinction deserves precision, because it is constantly confused with denormalization. Storing the unit price on an order line is not duplicating the product's price; it records a different fact, the price at the moment of sale, which must not change when the catalogue changes. Copying a value whose meaning is frozen in time is simply correct modelling.

Key idea: normalize first and denormalize only with a measured reason and a named mechanism for keeping the copy true; a point-in-time value such as a sale price is not denormalization at all.

Where people get stuck

• "Normalization is about saving disk space." Its real purpose is correctness: preventing the update, insertion, and deletion anomalies caused by duplicated facts.
• "A single-column primary key can violate 2NF." Partial dependencies require a composite key, so 2NF violations only appear when the key has more than one column.
• "More normal forms are always better." 3NF is the practical target for most designs; over-splitting can hurt readability, and controlled denormalization is sometimes used for performance.
• "Putting a comma-separated list in a cell is fine if it is short." Any list in a cell breaks 1NF and blocks clean querying, joining, and constraints.
• Normalizing by feel instead of by dependency. Every split should be justified by a functional dependency you can write down. If you cannot name the FD, you are guessing.
• Assuming 3NF means no redundancy. The tutor table above is in 3NF and still stores "Lee teaches Databases" twice. BCNF is what removes that.
• Denormalizing before measuring. "Joins are slow" is a hypothesis, not a fact. Index the join column and read the query plan first; most reported join costs disappear.
• Reading FDs off the sample data. Functional dependencies come from the real world, not from the rows you happen to have. If tutors are later allowed to teach two courses, FD-b was never true and the decomposition needs revisiting.

Recap

• Normalization removes duplicated facts to prevent update, insertion, and deletion anomalies, all three of which strike silently.
• 1NF: every cell holds a single atomic value with no repeating groups; it fixes shape, not redundancy.
• 2NF: in 1NF and no partial dependency on part of a composite key; each partial FD forces a split.
• 3NF: in 2NF and no transitive dependency between non-key columns.
• BCNF: every determinant is a superkey, which catches cases 3NF allows, at the possible cost of dependency preservation.
• The goal in one line: every non-key column depends on the key, the whole key, and nothing but the key.
• Denormalize only deliberately, with a measured read cost and a mechanism that keeps the duplicate correct.

Sources

1. Codd, E. F. (1970). A relational model of data for large shared data banks. Communications of the ACM, 13(6), 377-387. dl.acm.org
2. Kent, W. (1983). A simple guide to five normal forms in relational database theory. Communications of the ACM, 26(2), 120-125. bkent.net
3. Silberschatz, A., Korth, H. F., & Sudarshan, S. (2019). Relational database design. In Database system concepts (7th ed., ch. 7). McGraw-Hill. find source ↗
4. Date, C. J. (2019). Database design and relational theory: Normal forms and all that jazz (2nd ed.). Apress. find source ↗
5. Pavlo, A. (2024). Course schedule and lecture notes. 15-445/645 Introduction to Database Systems, Carnegie Mellon University. 15445.courses.cs.cmu.edu
6. PostgreSQL Global Development Group. (n.d.). 39.3. Materialized views. PostgreSQL documentation. postgresql.org
7. Wikipedia contributors. (n.d.). Boyce-Codd normal form. Wikipedia. en.wikipedia.org

Everything you have learned so far has been about arranging data. From here on you get it back out. SELECT is the statement you will write more often than all the others combined, and its two halves answer two independent questions: which columns do I want to see, and which rows do I want to keep? Keep those separate in your head and SQL stays simple for the rest of your career.

The big picture

The SELECT statement is the workhorse of SQL: it reads data from tables and hands you back a result. You choose which columns to see and, with a WHERE clause, which rows to keep. Almost every query you will ever write starts here, so getting comfortable with SELECT and WHERE is the single most valuable SQL skill.

Key idea: SELECT picks the columns, WHERE picks the rows.

hrdb: the schema we use for the rest of the course

Every query example from here to the end of the course runs against one small schema, so you can copy it once and follow along. Paste this into PostgreSQL (or into SQLite after removing the NUMERIC(10,2) precision, which SQLite ignores anyway):

-- hrdb. PostgreSQL syntax; differences are flagged where they matter.
CREATE TABLE departments (
    dept_id   INTEGER PRIMARY KEY,
    dept_name VARCHAR(50) NOT NULL UNIQUE,
    building  VARCHAR(20)
);

CREATE TABLE employees (
    id         INTEGER PRIMARY KEY,
    name       VARCHAR(50) NOT NULL,
    department VARCHAR(50),                            -- see the warning below
    dept_id    INTEGER REFERENCES departments(dept_id),
    salary     NUMERIC(10,2),
    hire_year  INTEGER
);

INSERT INTO departments (dept_id, dept_name, building) VALUES
  (1, 'Engineering', 'North'),
  (2, 'Sales',       'North'),
  (3, 'Marketing',   'South');

INSERT INTO employees (id, name, department, dept_id, salary, hire_year) VALUES
  (1, 'Ada',   'Engineering', 1, 95000, 2019),
  (2, 'Diego', 'Sales',       2, 62000, 2021),
  (3, 'Priya', 'Engineering', 1, 88000, 2020),
  (4, 'Mateo', 'Sales',       2, 71000, 2018);

So the employees table holds:

id	name	department	dept_id	salary	hire_year
1	Ada	Engineering	1	95000.00	2019
2	Diego	Sales	2	62000.00	2021
3	Priya	Engineering	1	88000.00	2020
4	Mateo	Sales	2	71000.00	2018

One honest warning, because you just spent a lesson on normalization. The department text column duplicates what departments.dept_name already stores. That is a transitive dependency and a deliberate 3NF violation, kept only so that these first single-table examples read without a join. From Lesson 8 onward we reach the department name properly, through dept_id. If this were a real schema, the text column would not exist.

Key idea: every example from here on uses the hrdb schema above, with four employees and three departments.

Choosing columns

List the columns you want after SELECT. This is called projection, like photocopying only certain columns off a spreadsheet:

SELECT name, salary
FROM employees;

Four rows come back, two columns wide:

 name  | salary
-------+----------
 Ada   | 95000.00
 Diego | 62000.00
 Priya | 88000.00
 Mateo | 71000.00
(4 rows)

Notice that projection never removes rows. To get all columns, use the shorthand *:

SELECT *
FROM employees;

In real code it is better to name the columns you actually need rather than *, so the query keeps working predictably if the table later gains columns.

The SELECT list is not limited to bare column names. It can hold expressions, and AS gives the result a readable name:

SELECT name,
       salary,
       ROUND(salary * 1.10, 2) AS proposed,
       2026 - hire_year         AS years_of_service
FROM employees
WHERE department = 'Engineering';

 name  | salary   | proposed  | years_of_service
-------+----------+-----------+------------------
 Ada   | 95000.00 | 104500.00 |                7
 Priya | 88000.00 |  96800.00 |                6
(2 rows)

The ROUND is there for a reason worth knowing: multiplying two NUMERIC values in PostgreSQL adds their scales, so salary * 1.10 would come back as 104500.0000 with four decimal places. Rounding says what you mean.

Key idea: projection is choosing columns, never rows; the SELECT list can compute expressions, and AS names them.

Filtering rows with WHERE

The WHERE clause keeps only the rows whose condition is true, like a sieve that lets through only matching rows. To list engineers:

SELECT name, department
FROM employees
WHERE department = 'Engineering';

Text literals go in single quotes; numbers do not. The comparison operators are =, <> (not equal), <, <=, >, and >=. Combine conditions with AND, OR, and NOT:

SELECT name
FROM employees
WHERE department = 'Sales' AND salary > 65000;

Against the table above, that returns only Mateo (71000). Diego is in Sales but earns 62000, so he fails the salary test. Remember that AND requires both sides to be true, while OR requires only one.

That last point hides the most common WHERE bug in existence: AND binds more tightly than OR. Suppose you want people who are in Sales or in Engineering, earning over 90000. Written without parentheses:

SELECT name, department, salary
FROM employees
WHERE department = 'Sales' OR department = 'Engineering' AND salary > 90000;

SQL reads that as Sales OR (Engineering AND salary > 90000), so it returns three rows:

 name  | department  | salary
-------+-------------+----------
 Ada   | Engineering | 95000.00     <- passed the salary test
 Diego | Sales       | 62000.00     <- got in on department alone
 Mateo | Sales       | 71000.00     <- got in on department alone
(3 rows)

Add the parentheses you meant and the answer changes completely:

WHERE (department = 'Sales' OR department = 'Engineering') AND salary > 90000;

 name | department  | salary
------+-------------+----------
 Ada  | Engineering | 95000.00
(1 row)

One row, not three. The habit that prevents this permanently: whenever a WHERE clause mixes AND with OR, parenthesize the OR group even when you are sure of the precedence. It costs two characters and removes an entire class of silent wrong answers.

Key idea: WHERE keeps rows where its condition is true; quote text, combine tests with AND, OR, and NOT, and always parenthesize a mixed AND/OR because AND binds tighter.

Handy special conditions

SQL offers compact operators for common tests. Each one below is run against hrdb with its actual output:

• BETWEEN checks an inclusive range: WHERE salary BETWEEN 60000 AND 90000 matches salaries from 60000 up to and including 90000.
• IN checks membership in a list: WHERE department IN ('Sales', 'Marketing').
• LIKE does pattern matching on text, where % matches any run of characters and _ matches exactly one: WHERE name LIKE 'A%' finds names starting with A.
• IS NULL / IS NOT NULL test for missing values, since = NULL never works.

WHERE salary BETWEEN 60000 AND 90000      -> Diego, Priya, Mateo   (Ada at 95000 is out)
WHERE department IN ('Sales','Marketing') -> Diego, Mateo          (nobody is in Marketing)
WHERE name LIKE 'A%'                      -> Ada
WHERE name LIKE '%a%'                     -> Ada, Priya, Mateo     (Diego has no letter a)
WHERE name LIKE '_a%'                     -> Mateo                 (_ is exactly one char)
WHERE dept_id IS NULL                     -> (0 rows)

Note that BETWEEN is inclusive at both ends, which surprises people who use it on dates: BETWEEN '2026-01-01' AND '2026-01-31' on a timestamp column misses anything after midnight on the 31st, because a timestamp of 09:14 that day is greater than 2026-01-31 00:00. For timestamps, prefer >= '2026-01-01' AND < '2026-02-01'.

Key idea: BETWEEN, IN, LIKE, and IS NULL are shorthands for range, list, pattern, and missing-value tests, and BETWEEN is inclusive at both ends.

Quoting, escaping, and case

Three details about text cause more beginner errors than everything else combined.

Single quotes are for values; double quotes are for identifiers. 'Sales' is the text Sales; "Sales" is a column or table named Sales. Mixing them up produces "column does not exist" errors that look mysterious until you know the rule. MySQL muddies this by accepting double quotes as string literals unless ANSI_QUOTES is set, and SQLite accepts them as a deliberate compatibility misfeature, so code that works there breaks on PostgreSQL.

Escape a quote by doubling it. There is no backslash escape in standard SQL:

SELECT * FROM employees WHERE name = 'O''Brien';   -- matches O'Brien

That doubling rule is also the reason naive string-concatenated SQL is dangerous: a value containing a quote changes the shape of the statement. Lesson 11 shows exactly how, and how parameters remove the problem entirely.

Comparison is usually case-sensitive. In PostgreSQL, WHERE department = 'engineering' returns zero rows, because the stored value is 'Engineering'. Use ILIKE or LOWER(department) = 'engineering'. MySQL's default collation is case-insensitive, so the same query does return rows there, which is exactly the kind of difference that makes a query behave differently in production than on your laptop.

Key idea: single quotes for values and double quotes for identifiers, double a quote to escape it, and never assume text comparison ignores case.

A worked example

SELECT name, hire_year
FROM employees
WHERE hire_year BETWEEN 2019 AND 2021
  AND name LIKE '%a%';

Reading it: keep employees hired from 2019 through 2021 whose name contains a lowercase letter a. Let us check each row:

name	hire_year in 2019-2021?	name has lowercase a?	kept?
Ada	2019 yes	yes (Ada)	yes
Diego	2021 yes	no	no
Priya	2020 yes	yes (Priya)	yes
Mateo	2018 no	yes (Mateo)	no

So the query returns Ada and Priya. Mateo is excluded by the year test, and Diego is excluded because his name has no lowercase a. Getting comfortable with WHERE is the key skill, because almost every query filters something.

Where people get stuck

• "SELECT changes the table." SELECT only reads and returns rows; it never modifies stored data.
• "You can write WHERE salary = NULL." Comparing to NULL with = yields unknown; use IS NULL to find missing values.
• "Text can go in double quotes." In standard SQL, string literals use single quotes, such as 'Sales'; double quotes are for identifiers.
• "LIKE 'A' matches names starting with A." Without a wildcard, LIKE 'A' matches only the exact single letter A; you need LIKE 'A%' for names starting with A.
• Mixing AND and OR without parentheses. AND binds tighter, so the query silently answers a different question. The three-row result above is the standard way this bug shows up.
• Assuming comparison ignores case. PostgreSQL and SQLite compare text exactly; MySQL's default collation does not. Write LOWER() or ILIKE when case should not matter.
• Using BETWEEN on timestamps. It is inclusive at both ends, so a month range ending on the 31st drops everything after midnight that day. Use a half-open range instead.
• Shipping SELECT *. It works today and breaks quietly the moment a column is added, renamed, or reordered, and it makes the database read columns nobody wanted.

Recap

• SELECT lists the columns (projection); FROM names the table; the SELECT list may also compute expressions named with AS.
• WHERE keeps rows whose condition is true, using comparison operators and AND, OR, NOT.
• AND binds more tightly than OR, so parenthesize every mixed condition.
• Text literals use single quotes and escape an embedded quote by doubling it; numbers are unquoted.
• BETWEEN, IN, LIKE (with % and _), and IS NULL cover ranges, lists, patterns, and missing values.
• SELECT is read-only and never changes the stored data.

Sources

1. PostgreSQL Global Development Group. (n.d.). SELECT. PostgreSQL documentation. postgresql.org
2. PostgreSQL Global Development Group. (n.d.). 7.2. Table expressions: the WHERE clause. PostgreSQL documentation. postgresql.org
3. PostgreSQL Global Development Group. (n.d.). 4.1. Lexical structure: string constants and quoted identifiers. PostgreSQL documentation. postgresql.org
4. PostgreSQL Global Development Group. (n.d.). 9.1. Logical operators: AND, OR, and NOT precedence. PostgreSQL documentation. postgresql.org
5. SQLite Consortium. (n.d.). SELECT. SQLite documentation. sqlite.org
6. SQLite Consortium. (n.d.). SQL language expressions: LIKE, GLOB, and the double-quoted string misfeature. SQLite documentation. sqlite.org
7. Silberschatz, A., Korth, H. F., & Sudarshan, S. (2019). Introduction to SQL. In Database system concepts (7th ed., ch. 3). McGraw-Hill. find source ↗

A paginated list is the most common screen in software, and it is also the most commonly broken. Users report seeing the same record on page 1 and page 2, or a record that never appears at all. Almost always the cause is a single missing word in an ORDER BY. This lesson covers the three clauses that shape a result set, and the surprisingly sharp edges on all three.

The big picture

Because a table is an unordered set, a query returns rows in no guaranteed order unless you ask. Three tools shape the final result set: ORDER BY to sort, a row limit such as LIMIT to trim, and DISTINCT to remove duplicates. Together they turn a raw pile of matching rows into a tidy, presentable answer.

Key idea: ORDER BY sorts, LIMIT trims, and DISTINCT de-duplicates the result set.

We continue with the employees table: Ada (Engineering, 95000, 2019), Diego (Sales, 62000, 2021), Priya (Engineering, 88000, 2020), and Mateo (Sales, 71000, 2018).

ORDER BY

Add ORDER BY and a column to sort the output, the way you might alphabetize a stack of index cards. By default the order is ascending (ASC): smallest to largest, or A to Z. Add DESC for descending order.

SELECT name, salary
FROM employees
ORDER BY salary DESC;

This lists employees from the highest salary to the lowest: Ada (95000), Priya (88000), Mateo (71000), Diego (62000). You can sort by several columns; ties in the first are broken by the next:

SELECT name, department, salary
FROM employees
ORDER BY department ASC, salary DESC;

That groups rows by department alphabetically, and within each department shows the highest earner first:

 name  | department  | salary
-------+-------------+----------
 Ada   | Engineering | 95000.00
 Priya | Engineering | 88000.00
 Mateo | Sales       | 71000.00
 Diego | Sales       | 62000.00
(4 rows)

Key idea: ORDER BY sorts ascending by default; add DESC to reverse, and list several columns to break ties.

Where NULLs sort

NULL is neither greater nor less than any value, so every engine has to make a choice, and they do not agree. Suppose employees also had a nullable bonus column holding Ada 5000, Diego NULL, Priya 2000, Mateo NULL. Then ORDER BY bonus ASC gives:

PostgreSQL and Oracle          SQLite and MySQL
(NULLs treated as largest)     (NULLs treated as smallest)
---------------------------    ---------------------------
Priya   2000                   Diego   NULL
Ada     5000                   Mateo   NULL
Diego   NULL                   Priya   2000
Mateo   NULL                   Ada     5000

The same query, two different answers. Say what you want instead of inheriting a default:

ORDER BY bonus ASC NULLS FIRST;   -- PostgreSQL, Oracle, SQLite 3.30+
ORDER BY bonus IS NULL, bonus;    -- MySQL, which has no NULLS FIRST/LAST

The MySQL trick works because bonus IS NULL yields 0 for real values and 1 for NULLs, and sorting on that puts the zeros first.

Key idea: engines disagree about where NULLs sort, so state NULLS FIRST or NULLS LAST explicitly whenever the column is nullable.

Limiting rows

To return only the first few rows, most databases use LIMIT. Combined with ORDER BY, it answers "top N" questions:

SELECT name, salary
FROM employees
ORDER BY salary DESC
LIMIT 3;

This returns the three highest-paid employees (Ada, Priya, Mateo). Row limiting is the place where dialects diverge most visibly, so it is worth having the table in front of you:

System	Skip 20, take 10
SQL standard (SQL:2008)	OFFSET 20 ROWS FETCH FIRST 10 ROWS ONLY
PostgreSQL	LIMIT 10 OFFSET 20 (also accepts the standard form)
SQLite	LIMIT 10 OFFSET 20
MySQL and MariaDB	LIMIT 10 OFFSET 20, or the older LIMIT 20, 10
SQL Server	SELECT TOP 10 ..., or OFFSET 20 ROWS FETCH NEXT 10 ROWS ONLY
Oracle 12c and later	OFFSET 20 ROWS FETCH FIRST 10 ROWS ONLY

Note the reversed argument order in MySQL's short form: LIMIT 20, 10 means offset 20, take 10, which is the opposite of what it looks like. Examples in this course use LIMIT ... OFFSET ..., the PostgreSQL and SQLite spelling.

Key idea: LIMIT caps the row count and OFFSET skips ahead, and ORDER BY plus LIMIT answers top-N questions - but the exact syntax is dialect-specific.

Two ways paging goes wrong

Paging is LIMIT plus OFFSET, and it fails in two distinct ways that look identical to a user.

An unstable sort. Page through employees two at a time, ordered only by department:

SELECT name, department FROM employees ORDER BY department LIMIT 2 OFFSET 0;  -- page 1
SELECT name, department FROM employees ORDER BY department LIMIT 2 OFFSET 2;  -- page 2

Both Engineering rows tie, and so do both Sales rows. The order within a tie is unspecified, so the engine may legitimately return Ada and Priya on page 1 the first time and Priya and Ada the second, which means a user can see Priya twice and never see Ada at all. The fix is to make the sort total by appending a unique column:

ORDER BY department, id     -- now no two rows can tie, so paging is stable

A deep offset. LIMIT 10 OFFSET 100000 does not skip cheaply. The engine must still produce and then discard one hundred thousand rows before it hands you ten, so page 10,000 costs a thousand times more than page 1. The standard fix is keyset pagination: remember the last row you showed and ask for what comes after it.

-- instead of OFFSET, carry the last seen sort key forward
SELECT name, department, id
FROM employees
WHERE (department, id) > ('Engineering', 3)     -- last row of the previous page
ORDER BY department, id
LIMIT 2;

That query jumps straight into an index on (department, id) and costs the same on page 10,000 as on page 1. The trade-off is that you can only move forward and backward one page at a time, not jump to page 47, which is usually an acceptable price.

Key idea: paging needs a total ordering to be correct and keyset pagination to stay fast; OFFSET alone gives you neither.

DISTINCT

Sometimes a projection produces duplicate rows and you want each unique value once. DISTINCT removes duplicates from the result:

SELECT DISTINCT department
FROM employees;

From our four employees this returns just two rows, Engineering and Sales, rather than repeating each department once per employee. Remember that DISTINCT applies to the whole selected row, so SELECT DISTINCT department, salary keeps a row for each unique combination of department and salary. Watch what that does here:

SELECT DISTINCT department FROM employees;          SELECT DISTINCT department, salary FROM employees;

 department                                          department  | salary
-------------                                       -------------+----------
 Engineering                                         Engineering | 95000.00
 Sales                                               Engineering | 88000.00
(2 rows)                                             Sales       | 71000.00
                                                     Sales       | 62000.00
                                                    (4 rows)

Adding one column took the answer from two rows back to four, because every salary is unique. DISTINCT does not de-duplicate the first column; it de-duplicates the row. Two related notes: DISTINCT treats all NULLs as equal to each other, so a nullable column collapses to exactly one NULL row, and SELECT DISTINCT x and SELECT x ... GROUP BY x return the same rows, differing only in that GROUP BY can then compute aggregates.

PostgreSQL adds a genuinely useful extension, DISTINCT ON, which keeps the first row of each group according to the ORDER BY:

SELECT DISTINCT ON (department) department, name, salary
FROM employees
ORDER BY department, salary DESC;

 department  | name  | salary
-------------+-------+----------
 Engineering | Ada   | 95000.00
 Sales       | Mateo | 71000.00
(2 rows)

That is the top earner per department in one clause. It is PostgreSQL-only; elsewhere the same answer needs a window function or a correlated subquery.

Key idea: DISTINCT collapses duplicate result rows and considers all selected columns together, so adding a column can undo the de-duplication entirely.

Order of the clauses

When you use all three, they appear in a fixed order in the written query: WHERE (if any) first, then ORDER BY, then LIMIT. The database applies them in a sensible sequence too: it filters rows, then sorts them, then trims to the limit. That is why "the top 3 highest paid" needs the sort before the limit; trimming first would give you an arbitrary three rows.

That evaluation order has a visible consequence for aliases. ORDER BY runs after SELECT, so it can use a name you invented in the SELECT list. WHERE runs before SELECT, so it cannot:

SELECT name, salary * 12 AS annual
FROM employees
WHERE salary * 12 > 800000    -- must repeat the expression; "annual" does not exist yet
ORDER BY annual DESC;         -- but here the alias is perfectly legal

 name  |   annual
-------+-------------
 Ada   | 1140000.00
 Priya | 1056000.00
 Mateo |  852000.00
(3 rows)

Diego is filtered out because 62000 times 12 is 744000. If a WHERE alias ever appears to work, you are on a dialect that quietly extends the standard, and the query will not port.

Key idea: sort before you limit, or the "top N" will be an arbitrary N; and ORDER BY can use a SELECT alias while WHERE cannot, because WHERE runs first.

Where people get stuck

• "Rows come out sorted automatically." Without ORDER BY, order is not guaranteed; you must sort explicitly.
• "LIMIT 3 alone gives the top 3." LIMIT without ORDER BY returns an arbitrary three rows; you must sort first to define "top."
• "DISTINCT applies to just the first column." DISTINCT considers the entire selected row, so extra columns can reintroduce duplicates you did not expect.
• "LIMIT is standard SQL everywhere." LIMIT is common (PostgreSQL, MySQL, SQLite), but the strict standard is FETCH FIRST, and SQL Server uses TOP.
• Paging on a non-unique sort column. Ties break paging silently: a row can appear on two pages while another is never shown. Always append a unique column to the ORDER BY.
• Expecting OFFSET to be cheap. The engine produces and discards every skipped row, so deep pages get linearly slower. Use keyset pagination when the list is long.
• Assuming NULLs sort the same everywhere. PostgreSQL puts them last in an ascending sort; SQLite and MySQL put them first. State NULLS FIRST or NULLS LAST.
• Reaching for DISTINCT to fix duplicated rows from a join. The duplicates usually mean the join condition is wrong or the join is fanning out. DISTINCT hides the symptom and makes the query slower.

Recap

• ORDER BY sorts results, ascending by default, DESC for descending, with multiple columns breaking ties.
• Engines disagree about where NULLs sort, so say NULLS FIRST or NULLS LAST when the column is nullable.
• LIMIT caps the number of returned rows; OFFSET skips rows for paging; the syntax varies by dialect (LIMIT, TOP, FETCH FIRST).
• ORDER BY combined with LIMIT answers top-N questions, and you must sort before limiting.
• Correct paging needs a total ordering; fast paging on long lists needs keyset pagination rather than a deep OFFSET.
• DISTINCT removes duplicate rows and considers all selected columns together; PostgreSQL's DISTINCT ON keeps the first row per group.

Sources

1. PostgreSQL Global Development Group. (n.d.). 7.5. Sorting rows (ORDER BY). PostgreSQL documentation. postgresql.org
2. PostgreSQL Global Development Group. (n.d.). 7.6. LIMIT and OFFSET. PostgreSQL documentation. postgresql.org
3. PostgreSQL Global Development Group. (n.d.). SELECT: DISTINCT and DISTINCT ON. PostgreSQL documentation. postgresql.org
4. Winand, M. (n.d.). Paging through results: why OFFSET is bad for skipping previous rows. Use The Index, Luke! use-the-index-luke.com
5. Microsoft. (n.d.). TOP (Transact-SQL). Microsoft Learn. learn.microsoft.com
6. SQLite Consortium. (n.d.). SELECT: ORDER BY, LIMIT, and OFFSET. SQLite documentation. sqlite.org
7. Silberschatz, A., Korth, H. F., & Sudarshan, S. (2019). Introduction to SQL. In Database system concepts (7th ed., ch. 3). McGraw-Hill. find source ↗

Normalization split your data across tables on purpose. Joins are how you put it back together to answer a question, and they are where beginners most often get an answer that looks right and is not. A join that quietly drops rows raises no error, produces a tidy result set, and can be wrong for years. This lesson traces joins by hand so you can always tell which rows survived and why.

The big picture

Because good design spreads data across tables, most real questions need data from more than one. A join combines rows from two tables by matching values in related columns, almost always a foreign key matching a primary key. The join type you choose decides which rows survive when there is no match, so picking the right one is the whole game.

Key idea: a join stitches two tables together on a matching column; the join type controls which unmatched rows are kept.

We use the hrdb schema from Lesson 6, plus one extra employee added for this lesson only: Sam, who has just been hired and has not been assigned to a department yet. Sam is what makes the difference between the join types visible. He is removed again at the end, so later lessons still see the original four employees.

INSERT INTO employees (id, name, department, dept_id, salary, hire_year)
VALUES (5, 'Sam', NULL, NULL, 58000, 2026);      -- temporary, for this lesson

employees		departments
name	dept_id	dept_id	dept_name	building
Ada	1	1	Engineering	North
Diego	2	2	Sales	North
Priya	1	3	Marketing	South
Mateo	2
Sam	NULL

Note the two deliberate loose ends: Sam is an employee with no department, and Marketing is a department with no employees. Every join type in this lesson is really a decision about what to do with those two rows.

Tracing a join by hand

Underneath every join is one very simple idea. Conceptually the engine forms every possible pairing of a left row with a right row, then keeps the pairs where the ON condition is true. Five employees and three departments give fifteen candidate pairs:

  employee  e.dept_id | d.dept_id  dept_name    | e.dept_id = d.dept_id ?
  --------  --------- | ---------  -----------  | -----------------------
  Ada           1     |     1      Engineering  | TRUE      <- kept
  Ada           1     |     2      Sales        | false
  Ada           1     |     3      Marketing    | false
  Diego         2     |     1      Engineering  | false
  Diego         2     |     2      Sales        | TRUE      <- kept
  Diego         2     |     3      Marketing    | false
  Priya         1     |     1      Engineering  | TRUE      <- kept
  Priya         1     |     2      Sales        | false
  Priya         1     |     3      Marketing    | false
  Mateo         2     |     1      Engineering  | false
  Mateo         2     |     2      Sales        | TRUE      <- kept
  Mateo         2     |     3      Marketing    | false
  Sam         NULL    |     1      Engineering  | UNKNOWN
  Sam         NULL    |     2      Sales        | UNKNOWN
  Sam         NULL    |     3      Marketing    | UNKNOWN

  4 of the 15 pairs survive.

Two things fall out of that trace. Sam produces three UNKNOWNs rather than three falses, because a comparison against NULL is never true - the three-valued logic from Lesson 2 turning up in a new place. And Marketing never appears on the right of a surviving pair, because no employee points at it. Real engines do not literally build all fifteen pairs, using an index or hash table instead, but the result is defined as if they did, so this is the right model for reasoning about correctness.

Key idea: a join is every pairing filtered by the ON condition, and a NULL join key produces UNKNOWN, so it matches nothing.

INNER JOIN

An INNER JOIN returns only rows that have a match on both sides, like the overlap in the middle of two circles. To pair each employee with their department name:

SELECT e.name, d.dept_name
FROM employees AS e
INNER JOIN departments AS d
  ON e.dept_id = d.dept_id;

The ON clause states the matching condition. The AS e and AS d are table aliases, short nicknames that keep the query readable. The result is exactly the four surviving pairs from the trace. Sam is dropped because his dept_id is NULL and matches nothing, and Marketing is dropped because no employee is in it.

 name  | dept_name
-------+-------------
 Ada   | Engineering
 Diego | Sales
 Priya | Engineering
 Mateo | Sales
(4 rows)

Key idea: an INNER JOIN keeps only rows matched on both sides, so unmatched rows on either side disappear.

LEFT JOIN

A LEFT JOIN (short for LEFT OUTER JOIN) keeps every row from the left table, and fills the right-side columns with NULL where there is no match. To list all employees even if unassigned:

SELECT e.name, d.dept_name
FROM employees AS e
LEFT JOIN departments AS d
  ON e.dept_id = d.dept_id;

Now Sam appears with a NULL dept_name, because the left join preserves him:

 name  | dept_name
-------+-------------
 Ada   | Engineering
 Diego | Sales
 Priya | Engineering
 Mateo | Sales
 Sam   | NULL          <- preserved, right side filled with NULL
(5 rows)

This is the standard way to find rows that lack a match: add WHERE d.dept_id IS NULL and you get exactly the employees with no department.

Key idea: a LEFT JOIN keeps all left rows and NULL-fills the right, which is how you find rows with no match.

RIGHT JOIN and FULL OUTER JOIN

A RIGHT JOIN is the mirror image: it keeps every row from the right table and NULL-fills the left. Using the same tables, a right join from employees to departments keeps Marketing (which has no employees) with a NULL employee name. In practice people usually write LEFT JOINs and simply put the table they care about on the left, but RIGHT JOIN exists for symmetry.

A FULL OUTER JOIN keeps everything from both sides. Run all four join types over the same two tables and the difference is one clean picture:

INNER JOIN (4 rows)        LEFT JOIN (5 rows)         RIGHT JOIN (5 rows)        FULL OUTER (6 rows)
Ada   | Engineering        Ada   | Engineering        Ada   | Engineering        Ada   | Engineering
Diego | Sales              Diego | Sales              Diego | Sales              Diego | Sales
Priya | Engineering        Priya | Engineering        Priya | Engineering        Priya | Engineering
Mateo | Sales              Mateo | Sales              Mateo | Sales              Mateo | Sales
                           Sam   | NULL               NULL  | Marketing          Sam   | NULL
                                                                                  NULL  | Marketing

Portability note: SQLite only gained RIGHT JOIN and FULL OUTER JOIN in version 3.39 (2022), and MySQL still has no FULL OUTER JOIN at all. Where it is missing, the usual workaround is a LEFT JOIN unioned with the rows the RIGHT JOIN would have added.

Key idea: RIGHT JOIN is LEFT JOIN with the sides swapped and FULL OUTER keeps every row from both, so the four types differ only in which unmatched rows survive.

Summary of who survives

Join type	Rows kept
INNER JOIN	Only rows matched in both tables
LEFT JOIN	All left rows; right side NULL when unmatched
RIGHT JOIN	All right rows; left side NULL when unmatched
FULL OUTER JOIN	All rows from both sides, NULL-filled where unmatched
CROSS JOIN	Every pairing, with no ON condition at all

The mental model: decide which table's rows you must not lose, then pick the join that guarantees they stay.

Key idea: choose the join by asking which table's rows you cannot afford to lose.

The trap: a WHERE clause that undoes your LEFT JOIN

This is the single most common join bug, and it produces a plausible-looking wrong answer rather than an error. You want every employee, with their building where one exists, restricted to the North building:

SELECT e.name, d.dept_name, d.building
FROM employees AS e
LEFT JOIN departments AS d ON e.dept_id = d.dept_id
WHERE d.building = 'North';

 name  | dept_name   | building
-------+-------------+----------
 Ada   | Engineering | North
 Diego | Sales       | North
 Priya | Engineering | North
 Mateo | Sales       | North
(4 rows)      <- Sam is gone. The LEFT JOIN behaved like an INNER JOIN.

Trace why. The LEFT JOIN produces Sam's row with every department column NULL. Then WHERE evaluates NULL = 'North', which is unknown, not true, so Sam is discarded. Any WHERE condition on the optional side of an outer join silently deletes exactly the rows the outer join existed to preserve. Two fixes, answering slightly different questions. Put the condition in ON when it is part of what counts as a match:

SELECT e.name, d.dept_name
FROM employees AS e
LEFT JOIN departments AS d
  ON e.dept_id = d.dept_id AND d.building = 'North';    -- 5 rows: Sam kept, dept_name NULL

Or allow the NULL explicitly in the WHERE when you really do want to filter afterwards:

WHERE d.building = 'North' OR d.building IS NULL;       -- 5 rows

The rule to remember: in an outer join, conditions on the optional side belong in ON; conditions on the preserved side belong in WHERE. The one deliberate exception is the anti-join below, where turning the outer join into a filter is exactly the point.

Key idea: a WHERE test on the optional table converts a LEFT JOIN into an INNER JOIN, because NULL fails every comparison; move the condition into ON.

Anti-joins: finding what is missing

Deliberately filtering on the NULL-filled side gives you the rows with no match, which answers a whole family of real questions:

-- employees with no department
SELECT e.name
FROM employees AS e
LEFT JOIN departments AS d ON e.dept_id = d.dept_id
WHERE d.dept_id IS NULL;                 --  Sam

-- departments with no employees
SELECT d.dept_name
FROM departments AS d
LEFT JOIN employees AS e ON e.dept_id = d.dept_id
WHERE e.id IS NULL;                      --  Marketing

Always test IS NULL against a column that can never legitimately be NULL in the right table, such as its primary key; testing a nullable column reports genuinely matched rows as missing. Finally, beware the accidental cross join: FROM employees e, departments d with no condition produces all fifteen pairs, and on two tables of ten thousand rows that is one hundred million rows - the usual explanation for a query that suddenly never finishes.

Key idea: a LEFT JOIN with IS NULL on the right side's key is the standard anti-join, and a join with no ON condition silently becomes a cross join.

With the joins covered, remove the temporary row so the rest of the course sees the original hrdb data: DELETE FROM employees WHERE id = 5;

Where people get stuck

• "A join needs no matching condition." Except for a deliberate cross join, you must give an ON condition; otherwise you get a huge, meaningless combination of every row pair.
• "An INNER JOIN keeps unmatched rows too." It keeps only rows with a match on both sides; unmatched rows are dropped.
• "NULL foreign keys still match in an inner join." A NULL matches nothing, so a row with a NULL join key is excluded from an inner join.
• "LEFT and RIGHT joins give different answers for the same tables." They keep the same information; they only differ in which table is treated as the one whose rows are always preserved.
• Filtering the optional table in WHERE. This is the big one. WHERE d.building = 'North' after a LEFT JOIN throws away every NULL-filled row and quietly turns the query into an inner join. Move the condition to ON.
• Testing IS NULL on a nullable column in an anti-join. Test the right table's primary key, which is never NULL in a genuine match, or you will report matched rows as missing.
• Row counts exploding after a join. If the ON condition is not unique on one side, each left row multiplies. Four orders joined to a table with three rows per order gives twelve rows, and any SUM over them is now triple-counted.
• Assuming every engine has every join. SQLite gained RIGHT and FULL OUTER only in 3.39, and MySQL still lacks FULL OUTER JOIN.

Recap

• A join combines rows from two tables using an ON condition, usually a foreign key equaling a primary key.
• Conceptually it is every pairing filtered by ON, which is why a NULL join key matches nothing.
• INNER JOIN keeps only matched rows on both sides; LEFT keeps all left rows, RIGHT all right rows, FULL OUTER all of both.
• A WHERE condition on the optional side of an outer join silently converts it to an inner join; put such conditions in ON.
• LEFT JOIN plus IS NULL on the right table's key is the anti-join, the standard way to find rows with no match.
• Choose the join by which table's rows must be preserved, and watch for row counts multiplying when the join key is not unique.

Sources

1. PostgreSQL Global Development Group. (n.d.). 7.2. Table expressions: joined tables, ON versus WHERE. PostgreSQL documentation. postgresql.org
2. PostgreSQL Global Development Group. (n.d.). 2.6. Joins between tables. PostgreSQL documentation. postgresql.org
3. SQLite Consortium. (n.d.). SELECT: join types, including RIGHT and FULL OUTER from version 3.39. SQLite documentation. sqlite.org
4. SQLite Consortium. (n.d.). The SQLite query optimizer overview: join order and index use. SQLite documentation. sqlite.org
5. Silberschatz, A., Korth, H. F., & Sudarshan, S. (2019). Intermediate SQL: join expressions. In Database system concepts (7th ed., ch. 4). McGraw-Hill. find source ↗
6. Pavlo, A. (2024). Course schedule and lecture notes: join algorithms. 15-445/645 Introduction to Database Systems, Carnegie Mellon University. 15445.courses.cs.cmu.edu
7. Madden, S., & Balakrishnan, H. (2010). Lecture notes: query execution and joins. 6.830 Database Systems, MIT OpenCourseWare. ocw.mit.edu

Nobody ever asked for four hundred thousand rows. They asked how many, how much, and which department is highest. Aggregation is the part of SQL that answers the questions people actually have, and it is also where NULL does its quietest damage: an average that silently divides by the wrong number looks completely plausible on a slide.

The big picture

Often you do not want individual rows but a summary: a total, an average, a count. SQL's aggregate functions collapse many rows into a single value, and GROUP BY lets you compute one summary per category. HAVING then filters those summaries. This trio turns raw rows into the kind of report a manager actually asks for.

Key idea: aggregates summarize rows, GROUP BY summarizes per category, and HAVING filters the summaries.

Aggregate functions

The five aggregates you will use constantly are:

• COUNT - how many rows (or non-NULL values).
• SUM - the total of a numeric column.
• AVG - the average of a numeric column.
• MIN and MAX - the smallest and largest value.

Applied to the whole table, they return one row. Using the employees table (Ada 95000 Engineering, Diego 62000 Sales, Priya 88000 Engineering, Mateo 71000 Sales):

SELECT COUNT(*) AS headcount,
       AVG(salary) AS avg_salary,
       MAX(salary) AS top_salary
FROM employees;

This returns a single row: headcount 4, average salary 79000, top salary 95000. (The four salaries sum to 316000; divided by 4 that is 79000.) An aggregate is like tallying a pile of receipts into one number at the bottom.

 headcount | avg_salary | top_salary
-----------+------------+------------
         4 | 79000.00   |   95000.00
(1 row)

Key idea: an aggregate function reduces many rows to one number, such as a count, sum, or average.

How aggregates treat NULL, and why it matters

Every aggregate except COUNT(*) ignores NULLs completely. That sounds harmless and is responsible for a whole genre of wrong reports. Suppose employees also has a nullable bonus column:

name	bonus
Ada	5000
Diego	NULL
Priya	2000
Mateo	NULL

SELECT COUNT(*)      AS all_rows,      -- 4   counts rows, NULLs included
       COUNT(bonus)  AS with_bonus,    -- 2   counts non-NULL values only
       SUM(bonus)    AS total_bonus,   -- 7000
       AVG(bonus)    AS avg_bonus      -- 3500, not 1750
FROM employees;

The average is 3500 because AVG computes 7000 divided by 2, the number of non-NULL bonuses, not by 4. Both answers are defensible in English: "the average bonus among people who got one" is 3500, and "the average bonus per employee" is 1750. SQL always gives you the first. If you want the second, say so:

SELECT AVG(COALESCE(bonus, 0)) FROM employees;   -- 1750.00

Two further rules. COUNT(DISTINCT department) counts distinct non-NULL values, giving 2 here. And an aggregate over zero rows returns NULL for SUM, AVG, MIN, and MAX, but 0 for COUNT:

SELECT SUM(salary), COUNT(*) FROM employees WHERE hire_year > 2030;

 sum  | count
------+-------
 NULL |     0

That NULL propagates: a report that adds this total to another number gets NULL, not the other number. Wrap it: COALESCE(SUM(salary), 0).

Key idea: aggregates skip NULLs, so AVG divides by the count of present values and SUM over no rows is NULL, not zero; COALESCE when you need a different convention.

GROUP BY: one summary per category

The real power appears with GROUP BY, which splits rows into groups sharing a value and computes the aggregate per group, like sorting receipts into labeled envelopes and totaling each envelope. To get the average salary in each department:

SELECT department,
       COUNT(*) AS headcount,
       AVG(salary) AS avg_salary
FROM employees
GROUP BY department;

This returns one row per department:

department	headcount	avg_salary
Engineering	2	91500
Sales	2	66500

Engineering averages 91500 (95000 and 88000), and Sales averages 66500 (62000 and 71000). A firm rule: every column in the SELECT list must either be inside an aggregate function or be listed in the GROUP BY. Mixing a bare column with an aggregate without grouping it is an error, because the database would not know which row's value to show:

SELECT department, name, AVG(salary)
FROM employees
GROUP BY department;

ERROR:  column "employees.name" must appear in the GROUP BY clause
        or be used in an aggregate function

The error is correct and worth reading literally: Engineering has two names, so there is no single name to print beside the group. Engines differ in how strictly they enforce this. PostgreSQL relaxes it when the grouping column is a primary key, since the key functionally determines everything else in its row, so GROUP BY e.id lets you select e.name freely. MySQL historically returned an arbitrary value from the group; since 5.7 the ONLY_FULL_GROUP_BY mode is on by default and it raises the same error. SQLite still accepts the query and picks a value from some arbitrary row in the group, which means the query runs and the answer is meaningless.

One more behaviour to know: GROUP BY puts all NULLs in a single group of their own. If Sam from the previous lesson were still present with a NULL department, the query above would return a third row with an empty department label and a headcount of 1, rather than dropping him.

Key idea: GROUP BY computes one aggregate per group, every selected column must be grouped or aggregated, and NULLs form one group rather than disappearing.

HAVING: filtering groups

To keep only some of the groups, you cannot use WHERE, because WHERE filters individual rows before grouping. Instead use HAVING, which filters groups after aggregation:

SELECT department, AVG(salary) AS avg_salary
FROM employees
GROUP BY department
HAVING AVG(salary) > 80000;

Against our data this returns only Engineering, whose average of 91500 exceeds 80000; Sales at 66500 is filtered out. The distinction is worth memorizing: WHERE filters rows before grouping; HAVING filters groups after grouping. You can use both in one query, WHERE first to discard rows, then GROUP BY, then HAVING to discard groups.

Key idea: WHERE filters rows before grouping; HAVING filters whole groups after aggregation.

Logical order of a query

It helps to know the order the database evaluates clauses, which differs from the order you write them: FROM, then WHERE, then GROUP BY, then HAVING, then SELECT, then ORDER BY, then LIMIT. That is why HAVING can reference an aggregate (grouping already happened) but WHERE cannot: when WHERE runs, no groups exist yet.

Key idea: grouping happens before HAVING and SELECT, which is why only HAVING can test an aggregate.

A full report, traced stage by stage

Put every clause together and answer a question a manager would really ask: among people hired in 2020 or earlier, which departments have at least two of them, and what do they earn?

SELECT d.dept_name,
       COUNT(*)                AS headcount,
       ROUND(AVG(e.salary), 2) AS avg_salary,
       MAX(e.salary)           AS top_salary
FROM employees AS e
JOIN departments AS d ON e.dept_id = d.dept_id
WHERE e.hire_year <= 2020
GROUP BY d.dept_name
HAVING COUNT(*) >= 2
ORDER BY avg_salary DESC;

Follow the six stages against hrdb:

1. FROM + JOIN   4 joined rows:  Ada/Eng, Diego/Sales, Priya/Eng, Mateo/Sales
2. WHERE         hire_year <= 2020 drops Diego (2021)  -> 3 rows
3. GROUP BY      Engineering {Ada 95000, Priya 88000}
                 Sales       {Mateo 71000}             -> 2 groups
4. HAVING        COUNT(*) >= 2 drops Sales (1 row)     -> 1 group
5. SELECT        compute COUNT, AVG, MAX on that group
6. ORDER BY      trivial, one row remains

 dept_name   | headcount | avg_salary | top_salary
-------------+-----------+------------+------------
 Engineering |         2 |   91500.00 |   95000.00
(1 row)

Both filters are doing different jobs and neither could do the other's. WHERE removed a row (Diego) before any grouping existed; HAVING removed a group (Sales) that only existed after grouping. Note also that ORDER BY avg_salary can use the SELECT alias, because ORDER BY runs last.

Key idea: a real report is FROM, WHERE, GROUP BY, HAVING, SELECT, ORDER BY in that order, and tracing the row count at each stage is how you debug one that returns the wrong answer.

The double-counting trap

Here is a wrong answer that looks entirely reasonable. Add a table recording which projects people work on:

assignments
emp_id  project
------  --------
   1    Atlas        <- Ada is on two projects
   1    Beacon
   3    Atlas

Now total the payroll per department for people who are on a project:

SELECT d.dept_name, SUM(e.salary) AS payroll
FROM employees   AS e
JOIN departments AS d ON e.dept_id = d.dept_id
JOIN assignments AS a ON a.emp_id  = e.id
GROUP BY d.dept_name;

 dept_name   | payroll
-------------+-----------
 Engineering | 278000.00     <- wrong; the real figure is 183000
(1 row)

The join to assignments made two copies of Ada's row, one per project, so SUM added her 95000 twice: 95000 + 95000 + 88000 = 278000. Nothing warned you, and the number is the right order of magnitude, which is exactly what makes it dangerous.

The fix is to keep the fan-out out of the thing you are summing. Use a membership test instead of a join:

SELECT d.dept_name, SUM(e.salary) AS payroll
FROM employees   AS e
JOIN departments AS d ON e.dept_id = d.dept_id
WHERE EXISTS (SELECT 1 FROM assignments AS a WHERE a.emp_id = e.id)
GROUP BY d.dept_name;

 dept_name   | payroll
-------------+-----------
 Engineering | 183000.00
(1 row)

The diagnostic habit: whenever a SUM looks too big, compare COUNT(*) against COUNT(DISTINCT e.id). If they differ, a join is duplicating rows.

Key idea: joining to a one-to-many table before aggregating multiplies the rows being summed, so check COUNT(*) against COUNT(DISTINCT key) whenever a total looks inflated.

Where people get stuck

• "WHERE can filter on an aggregate like AVG." WHERE runs before grouping, so it cannot see aggregates; put aggregate conditions in HAVING.
• "You can SELECT any column alongside an aggregate." Every non-aggregated column must appear in GROUP BY, or the query is invalid.
• "COUNT(column) and COUNT(*) are identical." COUNT(*) counts all rows, while COUNT(column) skips rows where that column is NULL.
• "HAVING replaces WHERE." They do different jobs; a query often uses WHERE to drop rows and HAVING to drop groups.
• Trusting AVG on a nullable column. It divides by the count of present values, not the number of rows. Decide whether a missing value means "unknown" or "zero" and write COALESCE if it means zero.
• Adding SUM over an empty set to something. It returns NULL, and NULL plus anything is NULL, so one empty category can blank out a whole total.
• Aggregating after a join that multiplies rows. If each employee joins to three rows in another table, SUM(salary) counts each salary three times. Aggregate before joining, or aggregate a distinct set.
• Relying on a bare column because SQLite allowed it. SQLite returns a value from an arbitrary row in the group. The query runs, the number is meaningless, and it will fail outright on PostgreSQL.

Recap

• Aggregate functions (COUNT, SUM, AVG, MIN, MAX) collapse many rows into one value.
• All aggregates except COUNT(*) ignore NULLs, so AVG divides by the count of present values and SUM over no rows is NULL.
• GROUP BY produces one aggregated row per category, and all NULLs form a single group.
• Every selected column must be inside an aggregate or listed in GROUP BY, with engines differing in how strictly they enforce it.
• WHERE filters rows before grouping; HAVING filters groups after aggregation.
• The logical order is FROM, WHERE, GROUP BY, HAVING, SELECT, ORDER BY, LIMIT, and tracing that order is how you debug a wrong report.

Sources

1. PostgreSQL Global Development Group. (n.d.). 2.7. Aggregate functions. PostgreSQL documentation. postgresql.org
2. PostgreSQL Global Development Group. (n.d.). 9.21. Aggregate functions: NULL handling, COUNT, and DISTINCT. PostgreSQL documentation. postgresql.org
3. PostgreSQL Global Development Group. (n.d.). 7.2. Table expressions: GROUP BY and HAVING. PostgreSQL documentation. postgresql.org
4. PostgreSQL Global Development Group. (n.d.). 3.5. Window functions: aggregates that keep the individual rows. PostgreSQL documentation. postgresql.org
5. SQLite Consortium. (n.d.). SELECT: the GROUP BY and HAVING clauses and bare columns. SQLite documentation. sqlite.org
6. SQLite Consortium. (n.d.). NULL handling in SQLite: aggregates and NULL. SQLite documentation. sqlite.org
7. Silberschatz, A., Korth, H. F., & Sudarshan, S. (2019). Introduction to SQL: aggregate functions. In Database system concepts (7th ed., ch. 3). McGraw-Hill. find source ↗

There is a query in this lesson that returns zero rows when it should return two, on data with nothing obviously wrong in it, using a construct that appears in every SQL tutorial. It is the NOT IN trap, it has been silently deleting records from reports for forty years, and by the end of the next few pages you will be immune to it.

The big picture

A subquery (or nested query) is a SELECT statement placed inside another statement. It lets you use the result of one query as an input to another, which is perfect for questions that reference an intermediate value, such as "who earns more than the company average?" A subquery is like solving a smaller problem in parentheses first, then plugging its answer into the bigger question.

Key idea: a subquery computes an inner result that the outer query then uses.

We will use the same employees table (Ada 95000, Diego 62000, Priya 88000, Mateo 71000).

Scalar subqueries

A scalar subquery returns a single value and can be used anywhere a value is expected. To find employees paid above the overall average:

SELECT name, salary
FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees);

The inner query computes the average (79000), and the outer query keeps everyone above it: Ada (95000) and Priya (88000). This is impossible in a single flat query because you need the aggregate before you can compare each row to it. Notice a bare WHERE salary > AVG(salary) would fail; the subquery is what makes the average available.

 name  | salary
-------+----------
 Ada   | 95000.00
 Priya | 88000.00
(2 rows)

Key idea: a scalar subquery returns one value, so it can stand in for a number inside a comparison.

Subqueries with IN

A subquery can return a list of values for use with IN. Suppose we also have a training table listing which employees enrolled in a course. To find employees who did enroll:

SELECT name
FROM employees
WHERE id IN (SELECT employee_id FROM training);

The subquery yields the set of enrolled ids, and the outer query keeps employees whose id is in that set. Using NOT IN instead would find employees who never enrolled. Take care that the subquery returns no NULLs, because a NULL in the list can make NOT IN behave surprisingly and return nothing.

Key idea: a subquery can supply a list for IN, and be cautious with NOT IN when NULLs are possible.

The NOT IN trap, worked out

That warning deserves a full demonstration, because "behave surprisingly" undersells it. Let the training table hold three rows, one of which has a NULL employee id (perhaps an external attendee who was never linked to a staff record):

training
employee_id
-----------
     1
     3
    NULL

Now ask the obvious question, "who has never taken the training?":

SELECT name
FROM employees
WHERE id NOT IN (SELECT employee_id FROM training);

(0 rows)

Zero rows, when the right answer is Diego and Mateo. Here is why. NOT IN expands into a chain of not-equals joined by AND:

id NOT IN (1, 3, NULL)   is   id <> 1  AND  id <> 3  AND  id <> NULL

Ada   (1):  1<>1 FALSE                                    -> FALSE     dropped
Diego (2):  2<>1 TRUE  AND 2<>3 TRUE  AND 2<>NULL UNKNOWN -> UNKNOWN   dropped
Priya (3):  3<>3 FALSE                                    -> FALSE     dropped
Mateo (4):  4<>1 TRUE  AND 4<>3 TRUE  AND 4<>NULL UNKNOWN -> UNKNOWN   dropped

Every row comes out FALSE or UNKNOWN, and a WHERE clause keeps only TRUE. The single NULL in the subquery poisoned the entire comparison. Worse, the query is not broken today and broken tomorrow: it is correct until the day someone inserts one NULL, and then it quietly returns nothing forever.

Three fixes, in order of preference:

-- 1. NOT EXISTS: NULL-safe by construction, and usually the fastest
SELECT name FROM employees AS e
WHERE NOT EXISTS (SELECT 1 FROM training AS t WHERE t.employee_id = e.id);

-- 2. exclude the NULLs explicitly
SELECT name FROM employees
WHERE id NOT IN (SELECT employee_id FROM training WHERE employee_id IS NOT NULL);

-- 3. the anti-join from Lesson 8
SELECT e.name FROM employees AS e
LEFT JOIN training AS t ON t.employee_id = e.id
WHERE t.employee_id IS NULL;

All three return Diego and Mateo. Note that plain IN is not affected in the same way: a NULL in the list can only turn a false into unknown, and both are dropped anyway, so IN gives the right answer. It is specifically the negation that breaks.

Key idea: a single NULL inside a NOT IN subquery makes the whole condition unknown for every non-matching row, silently returning zero rows; prefer NOT EXISTS.

EXISTS

EXISTS tests whether a subquery returns any row at all, and is often used for a correlated subquery, one that references the outer row. It is true as soon as one matching row is found, like a librarian answering "yes, we have at least one" without counting them all:

SELECT name
FROM employees AS e
WHERE EXISTS (
  SELECT 1 FROM training AS t
  WHERE t.employee_id = e.id
);

For each employee, the inner query checks whether any training row references that employee; if so, the employee is kept. EXISTS often reads more naturally than IN for "is there at least one related row?" questions and can be faster because it stops at the first match.

Correlation is the interesting part, and it is worth tracing once. Because the inner query mentions e.id, it cannot be computed in advance; it is conceptually re-run for each outer row. Here is a correlated aggregate that answers a question a plain scalar subquery cannot: who earns more than their own department's average?

SELECT name, department, salary
FROM employees AS e
WHERE salary > (SELECT AVG(salary)
                FROM employees AS e2
                WHERE e2.dept_id = e.dept_id);

the inner average, evaluated per row:
  Ada    Engineering  dept avg 91500   95000 > 91500  TRUE   kept
  Diego  Sales        dept avg 66500   62000 > 66500  false
  Priya  Engineering  dept avg 91500   88000 > 91500  false
  Mateo  Sales        dept avg 66500   71000 > 66500  TRUE   kept

 name  | department  | salary
-------+-------------+----------
 Ada   | Engineering | 95000.00
 Mateo | Sales       | 71000.00
(2 rows)

Notice that Priya at 88000 is excluded while Mateo at 71000 is included; each row is judged against a different threshold. Note also the alias e2: without two different aliases the inner employees would hide the outer one and the correlation would be lost.

Key idea: EXISTS is true if the subquery returns any row, and a correlated subquery is re-evaluated per outer row, which is what lets each row be compared against its own group's value.

Derived tables and CTEs

A subquery can also sit in the FROM clause, where it acts as a temporary table for the rest of the query. This is a derived table, and it must be given an alias:

SELECT dept_name, ROUND(avg_salary, 2) AS avg_salary
FROM (
    SELECT d.dept_name, AVG(e.salary) AS avg_salary
    FROM employees   AS e
    JOIN departments AS d ON e.dept_id = d.dept_id
    GROUP BY d.dept_name
) AS dept_avgs                       -- the alias is required
WHERE avg_salary > 80000;

Nesting like that reads badly once there are two or three levels, so modern SQL provides the common table expression, written with WITH. It is the same computation, named and lifted to the top:

WITH dept_avgs AS (
    SELECT d.dept_name, AVG(e.salary) AS avg_salary
    FROM employees   AS e
    JOIN departments AS d ON e.dept_id = d.dept_id
    GROUP BY d.dept_name
)
SELECT dept_name, ROUND(avg_salary, 2) AS avg_salary
FROM dept_avgs
WHERE avg_salary > 80000;

 dept_name   | avg_salary
-------------+------------
 Engineering |   91500.00
(1 row)

CTEs read top to bottom like a small program, can be chained (WITH a AS (...), b AS (... FROM a ...)), and can be referenced more than once. One performance caveat worth knowing: PostgreSQL before version 12 always materialized a CTE, which acted as an optimization fence and sometimes made the CTE version slower than the equivalent subquery. From version 12 it inlines them when it can, and you can force either behaviour with AS MATERIALIZED or AS NOT MATERIALIZED.

Key idea: a derived table is a subquery in FROM and needs an alias; a CTE is the same thing given a name at the top, which is almost always easier to read.

Subquery or join?

Many subqueries can be rewritten as joins and vice versa. As a guideline:

• Use a join when you want columns from both tables in the output.
• Use a subquery when you only need one table's rows but the filter depends on a computed value or another table's contents.

Both are correct; choose whichever makes the query easiest to read.

Key idea: reach for a join when you need columns from both tables, and a subquery when you only need to filter by an inner result.

Where people get stuck

• "You can compare a column directly to an aggregate in WHERE." A bare AVG in WHERE fails; wrap it in a scalar subquery so the value is computed first.
• "A scalar subquery can return many rows." Used where a single value is expected, it must return exactly one row, or you get an error.
• "NOT IN is always safe." If the subquery returns a NULL, NOT IN yields zero rows; EXISTS or filtering out NULLs avoids this.
• "A subquery is always slower than a join." Performance varies; EXISTS can stop at the first match, and modern optimizers often treat equivalent forms similarly.
• Reusing the same table name in a correlated subquery. Without a second alias the inner reference shadows the outer one, and the correlation you intended silently disappears.
• Forgetting the alias on a derived table. PostgreSQL and MySQL both reject a FROM subquery with no name, and the error message rarely says so plainly.
• Assuming a CTE is always free. On PostgreSQL 11 and earlier a CTE is materialized, which can be far slower than the same logic written inline. Check with EXPLAIN before assuming readability was free.
• Testing a subquery only against data with no NULLs. The NOT IN trap is invisible in tidy sample data and appears the first day production has one missing value.

Recap

• A subquery is a SELECT nested inside another statement, feeding it a value or set.
• A scalar subquery returns one value, usable inside a comparison such as above the average.
• A subquery with IN supplies a list; NOT IN over a list containing NULL returns zero rows, so prefer NOT EXISTS.
• EXISTS is true if the subquery returns any row and pairs well with correlated subqueries, which are re-evaluated per outer row.
• A derived table is a subquery in FROM (alias required); a CTE is the same logic named with WITH and is usually clearer.
• Use a join for columns from both tables, a subquery to filter by an inner result.

Sources

1. PostgreSQL Global Development Group. (n.d.). 9.24. Subquery expressions: EXISTS, IN, NOT IN, and ANY/ALL. PostgreSQL documentation. postgresql.org
2. PostgreSQL Global Development Group. (n.d.). 7.8. WITH queries (common table expressions): materialization and recursion. PostgreSQL documentation. postgresql.org
3. PostgreSQL Global Development Group. (n.d.). 7.2. Table expressions: subqueries in FROM. PostgreSQL documentation. postgresql.org
4. PostgreSQL Global Development Group. (n.d.). 9.2. Comparison functions and operators: NULL comparison semantics. PostgreSQL documentation. postgresql.org
5. SQLite Consortium. (n.d.). NULL handling in SQLite: IN and NOT IN with NULL operands. SQLite documentation. sqlite.org
6. SQLite Consortium. (n.d.). Many small queries are efficient in SQLite: subqueries versus joins. SQLite documentation. sqlite.org
7. Silberschatz, A., Korth, H. F., & Sudarshan, S. (2019). Introduction to SQL: nested subqueries. In Database system concepts (7th ed., ch. 3). McGraw-Hill. find source ↗

Reading a table wrong wastes an afternoon. Writing to it wrong can be permanent. This lesson covers the three statements that change data, the two ways they most often go wrong - a forgotten WHERE clause and a query assembled out of user input - and the small habits that make both mistakes almost impossible.

The big picture

So far we have only read data. The three data manipulation statements that change it are INSERT (add rows), UPDATE (change rows), and DELETE (remove rows). They are simple, but two of them carry a famous hazard: forgetting the WHERE clause, which makes the change apply to every row in the table.

Key idea: INSERT adds, UPDATE changes, DELETE removes, and a missing WHERE on the last two hits every row.

INSERT: adding rows

INSERT adds new rows. List the columns, then the matching values, like filling in a new line on a form:

INSERT INTO employees (name, department, salary, hire_year)
VALUES ('Nadia', 'Engineering', 90000, 2023);

Naming the columns explicitly is good practice; it keeps the statement working even if the table later gains a column, and it lets the database fill unlisted columns with their defaults or NULL. You can insert several rows at once by listing multiple value tuples separated by commas:

INSERT INTO employees (name, department, salary, hire_year)
VALUES ('Omar', 'Sales', 68000, 2022),
       ('Lena', 'Marketing', 74000, 2023);

Three variations are worth knowing now. INSERT ... SELECT copies rows from a query instead of listing literals, which is how archive and migration scripts are written:

INSERT INTO employees_archive (id, name, salary)
SELECT id, name, salary
FROM employees
WHERE hire_year < 2019;

RETURNING hands back the rows that were actually written, which saves a second query when the database generated the key:

INSERT INTO employees (name, department, dept_id, salary, hire_year)
VALUES ('Nadia', 'Engineering', 1, 90000, 2023)
RETURNING id, name;          -- PostgreSQL, and SQLite since 3.35

 id | name
----+-------
  6 | Nadia

And UPSERT handles "insert it, or update it if it is already there" in one atomic statement, instead of a check-then-insert that two concurrent users can both pass:

INSERT INTO departments (dept_id, dept_name, building)
VALUES (3, 'Marketing', 'West')
ON CONFLICT (dept_id) DO UPDATE SET building = EXCLUDED.building;

EXCLUDED is the row you tried to insert. This spelling works on PostgreSQL and SQLite 3.24 and later; MySQL writes ON DUPLICATE KEY UPDATE, and the SQL standard has MERGE.

Key idea: INSERT adds rows; name the columns so the statement stays robust, use INSERT ... SELECT to copy, RETURNING to get generated keys, and ON CONFLICT for a race-free upsert.

UPDATE: changing rows

UPDATE modifies existing rows. The SET clause assigns new values, and the WHERE clause chooses which rows to change. To give Nadia a raise:

UPDATE employees
SET salary = 95000
WHERE name = 'Nadia';

The WHERE clause is critical. If you omit it, UPDATE changes every row in the table. The statement UPDATE employees SET salary = 95000; would set every employee's salary to 95000. Always write and double-check the WHERE clause before running an UPDATE. You can update several columns at once by separating assignments with commas:

UPDATE employees
SET salary = salary * 1.05, department = 'Engineering'
WHERE name = 'Omar';

Note that salary = salary * 1.05 reads the old value and writes a 5 percent raise, a common pattern.

Here is what the missing WHERE actually does to hrdb, so the warning is not abstract:

UPDATE employees SET salary = 95000;      -- no WHERE clause

before                             after
name   salary                      name   salary
-----  --------                    -----  --------
Ada    95000.00                    Ada    95000.00
Diego  62000.00        ---->       Diego  95000.00
Priya  88000.00                    Priya  95000.00
Mateo  71000.00                    Mateo  95000.00

UPDATE 4          <- the row count the server reports back. Read it. It should have said 1.

Three original salaries are gone and there is no undo outside a transaction or a backup. The row count the server prints is your last line of defence, so make a habit of reading it: if you meant to change one row and it says four, you have three seconds to act - which brings us to the real safety net. Run the statement inside an explicit transaction and you can still change your mind:

BEGIN;
UPDATE employees SET salary = 95000 WHERE name = 'Nadia';
-- server says UPDATE 1. If it had said UPDATE 4, type ROLLBACK instead.
COMMIT;

Key idea: UPDATE ... SET changes columns, without WHERE it changes every row, and the reported row count plus an explicit transaction are what turn a disaster into a ROLLBACK.

DELETE: removing rows

DELETE removes rows that match its WHERE clause:

DELETE FROM employees
WHERE name = 'Lena';

Like UPDATE, DELETE without a WHERE clause empties the whole table: DELETE FROM employees; removes every row. Because of referential integrity, the database may also refuse to delete a row that other tables still reference through a foreign key, unless the relationship is set to cascade.

Three statements are easy to confuse, so keep them straight:

Statement	What it removes	Notes
DELETE FROM t WHERE ...	Matching rows	Row by row, fires triggers, can be rolled back, respects foreign keys.
TRUNCATE TABLE t	All rows, table stays	Much faster on big tables; usually skips row triggers; often blocked if other tables reference it.
DROP TABLE t	The table itself	Structure and data both gone.

Key idea: DELETE removes matching rows, TRUNCATE empties the table quickly, DROP removes the table, and without WHERE the first of those empties it row by row.

The safe habit: preview with SELECT

Before running a risky UPDATE or DELETE, run the same WHERE as a SELECT first to see exactly which rows you are about to affect. For example, before DELETE FROM employees WHERE hire_year < 2019; run SELECT * FROM employees WHERE hire_year < 2019; and confirm the list looks right. It is the database version of measuring twice and cutting once.

Key idea: preview a destructive change by running its WHERE as a SELECT first.

Never build SQL by gluing strings together

Once a program rather than a person is writing your statements, one rule matters more than all the others: values from outside your program must never become part of the SQL text. Here is the shape of code that breaks the rule:

# UNSAFE. Do not write this.
name = request.form["name"]
cur.execute("SELECT id, salary FROM employees WHERE name = '" + name + "'")

Start with an ordinary customer rather than an attacker. Someone called O'Brien fills in the form, and the statement the database receives is:

SELECT id, salary FROM employees WHERE name = 'O'Brien'
                                                  ^ the quote ends the literal here
ERROR:  syntax error at or near "Brien"

That crash is the whole vulnerability, visible in its harmless form. The apostrophe was data, and it changed the structure of the statement. A value chosen deliberately can change the structure in more useful ways: submit ' OR '1'='1 and the database is handed WHERE name = '' OR '1'='1', a condition that is true for every row, so a lookup of one person returns the entire table. The database did nothing wrong; it received a syntactically valid statement and executed it faithfully. The bug is entirely in the program that let input become syntax.

The fix is parameterized queries, also called prepared statements, and it is not a matter of escaping quotes more carefully. The statement and the values travel to the server separately. The server parses the statement once with a placeholder marking where a value goes, and then binds the value into the already-parsed plan, where it can never be re-read as syntax:

# SAFE. The value is passed as a parameter, not concatenated.
cur.execute("SELECT id, salary FROM employees WHERE name = %s", (name,))   # psycopg, PostgreSQL
cur.execute("SELECT id, salary FROM employees WHERE name = ?",  (name,))   # sqlite3, Python stdlib

# updates and inserts work the same way
cur.execute("UPDATE employees SET salary = %s WHERE id = %s", (new_salary, emp_id))

Now O'Brien is searched for as the literal seven-character name, and ' OR '1'='1 is searched for as a literal name that simply matches nobody. Notice also that the parameterized version is shorter and easier to read than the concatenated one, which is unusual for a security fix.

Two traps to be explicit about. First, %s in psycopg is a database placeholder, not Python string formatting. These two lines look almost identical to the safe one and are both unsafe, because Python builds the final string before the driver ever sees it:

# UNSAFE - Python interpolated the value before the driver was involved
cur.execute("SELECT * FROM employees WHERE name = '%s'" % name)
cur.execute(f"SELECT * FROM employees WHERE name = '{name}'")

Second, parameters substitute values, never identifiers. You cannot parameterize a table name, a column name, or a sort direction. When those genuinely have to vary, check the input against a fixed allowlist and let nothing else through:

SORTABLE = {"name", "salary", "hire_year"}
if column not in SORTABLE:
    raise ValueError("unsupported sort column")
cur.execute(f"SELECT * FROM employees ORDER BY {column}")   # safe only because of the check above

Around that core, two more layers help. Give the application's database account only the privileges it needs, so a role that only ever reads and writes rows cannot drop a table even if something slips through. And prefer a query builder or ORM, which parameterizes by default - while remembering that almost every one of them offers a raw-SQL escape hatch that does not.

Key idea: concatenating input into SQL lets data become syntax, which breaks on an apostrophe and can be steered deliberately; parameterized queries send values on a separate channel so they can never be parsed as code.

Where people get stuck

• "UPDATE without WHERE changes only one row." It changes every row; the WHERE clause is what limits the scope.
• "DELETE FROM table removes the table itself." DELETE removes rows; the empty table remains. DROP TABLE removes the table.
• "You must list every column in an INSERT." You list the columns you provide; the rest take their default or NULL.
• "A DELETE always succeeds." Referential integrity can block deleting a row that other tables still reference, unless the relationship cascades.
• Ignoring the reported row count. "UPDATE 4" when you expected "UPDATE 1" is the database telling you, in advance of the damage report, that something is wrong.
• "Escaping quotes is enough to be safe." Hand-rolled escaping misses encodings, comment syntax, and numeric contexts that need no quotes at all. Parameterize instead; it is the only approach that removes the whole class of problem.
• Believing an f-string is a parameterized query. If Python (or JavaScript, or Java) built the final string, the driver received one blob of SQL and there were never any parameters.
• Trying to parameterize a table or column name. Placeholders bind values only. Validate identifiers against an allowlist and interpolate only names that passed the check.
• Check-then-insert instead of upsert. Two sessions can both pass the check and both insert. Use ON CONFLICT so the database resolves the race.

Recap

• INSERT adds one or more rows; naming the columns keeps it robust and lets defaults fill the rest, and INSERT ... SELECT, RETURNING, and ON CONFLICT cover copying, generated keys, and upserts.
• UPDATE ... SET changes columns in rows chosen by WHERE; without WHERE it changes all rows.
• DELETE removes rows chosen by WHERE; TRUNCATE empties the table fast; DROP removes the table itself.
• Foreign keys can block a delete unless the relationship cascades.
• Preview any risky change by first running its WHERE as a SELECT, read the reported row count, and wrap real changes in a transaction so ROLLBACK is available.
• Never concatenate outside input into SQL text; use parameterized queries, and validate identifiers against an allowlist when a name must vary.

Sources

1. OWASP Foundation. (n.d.). SQL injection prevention cheat sheet. OWASP Cheat Sheet Series. cheatsheetseries.owasp.org
2. OWASP Foundation. (n.d.). Query parameterization cheat sheet. OWASP Cheat Sheet Series. cheatsheetseries.owasp.org
3. Python Software Foundation. (n.d.). sqlite3: DB-API 2.0 interface for SQLite databases: placeholders and how to use them. Python 3 documentation. docs.python.org
4. Psycopg contributors. (n.d.). Passing parameters to SQL queries. Psycopg 3 documentation. psycopg.org
5. PostgreSQL Global Development Group. (n.d.). Chapter 6. Data manipulation: inserting, updating, and deleting data. PostgreSQL documentation. postgresql.org
6. PostgreSQL Global Development Group. (n.d.). INSERT: ON CONFLICT and RETURNING. PostgreSQL documentation. postgresql.org
7. SQLite Consortium. (n.d.). UPSERT. SQLite documentation. sqlite.org

Application code that validates data is a promise. A constraint is a guarantee. The promise holds until someone writes a second application, or a migration script, or opens a database console at two in the morning. The guarantee holds always, for every writer, forever. This lesson is about moving as many of your rules as possible from the first category into the second.

The big picture

Reading and changing data assumes the tables already exist. The data definition statements create and alter that structure. The most important is CREATE TABLE, which defines a table's columns, their types, and the constraints that keep its data valid. Constraints are rules the database enforces for you, so bad data is rejected no matter which program tries to write it.

Key idea: CREATE TABLE defines structure, and constraints are automatic rules that keep the data valid.

A complete CREATE TABLE

CREATE TABLE employees (
    id          INTEGER      PRIMARY KEY,
    name        VARCHAR(100) NOT NULL,
    email       VARCHAR(255) UNIQUE,
    department  VARCHAR(50)  DEFAULT 'Unassigned',
    salary      DECIMAL(10,2) CHECK (salary >= 0),
    dept_id     INTEGER,
    FOREIGN KEY (dept_id) REFERENCES departments(dept_id)
);

Read it column by column. Each line gives a column name, a data type, and zero or more constraints. This one statement encodes a great deal of protection, like a bouncer at the door who checks every value before it gets in.

Key idea: each column declares a name, a type, and any constraints, all in one statement.

The constraint toolbox

Here is what each constraint in the example does, with a plain description:

Constraint	What it guarantees
PRIMARY KEY	Makes id unique and non-NULL, the row's identifier.
NOT NULL	Requires name to always have a value; an insert that omits it is rejected.
UNIQUE	Forbids two rows from sharing an email, without making it the primary key.
DEFAULT	Supplies a value ('Unassigned') when an insert does not specify department.
CHECK	Enforces a custom rule; here salary can never be negative.
FOREIGN KEY ... REFERENCES	Ties dept_id to departments, enforcing referential integrity.

Rather than hoping every program that touches the data remembers the rules, you declare the rules once and the DBMS enforces them for everyone, forever. This is one of the strongest arguments for a real database over a pile of files.

Key idea: declare rules once as constraints and the database enforces them for every writer, permanently.

Watching each constraint refuse a row

Constraints are more convincing when you see them fire. Every error below is a real PostgreSQL message from the table defined above.

INSERT INTO employees (id, email) VALUES (7, 'x@example.com');
ERROR:  null value in column "name" of relation "employees"
        violates not-null constraint

INSERT INTO employees (id, name, email) VALUES (8, 'Ana', 'ada@example.com');
ERROR:  duplicate key value violates unique constraint "employees_email_key"
DETAIL:  Key (email)=(ada@example.com) already exists.

INSERT INTO employees (id, name, salary) VALUES (9, 'Bo', -100);
ERROR:  new row for relation "employees" violates check constraint
        "employees_salary_check"

INSERT INTO employees (id, name, dept_id) VALUES (10, 'Cy', 99);
ERROR:  insert or update on table "employees" violates foreign key
        constraint "employees_dept_id_fkey"
DETAIL:  Key (dept_id)=(99) is not present in table "departments".

INSERT INTO employees (id, name) VALUES (1, 'Zed');
ERROR:  duplicate key value violates unique constraint "employees_pkey"
DETAIL:  Key (id)=(1) already exists.

Five bad rows, five refusals, and not one line of application code involved. Notice that the messages name the constraint, which is the practical reason to give constraints explicit names in production schemas: an error saying employees_salary_nonnegative is far more useful to a support engineer at 3 a.m. than one saying employees_check1.

Now the case that surprises everyone. UNIQUE does not stop repeated NULLs:

INSERT INTO employees (id, name, email) VALUES (11, 'Dee', NULL);   -- accepted
INSERT INTO employees (id, name, email) VALUES (12, 'Eve', NULL);   -- also accepted

Two NULLs are not equal to each other (three-valued logic again), so the uniqueness test never fires. If a column must be genuinely unique, mark it NOT NULL UNIQUE. PostgreSQL 15 and later also accept UNIQUE NULLS NOT DISTINCT, which treats NULLs as equal for the purposes of the constraint.

That is also the clean way to distinguish the three similar-looking constraints:

	Forbids duplicates	Forbids NULL	How many per table
PRIMARY KEY	Yes	Yes	Exactly one
UNIQUE	Yes, among non-NULL values	No	Any number
NOT NULL	No	Yes	Any number

Key idea: constraints reject bad rows with named errors before any application code runs, and UNIQUE permits many NULLs because NULLs are never equal to each other.

CHECK constraints, and their limits

A CHECK can look at more than one column of the row being written, which is where it earns its keep:

CREATE TABLE assignments (
    emp_id     INTEGER NOT NULL REFERENCES employees(id),
    start_date DATE    NOT NULL,
    end_date   DATE,
    hours      NUMERIC(4,1) NOT NULL,
    CONSTRAINT assignments_dates_ordered
        CHECK (end_date IS NULL OR end_date >= start_date),
    CONSTRAINT assignments_hours_sane
        CHECK (hours > 0 AND hours <= 40)
);

Two habits are visible there. The date rule explicitly allows NULL, because a CHECK passes when its condition is unknown, and being deliberate about that beats discovering it later. And both constraints are named, so violations report something readable.

What a CHECK cannot do is just as important. It only sees the row in front of it, and standard SQL forbids subqueries inside it, so a rule like "an employee's salary must not exceed their department's budget" is not expressible as a CHECK: it depends on another table. Rules that span rows or tables need a foreign key where one fits, and otherwise a trigger or carefully written application logic inside a transaction.

Key idea: a CHECK can enforce any rule about a single row and nothing beyond it, and it passes when its condition is unknown, so handle NULL explicitly.

Generated keys across dialects

Almost every table wants an automatically assigned surrogate key, and every engine spells it differently:

System	Auto-assigned integer primary key
PostgreSQL (10+)	id INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY (older code uses SERIAL)
SQLite	id INTEGER PRIMARY KEY - this exact spelling aliases the internal rowid and auto-assigns
MySQL and MariaDB	id INT AUTO_INCREMENT PRIMARY KEY
SQL Server	id INT IDENTITY(1,1) PRIMARY KEY

The SQLite row is a genuine trap: INTEGER PRIMARY KEY auto-assigns, but INT PRIMARY KEY or BIGINT PRIMARY KEY do not, because only the exact type name INTEGER triggers the rowid alias. While you are in SQLite, remember from Lesson 2 that declared types are only advisory unless the table is created with STRICT.

Key idea: every engine has auto-assigned keys and every engine spells them differently, and in SQLite only the literal type INTEGER gets the behaviour.

Changing and dropping tables

Two more statements round out schema work. ALTER TABLE modifies an existing table, for instance adding a column:

ALTER TABLE employees ADD COLUMN start_date DATE;

Adding a plain nullable column is easy because existing rows simply get NULL. Adding a NOT NULL column to a table that already has rows is not:

ALTER TABLE employees ADD COLUMN status VARCHAR(10) NOT NULL;
ERROR:  column "status" of relation "employees" contains null values

ALTER TABLE employees ADD COLUMN status VARCHAR(10) NOT NULL DEFAULT 'active';   -- works

The default gives the existing four rows something to hold, which is why almost every real migration that tightens a column is a three-step dance: add it nullable, backfill it, then add the constraint.

SQLite deserves a warning here. Its ALTER TABLE supports only RENAME TO, ADD COLUMN, RENAME COLUMN (3.25+), and DROP COLUMN (3.35+). You cannot change a column's type or add a constraint after the fact. The documented workaround is to create a new table with the desired shape, copy the rows across with INSERT ... SELECT, drop the old table, and rename the new one - all inside one transaction.

A related feature worth knowing: a generated column is computed from other columns and cannot be written directly, which makes it the correct home for derived values that you nonetheless want to index:

ALTER TABLE employees
  ADD COLUMN annual_salary NUMERIC(12,2) GENERATED ALWAYS AS (salary * 12) STORED;

PostgreSQL supports STORED generated columns from version 12; SQLite supports both STORED and VIRTUAL from 3.31.

And DROP TABLE deletes a table and all its data permanently:

DROP TABLE employees;

Handle DROP with great care; it is irreversible and takes the data with it. A related statement, TRUNCATE TABLE, quickly removes all rows but keeps the empty table. In short: CREATE builds structure, ALTER changes it, DROP destroys it, and constraints defend the data living inside.

Key idea: CREATE builds, ALTER changes, DROP destroys; TRUNCATE empties but keeps the table.

Where people get stuck

• "UNIQUE and PRIMARY KEY are the same." Both forbid duplicates, but a table has one primary key (also non-NULL and the row's identity), while UNIQUE can appear on several columns and may allow a NULL.
• "A CHECK constraint runs only at creation time." CHECK is enforced on every insert and update, so any change that would violate it fails.
• "DEFAULT forces the column to that value." DEFAULT only supplies the value when the insert omits the column; an explicit value overrides it.
• "TRUNCATE and DROP do the same thing." TRUNCATE removes all rows but keeps the table; DROP removes the table itself.
• Expecting UNIQUE to block repeated NULLs. It does not, because NULLs are never equal. Write NOT NULL UNIQUE when you mean it.
• Writing a CHECK that needs another table. CHECK sees only the current row and may not contain a subquery. Cross-table rules need a foreign key, a trigger, or transactional application logic.
• Adding a NOT NULL column to a populated table. It fails unless you supply a DEFAULT, or split the migration into add, backfill, then constrain.
• Leaving constraints unnamed. The engine invents a name, and the day something violates it the error tells nobody anything. Name every constraint you would want to read in a log.
• Assuming ALTER TABLE is portable. SQLite cannot change a column's type or add a constraint at all; the fix is rebuild-and-copy inside a transaction.

Recap

• CREATE TABLE defines columns, types, and constraints in one statement.
• PRIMARY KEY, NOT NULL, UNIQUE, DEFAULT, CHECK, and FOREIGN KEY each guard the data in a specific way, and each refuses bad rows with a named error.
• PRIMARY KEY is unique and non-NULL and there is one per table; UNIQUE allows multiple NULLs; NOT NULL says nothing about duplicates.
• CHECK can enforce any rule about the row in front of it and nothing beyond it.
• Auto-assigned keys are spelled differently in every engine, and SQLite requires the literal type INTEGER.
• ALTER TABLE changes structure (with a DEFAULT needed to add NOT NULL to populated tables), DROP TABLE removes a table permanently, and TRUNCATE empties one but keeps its definition.

Sources

1. PostgreSQL Global Development Group. (n.d.). 5.5. Constraints: NOT NULL, UNIQUE, CHECK, primary and foreign keys. PostgreSQL documentation. postgresql.org
2. PostgreSQL Global Development Group. (n.d.). CREATE TABLE: identity columns, generated columns, and table constraints. PostgreSQL documentation. postgresql.org
3. PostgreSQL Global Development Group. (n.d.). ALTER TABLE. PostgreSQL documentation. postgresql.org
4. PostgreSQL Global Development Group. (n.d.). TRUNCATE. PostgreSQL documentation. postgresql.org
5. SQLite Consortium. (n.d.). CREATE TABLE: INTEGER PRIMARY KEY, constraints, and generated columns. SQLite documentation. sqlite.org
6. SQLite Consortium. (n.d.). Datatypes in SQLite: type affinity and STRICT tables. SQLite documentation. sqlite.org
7. Silberschatz, A., Korth, H. F., & Sudarshan, S. (2019). Intermediate SQL: integrity constraints. In Database system concepts (7th ed., ch. 4). McGraw-Hill. find source ↗

The same query, on the same data, on the same machine, can take four milliseconds or forty seconds. Nothing in the SQL text tells you which. The difference is whether the engine could use an index, and that depends on facts about the index most people never learn: what it physically stores, how selective the condition is, and whether the condition preserves the sorted order the index is built on. This lesson makes all three concrete.

The big picture

As tables grow to millions of rows, a query with a WHERE clause could, in the worst case, examine every row, a full table scan that is slow. An index is a separate, sorted data structure the database maintains to find rows fast, turning a slow scan of every row into a quick targeted lookup. It is the single most important tool for query performance.

Key idea: an index is a fast lookup structure that lets the database find rows without scanning the whole table.

The book-index analogy

An index in a database works exactly like the index at the back of a textbook. Without it, finding every mention of "normalization" means reading every page, front to back. With it, you jump to an alphabetized list, find the term, and go straight to the right pages. A database index similarly keeps the values of a column in a sorted, searchable structure (commonly a B-tree, a balanced tree that stays shallow even for huge tables) that points to the matching rows, so the engine can locate them without scanning the whole table.

Key idea: like a book's index, a database index points straight to the rows you want instead of reading everything.

What a B-tree index actually stores

The analogy takes you only so far. Physically, a B-tree index is a shallow tree of fixed-size pages. The leaf pages hold the real content: pairs of an indexed value and a pointer to the row that has it, kept in sorted order. The pages above hold only separator keys that say which child to descend into.

                        +-----------------+
   root page            |  Mateo          |          separator keys only
                        +-----------------+
                           |           |
              +------------+           +------------+
              v                                     v
      +-------------------+               +---------------------+
      | Ada   | Diego     |  ---next-->   | Mateo  | Priya      |   leaf pages, sorted
      +-------------------+               +---------------------+

   what one leaf entry holds:
      ( 'Ada'   , row pointer -> page 12, slot 3 )
      ( 'Diego' , row pointer -> page 12, slot 1 )
      ( 'Mateo' , row pointer -> page 41, slot 7 )
      ( 'Priya' , row pointer -> page 12, slot 2 )

Three consequences follow from that picture, and they explain nearly everything about index behaviour.

It is shallow, so lookups are cheap. An 8 KB page holds hundreds of entries. With a fan-out of roughly 200, three levels address about eight million rows and four levels 1.6 billion, so finding one row in a billion-row table costs about four page reads.

It is sorted, so ranges are cheap too. Leaf pages are chained left to right, so once the first matching key is found the engine simply reads forward. That is why one index serves =, <, BETWEEN, and ORDER BY equally well.

It stores a pointer, not the row. After finding an entry the engine must usually fetch the actual row, and those fetches land in table order, unrelated to index order. This second step is the hidden cost behind most of what follows. (In PostgreSQL the pointer is a physical ctid; InnoDB stores the primary key value instead, which is why a wide primary key is expensive in MySQL.)

Key idea: a B-tree leaf stores sorted value-plus-row-pointer pairs in a shallow chained structure, which makes both equality and range lookups cheap but leaves a second fetch for the row itself.

Creating an index

CREATE INDEX idx_employees_dept
ON employees (department);

After this, a query like SELECT * FROM employees WHERE department = 'Sales' can use the index to jump to the matching rows instead of scanning. Primary keys and columns marked UNIQUE are indexed automatically by most databases, which is why looking a row up by its primary key is fast.

Key idea: CREATE INDEX builds a lookup structure, and primary keys are indexed automatically.

Which columns to index

Index the columns that appear often in the places the database searches or sorts:

• WHERE filters, so the engine can jump to matching rows.
• JOIN conditions, especially foreign keys, so matches are found quickly.
• ORDER BY columns, so results can be returned already in order.

These are exactly where an index pays off most. Indexing a column that is rarely searched wastes space and effort.

Key idea: index the columns used in WHERE, JOIN, and ORDER BY, not columns nobody searches.

Why an index on a low-cardinality column rarely helps

Suppose employees has a million rows and a boolean is_active column, 950,000 of them true. You index it and query WHERE is_active = true. The index is not used, and the planner is right.

Work through what using it would cost. The engine reads 950,000 leaf entries and follows each pointer to a row. Those rows are scattered in no particular order, so it performs close to a million random page accesses, revisiting many pages repeatedly. A sequential scan instead reads every page exactly once in physical order, which storage hardware handles far better. Reading everything in order beats reading almost everything out of order.

rows matched      what the planner does
------------      -----------------------------------------------
      10          index scan: 10 index reads + 10 row fetches
   5,000          index scan: still far cheaper than the whole table
 300,000          borderline; depends on row width and correlation
 950,000          sequential scan: the index is pure overhead

The rule of thumb is that an index earns its keep when the condition selects roughly the first few percent of a table and loses beyond that. This property is called selectivity. A column with few distinct values - a boolean, a two-option status - is low-cardinality and therefore low-selectivity, and an index on it mostly sits there costing storage and slowing writes.

One important exception: if the value you search for is the rare one, say status = 'failed' in a job table that is 99.9 percent successful, the index is excellent even though the column has two values. Better still, index only the rows you care about:

CREATE INDEX idx_jobs_failed ON jobs (created_at) WHERE status = 'failed';

That is a partial index. It is tiny, it is only touched by queries that filter on failures, and it costs nothing to maintain for the 99.9 percent of rows that succeed.

Key idea: an index only wins when it selects a small fraction of the table, so low-cardinality columns rarely benefit - unless you query the rare value, in which case a partial index is the right tool.

When a condition cannot use the index at all

An index is useful because it is sorted. Any condition that destroys the connection to that sort order makes the index unusable, no matter how selective it is.

A leading wildcard. Compare two patterns against an index on name:

WHERE name LIKE 'Ad%'      -- CAN use the index
WHERE name LIKE '%da'      -- CANNOT use the index

With a known prefix, the engine descends to the first key beginning "Ad" and reads forward until the prefix stops matching; the matches form one contiguous stretch of the index. With a leading %, matching keys are scattered everywhere in alphabetical order - Ada, Nevada, Wanda - so there is no contiguous range to scan. This is not a quirk of any particular database; it follows from what sorting means.

A function on the column. The same logic applies:

WHERE LOWER(name) = 'ada'          -- index on (name) is useless: it stores 'Ada', not 'ada'
WHERE hire_year + 1 = 2020         -- index on (hire_year) is useless for the same reason
WHERE hire_year = 2019             -- fine; the column is left alone

CREATE INDEX idx_emp_lower_name ON employees (LOWER(name));   -- an expression index fixes case 1

Either rewrite the condition so the bare column stands alone, or build an expression index that stores the computed value. For genuine infix search, no B-tree can help; that is what trigram and full-text indexes exist for.

Key idea: a leading wildcard or a function wrapped around the column breaks the sorted order the index depends on, so rewrite the condition or index the expression instead.

Composite indexes and the leftmost prefix

An index can cover several columns, and their order matters enormously:

CREATE INDEX idx_emp_dept_salary ON employees (department, salary);

The entries are sorted by department first, and only by salary within each department. So:

WHERE department = 'Sales'                        -- uses the index
WHERE department = 'Sales' AND salary > 70000     -- uses the index, both columns
ORDER BY department, salary                       -- uses the index, no sort needed
WHERE salary > 70000                              -- CANNOT seek: salary is not the leading column

A printed phone book makes it obvious: sorted by surname then first name, finding "Smith, John" is instant, while finding every John in the city means reading the whole book. This is the leftmost prefix rule - an index on (A, B, C) serves queries on A, on (A, B), and on (A, B, C), but not on B alone.

Composite indexes have a second benefit. If every column a query needs is already in the index, the engine answers from the index and never touches the table: SELECT salary FROM employees WHERE department = 'Sales' is fully served by an index on (department, salary). PostgreSQL reports this as an Index Only Scan, and the index is called a covering index. It removes exactly the random-fetch cost that made the low-cardinality case so expensive.

Key idea: a composite index is sorted left to right, so it only serves conditions that start from its leftmost column, and it can answer a query outright when it contains every column the query touches.

The trade-off: indexes are not free

An index dramatically speeds up reads, but it has costs:

• Extra storage. Each index is an additional structure that takes disk space.
• Slower writes. Every INSERT, UPDATE, or DELETE must also update every affected index, so heavy write workloads with many indexes get slower. It is like keeping a book's index perfectly accurate while you are still writing new chapters.

Count the writes. Inserting one row into a table with eight indexes is nine write operations: the row plus one entry per index. Deleting is the same in reverse, and an UPDATE to an indexed column removes the old entry and inserts a new one elsewhere. Add page splits when a leaf fills, plus the fact that every index competes for the same memory cache, and the cost of an unused index is real rather than theoretical.

The art is balance: add indexes that accelerate your common queries, but do not blanket every column. A table that is written far more than it is read may want few indexes; a table that is queried constantly benefits from more. Many databases offer an EXPLAIN command that shows whether a query used an index or fell back to a full scan, which is how professionals verify their indexing choices.

EXPLAIN ANALYZE SELECT * FROM employees WHERE department = 'Sales';

Index Scan using idx_employees_dept on employees
  (cost=0.29..8.31 rows=2 width=64) (actual time=0.021..0.023 rows=2 loops=1)
  Index Cond: ((department)::text = 'Sales'::text)

-- versus, without a usable index:

Seq Scan on employees  (cost=0.00..1.05 rows=2 width=64)
  Filter: ((department)::text = 'Sales'::text)
  Rows Removed by Filter: 2

Read three things there: the access method on the top line, the planner's rows= estimate compared against the actual count (a mismatch means stale statistics), and Rows Removed by Filter, which counts wasted work. One honest warning: on the four-row hrdb table PostgreSQL picks the sequential scan every time, because reading four rows beats consulting an index. Indexes are a large-table phenomenon, and testing them on toy data teaches you nothing.

Key idea: indexes speed reads but cost storage and turn one row write into one write per index, so index deliberately and confirm each choice with EXPLAIN on realistic data volumes.

Where people get stuck

• "More indexes are always better." Each index slows every write and uses storage; too many can hurt overall performance.
• "An index changes the query's results." An index only changes how fast rows are found; the answer is identical with or without it.
• "You must index the primary key yourself." Most databases index primary keys and UNIQUE columns automatically.
• "Indexing a rarely searched column still helps." If a column is not used in WHERE, JOIN, or ORDER BY, its index adds cost without benefit.
• Indexing a boolean or a two-value status. It selects most of the table, so the planner ignores it. Use a partial index if you only ever query the rare value.
• Wrapping the indexed column in a function. WHERE LOWER(email) = ... or WHERE DATE(created_at) = ... silently disables the index. Rewrite the condition or build an expression index.
• Expecting LIKE '%text%' to be fast. No B-tree can serve a leading wildcard. Use a trigram or full-text index for infix search.
• Adding an index without measuring. Run EXPLAIN before and after, on data of a realistic size, and put the equality column first in a composite index. Many "performance indexes" in real schemas are never used by any query.

Recap

• An index is a sorted structure (usually a B-tree) whose leaves hold value-plus-row-pointer pairs, shallow enough to find any row in a few page reads.
• CREATE INDEX builds one; primary keys and UNIQUE columns are usually indexed automatically.
• Index columns used in WHERE filters, JOIN conditions, and ORDER BY, and only where the condition is selective.
• Low-cardinality columns rarely benefit, because scanning everything in order beats fetching almost everything at random; a partial index handles the rare-value case.
• A leading wildcard or a function on the column breaks the sort order and disables the index; composite indexes work left to right and can cover a query entirely.
• Indexes cost storage and one extra write each per row change, so index deliberately and verify with EXPLAIN.

Sources

1. Winand, M. (n.d.). The balanced search tree (B-tree) in SQL databases. Use The Index, Luke! use-the-index-luke.com
2. Winand, M. (n.d.). Tuning SQL LIKE using indexes: why a leading wildcard cannot use a B-tree. Use The Index, Luke! use-the-index-luke.com
3. Winand, M. (n.d.). Drawbacks of indexes: write slowdown. Use The Index, Luke! use-the-index-luke.com
4. PostgreSQL Global Development Group. (n.d.). Chapter 11. Indexes: types, multicolumn indexes, and partial indexes. PostgreSQL documentation. postgresql.org
5. PostgreSQL Global Development Group. (n.d.). 11.9. Index-only scans and covering indexes. PostgreSQL documentation. postgresql.org
6. PostgreSQL Global Development Group. (n.d.). 14.1. Using EXPLAIN. PostgreSQL documentation. postgresql.org
7. SQLite Consortium. (n.d.). Query planning: how SQLite chooses and uses indexes. SQLite documentation. sqlite.org

Everything so far assumed you were the only person using the database. Take that assumption away and code that is obviously correct stops being correct: two withdrawals succeed and only one is recorded, a report totals a column that changes underneath it, a row appears in the middle of a loop that was counting rows. Transactions are how a database stays truthful with thousands of people writing to it at once, and this lesson shows exactly what goes wrong without them.

The big picture

Some operations must happen as an all-or-nothing unit. The classic example is a bank transfer: subtract 100 from account A and add 100 to account B. If the system crashes after the subtraction but before the addition, 100 dollars vanishes. A transaction groups several statements so they either all take effect or none do, like a bank transfer that is either fully completed or fully cancelled, never left halfway.

Key idea: a transaction makes a group of statements happen completely or not at all.

Controlling a transaction

You begin a transaction, run statements, then either COMMIT to make every change permanent or ROLLBACK to undo them all:

BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 'A';
UPDATE accounts SET balance = balance + 100 WHERE id = 'B';
COMMIT;

If anything goes wrong between BEGIN and COMMIT, you issue ROLLBACK instead and the database restores the state as if the transaction never happened. Nothing partial is ever left behind. Think of COMMIT as clicking "confirm" and ROLLBACK as clicking "cancel."

A savepoint gives you a partial undo inside a transaction, which is useful when one step is allowed to fail without abandoning the rest:

BEGIN;
INSERT INTO accounts VALUES ('C', 100);
SAVEPOINT after_c;
INSERT INTO accounts VALUES ('D', -50);      -- fails a CHECK (balance >= 0)
ROLLBACK TO SAVEPOINT after_c;               -- undo only that statement
COMMIT;                                      -- account C still exists

Key idea: COMMIT makes the whole transaction permanent, ROLLBACK undoes all of it, and a SAVEPOINT lets you undo only part.

The ACID properties

A reliable DBMS guarantees four properties for transactions, remembered by the acronym ACID:

Property	Meaning
Atomicity	All statements in the transaction succeed together, or none do. No half-finished transfers.
Consistency	A transaction moves the database from one valid state to another, never violating constraints.
Isolation	Concurrent transactions do not interfere; each behaves as if it ran alone.
Durability	Once committed, changes survive crashes and power loss; they are safely on disk.

Key idea: ACID stands for atomicity, consistency, isolation, and durability, the four guarantees that make transactions trustworthy.

Why isolation is subtle

Isolation is the hardest of the four because databases run many transactions at once for speed. Without care, one transaction could read another's half-finished work (a "dirty read") or see data change under its feet. Databases provide isolation levels that trade strictness for speed: stricter levels prevent more anomalies but allow less concurrency. The default in most systems prevents dirty reads while still permitting good throughput. You do not need to master every level now, only to know that isolation is what keeps concurrent users from corrupting each other's transactions.

Key idea: isolation keeps simultaneous transactions from seeing each other's unfinished work, tuned by isolation levels.

The three anomalies, as interleavings

The isolation levels are named after the specific things that can go wrong, so the levels only make sense once you have seen each failure happen. Start with two accounts: A holds 500 and B holds 300. Time runs downward; the two columns are two concurrent sessions.

Dirty read - reading data another transaction has not committed. Possible only at READ UNCOMMITTED:

T1                                    T2
--------------------------------      --------------------------------
BEGIN;
UPDATE accounts SET balance = 400
  WHERE id = 'A';        (uncommitted)
                                      BEGIN;
                                      SELECT balance
                                        FROM accounts WHERE id = 'A';
                                        -> 400          <- DIRTY READ
ROLLBACK;   -- A is 500 again
                                      -- T2 is now acting on 400,
                                      -- a value that never officially existed

Non-repeatable read - the same row gives a different answer twice in one transaction. Possible at READ COMMITTED:

T1                                    T2
--------------------------------      --------------------------------
BEGIN;
SELECT balance FROM accounts
  WHERE id = 'A';   -> 500
                                      BEGIN;
                                      UPDATE accounts SET balance = 400
                                        WHERE id = 'A';
                                      COMMIT;
SELECT balance FROM accounts
  WHERE id = 'A';   -> 400   <- same query, different answer
COMMIT;

Phantom read - the same range query returns a different set of rows, because someone inserted into that range. Possible at REPEATABLE READ under the standard:

T1                                    T2
--------------------------------      --------------------------------
BEGIN;
SELECT COUNT(*) FROM accounts
  WHERE balance > 250;   -> 2
                                      BEGIN;
                                      INSERT INTO accounts
                                        VALUES ('C', 900);
                                      COMMIT;
SELECT COUNT(*) FROM accounts
  WHERE balance > 250;   -> 3   <- a row appeared inside the range
COMMIT;

The difference between the last two is worth stating precisely, because it is the exam question everyone gets wrong: a non-repeatable read is an existing row changing; a phantom is a new row appearing in a range you already queried.

Key idea: a dirty read sees uncommitted data, a non-repeatable read sees a row change mid-transaction, and a phantom sees a new row enter a range you already counted.

The four isolation levels

Each level is defined purely by which of those anomalies it forbids:

Isolation level	Dirty read	Non-repeatable read	Phantom
READ UNCOMMITTED	possible	possible	possible
READ COMMITTED	prevented	possible	possible
REPEATABLE READ	prevented	prevented	possible
SERIALIZABLE	prevented	prevented	prevented

Set one with SET TRANSACTION ISOLATION LEVEL REPEATABLE READ; immediately after BEGIN. Reality is more generous than that table, and the differences matter:

• PostgreSQL never permits dirty reads at all. Asking for READ UNCOMMITTED silently gives you READ COMMITTED, which is also the default. Its REPEATABLE READ uses a snapshot taken at the first statement, so it prevents phantoms too, exceeding what the standard requires. Its SERIALIZABLE can abort a transaction with a serialization failure, so any code that uses it must be prepared to retry.
• MySQL InnoDB defaults to REPEATABLE READ and prevents phantoms for ordinary reads via consistent snapshots.
• SQLite allows only one writer at a time, so its write behaviour is effectively serializable; in WAL mode readers do not block that writer.

The trade-off is real: stricter levels mean more blocking, or more aborted transactions to retry. READ COMMITTED is the default nearly everywhere because it is a sensible balance, not because it is the safest.

Key idea: the four levels are defined by which anomalies they forbid, and real engines are often stricter than the standard requires, so check your own database's documentation rather than the generic table.

The lost update, and how to stop it

Here is an anomaly the standard table does not even list, and it bites at the default isolation level. Two clerks each process a withdrawal from account A:

T1                                    T2
--------------------------------      --------------------------------
SELECT balance ... id='A';  -> 500
                                      SELECT balance ... id='A';  -> 500
UPDATE accounts
  SET balance = 500 - 100
  WHERE id = 'A';   COMMIT;   -> 400
                                      UPDATE accounts
                                        SET balance = 500 - 50
                                        WHERE id = 'A';   COMMIT;   -> 450

Two withdrawals happened. The balance says 450. One of them vanished.

Nothing here is a dirty, non-repeatable, or phantom read; both sessions read committed data. The bug is reading a value, computing in application code, and writing back a number that is stale by then. Three fixes:

-- 1. do the arithmetic in SQL, so the read and write are one atomic step
UPDATE accounts SET balance = balance - 100 WHERE id = 'A';

-- 2. lock the row while you think about it
BEGIN;
SELECT balance FROM accounts WHERE id = 'A' FOR UPDATE;   -- T2 now waits here
UPDATE accounts SET balance = ... WHERE id = 'A';
COMMIT;

-- 3. run at SERIALIZABLE and retry the transaction when it is aborted

The first is the cheapest and covers most cases; use the second when the decision genuinely needs application logic between the read and the write.

Key idea: read-modify-write in application code loses updates even at the default isolation level; do the arithmetic in SQL, or lock the row with SELECT ... FOR UPDATE.

Deadlocks

Locking introduces one more failure mode. Two transactions grab locks in opposite orders and each ends up waiting for the other:

T1                                    T2
--------------------------------      --------------------------------
BEGIN;                                BEGIN;
UPDATE accounts ... WHERE id='A';     UPDATE accounts ... WHERE id='B';
   (now holds the lock on A)             (now holds the lock on B)
UPDATE accounts ... WHERE id='B';     UPDATE accounts ... WHERE id='A';
   (waits for T2)                        (waits for T1)   -- neither can proceed

Databases detect the cycle rather than hanging forever, and kill one participant:

ERROR:  deadlock detected
DETAIL:  Process 1234 waits for ShareLock on transaction 567;
         blocked by process 5678.

Two habits prevent almost all of them. Acquire locks in a consistent order everywhere in your codebase, for instance always touching accounts in ascending id order, so no cycle can form. And keep transactions short: a transaction that stays open while it waits for a network call or a user is holding locks for a hundred thousand times longer than it needs to.

Key idea: deadlocks come from acquiring locks in inconsistent orders; the database resolves them by aborting one transaction, so lock in a fixed order, keep transactions short, and retry.

Why this matters

ACID transactions are the reason you trust a bank, a store, or an airline to get your data right even when thousands of people act at once and hardware occasionally fails. When you wrap related changes in a transaction and let the database enforce ACID, you inherit decades of engineering that guarantees your data stays correct and complete. Together with constraints and indexes, transactions complete the picture of what makes a relational database dependable at scale.

Key idea: wrapping related changes in a transaction lets the database guarantee correctness under failure and concurrency.

Where people get stuck

• "A committed transaction can still be lost in a crash." Durability guarantees committed changes survive crashes and power loss.
• "Atomicity means fast." Atomicity means all-or-nothing, not speed; a transaction either fully applies or fully rolls back.
• "Isolation means only one user at a time." Isolation lets many transactions run concurrently while behaving as if each ran alone.
• "ROLLBACK only undoes the last statement." ROLLBACK undoes every change made since the transaction began, unless you rolled back to a savepoint.
• Confusing a non-repeatable read with a phantom. One is an existing row changing value; the other is a new row entering a range. They are prevented at different levels.
• Assuming the default level is safe enough. READ COMMITTED, the usual default, still permits non-repeatable reads, phantoms, and lost updates. Know what your code assumes.
• Read-modify-write in application code. Fetching a value, adding to it in Python, and writing it back loses concurrent updates. Do the arithmetic in the UPDATE.
• Holding a transaction open across a network call. Locks are held the whole time, which turns a fast system into a queue and makes deadlocks far more likely.
• Using SERIALIZABLE without a retry loop. PostgreSQL aborts conflicting transactions rather than blocking, so the caller must be able to run the whole transaction again.

Recap

• A transaction groups statements into one all-or-nothing unit.
• COMMIT makes all changes permanent; ROLLBACK undoes all of them, and SAVEPOINT allows a partial undo.
• ACID is atomicity, consistency, isolation, and durability.
• The three classic anomalies are the dirty read, the non-repeatable read, and the phantom, and the four isolation levels are defined by which of them they forbid.
• Real engines are often stricter than the standard: PostgreSQL never allows dirty reads and its REPEATABLE READ also blocks phantoms.
• Lost updates and deadlocks are separate hazards, fixed by doing arithmetic in SQL, locking with FOR UPDATE, ordering locks consistently, and keeping transactions short.

Sources

1. PostgreSQL Global Development Group. (n.d.). 13.2. Transaction isolation: read committed, repeatable read, and serializable. PostgreSQL documentation. postgresql.org
2. PostgreSQL Global Development Group. (n.d.). 13.1. Introduction to concurrency control. PostgreSQL documentation. postgresql.org
3. PostgreSQL Global Development Group. (n.d.). 13.3. Explicit locking: SELECT FOR UPDATE and deadlocks. PostgreSQL documentation. postgresql.org
4. PostgreSQL Global Development Group. (n.d.). SAVEPOINT. PostgreSQL documentation. postgresql.org
5. SQLite Consortium. (n.d.). Isolation in SQLite. SQLite documentation. sqlite.org
6. SQLite Consortium. (n.d.). Transaction: BEGIN, COMMIT, ROLLBACK, and savepoints. SQLite documentation. sqlite.org
7. Kleppmann, M. (2017). Designing data-intensive applications: The big ideas behind reliable, scalable, and maintainable systems (ch. 7, Transactions). O'Reilly Media. find source ↗

Fourteen lessons of separate skills now have to work together on one problem. That is a different exercise from any of them individually, because the decisions interact: the entities you name determine the tables, the tables determine the joins, the joins determine which indexes are worth building, and a constraint you declared in step two is what saves you in step five. Build this bookstore alongside the lesson and you will have done, in miniature, exactly what a data engineer does at full scale.

The big picture

This capstone ties the whole course together by building a tiny bookstore database from scratch: design, creation, data, and queries. Following one small system end to end is the best way to see how the separate skills fit into a single workflow. By the end you will have exercised every major skill in the course.

Key idea: a real database is built in four steps, design, create, load, and query, and this lesson walks all four.

Step 1: design

The scenario: a bookstore sells books, each written by one author, and records customer orders. The entities are authors, books, customers, and orders. An author writes many books (one-to-many, so the foreign key goes on books). A customer places many orders (one-to-many, foreign key on orders). Each order is for one book here, to keep it simple. This design is already in 3NF: each fact (an author's name, a book's title, a customer's email) lives in exactly one table.

Write the model down before writing SQL, using the notation from Lesson 4:

AUTHOR   ---|<---  writes    --->||--- BOOK      an author writes 1..n books; a book has exactly 1 author
CUSTOMER ---o<---  places    --->||--- ORDER     a customer places 0..n orders; an order has 1 customer
BOOK     ---o<---  is sold in --->||--- ORDER    a book appears in 0..n orders; an order is for 1 book

Two of those decisions deserve a note. "A book has exactly one author" is a deliberate simplification; real catalogues have co-authored books, which would need the book_authors junction table from Lesson 4. And "an order is for one book" is also a simplification: a real order has many lines, which would mean an order_lines weak entity keyed by (order_id, line_no). Both are the right shape of decision to make consciously and record, rather than to discover after the data is loaded.

Key idea: design first by naming entities and relationships, placing each foreign key on the many side, and write down the simplifications you chose on purpose.

Step 2: create the tables

CREATE TABLE authors (
    author_id  INTEGER PRIMARY KEY,
    name       VARCHAR(100) NOT NULL
);

CREATE TABLE books (
    book_id    INTEGER PRIMARY KEY,
    title      VARCHAR(200) NOT NULL,
    price      DECIMAL(6,2) CHECK (price >= 0),
    author_id  INTEGER,
    FOREIGN KEY (author_id) REFERENCES authors(author_id)
);

CREATE TABLE customers (
    customer_id INTEGER PRIMARY KEY,
    name        VARCHAR(100) NOT NULL,
    email       VARCHAR(255) UNIQUE
);

CREATE TABLE orders (
    order_id    INTEGER PRIMARY KEY,
    customer_id INTEGER,
    book_id     INTEGER,
    order_date  DATE DEFAULT '2026-01-01',
    FOREIGN KEY (customer_id) REFERENCES customers(customer_id),
    FOREIGN KEY (book_id) REFERENCES books(book_id)
);

Notice how the design decisions become constraints: the foreign keys enforce that every book has a real author and every order points at a real customer and book, while CHECK keeps prices non-negative.

Key idea: CREATE TABLE turns the design into enforced structure, with foreign keys guarding every relationship.

Step 3: add data

INSERT INTO authors (author_id, name) VALUES
  (1, 'Ursula K. Le Guin'),
  (2, 'Toni Morrison');

INSERT INTO books (book_id, title, price, author_id) VALUES
  (10, 'A Wizard of Earthsea', 12.99, 1),
  (11, 'The Dispossessed', 14.50, 1),
  (12, 'Beloved', 13.00, 2);

INSERT INTO customers (customer_id, name, email) VALUES
  (100, 'Ren', 'ren@example.com'),
  (101, 'Sol', 'sol@example.com');

INSERT INTO orders (order_id, customer_id, book_id, order_date) VALUES
  (1000, 100, 10, '2026-02-10'),
  (1001, 100, 12, '2026-02-11'),
  (1002, 101, 11, '2026-03-01');

Key idea: INSERT populates the tables, and the foreign keys would reject any order referencing a missing book or customer.

Step 4: ask real questions

List each order with the customer and book title (a three-table join):

SELECT o.order_id, c.name AS customer, b.title
FROM orders AS o
INNER JOIN customers AS c ON o.customer_id = c.customer_id
INNER JOIN books AS b     ON o.book_id = b.book_id
ORDER BY o.order_id;

 order_id | customer | title
----------+----------+----------------------
     1000 | Ren      | A Wizard of Earthsea
     1001 | Ren      | Beloved
     1002 | Sol      | The Dispossessed
(3 rows)

How many books has each author sold? (join plus grouping). Because Ren bought Le Guin's book 10 and Morrison's book 12, and Sol bought Le Guin's book 11, Le Guin has 2 sales and Morrison has 1:

SELECT a.name, COUNT(*) AS books_sold
FROM orders AS o
INNER JOIN books AS b   ON o.book_id = b.book_id
INNER JOIN authors AS a ON b.author_id = a.author_id
GROUP BY a.name
ORDER BY books_sold DESC;

 name              | books_sold
-------------------+------------
 Ursula K. Le Guin |          2
 Toni Morrison     |          1
(2 rows)

What has each customer spent? (three-table join plus two aggregates). Ren bought books priced 12.99 and 13.00; Sol bought one at 14.50:

SELECT c.name,
       COUNT(*)      AS orders_placed,
       SUM(b.price)  AS total_spent
FROM orders    AS o
JOIN customers AS c ON o.customer_id = c.customer_id
JOIN books     AS b ON o.book_id     = b.book_id
GROUP BY c.name
ORDER BY total_spent DESC;

 name | orders_placed | total_spent
------+---------------+-------------
 Ren  |             2 |       25.99
 Sol  |             1 |       14.50
(2 rows)

Which books have never been ordered? (a subquery). All three books here were ordered, so this returns no rows, but the pattern is essential:

SELECT title
FROM books
WHERE book_id NOT IN (SELECT book_id FROM orders);

Stock the shop with one more title so the question has an answer, and then write it the safer way:

INSERT INTO books (book_id, title, price, author_id)
VALUES (13, 'The Left Hand of Darkness', 15.25, 1);

SELECT b.title
FROM books  AS b
LEFT JOIN orders AS o ON o.book_id = b.book_id
WHERE o.order_id IS NULL;

 title
---------------------------
 The Left Hand of Darkness
(1 row)

Both forms give the same answer here. Prefer the anti-join, for the reason Lesson 10 established: the moment a single NULL book_id appears in orders, the NOT IN version returns zero rows and tells you nothing is missing. The LEFT JOIN form is immune to that.

Key idea: joins, grouping, and anti-joins together answer the real questions a bookstore would ask, and the anti-join is the NULL-safe way to ask what is missing.

Step 5: protect the writes

Selling a book is two changes that must both happen or neither: record the order, and reduce the stock. Give books a stock column first, with a constraint that makes overselling impossible:

ALTER TABLE books ADD COLUMN stock INTEGER NOT NULL DEFAULT 3 CHECK (stock >= 0);

BEGIN;
INSERT INTO orders (order_id, customer_id, book_id, order_date)
VALUES (1003, 101, 13, '2026-03-15');
UPDATE books SET stock = stock - 1 WHERE book_id = 13;
COMMIT;

Now watch the two safeguards work together. If that book's stock were already 0, the UPDATE would drive it to -1, the CHECK would reject it, and the whole transaction - including the order - would roll back. The database will not let you record a sale for something you cannot ship, and no application code had to remember the rule. Note also that stock = stock - 1 does the arithmetic in SQL rather than in application code, which is what prevents the lost update from Lesson 14 when two customers buy the last copy at the same moment.

Key idea: a transaction plus a CHECK constraint make an impossible state unreachable, and doing the arithmetic inside the UPDATE keeps concurrent sales correct.

Step 6: decide the indexes

Every primary key in this schema is already indexed automatically. What is not indexed is the referencing side of each foreign key, and every join above uses exactly those columns:

CREATE INDEX idx_orders_customer ON orders (customer_id);
CREATE INDEX idx_orders_book     ON orders (book_id);
CREATE INDEX idx_books_author    ON books  (author_id);

The reasoning is the same for all three: the join looks up "all orders for this customer", "all orders for this book", "all books by this author", each of which reads a foreign-key column and is highly selective on a real dataset. Two honest caveats. On six rows PostgreSQL will ignore every one of these indexes and scan, exactly as Lesson 13 predicted, so verify the choice with EXPLAIN on realistic volumes. And each index slows every insert into orders, which for a bookstore is the busiest write path in the system.

You have now designed a normalized schema, created it with typed columns and constraints, populated it, answered layered questions with joins, aggregation, and an anti-join, protected a multi-step write with a transaction, and reasoned about indexes. That is the full arc of working with a relational database, and it is exactly what the rest of your data career builds upon.

Key idea: index the referencing side of foreign keys because that is where joins do their lookups, and confirm every index choice against realistic data.

Where people get stuck

• "Counting sales per author needs only one table." It requires joining orders to books to authors, then grouping by author.
• "You can insert an order for a book that does not exist." The foreign key on orders rejects any book_id not present in books.
• "Design can be skipped for a small database." Even a tiny schema benefits from naming entities and relationships first, so the tables and keys are right.
• "NOT IN with a subquery finds ordered books." It finds books whose id is absent from the orders set, meaning the never-ordered books - and it returns nothing at all if that set contains a NULL.
• Recording the order and the stock change separately. Without a transaction, a crash between the two leaves an order for a book that was never taken off the shelf.
• Assuming foreign keys are indexed. Primary keys are; the referencing columns are not, and those are the ones your joins actually search.
• Summing prices across a fanned-out join. Here each order has one book, so SUM(b.price) is correct. Add order lines and the same query starts double-counting, exactly as Lesson 9 showed.
• Treating the price on an order as a duplicate of the catalogue price. A real bookstore stores the price paid on the order line, because it is a different fact: what the customer was charged that day.

Recap

• Building a database follows six steps: design, create, load, query, protect the writes, and index.
• The bookstore design places each foreign key on the many side and is already in 3NF.
• CREATE TABLE encodes design decisions as constraints, including foreign keys and CHECK.
• Three-table joins, GROUP BY, and an anti-join answer real business questions, and the anti-join is the NULL-safe form.
• A transaction plus a CHECK makes overselling structurally impossible, and arithmetic inside the UPDATE keeps concurrent sales correct.
• Index the referencing side of foreign keys, and verify with EXPLAIN on realistic data rather than on six rows.

Sources

1. PostgreSQL Global Development Group. (n.d.). Chapter 5. Data definition: creating tables, constraints, and defaults. PostgreSQL documentation. postgresql.org
2. PostgreSQL Global Development Group. (n.d.). 2.6. Joins between tables. PostgreSQL documentation. postgresql.org
3. PostgreSQL Global Development Group. (n.d.). 3.4. Transactions. PostgreSQL documentation. postgresql.org
4. PostgreSQL Global Development Group. (n.d.). CREATE INDEX. PostgreSQL documentation. postgresql.org
5. SQLite Consortium. (n.d.). SQLite in 5 minutes or less: building and querying a small database. SQLite documentation. sqlite.org
6. Silberschatz, A., Korth, H. F., & Sudarshan, S. (2019). Introduction to SQL, E-R design, and relational database design. In Database system concepts (7th ed., chs. 3, 6, 7). McGraw-Hill. find source ↗
7. Pavlo, A. (2024). Course schedule and lecture notes. 15-445/645 Introduction to Database Systems, Carnegie Mellon University. 15445.courses.cs.cmu.edu

The gap between someone who knows SQL and someone you would trust with a production database is not more syntax. It is a short list of habits, each of which exists because of a specific way queries go wrong quietly. This closing lesson adds one genuinely useful tool - the view - and then collects those habits into a checklist you can actually run down before shipping a query.

The big picture

You have now written every core kind of SQL. This closing lesson adds one more genuinely useful tool, the view, and gathers the professional habits that keep your queries correct as they grow. A view lets you name a complex query and reuse it, and the habits protect you from the small mistakes that cause big data problems. We continue with the bookstore database from the previous lesson.

Key idea: views reuse complex queries by name, and good habits keep queries correct and safe.

What a view is

A view is a saved query that you can treat like a table. It stores no data of its own; instead it holds the SQL text, and every time you query the view the database runs that underlying query and returns fresh results. A view is like a saved search or a bookmark: it does not copy the pages, it just re-runs the lookup whenever you open it.

CREATE VIEW order_details AS
SELECT o.order_id, c.name AS customer, b.title, b.price
FROM orders AS o
INNER JOIN customers AS c ON o.customer_id = c.customer_id
INNER JOIN books AS b     ON o.book_id = b.book_id;

Now the three-table join has a name. Anyone can ask a simple question against it without repeating the join:

SELECT * FROM order_details ORDER BY order_id;

 order_id | customer | title                | price
----------+----------+----------------------+-------
     1000 | Ren      | A Wizard of Earthsea | 12.99
     1001 | Ren      | Beloved              | 13.00
     1002 | Sol      | The Dispossessed     | 14.50
(3 rows)

SELECT customer, title
FROM order_details
WHERE price > 13.00
ORDER BY customer;

 customer | title
----------+------------------
 Sol      | The Dispossessed
(1 row)

One row, not two: Beloved is priced at exactly 13.00, and > is strict. That is the sort of off-by-one that a view makes easier to catch, because you can inspect the view's full output first and then reason about the filter separately.

Key idea: a view is a named, saved query that stores no data and re-runs each time you use it.

View versus table

	Table	View
Stores data?	Yes, rows on disk	No, just a saved query
Always current?	Reflects the last write	Recomputed from base tables each query
Main benefit	Holds the actual data	Simplifies and standardizes complex queries

Because a view reads from its base tables (the real tables it queries), when those tables change the view's results change too, automatically. Views are also useful for security: you can grant a user access to a view that exposes only certain columns or rows, without giving them the whole underlying table.

Key idea: a table stores rows; a view stores a query and always reflects its base tables.

Can you write through a view?

Sometimes. A view that selects from a single table, with no DISTINCT, GROUP BY, HAVING, LIMIT, set operation, or window function, is automatically updatable: an UPDATE against it rewrites the underlying table. Anything more complex is read-only:

UPDATE order_details SET title = 'Oops' WHERE order_id = 1000;

ERROR:  cannot update view "order_details"
DETAIL:  Views that do not select from a single table or view are not
         automatically updatable.
HINT:  To enable updating the view, provide an INSTEAD OF UPDATE trigger.

A simple view does accept writes, and comes with a trap:

CREATE VIEW cheap_books AS
SELECT book_id, title, price FROM books WHERE price < 14.00;

UPDATE cheap_books SET price = 20.00 WHERE book_id = 10;   -- succeeds!

The update is applied to books, and the row promptly vanishes from cheap_books, because it no longer satisfies the view's own condition. You have written a row out of existence as far as the view is concerned. Add WITH CHECK OPTION and the database refuses instead:

CREATE VIEW cheap_books AS
SELECT book_id, title, price FROM books WHERE price < 14.00
WITH CHECK OPTION;

UPDATE cheap_books SET price = 20.00 WHERE book_id = 10;
ERROR:  new row violates check option for view "cheap_books"

Key idea: single-table views are updatable, and WITH CHECK OPTION stops a write from pushing a row outside the view's own definition.

Materialized views

An ordinary view recomputes every time, which is correct but can be slow for an expensive aggregate that thousands of page loads need. A materialized view stores the result on disk and recomputes only when told to:

CREATE MATERIALIZED VIEW author_sales AS
SELECT a.name, COUNT(*) AS books_sold
FROM orders  AS o
JOIN books   AS b ON o.book_id   = b.book_id
JOIN authors AS a ON b.author_id = a.author_id
GROUP BY a.name;

REFRESH MATERIALIZED VIEW author_sales;   -- recompute on demand

That is the denormalization trade-off from Lesson 5, made explicit and managed by the database: fast reads, in exchange for data that is stale between refreshes. Use it when a stale answer is genuinely acceptable, and schedule the refresh. REFRESH ... CONCURRENTLY avoids locking readers during the rebuild, but requires a unique index on the view. Materialized views are a PostgreSQL and Oracle feature; MySQL and SQLite do not have them.

Key idea: a materialized view stores its result and must be refreshed, trading freshness for speed in a way the database manages for you.

Views as a security boundary

The security use of views deserves more than a mention, because it is how real systems limit exposure. Grant access to a view instead of the table, and the columns you left out simply do not exist for that user:

CREATE VIEW customer_contacts AS
SELECT customer_id, name FROM customers;     -- email deliberately omitted

GRANT SELECT ON customer_contacts TO support_role;
-- support_role is never granted any privilege on the customers table itself

The same trick restricts rows rather than columns, with a WHERE clause tied to the current user. For anything beyond simple cases, PostgreSQL offers row-level security policies (CREATE POLICY), which apply to the table directly and cannot be bypassed by writing a different query.

Key idea: granting access to a view rather than a table hides columns and rows at the database level, which no application bug can undo.

Habits that keep queries correct

The difference between a beginner and a confident practitioner is often a handful of disciplines. Adopt these:

1. Filter first, verify with SELECT. Before an UPDATE or DELETE, run the same WHERE as a SELECT to see exactly which rows you will change.
2. Qualify columns in joins. Write c.name and b.title, not bare name, so an ambiguous column never silently picks the wrong table.
3. Mind NULL. Remember that = NULL never matches; use IS NULL, and be careful with NOT IN when the subquery might return a NULL.
4. Group correctly. Every non-aggregated column in a grouped SELECT must appear in GROUP BY.
5. Wrap multi-step changes in a transaction. If two or more writes must all succeed or all fail, put them between BEGIN and COMMIT so ACID protects them.
6. Let the database enforce rules. Prefer constraints (NOT NULL, UNIQUE, CHECK, FOREIGN KEY) over hoping application code remembers them.
7. Never build SQL from strings. Every value that came from outside your program goes in as a parameter. If you find yourself concatenating, stop; there is a placeholder for that.
8. Parenthesize mixed AND and OR. AND binds tighter, so an unparenthesized condition answers a different question than the one you asked.
9. Put outer-join filters in ON. A WHERE test on the optional side turns a LEFT JOIN into an INNER JOIN and deletes exactly the rows you were preserving.

Key idea: preview risky changes, qualify columns, handle NULL, group correctly, use transactions, lean on constraints, and never let input become SQL syntax.

A checklist to run before shipping a query

Every item below corresponds to a specific failure this course has shown you, in the lesson given in brackets. Run down it before a query goes anywhere near production data.

[ ] Does every WHERE mixing AND with OR have parentheses?              [L6]
[ ] Is every nullable column handled deliberately (IS NULL, COALESCE)?  [L2, L9]
[ ] For each outer join, is the filter on the optional side in ON?      [L8]
[ ] Did the row count change unexpectedly after a join?                 [L8, L9]
[ ] Is every non-aggregated SELECT column listed in GROUP BY?           [L9]
[ ] Is NOT IN used anywhere its subquery could return NULL?             [L10]
[ ] If the result is paged, does ORDER BY give a total ordering?        [L7]
[ ] Are any outside values concatenated into the SQL text?              [L11]
[ ] Do UPDATE and DELETE have a WHERE, previewed as a SELECT first?     [L11]
[ ] Are multi-statement changes wrapped in BEGIN and COMMIT?            [L14]
[ ] Has EXPLAIN been run on data of a realistic size?                   [L13]

None of these takes more than a few seconds. Together they cover almost every way a syntactically valid query returns a confidently wrong answer.

Key idea: a short written checklist catches the silent failure modes that no error message will ever tell you about.

Bringing the course together

Put together, these habits and the tools from the whole course give you a complete, dependable command of relational databases: design and normalization, SELECT and WHERE, sorting and limiting, joins, aggregation, subqueries, data changes, schema and constraints, indexes, and transactions. That toolkit is the foundation of nearly every data-driven system you will ever build.

Key idea: design, querying, modification, and reliability together form a complete command of relational databases.

Where people get stuck

• "A view stores its own copy of the data." An ordinary view stores only the query and reads fresh from its base tables every time; a materialized view is the one that stores data.
• "A view's results can go stale." An ordinary view cannot; a materialized view is stale by design until refreshed.
• "Qualifying columns is just style." When two joined tables share a column name, qualification prevents the query from silently using the wrong one.
• "Views are only for convenience." They also provide security by exposing only selected columns or rows to a user.
• Expecting every view to be writable. Only single-table views without grouping or DISTINCT are automatically updatable; anything else needs an INSTEAD OF trigger.
• Updating through a view without CHECK OPTION. The write succeeds and the row disappears from the view, which looks exactly like the update failing.
• Assuming a view makes a slow query fast. It runs the same SQL every time. If the underlying query needs an index, naming it changes nothing.
• Stacking views on views. Three layers deep, nobody can tell what the query actually does, and the planner may not simplify it as well as you hope. Keep the nesting shallow.

Recap

• A view is a named saved query that stores no data and recomputes from its base tables.
• Single-table views are updatable, and WITH CHECK OPTION prevents a write from pushing a row out of the view.
• A materialized view stores its result for speed and must be refreshed, which is managed denormalization.
• Tables hold rows; views simplify and standardize complex queries and can restrict access to columns and rows.
• Good habits: preview with SELECT, qualify columns, handle NULL, group correctly, parenthesize mixed AND/OR, filter outer joins in ON, use transactions and constraints, and never concatenate input into SQL.
• The full course toolkit spans design, querying, modification, and reliability, and a written checklist is what turns it into reliable practice.

Sources

1. PostgreSQL Global Development Group. (n.d.). CREATE VIEW: updatable views and WITH CHECK OPTION. PostgreSQL documentation. postgresql.org
2. PostgreSQL Global Development Group. (n.d.). 39.3. Materialized views. PostgreSQL documentation. postgresql.org
3. PostgreSQL Global Development Group. (n.d.). 7.4. Combining queries (UNION, INTERSECT, EXCEPT). PostgreSQL documentation. postgresql.org
4. OWASP Foundation. (n.d.). SQL injection prevention cheat sheet. OWASP Cheat Sheet Series. cheatsheetseries.owasp.org
5. SQLite Consortium. (n.d.). SELECT: views and query structure. SQLite documentation. sqlite.org
6. Silberschatz, A., Korth, H. F., & Sudarshan, S. (2019). Intermediate SQL: views. In Database system concepts (7th ed., ch. 4). McGraw-Hill. find source ↗
7. Pavlo, A. (2024). Course schedule and lecture notes. 15-445/645 Introduction to Database Systems, Carnegie Mellon University. 15445.courses.cs.cmu.edu