A database is an organised collection of data that a program can store, search, and change reliably. Most database systems, such as MySQL or PostgreSQL, run as a separate server program that other programs connect to over a network. SQLite is different: it is serverless. The entire database, including every table, row, and index, lives in one ordinary file on disk, and the SQLite library reads and writes that file directly. There is nothing to install as a service, no user accounts to set up, and nothing running in the background. That simplicity is why SQLite is the most widely deployed database in the world: it is built into every smartphone, every major web browser, and countless desktop programs.

The sqlite3 command is SQLite's command-line shell: an interactive program where you type a command, press Enter, and see the result immediately. Giving it a filename opens that database, creating the file if it doesn't exist yet. Run it with no filename and you get an in-memory database, which lives only in RAM and vanishes when you quit. That makes it perfect for experiments you don't want to keep. The sqlite> prompt tells you the shell is waiting for input. If a statement runs over several lines, the prompt changes to ...> until you finish it with a semicolon.

Serverless database

A database that is a single file read and written directly by a library, with no separate server program.

Shell

An interactive text program that runs each command as you type it and prints the result.

In-memory database

A temporary database held in RAM; it disappears when the shell closes.

Inside the shell you can type two completely different kinds of input, and telling them apart is the first thing to learn. SQL statements, such as SELECT, CREATE TABLE, and INSERT, are the standard database language, understood by SQLite and by every other SQL database. They must end with a semicolon, and they are what you would also send from a Python, PHP, or C program. Dot-commands are instructions to the shell program itself, not to the database. They start with a period, take up exactly one line, need no semicolon, and exist only in this command-line tool.

Dot-commands fall into two groups. Some let you inspect the database: .tables lists the tables, .schema shows the CREATE statement that defined each one, and .databases shows which file is open. Others change how results are displayed: .headers on prints column names above results, and .mode chooses a layout such as a boxed table, fixed columns, or CSV for exporting to a spreadsheet. These display settings reset every time you start the shell. If you want them permanently, put them in a file named .sqliterc in your home folder, and the shell runs it at startup.

SQL statement

A command in the standard database language; it ends with a semicolon and works in any SQL database.

Dot-command

A one-line instruction to the sqlite3 shell itself, starting with a period; it isn't SQL.

Schema

The structure of a database: its tables, columns, types, and constraints.

A relational database stores data in tables, grids of rows and columns much like a spreadsheet, but with firmer rules. Each column describes one attribute, such as a name or an age, and each row is one record, such as one user. CREATE TABLE defines that structure, the table's schema, before any data goes in. Every table should have a primary key: a column whose value uniquely identifies each row, so a row can always be found, changed, or referred to from another table. In SQLite, a column declared INTEGER PRIMARY KEY is special: it becomes the row's internal ID (the rowid), and SQLite fills it in automatically when you don't supply a value.

Constraints are rules the database enforces for you, so bad data is rejected at the door. NOT NULL means the column must always have a value, UNIQUE forbids two rows from sharing a value (ideal for email addresses), and DEFAULT supplies a value when an insert leaves the column out. IF NOT EXISTS makes a script safe to run twice. One SQLite quirk is worth knowing early: its column types are flexible. A column's declared type is a preference, called type affinity, rather than a strict rule. SQLite converts values where it sensibly can (the text '42' stored in an INTEGER column becomes the number 42), but it will still accept 'abc' in that column rather than raise an error. Since version 3.37 you can add the word STRICT after a table definition to get rigid, error-raising types instead.

Primary key

A column (or set of columns) whose value uniquely identifies each row.

Constraint

A rule enforced by the database, such as NOT NULL, UNIQUE, or DEFAULT.

Type affinity

SQLite's flexible typing: a column's declared type is a preference, not a strict rule.

INSERT adds new rows to a table. The statement names the table, lists the columns you're filling, and then gives the values in the same order: the first value goes into the first column named, the second into the second, and so on. Any column you leave out receives its DEFAULT value, or NULL if it has none, and an INTEGER PRIMARY KEY is numbered automatically. A single INSERT can add many rows by listing several parenthesised groups after VALUES, which is both shorter and faster than repeating the statement.

Two details trip up newcomers. First, in SQL, text values go in single quotes: 'Alice'. Double quotes are meant for the names of tables and columns. To put an apostrophe inside a text value, double it: 'O''Brien'. Second, when a program builds an INSERT from user input, it must never paste that input directly into the SQL text. Real applications use parameters (placeholders such as ?) and pass the values separately. This prevents SQL injection, where a malicious value rewrites the query. After an insert, the built-in function last_insert_rowid() returns the ID of the row that was just created.

Row / record

One entry in a table, with a value (or NULL) for every column.

NULL

SQL's marker for “no value” or “unknown”; it isn't zero or an empty string.

SQL injection

An attack where untrusted input is pasted into SQL text; prevented by using parameters.

SELECT is how you ask a database questions, and it is by far the most used SQL statement. Its result is itself a small table, called a result set. A query is built from clauses, each with one job. SELECT lists the columns you want (* means all of them). FROM names the table. WHERE keeps only the rows that satisfy a condition. ORDER BY sorts them, ascending by default or descending with DESC. LIMIT caps how many come back, and adding OFFSET skips some first, which is how page 2 of a list is built.

SQL is declarative: you describe what result you want, not how to find it, and the database works out an efficient way to produce it. It helps to know that SQLite logically processes the clauses in a different order from how you write them: first FROM (which table), then WHERE (which rows), then SELECT (which columns), then ORDER BY, then LIMIT. Also, rows have no guaranteed order unless you ask for one. Without ORDER BY, SQLite may return them in any order it likes, and that order can change as the table grows. Finally, COUNT(*) is the first of the aggregate functions you'll meet at the Intermediate level: instead of listing rows, it reports how many there are.

Query

A request for data, written as a SELECT statement.

Clause

One part of a statement with a single job: FROM, WHERE, ORDER BY, LIMIT…

Result set

The rows and columns a query returns: a temporary table.

UPDATE changes values in rows that already exist, and DELETE removes rows. Both work on every row that matches their WHERE clause, and that is the single most important fact about them. With a precise WHERE, such as a primary-key match like WHERE id = 1, exactly one row is affected. With a loose one, many rows are. And with no WHERE at all, every row in the table is changed or deleted. The shell won't ask for confirmation, and outside a transaction (covered at the Advanced level) there is no undo. After either statement, SELECT changes(); reports how many rows were affected.

That is why professionals follow a simple habit: write the WHERE clause first, run it as a SELECT to see exactly which rows it matches, and only then turn it into an UPDATE or DELETE. Three statements remove things at different scales. DELETE FROM users WHERE … removes chosen rows. DELETE FROM users empties the table but keeps its structure, so you can insert into it again. DROP TABLE users removes the table itself: structure, data, indexes and all.

UPDATE / DELETE

Change / remove every row matching the WHERE clause.

Rows affected

How many rows a statement changed; shown by SELECT changes().

DROP

Removes a whole database object, such as a table, rather than its rows.

