SQL: model and query your first database

Learn in your browser · Chapter 01 of 06

Open SQLite from Python, make a table and see what flexible typing does to numbers.

A relational database keeps data in tables: a row per thing, such as a plant, and a column per fact, such as its price. SQL is the language for defining, changing and querying them; ISO/IEC 9075 standardises it, though no two engines behave exactly alike.

SQLite runs inside your program, needs no server and keeps a database in one file. Python includes it and, since 3.12, a small SQL shell. Check its version (on macOS and Linux, type python3 for python):

python -m sqlite3 -v
SQLite version 3.49.1

Tested here: Python 3.12.10 and SQLite 3.49.1 on Windows 11; STRICT tables need SQLite 3.37.0 or later. python -m sqlite3 alone opens a throwaway in-memory database. At the sqlite> prompt, end each statement with a semicolon (unfinished ones continue at ...); .quit leaves. In SQL transcripts, lines in brackets or naming an error are replies; you type the rest.

CREATE TABLE counts (plant TEXT, qty INTEGER);
INSERT INTO counts VALUES ('Lavender', 12);
INSERT INTO counts VALUES ('Fern', '7');
INSERT INTO counts VALUES ('Basil', 'seven');
SELECT plant, qty, typeof(qty) FROM counts;
('Lavender', 12, 'integer')
('Fern', 7, 'integer')
('Basil', 'seven', 'text')
SELECT sum(qty) FROM counts;
(19.0,)
SELECT plant FROM counts WHERE qty > 10;
('Lavender',)
('Basil',)

CREATE TABLE names columns and types, INSERT adds rows, SELECT reads them and typeof() shows how values were stored. A declared type is only a preference, its affinity: '7' became a number, 'seven' stayed text. The sum counted it as zero, and qty > 10 let Basil through: SQLite sorts all text after numbers.

Most other engines use rigid typing, the SQLite documentation notes. A STRICT table does too, converting a value only when nothing is lost:

CREATE TABLE strict_counts (plant TEXT, qty INTEGER) STRICT;
INSERT INTO strict_counts VALUES ('Fern', '7');
INSERT INTO strict_counts VALUES ('Basil', 'seven');
IntegrityError (unknown): cannot store TEXT value in INTEGER column strict_counts.qty
SELECT 0.1 + 0.2;
(0.30000000000000004,)

Read the message, not the bracketed label. SQLite before 3.37.0 reports an error on files with STRICT tables. The last line is why money belongs in whole pence: REAL numbers are binary floating point, so decimals are approximate.

Learn in your browser · Chapter 02 of 06

Design four related tables for an invented plant nursery, and let the database reject bad data.

Each kind of thing gets its own table, so each fact is stored once: customers, plants, orders and order lines.

A primary key identifies each row; INTEGER PRIMARY KEY numbers new rows for you. A foreign key must match a primary key elsewhere, so no order names a missing customer. Constraints are checked on every change: NOT NULL needs a value, UNIQUE forbids duplicates, CHECK tests a condition.

In a new folder, sql-first, save this as nursery.sql with a plain text editor:

PRAGMA foreign_keys = ON;

CREATE TABLE customers (
  id   INTEGER PRIMARY KEY,
  name TEXT NOT NULL,
  town TEXT
) STRICT;

CREATE TABLE plants (
  id          INTEGER PRIMARY KEY,
  name        TEXT NOT NULL UNIQUE,
  price_pence INTEGER NOT NULL CHECK (price_pence > 0)
) STRICT;

CREATE TABLE orders (
  id             INTEGER PRIMARY KEY,
  customer_id    INTEGER NOT NULL REFERENCES customers (id),
  ordered_on     TEXT NOT NULL,
  delivery_pence INTEGER NOT NULL DEFAULT 0
) STRICT;

CREATE TABLE order_lines (
  order_id   INTEGER NOT NULL REFERENCES orders (id),
  plant_id   INTEGER NOT NULL REFERENCES plants (id),
  qty        INTEGER NOT NULL CHECK (qty > 0),
  unit_pence INTEGER NOT NULL,
  PRIMARY KEY (order_id, plant_id)
) STRICT;