A WHERE condition is a boolean expression: for each row it works out to true or false, and only rows where it's true are kept. You combine simple conditions with AND (both must hold) and OR (either may hold). AND is evaluated before OR, just as multiplication comes before addition, so a OR b AND c means a OR (b AND c). Always add parentheses when you mix them; it's the most common source of “why does this return the wrong rows?” SQL provides shorthands for common patterns. BETWEEN x AND y is an inclusive range, and IN (…) matches any value in a list, which is tidier than a chain of ORs.

LIKE matches text against a pattern: % stands for any run of characters (including none), and _ for exactly one character. In SQLite, LIKE ignores upper and lower case for English letters, so 'Bob' LIKE 'b%' is true. Use GLOB when you need case-sensitive matching. Finally, NULL needs special care, because it means “unknown”. Any comparison with an unknown value is itself unknown, so email = NULL is never true, not even for rows where the email really is missing. SQL calls this three-valued logic (true, false, unknown). To test for missing values, always use IS NULL or IS NOT NULL.

Boolean expression

A condition that evaluates to true or false for each row.

Wildcard

In LIKE patterns, % matches any run of characters and _ matches exactly one.

Three-valued logic

SQL's true / false / unknown; any comparison with NULL is unknown.

Ordinary functions work on one value at a time. Aggregate functions work on a whole column of rows and collapse them into a single summary value: COUNT counts, SUM adds, AVG averages, and MIN/MAX find the extremes. Aggregates skip NULLs, which explains a subtle difference: COUNT(*) counts rows, while COUNT(email) counts only the rows where the email is not NULL. AS gives a result column a readable name, called an alias.

GROUP BY turns a single summary into a summary per category. SQLite sorts the rows into buckets that share the same value (for example, one bucket per order status), then runs the aggregate once for each bucket, producing one output row per group. To filter those groups, use HAVING. The difference from WHERE is timing: WHERE removes individual rows before they are grouped, and HAVING removes whole groups after the aggregates are calculated. That's why WHERE COUNT(*) > 5 is an error but HAVING COUNT(*) > 5 works. One rule keeps grouped queries correct: every column in the SELECT list should either appear in GROUP BY or be inside an aggregate.

Aggregate function

Summarises many rows into one value: COUNT, SUM, AVG, MIN, MAX.

GROUP BY

Splits rows into groups sharing a value, producing one summary row per group.

HAVING vs WHERE

Filters groups after aggregation / filters rows before grouping.

Good database design avoids storing the same fact twice. Instead of writing a department's name on every employee's row, you store departments in their own table and give each employee a dept_id that points to one department. This is called normalisation, and it means a department's name only ever needs changing in one place. The pointing column is a foreign key: its values are the primary keys of rows in another table. The REFERENCES clause declares that relationship, and in SQLite it is only enforced once PRAGMA foreign_keys = ON has been run (see the PRAGMA step).

A JOIN reassembles the related rows at query time. The ON condition says how the rows line up: e.dept_id = d.id. Short table aliases such as e and d keep the query readable and resolve columns that share a name. An INNER JOIN returns only the pairs that match in both tables, so an employee with no department disappears. A LEFT JOIN keeps every row from the left table and fills the right side's columns with NULL where nothing matches, which is exactly how you find “employees without a department”. A self-join joins a table to itself under two aliases, to compare rows within it. Since version 3.39.0 (2022), SQLite also supports RIGHT JOIN and FULL OUTER JOIN.

Foreign key

A column holding the primary key of a row in another table, linking the two.

Normalisation

Designing tables so each fact is stored once, with relationships instead of copies.

INNER vs LEFT JOIN

Only matching pairs / every left-table row, with NULLs where nothing matches.

Applications change over time, and their tables have to change with them. Altering a live table's structure without losing its data is called a schema migration. ALTER TABLE makes the most common changes in place: ADD COLUMN appends a new column (existing rows get its default value, or NULL), RENAME TO renames the table, and RENAME COLUMN renames a column. Since version 3.35.0 (2021), DROP COLUMN removes one, as long as nothing such as an index or a foreign key depends on it.

SQLite's ALTER TABLE is still deliberately smaller than those of server databases. It can't change a column's type or add a constraint to an existing column. For those changes, the standard technique is to rebuild the table: create a new table with the structure you want, copy the rows across with INSERT INTO … SELECT or CREATE TABLE … AS SELECT, drop the old table, and rename the new one into its place. Do it inside a transaction, so a failure halfway through can't leave you with half a table. In real projects, migrations are saved as numbered script files, so every copy of the database can be upgraded the same way, in the same order.

Schema migration

A controlled change to a database's structure that keeps its existing data.

ALTER TABLE

Changes a table in place: add, rename, or drop a column, or rename the table.

Table rebuild

Create a new table, copy the rows over, drop the old one, and rename the new one.

SQLite includes a library of scalar functions: functions that take one row's values and return one value, unlike aggregates, which summarise many rows. They can be used anywhere an expression is allowed: in the SELECT list to reshape output, in WHERE to filter, or in ORDER BY to sort. The string functions clean and inspect text (TRIM, UPPER, SUBSTR, REPLACE, INSTR), and the || operator joins strings together, which is SQL's version of concatenation. Note that string positions in SQL start at 1, not 0.

SQLite has no separate date type. Dates are normally stored as ISO-8601 text such as '2025-05-02 14:30:00'. That format sorts correctly as plain text, and the date functions (DATE, DATETIME, STRFTIME) read and produce it. Remember too that NULL is contagious: almost any calculation involving NULL produces NULL, so 'Mr ' || NULL is NULL, not 'Mr '. COALESCE(a, b, …) returns its first non-NULL argument, the standard way to substitute a default. IFNULL is the two-argument version.

Scalar function

Takes values from one row and returns one value, e.g. UPPER or ROUND.

Concatenation

Joining strings; SQL uses the || operator.

COALESCE

Returns its first non-NULL argument; the usual way to supply a default for NULL.

Without help, answering WHERE email = 'g@x.com' means reading every row in the table and checking it. That is a full table scan, and it grows slower as the table grows. An index is a separate, sorted copy of one or more columns, each entry pointing back to its row. SQLite stores indexes as B-trees, balanced search trees that find any value in a handful of steps even among millions of rows, the way a book's index takes you straight to a page instead of making you read every chapter. An index also speeds up ORDER BY on its columns, and a UNIQUE index doubles as a constraint.

Indexes aren't free, which is why databases don't index everything automatically. Each one takes disk space, and every INSERT, UPDATE, and DELETE must also update every index on the table, which slows writes. So index the columns you actually search, join, or sort on, especially ones with many distinct values. A composite index on (user_id, created_at) follows the leftmost-prefix rule: it helps queries filtering on user_id, or on user_id and created_at together, but not on created_at alone, just as a phone book sorted by surname then first name can't find everyone called “Ann”. EXPLAIN QUERY PLAN shows what SQLite will do: SCAN means it reads every row, while SEARCH … USING INDEX means your index is working.

Index

A sorted lookup structure (a B-tree) that finds rows without scanning the whole table.

Full table scan

Reading every row to answer a query; what an index avoids.

Query plan

The database's chosen strategy for running a query, shown by EXPLAIN QUERY PLAN.

A view is a SELECT statement stored in the database under a name. Once created, you query it exactly like a table, but it holds no data of its own: every time you read from it, SQLite runs the underlying query against the current data, so a view is never out of date. That's why views are sometimes called virtual tables. Their definitions live alongside tables in sqlite_master, SQLite's built-in catalogue of everything in the database.

Views are a tool for abstraction. They let you write a complicated query once, with its joins, filters, and calculated columns, and give it a meaningful name such as active_users. Everyone who uses the view gets the same definition of “active”, and the rest of your SQL gets simpler. If the underlying tables are reorganised later, you can often update just the view and leave every query that uses it untouched. In SQLite, views are read-only: you can't INSERT or UPDATE through them directly, unless you define INSTEAD OF triggers that say what such a write should do.

View

A named, stored SELECT that behaves like a read-only table.

Abstraction

Hiding a complex query behind a simple, well-named interface.

sqlite_master

SQLite's internal table listing every table, index, view, and trigger.

A subquery is a complete SELECT placed inside another statement, in parentheses. Its result is used by the outer query, so a question that needs two steps can be answered in one statement. Subqueries come in shapes that match where they appear. A scalar subquery returns a single value and can go anywhere one value is expected, such as comparing each user's age to (SELECT AVG(age) FROM users). A subquery that returns a column of values feeds IN. A subquery in the FROM clause acts as a temporary table, called a derived table, that the outer query can filter and join like any other.

A correlated subquery refers to columns of the outer query, as in WHERE o.user_id = u.id inside an EXISTS. Conceptually it is re-evaluated for each row of the outer query, asking “does this user have any orders?”. EXISTS is true as soon as one matching row is found, which is why the inner query just selects 1: the value itself is never looked at. Many subqueries can also be written as joins, and SQLite often optimises both forms into the same plan. Choose whichever states the question most clearly, and when nesting gets deep, move to CTEs (two steps on).

Subquery

A SELECT nested inside another SQL statement.

Scalar subquery

A subquery that returns exactly one value.

Correlated subquery

A subquery that refers to the outer query's current row.

A transaction groups several statements into one indivisible unit of work. Placing an order might need to insert the order and reduce the stock count. If the program crashed between those two steps, the data would be inconsistent: an order with no matching stock change. Inside a transaction, either every statement takes effect (COMMIT) or none of them do (ROLLBACK). Databases promise four properties, known as ACID. Transactions are Atomic (all or nothing) and leave the data Consistent (every rule still holds). They are Isolated (others never see a half-finished transaction) and Durable (once committed, changes survive a crash or power cut).

By default SQLite runs in autocommit mode: every statement is its own tiny transaction, committed the moment it finishes. BEGIN switches that off until the next COMMIT or ROLLBACK. This also explains the dramatic speed-up for bulk inserts: to guarantee durability, each commit waits for the data to be physically written to disk, so a thousand autocommitted inserts means a thousand waits, while one transaction means one. SAVEPOINT adds named checkpoints inside a transaction, so ROLLBACK TO can undo part of the work without abandoning all of it.

Transaction

A group of statements that succeed or fail together.

ACID

Atomicity, Consistency, Isolation, Durability: the guarantees transactions provide.

Autocommit

SQLite's default: each statement is committed on its own immediately.

A common table expression (CTE) uses the WITH keyword to give a subquery a name at the start of a statement. The main query then refers to it like a table. The result is the same as nesting a subquery, but the query reads top to bottom as a series of named steps: first work out high spenders, then join them to users. You can define several CTEs, separated by commas, and each can build on the ones before it. A CTE exists only for the single statement it belongs to; unlike a view, nothing is stored.

WITH RECURSIVE lets a CTE refer to itself, which solves problems plain SQL can't, such as walking an organisation chart or a folder tree of unknown depth. A recursive CTE has two parts joined by UNION ALL. The anchor selects the starting rows (the boss, who has no manager), and the recursive member finds the next level by joining the table to the rows found so far. SQLite repeats the recursive member until it produces no new rows. That also means you must guarantee it eventually stops: a loop in the data, or a missing stopping condition, runs forever. The chained example also uses RANK() OVER (…), a window function, which is covered in the last step.

CTE

A named temporary result set, defined with WITH, that lasts for one statement.

Recursive CTE

A CTE that references itself, repeating until no new rows appear.

Anchor / recursive member

The starting rows / the step that finds the next level from the previous one.

A trigger is a block of SQL stored in the database that runs automatically whenever a particular event (an INSERT, UPDATE, or DELETE) happens on a particular table. Nobody has to remember to call it. That makes triggers ideal for rules that must always hold, no matter which program or person changes the data: stamping an updated_at time, writing an audit log, or keeping a summary table in step. BEFORE triggers run before the change is applied, AFTER triggers run once it's done, and INSTEAD OF triggers (on views) replace the change entirely.

SQLite triggers are row-level: FOR EACH ROW means the body runs once for every row the statement affects. Inside the body, NEW.column is the row's value after the change (for INSERT and UPDATE), and OLD.column is its value before (for UPDATE and DELETE). An optional WHEN condition restricts when the trigger fires. You might expect set_updated_at to fire itself forever, since it updates the same table. It doesn't, because SQLite leaves recursive_triggers off by default. Use triggers thoughtfully: they are invisible in the statements that set them off, so behaviour can surprise someone who doesn't know they exist.

Trigger

SQL that runs automatically on an INSERT, UPDATE, or DELETE.

NEW / OLD

The row's values after / before the change, available inside a trigger.

Row-level trigger

Runs once per affected row; SQLite's only kind (FOR EACH ROW).

PRAGMA statements are SQLite's own control panel. They aren't part of standard SQL, and other databases configure these things differently. Some PRAGMAs report information, such as table_info (a table's columns and types), index_list, and integrity_check (verify the file isn't corrupted). Others change how SQLite behaves. The crucial distinction is scope. Some settings are stored in the database file and persist: journal_mode = WAL stays on once set. Others apply only to the current connection and are forgotten when it closes.

foreign_keys is the most important per-connection setting. SQLite ships with foreign-key enforcement off for backward compatibility, so REFERENCES clauses are silently ignored until every new connection runs PRAGMA foreign_keys = ON. Applications typically do this right after connecting. WAL (write-ahead logging) mode records changes in a separate log file first, which lets readers keep reading while one writer writes. VACUUM rebuilds the database file to reclaim space left behind by deleted data; while it runs it can need free disk space of up to twice the database's size, and it locks the database.

PRAGMA

A SQLite-specific statement that reads or changes internal settings.

Connection

One open session to a database file; per-connection settings reset when it closes.

WAL mode

Write-ahead logging, which lets readers and one writer work at the same time.

An UPSERT (“update or insert”) solves a very common need: add this row, but if one with the same key already exists, update it instead. ON CONFLICT(id) DO UPDATE does exactly that in one atomic statement, and inside it excluded.column refers to the value you tried to insert. The older INSERT OR REPLACE looks similar but works differently: it deletes the old row and inserts a brand-new one. Any column you didn't mention resets to its default, and delete triggers fire. Prefer the true UPSERT when a row has other data worth keeping.

Window functions calculate across a set of related rows, the “window”, without collapsing them the way GROUP BY does, so every row stays in the result with the calculation beside it. OVER (ORDER BY …) defines the window. ROW_NUMBER and RANK number the rows, SUM(…) OVER produces running totals, and LAG/LEAD look at the previous or next row. Adding PARTITION BY restarts the calculation for each group. SQLite also has built-in JSON support, part of the core since 3.38. JSON documents are stored as text, json_extract pulls values out by a path such as '$.color', and the shorthand operators -> and ->> do the same. This gives you flexible, schema-free attributes inside an otherwise structured table.