INSERT INTO customers VALUES
  (1, 'Ada Birch', 'Leeds'), (2, 'Ben Holly', 'York'),
  (3, 'Cara Moss', NULL), (4, 'Dev Rowan', 'Leeds');

INSERT INTO plants VALUES
  (1, 'Lavender', 450), (2, 'Rosemary', 350), (3, 'Fern', 600),
  (4, 'Olive tree', 2500), (5, 'Basil', 250);

INSERT INTO orders VALUES
  (1, 1, '2026-09-01', 0), (2, 2, '2026-09-02', 500),
  (3, 1, '2026-09-05', 500), (4, 3, '2026-09-06', 0);

INSERT INTO order_lines VALUES
  (1, 1, 2, 450), (1, 2, 1, 350), (2, 4, 1, 2500),
  (3, 5, 3, 250), (3, 3, 1, 600), (3, 1, 1, 450), (4, 3, 2, 600);

Order lines record the price paid, because prices change. Dates are year-month-day text, which sorts correctly. Cara Moss's town is NULL: missing or unknown. Save build.py beside it and run it there:

# build.py: create nursery.db from nursery.sql, never overwriting.
import sqlite3
from pathlib import Path

if Path("nursery.db").exists():
    raise SystemExit("nursery.db exists: rename it to rebuild.")
con = sqlite3.connect("nursery.db")
con.executescript(Path("nursery.sql").read_text(encoding="utf-8"))
con.close()
print("Built nursery.db")

python build.py
Built nursery.db

Foreign keys are off by default. SQLite enforces them only on connections that switch them on, outside a transaction. The build did; the file does not remember. Open python -m sqlite3 nursery.db:

PRAGMA foreign_keys;
(0,)
PRAGMA foreign_keys = ON;
INSERT INTO orders (customer_id, ordered_on) VALUES (99, '2026-09-07');
IntegrityError (SQLITE_CONSTRAINT_FOREIGNKEY): FOREIGN KEY constraint failed
INSERT INTO plants (name, price_pence) VALUES ('Fern', 500);
IntegrityError (SQLITE_CONSTRAINT_UNIQUE): UNIQUE constraint failed: plants.name
INSERT INTO plants (name, price_pence) VALUES ('Mint', 0);
IntegrityError (SQLITE_CONSTRAINT_CHECK): CHECK constraint failed: price_pence > 0

Each refusal left the data unchanged. If the order was accepted, foreign keys were off (a mistyped PRAGMA is silently ignored), so rename nursery.db and rebuild. Switch foreign keys on in every session and program.

Learn in your browser · Chapter 03 of 06

Most SQL reads data. Learn the shape of a query, the trap NULL sets and the join that quietly multiplies.

SELECT picks columns, FROM a table, WHERE the rows, ORDER BY their order (DESC for largest first) and LIMIT how many; without ORDER BY, order is undefined. Text goes in single quotes. In python -m sqlite3 nursery.db:

SELECT name, price_pence FROM plants
WHERE price_pence < 500 ORDER BY price_pence DESC LIMIT 2;
('Lavender', 450)
('Rosemary', 350)
SELECT name, town FROM customers WHERE town <> 'Leeds';
('Ben Holly', 'York')

Cara Moss is missing. Comparing with NULL gives NULL, meaning unknown, and WHERE keeps only rows where the condition is true: three-valued logic. Test with IS NULL or IS NOT NULL, never = NULL. Python shows NULL as None, true as 1, false as 0:

SELECT NULL = NULL, NULL <> 'Leeds', NULL IS NULL;
(None, None, 1)
SELECT NULL OR 1, NULL AND 0, 4 NOT IN (1, 2, NULL);
(1, 0, None)

NULL OR 1 is true whatever the unknown is; NULL AND 0 is false. One NULL in a NOT IN list makes the answer unknown, a classic trap.

Aggregate functions (count, sum, avg, min, max) turn many rows into one value. GROUP BY makes one result row per group, and HAVING filters groups as WHERE filters rows:

SELECT order_id, count(*), sum(qty * unit_pence) FROM order_lines
GROUP BY order_id HAVING count(*) > 1;
(1, 2, 1250)
(3, 3, 1800)

A join pairs rows from two tables, usually on a foreign key and the key it points to. JOIN keeps matched rows; LEFT JOIN keeps every left-hand row, filling gaps with NULL. AS sets an alias:

SELECT c.name, count(o.id) AS orders
FROM customers AS c
LEFT JOIN orders AS o ON o.customer_id = c.id
GROUP BY c.id, c.name;
('Ada Birch', 2)
('Ben Holly', 1)
('Cara Moss', 1)
('Dev Rowan', 0)

Dev Rowan never ordered: JOIN would drop him, and count(*) would show 1, while count(o.id) skips NULLs. Group by id too, so namesakes stay apart. SQLite accepts ungrouped columns; most engines refuse.

The join that multiplies. What did delivery earn?

SELECT sum(delivery_pence) FROM orders;
(1000,)
SELECT sum(o.delivery_pence), sum(l.qty * l.unit_pence)
FROM orders AS o JOIN order_lines AS l ON l.order_id = o.id;
(2000, 6750)

One row per order line counts order 3's 500 pence delivery three times, silently, so count a join's rows before summing across it. The fix totals each table at its own level first, in a subquery: a query in brackets inside another.

SELECT sum(o.delivery_pence), sum(g.goods)
FROM orders AS o
JOIN (SELECT order_id, sum(qty * unit_pence) AS goods
      FROM order_lines GROUP BY order_id) AS g
  ON g.order_id = o.id;
(1000, 6750)

Learn in your browser · Chapter 04 of 06

UPDATE and DELETE change a thousand rows as easily as one. Copy first, preview, and keep an undo button.

Practise on a copy. In the nursery.db shell, VACUUM INTO writes a clean copy to a new file, even while the database is in use, and will not write over a non-empty file:

VACUUM INTO 'practice.db';
VACUUM INTO 'practice.db';
OperationalError (SQLITE_ERROR): output file already exists

Quit and open python -m sqlite3 practice.db. Before any change, run a SELECT with the same WHERE. Afterwards, changes() reports how many rows the change touched:

PRAGMA foreign_keys = ON;
SELECT name, price_pence FROM plants WHERE name = 'Basil';
('Basil', 250)
UPDATE plants SET price_pence = 275 WHERE name = 'Basil';
SELECT changes();
(1,)
SELECT id, name FROM customers
WHERE id NOT IN (SELECT customer_id FROM orders);
(4, 'Dev Rowan')
DELETE FROM customers
WHERE id NOT IN (SELECT customer_id FROM orders);
SELECT changes();
(1,)

Without WHERE, UPDATE and DELETE hit every row. A transaction makes statements count together or not at all. The shell saves each statement at once unless BEGIN starts one; COMMIT keeps it, ROLLBACK discards it:

BEGIN;
SELECT count(*) FROM order_lines;
(7,)
DELETE FROM order_lines;
SELECT count(*) FROM order_lines;
(0,)
ROLLBACK;
SELECT count(*) FROM order_lines;
(7,)

An index is an extra sorted table of chosen columns pointing back to rows, so SQLite can search rather than scan every row. EXPLAIN QUERY PLAN shows which (read the end of each line):

EXPLAIN QUERY PLAN SELECT * FROM orders WHERE customer_id = 1;
(2, 0, 216, 'SCAN orders')
CREATE INDEX orders_customer ON orders (customer_id);
EXPLAIN QUERY PLAN SELECT * FROM orders WHERE customer_id = 1;
(3, 0, 61, 'SEARCH orders USING INDEX orders_customer (customer_id=?)')

SQLite does not index foreign key columns for you, though its documentation recommends it for most real systems; it also warns this output may change between versions. Indexes cost space and upkeep, so add them for real queries on growing tables.

Learn in your browser · Chapter 05 of 06

Programs send SQL too. Pass values safely, and keep copies you can restore.