UPSERT

Insert a row, or update the existing one if its key already exists.

Window function

A calculation across related rows that keeps every row, defined with OVER (…).

json_extract

Reads a value out of JSON text using a path such as '$.color'.

Builds on Basics & Commands › Joining Tables

Basics declared dept_id INTEGER REFERENCES departments(id) and joined on it. A declaration like that is a promise, and this lesson is about who keeps it. The relational model (Codd, 1970) requires referential integrity: every foreign-key value must match a key in the parent table or be NULL. A row that breaks the rule is an orphan, and every join that touches it silently gives the wrong answer.

SQLite does not check this by default. Foreign-key enforcement arrived in version 3.6.19 (2009), and turning it on for everyone would have broken existing databases that already held orphans, so it stays off for backward compatibility. PRAGMA foreign_keys = ON applies only to the connection that runs it, has to be repeated on every new connection, and is silently ignored inside a transaction. In the session below, Dave joins department 99, which doesn't exist, without complaint. PRAGMA foreign_key_check finds him afterwards. With enforcement on, the same insert fails with error 19 (SQLITE_CONSTRAINT). Django runs PRAGMA foreign_keys = ON on every SQLite connection it opens, so a Django project is protected even though the sqlite3 shell isn't.

Referential actions decide what happens to the children when a parent row is deleted or its key changes. NO ACTION, the default, refuses the change if children would be left behind. RESTRICT also refuses, but checks at once, even when the constraint is deferred. CASCADE deletes or updates the children too, and SET NULL / SET DEFAULT detach them. Pick the action by what the link means. Use CASCADE when the child can't exist without its parent (an order line without its order), SET NULL when the link is optional (an employee whose manager leaves), and NO ACTION when deleting the parent would destroy history (a customer with orders). Below, deleting Marketing takes Bob with it, and deleting Alice clears Carol's manager_id. Northwind uses NO ACTION everywhere, which is why you can't delete a customer who has orders.

When the check runs matters as much as whether it runs. An ordinary (immediate) constraint is checked at the end of each statement, not after each row, so one multi-row INSERT may add a child before its parent. DEFERRABLE INITIALLY DEFERRED moves the check to COMMIT, which is what cycles and self-references need when rows arrive in any order. If violations remain at COMMIT, the COMMIT fails and the transaction stays open. The session shows Eve's row still visible after the failed COMMIT, until ROLLBACK removes it. Code that assumes a failed COMMIT ended the transaction carries on inside it. PRAGMA defer_foreign_keys = ON defers every constraint for the current transaction and switches itself off at COMMIT. That is how northwind.db is loaded, because Employees.ReportsTo refers to employees inserted later.

Referential integrity

Every foreign-key value matches a parent key or is NULL. A row that breaks it is an orphan.

Referential action

What a delete or key update on the parent does to the children: NO ACTION, RESTRICT, CASCADE, SET NULL or SET DEFAULT.

Deferred constraint

A constraint checked at COMMIT instead of at the end of each statement.

SQLITE_CONSTRAINT (19)

The error for any constraint failure. The shell prints “FOREIGN KEY constraint failed (19)”.

Everything in an SQLite database lives in fixed-size pages, 4,096 bytes by default. Page 1 starts with a 100-byte header; after that, every table and every index is its own B-tree made of pages. dbstat is a built-in virtual table that shows those pages directly, so the storage layout stops being theory and becomes something you can query.

Tables and indexes use two different kinds of tree. A table is a B+tree keyed by rowid: the rows live only in the leaves, and interior pages hold nothing but copies of keys that point downward. An index is a plain B-tree: each entry is stored exactly once, in whichever page it lands in, interior or leaf. The output below shows the difference. people reports 10,090 cells for 10,000 rows, because its one interior page holds 90 extra routing keys. idx_people_email reports exactly 10,000 cells, because its interior entries are index entries.

The numbers also show why B-trees scale. The root's 90 cells point to 91 leaves (90 keys plus a right-most child pointer), and each leaf holds about 110 rows. One interior level already reaches 10,000 rows, and an interior page can point to hundreds of children, so a third level reaches millions of rows. Finding any row by its key costs one page read per level: a WHERE id = ? lookup on a table of tens of millions of rows reads about three or four pages.

Page

The fixed-size unit SQLite reads and writes; 4,096 bytes by default (PRAGMA page_size).

B+tree

A tree whose data lives only in the leaves; interior pages hold routing keys. SQLite uses it for rowid tables.

Fan-out

How many children one interior page points to. High fan-out keeps trees shallow.

dbstat

A virtual table with one row per page: its type, cell count, free space and position in its tree.

By default SQLite runs in rollback-journal mode (journal_mode = delete). Before changing a page in the database file, it copies the original page into a separate -journal file. If the power fails halfway through a commit, the next connection finds the journal and copies the old pages back. That is how a single file gets atomic commits without a server.

Concurrency is controlled by five lock states on the file. SHARED lets a connection read, and many can hold it at once. RESERVED means “I intend to write”: only one connection can hold it, but readers carry on. PENDING blocks new readers while a writer waits for the current ones to finish, and EXCLUSIVE is held for the moment the writer changes the file. The result is one writer at a time. In the session below, terminal A takes RESERVED with BEGIN IMMEDIATE. Terminal B can still read and sees the old balances, because A's changes are still only in A's memory. But B cannot write.