In Python, sqlite3.connect() opens a database and execute() runs one statement. Never paste someone's input into a query's text. Save find.py; it only reads practice.db.

# find.py: look up customers by town, unsafely then safely.
import sqlite3

con = sqlite3.connect("practice.db")
town = input("Town: ")

unsafe = f"SELECT name FROM customers WHERE town = '{town}'"
print("Pasted:", con.execute(unsafe).fetchall())

safe = "SELECT name FROM customers WHERE town = ?"
print("Placeholder:", con.execute(safe, (town,)).fetchall())
con.close()

Run it twice, typing what follows Town:.

python find.py
Town: York
Pasted: [('Ben Holly',)]
Placeholder: [('Ben Holly',)]
python find.py
Town: x' OR '1'='1
Pasted: [('Ada Birch',), ('Ben Holly',), ('Cara Moss',)]
Placeholder: []

The quote ended the text early and added an always-true condition, returning every customer: SQL injection, which OWASP warns can let attackers read, change or destroy data. Innocent input breaks it too: Bishop's Stortford causes a syntax error. A placeholder (?) sends the value separately, as data only; the Python documentation says always use one.

Backups and exports. VACUUM INTO and Python's Connection.backup() copy a database safely while it is in use; copying the file is safe only when no transaction is in progress, the SQLite documentation warns. For spreadsheets, export CSV:

# export.py: copy the plants table to plants.csv, never overwriting.
import csv
import sqlite3

con = sqlite3.connect("nursery.db")
cur = con.execute("SELECT * FROM plants ORDER BY id")
with open("plants.csv", "x", newline="", encoding="utf-8") as f:
    writer = csv.writer(f)
    writer.writerow([col[0] for col in cur.description])
    writer.writerows(cur)
con.close()
print("Wrote plants.csv")

cur.description holds the column names. A trap: connect() creates an empty database if the file is missing, so a script run in the wrong folder fails with no such table.

Learn in your browser · Chapter 06 of 06

An assistant drafts SQL in seconds. Check it against the real schema before it touches anything.

A chat or editor assistant writes plausible, unchecked SQL, guessing what it has not seen. Give it your CREATE TABLE statements and a few invented rows, never real records: prompts can leave your control, and customers' names and orders are personal data under UK GDPR, the ICO explains.

Asked to raise plants under £5 by 10 per cent and list the top three customers by spend since 1 September, with emails, an assistant might answer:

UPDATE plants SET price_pence = price_pence * 1.1;

SELECT c.name, c.email, sum(l.unit_pence) AS spend
FROM customers c
JOIN orders o ON o.id = c.id
JOIN order_lines l ON l.order_id = o.id
WHERE o.ordered_on >= 2026-09-01
GROUP BY c.name
ORDER BY spend DESC
FETCH FIRST 3 ROWS ONLY;

Scroll sideways to see every column.

Make a fresh copy (VACUUM INTO 'review.db'; in the nursery.db shell) and open it. The preview shows the UPDATE would hit all five plants:

SELECT count(*) FROM plants;
(5,)
UPDATE plants SET price_pence = price_pence * 1.1;
IntegrityError (unknown): cannot store REAL value in INTEGER column plants.price_pence
SELECT c.email FROM customers c;
OperationalError (SQLITE_ERROR): no such column: c.email
SELECT count(*) FROM orders WHERE ordered_on >= 2026-09-01;
(4,)
SELECT 2026-09-01;
(2016,)
SELECT name FROM plants ORDER BY name FETCH FIRST 3 ROWS ONLY;
OperationalError (SQLITE_ERROR): near "FETCH": syntax error

STRICT refused fractional pence that a flexible table would have stored silently. Unquoted, 2026-09-01 is arithmetic (2016), which the TEXT column compares as the text '2016', so the filter keeps everything. LIMIT is not standard SQL, notes the PostgreSQL documentation, but SQLite lacks the standard FETCH FIRST. Name your database and version, then check anyway.

Know your limits. This is SQL's core, not the whole craft. For many programs writing at once, or sharing a database over a network, the SQLite documentation recommends a client/server engine.