A second writer does not have to fail at once: .timeout (or sqlite3_busy_timeout, or Django's timeout option) makes it wait and retry. The shell's default timeout is zero, which is why B fails at once here. Whether waiting is good enough depends on how much of the time the write lock is taken. Multiply writes per second by the average write-transaction time. That gives the fraction of time the lock is busy, the utilisation ρ from queueing theory. Waits grow roughly as ρ / (1 − ρ): below about 20% they are rare, and past about 50% queues build quickly.

Rollback journal

A file of original pages, written before the database is changed, used to undo an interrupted commit.

RESERVED lock

Held by the one connection that intends to write; readers may continue.

SQLITE_BUSY (5)

The error returned when a lock cannot be obtained. The shell prints it as “database is locked (5)”.

Utilisation (ρ)

Writes per second × seconds per write: the fraction of time the write lock is held.

Builds on Basics & Commands › PRAGMA (WAL mode)

WAL lets readers and a writer work at the same time by giving each read transaction a fixed snapshot: whatever had been committed when it first read. In the session below, B keeps seeing Alice's balance as 100 even after A has committed 70. That is what makes SQLite's transactions serializable, the strictest isolation level in the SQL standard.

Now B tries to write. Its snapshot is out of date, and if the update were allowed B would compute 100 − 30 and overwrite A's change. That is a lost update. SQLite refuses. Note the timing: B had a 5-second busy timeout, yet the error came back in 0.000 s. Waiting cannot help, because the snapshot will never become current, so SQLite skips the busy handler and returns at once (the extended code, SQLITE_BUSY_SNAPSHOT, is 517). The only way forward is to roll back and start again.

Rollback-journal mode has the same trap in a different form. A transaction that reads first holds SHARED and then asks to upgrade to RESERVED. If another connection holding RESERVED is waiting to commit, both would wait for each other forever, so SQLite returns SQLITE_BUSY to the upgrader without waiting. This is exactly what Django's update_or_create does: a SELECT, then a write, inside one transaction. On this site it caused 81 “database is locked” errors even though a generous busy timeout was set. The fix is to say up front that you will write. BEGIN IMMEDIATE takes the write lock before the first read, so a conflict turns into an ordinary wait. Or write in one statement, such as an INSERT … ON CONFLICT DO UPDATE. Its transaction starts as a write instead of upgrading from a read, so SQLite can safely let it wait and retry.

Snapshot

The fixed view of committed data a read transaction sees from its first read until it ends.

Lost update

Two transactions read the same value, and one overwrites the other's change with a result based on stale data.

BEGIN IMMEDIATE

Starts a transaction and takes the write lock at once, before any reads.

Builds on Basics & Commands › Indexes

Beyond SCAN and SEARCH, two more plan lines matter. With a plain index, SQLite descends the index B-tree to each match and then makes a second lookup in the table, by rowid, to fetch the row. USING COVERING INDEX means the index alone holds every column the query needs, so the table is never touched. USE TEMP B-TREE FOR ORDER BY means the results are sorted after the fact, because no index delivers them in order.

On the 200,000-row table below, adding an index turns a 15 ms scan into a lookup too fast for the shell's timer to register. The time saved grows with the size of the table: a scan costs work in proportion to the number of rows, while an index lookup costs only a few page reads for each match.

When several plans are possible, SQLite estimates the cost of each and picks the cheapest. Since version 3.8.0 its planner (the “next-generation query planner”) has kept only the few cheapest partial join orders at each step (its “N nearest neighbours” search) rather than trying every possible order. The estimates depend on statistics: ANALYZE samples each index and writes a summary to sqlite_stat1. The line 200000 40 1 means the index covers 200,000 rows, each customer_id matches about 40 of them, and each (customer_id, total) pair matches about 1. Stale or missing statistics are a common cause of a sudden bad plan in production.

Covering index

An index that contains every column a query reads, so the table itself is never visited.

Selectivity

The fraction of rows a condition matches. The more selective a condition, the more an index on it helps.

ANALYZE

Collects index statistics into sqlite_stat1 for the planner's cost estimates.

Cost-based optimiser

A planner that estimates the cost of several alternative plans and runs the cheapest.

Builds on Basics & Commands › CTEs

Underneath, SQLite runs a recursive CTE with a queue. The anchor seeds the queue, and the recursive step runs once for each row taken off it, adding any new rows it produces. The query ends when the queue is empty. Because each row goes through the recursive step exactly once, this is the same idea as semi-naive evaluation in Datalog. It lets plain SQL compute a transitive closure: everything reachable from a starting point in a graph.

Graphs can have cycles. The route table below includes DSM → ORD → DEN → DSM, so with UNION ALL the queue never empties and the query only stops because of LIMIT. Switching to UNION discards any row that has already been produced, so the loop reaches a fixed point (no new rows) and ends by itself. This works because the graph is finite.

To list the paths themselves, rather than just the cities reached, carry the path along as a column and refuse to revisit a city already on it. instr is enough here because every airport code is exactly three letters. With labels of different lengths you would add separators so that one label can't match inside another. Rows come off SQLite's queue first in, first out, so the recursion runs breadth-first: in the first query every city one hop away appears before any city two hops away.

Transitive closure

Every node reachable from a start node by following edges any number of times.

Fixed point

The state where one more round of the recursive step adds nothing new.

UNION vs UNION ALL

UNION discards duplicate rows, which stops cycles; UNION ALL keeps them all.

Builds on Basics & Commands › Transactions

This lesson measures the wait behind every commit. For a commit to be durable, the data must be on the disk itself, not just in the operating system's memory. To make sure, SQLite calls fsync, which waits for the disk to confirm the write, and that wait is by far the most expensive part of a commit. The benchmark below timed 1,000 single-row inserts on this site's server under four settings. In every case the CPU time was under a second. The rest of the time was spent waiting for the disk.

A is SQLite's default: a rollback journal with synchronous = FULL and one commit per insert. Each commit writes and flushes the journal, updates the database, flushes it again and deletes the journal, which comes to about 4.2 ms, or roughly 240 commits a second. B runs the same inserts in one transaction and is about 350 times faster, because it pays the disk cost once. C uses WAL, where a commit appends to the log and flushes once, which roughly halves the cost. D is WAL with synchronous = NORMAL, where a commit doesn't wait for the disk at all and flushes happen only at checkpoints.

D is not free. The database cannot be corrupted, but after a power cut or operating-system crash the last few commits can be missing. Your application was told they succeeded, and they are gone. Whether that is acceptable depends on the application, and it is the classic trade between durability and throughput that every database makes somewhere.

fsync

An operating-system call that returns only when the disk confirms the data has been written.

synchronous

The PRAGMA controlling how often SQLite calls fsync: OFF, NORMAL, FULL or EXTRA.

Checkpoint

In WAL mode, copying the log's pages back into the main database file.

Most tables store only the current value. A bitemporal table stores two separate timelines. Valid time is when a fact is true in the world: here, the fiscal year the revenue belongs to. Transaction time is when the database believed it: from recorded_at until replaced_at. When a company restates last year's revenue, you don't overwrite the old figure. You close its transaction-time period and add a new row. Nothing is ever lost, and the database can answer “what did we know on 30 June 2025?” as easily as “what do we know now?”

This matters wherever history is analysed: audits, regulatory reporting and, above all, testing investment strategies on past data. A backtest that uses today's restated figures is using information nobody had at the time, called look-ahead bias, and it makes a strategy look better than it could ever have been. An as-of query removes that bias by filtering on transaction time.

Periods are stored half-open, [start, end), so one period can end on the same day the next begins without the two overlapping. Two periods overlap exactly when a.start < b.end AND b.start < a.end. PostgreSQL can enforce “no overlapping periods” declaratively with an exclusion constraint (EXCLUDE USING gist). SQLite has no such constraint, so a BEFORE INSERT trigger with RAISE(ABORT, …) enforces it instead. The trigger rejects the careless insert below, and the correct restatement, which closes the old row and inserts the new one in one transaction, goes through. ISO-8601 dates stored as text sort in date order, so plain < comparisons work.

Valid time

When a fact is true in the modelled world, such as the fiscal year a figure describes.

Transaction time

When the database recorded a fact, and when it was superseded.

Half-open interval

[start, end): includes the start, excludes the end, so adjacent periods never overlap.

Look-ahead bias

Using information in a historical test that was not available at the time being tested.

Northwind Traders is a made-up company that imports speciality foods (Ipoh Coffee from Singapore, Chartreuse from France, Gorgonzola from Italy) and sells them to shops and restaurants in 21 countries. Microsoft created it as the sample database for Microsoft Access in the early 1990s, and SQL Server 7.0 and 2000 installed it alongside pubs. From SQL Server 2005, Microsoft replaced both with AdventureWorks, but Northwind stayed the database every SQL tutorial seemed to use. Microsoft still publishes the original install script, instnwnd.sql, on GitHub under the MIT licence.

It lasted because it is small enough to keep in your head and still realistic. In 13 tables it has a business you already understand and every relationship type: one-to-many, many-to-many through a junction table, and a table that points at itself. It has deliberate copies of data, NULLs with a meaning, derived money values, dates, pictures, and a set of saved reports. It also has 830 orders from July 1996 to May 1998, enough data that a wrong query gives an answer that looks wrong.

This tab teaches what Northwind was built to teach, in the order a course would: the model (tables, keys, relationships, normalisation), asking questions (filtering, NULL, joins, aggregates, dates, subqueries), Microsoft's own reports (views, stored procedures, and window functions, which Northwind predates), and keeping data correct (constraints, transactions, indexes, BLOBs). It ends with exercises and an account of what changed in the move to SQLite.

Every arrow on the diagram above is a foreign key: a column in one table that holds the primary key of a row in another table. Order 10248 has CustomerID = 'VINET', and that value is the entire link. Orders holds no copy of the customer's name or address, only the key, and a query uses the key to look up the rest. Each arrow starts at the column holding the key (the child table, the “many” side) and points to the table that key identifies (the parent table, the “one” side).

Why split the data at all? Northwind could have been a single wide spreadsheet with one row per order line and the customer, product, supplier and salesperson typed out on every row. Each customer's address would then be repeated on every line of every order they placed. To change a phone number you'd have to find every copy, and a copy you missed would contradict the others. A new customer couldn't be recorded until they ordered something. Splitting the data into tables stores each fact once, and the foreign keys put the pieces back together when you ask a question. Lesson 5 covers the few copies Northwind keeps on purpose.

Northwind's 13 tables are joined by 14 foreign keys. The table below groups them into four areas of the business and explains why each one exists.

Northwind Traders imports speciality foods from around the world and resells them. Its database records each step of that business. Suppliers provide Products, which belong to Categories. Customers place Orders, each order has one or more lines in Order Details, an Employee takes the order, and one of three Shippers delivers it. Employees also cover sales Territories, grouped into four Regions.

.tables lists tables and views together: 13 tables and 16 views. The views are saved queries; Part 3 covers them. The first query uses UNION ALL to stack five one-row counts into a single result, which is a quick way to size up a database you've just opened.

One table name has a space in it, and the first time you type it you will get an error. Without quotes, SQLite reads Order as the start of ORDER BY, and the statement stops making sense. Double quotes turn any text into an identifier: "Order Details". (Single quotes make a string value, which is something different.) Microsoft kept the space from the original Access database. It teaches the quoting rule well, but in your own schemas use names that never need quotes.

Identifier

The name of a table, column, view or index. Double quotes let it contain spaces or reserved words.

UNION ALL

Stacks the rows of two SELECTs with the same number of columns, keeping duplicates.

.schema

A shell command that prints the CREATE statement SQLite stored for a table.

Every table needs a primary key: a column, or group of columns, whose value is different in every row. Northwind uses all three common kinds. Customers have a natural key, a five-letter code made from the company name (ALFKI for Alfreds Futterkiste), which people can read and remember. Orders, products and employees have surrogate keys: plain numbers with no meaning of their own. A surrogate key never has to change. A natural key does when the thing it was made from changes, for example when a company is renamed.

Order Details has a composite key: (OrderID, ProductID). Neither column is unique on its own, because an order has many lines and a product appears on many orders, but the pair is. In pragma_table_info the pk column numbers the key's columns 1 and 2, and every other column gets 0. The key also enforces a business rule: an order can't list the same product twice. Try it and SQLite refuses the second line.

A foreign key is a column that holds another table's primary key. pragma_foreign_key_list('Orders') shows that each order points at one customer, one employee and one shipper. Microsoft named the shipper column ShipVia, not ShipperID, so a foreign key doesn't need the same name as the key it references.

Natural key

A key made from real data people already use, such as a customer code or an ISBN.

Surrogate key

A meaningless number assigned by the database. In SQLite, an INTEGER PRIMARY KEY.

Composite key

A primary key made of two or more columns that are unique only together.

Foreign key

A column whose values must exist as the primary key of another (or the same) table.

Most of Northwind is one-to-many. One customer has many orders, one order has many lines, and one category has many products. The foreign key always sits on the many side: Orders.CustomerID, not a list of order numbers stored in Customers. Joining from the one side to the many side and counting is the most common question you'll ask a database.

Employees and territories are many-to-many. An employee covers several territories, and the design allows a territory to have several employees. A many-to-many relationship can't be stored with a single foreign key, so Northwind uses a junction table, EmployeeTerritories, with one row per link and a composite key made of two foreign keys. To get from employees to regions you join through it: Employees → EmployeeTerritories → Territories → Region.

The data is narrower than the design allows. The 49 links cover 49 different territories, so no territory is actually shared, and the LEFT JOIN finds four of the 53 territories that nobody covers. This is a real modelling lesson: the schema says what may be true, and only a query tells you what is true.

One-to-many

Each row on the "one" side matches zero or more rows on the "many" side. The foreign key lives on the many side.

Many-to-many

Rows on both sides can match several rows on the other. It needs a junction table.

Junction table

A table whose rows each link one row of table A to one row of table B, also called a bridge or link table.

Employees.ReportsTo is a foreign key that points back into Employees. A table can reference itself, and this is how org charts, threaded comments, folder trees and bills of materials are usually stored. To read it you join the table to itself under two aliases: e for the employee and m for the manager. It has to be a LEFT JOIN, because Andrew Fuller, the Vice President, reports to nobody. An inner join would drop him.

A self-join climbs only one level. To walk the whole tree, however deep, use a recursive CTE. The first SELECT (the anchor) finds the root, the person with ReportsTo IS NULL. The second SELECT joins each new level to the rows found so far, and SQLite repeats it until a level comes back empty. The path column builds a breadcrumb like Fuller > Buchanan > King. Sorting by it puts each manager directly above their own staff, and depth drives the indent.

The tree is three levels deep. Fuller has five direct reports. One of them, Steven Buchanan, the Sales Manager, runs three more representatives of his own.

Self-referencing key

A foreign key that points at the primary key of its own table.

Table alias

A short second name for a table within one query (Employees e). It is required when the same table appears twice.

Recursive CTE

WITH RECURSIVE: an anchor query plus a recursive query that runs repeatedly until it adds no new rows.

Normalisation means storing each fact in exactly one place. A category's name lives only in Categories, and a product stores only the number CategoryID. Rename Dairy Products once and all ten dairy products show the new name, because none of them holds a copy. Copies drift apart; references can't.

Northwind also breaks the rule twice, and it does so deliberately. Each Order Details line has its own UnitPrice, a copy of the product's price on the day of the sale. Queso Cabrales was sold at 14.00, then 16.80, then 21.00, and the catalogue now says 21.00. If the order line only pointed at Products, every old invoice would be re-priced each time the catalogue changed. Across the database, 658 of the 2,155 lines were charged a price that is no longer the list price.

The second copy is the ship-to address on every order. A customer can ask for delivery somewhere else. Around the Horn's office is on Hanover Square in London, but all 13 of its orders were shipped to Brook Farm near Colchester. An order also has to keep showing where it actually went after the customer moves. A copy is the right design when it records what was true at that moment, not a second version of a fact that is true now.

Normalisation

Organising tables so each fact is stored once and everything else refers to it by key.

Denormalisation

Storing a copy of data on purpose, usually to record history or to speed up reads.

Point-in-time value

A value copied at the moment of a transaction (price charged, address shipped to) that must not change later.

WHERE keeps the rows for which a condition is true. IN (...) is shorthand for several ORs, and in LIKE, % matches any run of characters. Northwind's data is international, so München and Münster come back with their umlauts intact.

Region is where Northwind teaches NULL. Only some countries' addresses have a state or province, so just 31 customers (mostly in the USA, Brazil, Venezuela and Canada) have one, and the other 60 have no region at all. NULL means “unknown”, and any comparison with an unknown value is itself unknown, never true. So Region = NULL matches nothing, not even the 60 NULLs. Use IS NULL. The same logic makes Region <> 'WA' return 28, not 88: the 60 customers with no region are not counted as “not WA”, because SQLite can't say what their region isn't.

Orders use NULL for “hasn't happened yet”: 21 orders, the oldest placed on 8 April 1998, have no ShippedDate. count(Region) counts only non-NULL values, which makes it a quick NULL census, and coalesce() replaces NULL with something readable for display.

NULL

A marker for a missing or unknown value. It is not zero and not an empty string.

Three-valued logic

In SQL a condition is TRUE, FALSE or UNKNOWN. WHERE keeps only rows where it is TRUE.

coalesce(a, b, ...)

Returns the first argument that is not NULL.

An order row on its own is mostly numbers: VINET, 5, 3. A join follows each foreign key to the row it names and brings back readable columns. Each JOIN ... ON adds one table, and the ON clause says which key matches which. Four tables give the company, the salesperson's surname and the shipper's name for each order.

An inner join keeps only rows that have a match on both sides. A left join keeps every row from the left table, and where there is no match it fills the right table's columns with NULL. That makes LEFT JOIN ... WHERE right.key IS NULL the standard way to find rows with nothing related, also called an anti-join. In Northwind it finds the two customers who have never ordered, FISSA and Paris spécialités.

The trap comes when you count after a left join. A customer with no orders still produces one row, padded with NULLs, so count(*) reports 1 order for FISSA. count(o.OrderID) counts only non-NULL values, so it correctly reports 0. Whenever a left join feeds an aggregate, count a column from the right-hand table.

INNER JOIN

Returns only pairs of rows that satisfy the ON condition.

LEFT JOIN

Returns every left row once or more; missing right-hand values become NULL.

Anti-join

A query for rows that have no match: LEFT JOIN plus IS NULL, or NOT EXISTS.

Northwind has no revenue column. Revenue is derived: each order line is worth UnitPrice × Quantity × (1 − Discount), where Discount is a fraction such as 0.15. Storing only the inputs means the formula can never fall out of step with the data. Summed over all 2,155 lines, the total comes to $1,265,793.04.

When you round changes the answer. Rounding every line to the cent first, which is what an invoice does, gives $1,265,793.15, eleven cents more. Neither number is wrong: the first is the exact total and the second is what the invoices add up to. Decide which one a report means, and round once, at the step that matches it.

GROUP BY collapses rows into one row per group, and aggregate functions (sum, count, avg) summarise each group. count(DISTINCT od.OrderID) counts orders, not lines. WHERE filters rows before grouping, and HAVING filters groups after, so “employees with more than 100 orders” needs HAVING, because the count doesn't exist until the group does. Beverages leads revenue and Margaret Peacock leads the sales team with 156 orders.

Derived value

A value computed from stored columns when it is needed, instead of being stored.

GROUP BY

Forms one output row per distinct value of the grouping columns.

HAVING

A filter applied to groups after aggregation. WHERE runs before it.

SQLite has no DATE type. Northwind's dates are stored as TEXT in ISO-8601 form, YYYY-MM-DD, and that choice does real work. ISO dates sort and compare correctly as plain strings, so BETWEEN '1997-01-01' AND '1997-12-31', min(), max() and ORDER BY all behave. Microsoft's script wrote dates as '7/4/1996'. Kept as text, that format compares character by character, so July 4th sorts after December 1st because '7' > '1'. Converting every date to ISO form was the most important step in bringing Northwind to SQLite.

SQLite's date functions read ISO text directly. strftime('%Y', d) extracts the year, and the month gives the quarter with (month + 2) / 3 in integer arithmetic. julianday() turns a date into a day number, so subtracting two of them gives the days in between.

The data runs from 4 July 1996 to 6 May 1998, so 1996 and 1998 are partial years. Compare 152 orders with 408 and you're comparing six months with twelve. Federal Shipping is the fastest carrier at 7.5 days on average, and United Package carries the most orders but is late most often.

ISO-8601

The date format YYYY-MM-DD (optionally with a time). It is the only text format that sorts in date order.

strftime(format, date)

Formats part of a date: %Y year, %m month, %d day, %W week.

julianday(date)

The number of days since 4714 BC as a REAL. The difference of two is a span in days.

A subquery is a SELECT nested inside another. A scalar subquery returns one value and can go wherever a value can: Microsoft's view Products Above Average Price compares each price with (SELECT avg(UnitPrice) FROM Products). A correlated subquery refers to the outer row (p2.CategoryID = p.CategoryID), so it is worked out again for each product. That gives the most expensive product in each category without any grouping.

EXISTS asks whether a subquery returns any rows at all, and it stops at the first one. Combining EXISTS with NOT EXISTS describes a pattern that is awkward to write any other way: a customer who ordered in 1996 and never came back. Only Centro comercial Moctezuma fits.

Set operators combine whole result sets. UNION merges two lists and removes duplicates, and Microsoft's view Customer and Suppliers by City uses it to put customers and suppliers in one directory. EXCEPT keeps rows from the first query that the second doesn't have: nine countries where Northwind sells but buys nothing.

Scalar subquery

A subquery that returns exactly one value.

Correlated subquery

A subquery that refers to columns of the outer query, so it is evaluated for each outer row.

UNION / EXCEPT / INTERSECT

Combine two SELECTs: everything, first-minus-second, and only rows in both.

A view is a named SELECT. It holds no data of its own: .schema shows that Order Subtotals stores only its query, and SQLite runs that query every time you select from the view. Northwind ships 16 views, and they were its main teaching aid. Each one is a report someone would really ask for (Invoices, Sales by Category, Quarterly Orders), saved so that nobody has to rewrite the join.

Views can be built on views. Order Subtotals sums each order's lines, Product Sales for 1997 uses it, and Category Sales for 1997 is built on that. Each layer is simple, and together they answer a hard question. Name the layers well and a report becomes a short chain of readable steps.

All 16 views are written in Microsoft's dialect, T-SQL, and several needed translating. The last query shows why. T-SQL joins strings with +, but in SQLite + always means arithmetic, so 'Nancy' + ' ' + 'Davolio' becomes 0 + 0 + 0 without any error. String concatenation in SQLite is ||. The table under About this conversion lists every change.

View

A stored SELECT you can query like a table. It is re-evaluated on every use.

T-SQL

Transact-SQL, the dialect of Microsoft SQL Server, which Northwind's script was written in.

|| (concatenation)

SQLite's string-join operator. + is numeric addition and turns text into 0.

Northwind's SQL Server version also ships seven stored procedures: queries with named parameters such as CustOrderHist @CustomerID, stored and run by the server. SQLite has none. It is a library linked into your program, not a server, so your program is the place to keep the query. The idea that carries over is the parameter: write the SQL once with a placeholder, and supply the value separately each time you run it.

The sqlite3 shell can bind parameters with .parameter set. Watch the types. An unquoted 1997 is bound as an INTEGER, and strftime() returns TEXT, so the comparison is false and SalesByCategory would return nothing at all. Quoting the value twice, "'1997'", binds it as text. Ten Most Expensive Products needs no parameter: its SET ROWCOUNT 10 becomes LIMIT 10.

In application code you pass parameters with the driver's placeholders, ? in Python. The value never becomes part of the SQL text, so a customer ID like x' OR '1'='1 is looked up as a strange ID, not run as SQL. That protection against SQL injection is the main reason stored procedures were popular, and parameterised queries give you the same protection.

Stored procedure

A named, parameterised program stored inside a database server. SQLite has none.

Bound parameter

A placeholder (?, :name, @name) whose value is supplied separately from the SQL text.

SQL injection

An attack in which user input is pasted into SQL text and changes what the statement does.

Northwind's reports were written for SQL Server 2000, which had no window functions. A window function computes across a set of related rows, as an aggregate does, but it keeps every row instead of collapsing them. OVER (PARTITION BY Country ORDER BY revenue DESC) means “within each country, ranked by revenue”, so every customer gets a rank in its own country in a single pass.

sum(sales) OVER (ORDER BY month) is a running total: the sum of this month and every month before it. lag(sales) reads the previous row's value, which gives month-over-month change. The first month has nothing before it, so its change is NULL. Before window functions, both of these needed a self-join or a correlated subquery for every row.

row_number() numbers the rows within each partition, and filtering on that number in an outer query gives a top-N per group, here the two best-selling products in each category. SQLite has supported window functions since version 3.25 (2018).

Window function

A function evaluated over a "window" of rows related to the current row, without grouping them away.

PARTITION BY

Splits rows into independent windows, one per value.

rank() / row_number() / lag()

Rank with ties, a unique sequence number, and the value from the previous row.

The most important line in this lesson is the first one. SQLite does not enforce foreign keys unless you turn them on, and the setting applies only to the current connection. With PRAGMA foreign_keys at its default of 0, an order for a customer called ZZZZZ, who doesn't exist, goes straight in. Run PRAGMA foreign_keys = ON and the same INSERT is refused. Deleting ALFKI is refused too, because six orders still point at it. Some frameworks, Django among them, turn it on for every connection they open. In the shell you have to do it yourself.

CHECK constraints enforce rules within a row. The SQLite file keeps Microsoft's rules: quantities must be positive, a discount must be between 0 and 1, and stock can't go below zero. The error message quotes the rule that failed. One rule couldn't be kept: CK_Birthdate required BirthDate < getdate(). SQLite refuses date('now') inside a CHECK, because a check must give the same answer every time it runs and “now” keeps changing. The SQLite file checks BirthDate < HireDate instead.

PRAGMA foreign_key_check scans every existing row for broken references and prints nothing when there are none. Run it after loading data, or after any session in which foreign keys were off.

PRAGMA foreign_keys

Turns foreign key enforcement on (1) or off (0). It is off by default and must be set on each connection.

CHECK constraint

A condition that every row must satisfy. It is tested on INSERT and UPDATE.

Deterministic

Always giving the same result for the same inputs. CHECK constraints may only use deterministic functions.

Taking an order is several writes that only make sense together: the order header, its lines, and a reduction in stock. A transaction makes them atomic. Between BEGIN and COMMIT nothing is final, and ROLLBACK throws all of it away. The last query proves it: still 830 orders, and Chai back at 39 in stock.

RETURNING (SQLite 3.35+) hands back the new key straight from the INSERT, so you don't need a separate lookup. OrderID 11078 is one more than the highest existing order, which is how an INTEGER PRIMARY KEY assigns keys. The order lines copy UnitPrice from Products at this moment, the point-in-time copy from lesson 5. The address is copied the same way.

In the middle, an attempt to sell 100 Chai fails the UnitsInStock >= 0 check. A failed statement inside a transaction undoes only that one statement. The transaction stays open, and your program decides whether to carry on or roll back. That is why an application must check for errors after each statement, instead of only checking the final COMMIT.

Transaction

A group of statements that succeed or fail as a unit (BEGIN ... COMMIT / ROLLBACK).

Atomic

All or nothing: other connections never see half of a transaction.

RETURNING

A clause on INSERT, UPDATE or DELETE that outputs columns of the rows it changed.

Microsoft's script creates an index on almost every foreign key, and EXPLAIN QUERY PLAN shows why. SEARCH Orders USING INDEX Orders_CustomerID means SQLite jumps straight to ALFKI's six orders. SCAN Orders means it reads all 830 rows, because nothing indexes ShipCountry. On 830 rows nobody would notice; on 83 million they would.

A composite primary key gets an index automatically, here sqlite_autoindex_Order Details_1 on (OrderID, ProductID). Like a phone book sorted by surname and then first name, it can find rows by its first column, but not by its second column alone. That is why ProductID needs an index of its own. For a join, the plan shows the loop order: SQLite scans the customers and, for each Mexican one, looks up orders through the index. It doesn't even read the Orders table, because the index holds every column the query needs: a covering index.

The conversion fixed two index problems in the original. SQL Server scopes index names to a table, so Microsoft named three different indexes PostalCode. SQLite index names are shared across the whole database, so they became Customers_PostalCode and so on. Microsoft also created pairs of identical indexes, such as CustomerID and CustomersOrders, both on Orders(CustomerID). A duplicate index makes every write slower and never speeds up a read, so each pair became one.

SEARCH / SCAN

In a query plan: an index lookup versus reading every row.

Leftmost prefix

A multi-column index helps queries that constrain its first column (first two, ...), not later columns alone.

Covering index

An index that contains every column a query needs, so the table itself is never read.

Categories.Picture and Employees.Photo hold images as BLOBs: raw bytes that SQLite stores and returns without interpreting them. typeof() reports blob, and length() counts bytes rather than characters. Every category picture is exactly 10,746 bytes.

The first bytes aren't an image header. Microsoft Access wrapped each picture in an OLE object, a container that recorded which program (Paint) should open it. The real Windows bitmap starts where its signature BM (hex 424D) appears, at byte 79, after a 78-byte wrapper. substr() works on BLOBs byte by byte, so substr(Picture, 79) peels the wrapper off. The shell's writefile() saves the result as a file the file command recognises: a 172 × 120, 16-colour bitmap.

Storing images in the database keeps everything in one file, which suits small images like these. For large media, most designs store a file path and keep the bytes on disk. Northwind does both: it also has a PhotoPath column holding the old http://accweb/... address of each photo on Microsoft's internal network.

BLOB

Binary Large OBject: bytes stored exactly as given. X'424D' is a BLOB literal in hex.

OLE object

An Access/Windows wrapper around embedded content, naming the program that created it.

writefile(name, data)

A sqlite3 shell function (not in the core library) that writes bytes to a file and returns the count.