Free Week 1 · Lesson 5 of 5 · about 210 min

SQL from the relational model up: keys, joins, grouping, NULLs and indexes

Roadmap task: SQL basics: SELECT, JOIN, GROUP BY, against a real Postgres or SQLite database

In this lesson: 12 ideas

The mental model: tables are sets of facts, SQL describes the answer
Keys: how rows find each other
SELECT, WHERE, ORDER BY, LIMIT
JOINs, one shape at a time
GROUP BY, HAVING and aggregate functions
and 7 more

210 min: to read and practise
18 steps: one idea at a time
2 visuals: diagrams and hands-on
19 code: 9 you can run here
6 checks: test yourself

Why this matters

Nearly every client system you will touch keeps its truth in a relational database: orders, customers, tickets, invoices, permissions. When an agent answers "which customers in Pune have unpaid orders?", some SQL ran somewhere, written by you, by an ORM, or by a model. If you cannot read that SQL and say whether it is right, you cannot vouch for the answer. The failures are quiet: a LEFT JOIN that silently became an INNER JOIN, a NOT IN that returns nothing because of one NULL, a revenue total doubled by a join, a query that takes nine seconds because nobody added an index, and one string-formatted query that leaks every customer's data. This lesson teaches SQL properly, against a real SQLite database you can run from Python, and finishes by planning your Week 1 deliverable.

The mental model: tables are sets of facts, SQL describes the answer

The relational model stores data as tables (relations). Each row is one fact ("order 103 was placed by customer 2 on 1 October"), each column is one attribute of that fact, and every column has a type. Two properties matter more than any syntax:

A table is a set, not a list. Rows have no inherent order. If you need an order, you ask for it with ORDER BY, every time. "It came back sorted yesterday" is luck, not a guarantee.
Each fact lives in one place. A customer's city is stored once, in customers, not copied into every order. If it were copied, changing it would mean updating many rows, and missing one would leave the database contradicting itself. Splitting data this way is called normalisation; JOINs are how you put the pieces back together when you query.

SQL is declarative: you describe the result you want, and the database's query planner decides how to get it (which index, which join algorithm, which order). That is why the same query can be fast or slow depending on indexes, and why the planner's plan is worth reading.

Keys: how rows find each other

A primary key identifies exactly one row. It must be unique and never NULL. Most tables use a surrogate key, a meaningless integer id, rather than a natural one like email, because natural values change (people change emails) and keys should not. You still enforce natural uniqueness with a UNIQUE constraint.

A foreign key is a column whose value must exist as a key in another table: orders.customer_id must match some customers.id. It is how the database guarantees there is no order for a customer who does not exist.

Here is the shop we will query all lesson. Four tables: customers place orders, each order has line items, each line item points at a product. order_items is the classic junction table: an order has many products and a product appears in many orders, and a many-to-many relationship always becomes a table of its own.

Python · runs as is

import sqlite3

SCHEMA = """
CREATE TABLE customers (
    id     INTEGER PRIMARY KEY,           -- surrogate key: meaningless and stable
    name   TEXT NOT NULL,
    email  TEXT NOT NULL UNIQUE,          -- a natural key, enforced as UNIQUE
    city   TEXT                           -- nullable: not every customer gave one
);
CREATE TABLE products (
    id          INTEGER PRIMARY KEY,
    name        TEXT NOT NULL,
    category    TEXT NOT NULL,
    price_paise INTEGER NOT NULL CHECK (price_paise > 0)   -- money as integer paise
);
CREATE TABLE orders (
    id          INTEGER PRIMARY KEY,
    customer_id INTEGER NOT NULL REFERENCES customers(id), -- foreign key
    status      TEXT NOT NULL
                CHECK (status IN ('placed', 'shipped', 'delivered', 'cancelled')),
    placed_at   TEXT NOT NULL,            -- ISO 8601 text; Postgres would use timestamptz
    coupon      TEXT                      -- NULL means "no coupon"
);
CREATE TABLE order_items (
    order_id         INTEGER NOT NULL REFERENCES orders(id),
    product_id       INTEGER NOT NULL REFERENCES products(id),
    quantity         INTEGER NOT NULL CHECK (quantity > 0),
    unit_price_paise INTEGER NOT NULL,    -- the price when bought, not today's price
    PRIMARY KEY (order_id, product_id)    -- composite key: one line per product per order
);
"""

SEED = """
INSERT INTO customers VALUES
  (1, 'Aarav Sharma', 'aarav@example.in', 'Bengaluru'),
  (2, 'Priya Iyer',   'priya@example.in', 'Chennai'),
  (3, 'Rohan Gupta',  'rohan@example.in', 'Delhi'),
  (4, 'Sneha Patil',  'sneha@example.in', 'Pune'),
  (5, 'Kabir Khan',   'kabir@example.in', NULL);
INSERT INTO products VALUES
  (1, 'Masala chai 250g', 'Grocery',      24900),   -- ₹249
  (2, 'Cotton kurta',     'Apparel',      89900),
  (3, 'Steel bottle 1L',  'Kitchen',      54900),
  (4, 'Wireless earbuds', 'Electronics', 199900),
  (5, 'Basmati rice 5kg', 'Grocery',      64900),
  (6, 'Yoga mat',         'Fitness',      79900);   -- never ordered
INSERT INTO orders VALUES
  (101, 1, 'delivered', '2026-09-02', 'FESTIVE10'),
  (102, 1, 'delivered', '2026-09-20', NULL),
  (103, 2, 'shipped',   '2026-10-01', NULL),
  (104, 3, 'cancelled', '2026-10-03', 'FESTIVE10'),
  (105, 2, 'placed',    '2026-10-10', NULL),
  (106, 3, 'delivered', '2026-10-11', 'WELCOME50');
INSERT INTO order_items VALUES
  (101, 1, 2,  24900), (101, 3, 1,  54900),
  (102, 4, 1, 199900),
  (103, 2, 2,  89900), (103, 1, 1,  24900),
  (104, 5, 1,  64900),
  (105, 4, 1, 189900),                       -- earbuds were on sale that day
  (106, 5, 2,  64900), (106, 1, 3,  24900);
"""

db = sqlite3.connect(":memory:")
db.executescript(SCHEMA + SEED)
for table in ["customers", "products", "orders", "order_items"]:
    n = db.execute(f"SELECT COUNT(*) FROM {table}").fetchone()[0]   # fixed names, not user input
    print(f"{table:12} {n} rows")

# The database refuses bad data. Each of these breaks one rule.
bad = [
    ("duplicate email", "INSERT INTO customers VALUES (6, 'A. Sharma', 'aarav@example.in', 'Mysuru')"),
    ("negative price",  "INSERT INTO products VALUES (7, 'Free sample', 'Grocery', -100)"),
    ("unknown status",  "INSERT INTO orders VALUES (107, 1, 'lost', '2026-10-12', NULL)"),
    ("duplicate line",  "INSERT INTO order_items VALUES (101, 1, 5, 24900)"),
]
for label, sql in bad:
    try:
        db.execute(sql)
    except sqlite3.IntegrityError as e:
        print(f"{label:16} -> IntegrityError: {e}")

# The SQLite surprise: foreign keys are NOT enforced unless you ask, per connection.
print("\nPRAGMA foreign_keys =", db.execute("PRAGMA foreign_keys").fetchone()[0])
db.execute("INSERT INTO orders VALUES (900, 99, 'placed', '2026-10-12', NULL)")
print("order for customer 99 accepted?!", db.execute("SELECT COUNT(*) FROM orders WHERE id = 900").fetchone()[0] == 1)
db.rollback()                                 # undo it; the pragma cannot change inside a transaction
db.execute("PRAGMA foreign_keys = ON")
try:
    db.execute("INSERT INTO orders VALUES (900, 99, 'placed', '2026-10-12', NULL)")
except sqlite3.IntegrityError as e:
    print("with foreign_keys = ON -> IntegrityError:", e)

What will this print? Guess first, then check.Show output

customers    5 rows
products     6 rows
orders       6 rows
order_items  9 rows
duplicate email  -> IntegrityError: UNIQUE constraint failed: customers.email
negative price   -> IntegrityError: CHECK constraint failed: price_paise > 0
unknown status   -> IntegrityError: CHECK constraint failed: status IN ('placed', 'shipped', 'delivered', 'cancelled')
duplicate line   -> IntegrityError: UNIQUE constraint failed: order_items.order_id, order_items.product_id

PRAGMA foreign_keys = 0
order for customer 99 accepted?! True
with foreign_keys = ON -> IntegrityError: FOREIGN KEY constraint failed

Text

customers    5 rows
products     6 rows
orders       6 rows
order_items  9 rows
duplicate email  -> IntegrityError: UNIQUE constraint failed: customers.email
negative price   -> IntegrityError: CHECK constraint failed: price_paise > 0
unknown status   -> IntegrityError: CHECK constraint failed: status IN ('placed', 'shipped', 'delivered', 'cancelled')
duplicate line   -> IntegrityError: UNIQUE constraint failed: order_items.order_id, order_items.product_id

PRAGMA foreign_keys = 0
order for customer 99 accepted?! True
with foreign_keys = ON -> IntegrityError: FOREIGN KEY constraint failed

Three design decisions in that schema are worth defending in an interview. Money is integer paise, because floats cannot represent most decimal fractions exactly and rounding errors accumulate across thousands of orders; Postgres also offers an exact NUMERIC(12,2). unit_price_paise is copied onto each line, because the product's price changes (order 105 bought earbuds on sale) and an invoice must show what the customer actually paid; this is a deliberate, justified exception to "store each fact once". Constraints live in the database, not only in your Pydantic models, because other scripts, admin tools and future services will write to the same tables without going through your API.

And the trap: SQLite parses REFERENCES but enforces it only after PRAGMA foreign_keys = ON, and that setting is per connection. Put it in the one function that opens connections (the connect() from the FastAPI lesson) so it can never be forgotten. Postgres always enforces foreign keys.

SELECT, WHERE, ORDER BY, LIMIT

Every block from here on rebuilds the same shop (compacted, some constraints trimmed) so you can run any block alone, and uses a small show() helper that prints results as a text table.

Python · runs as is

# --- the shop database from above, compacted; each runnable block stands alone ---
import sqlite3

db = sqlite3.connect(":memory:")
db.executescript("""
CREATE TABLE customers (id INTEGER PRIMARY KEY, name TEXT NOT NULL, email TEXT NOT NULL UNIQUE, city TEXT);
CREATE TABLE products (id INTEGER PRIMARY KEY, name TEXT NOT NULL, category TEXT NOT NULL, price_paise INTEGER NOT NULL CHECK (price_paise > 0));
CREATE TABLE orders (id INTEGER PRIMARY KEY, customer_id INTEGER NOT NULL REFERENCES customers(id), status TEXT NOT NULL, placed_at TEXT NOT NULL, coupon TEXT);
CREATE TABLE order_items (order_id INTEGER NOT NULL REFERENCES orders(id), product_id INTEGER NOT NULL REFERENCES products(id), quantity INTEGER NOT NULL, unit_price_paise INTEGER NOT NULL, PRIMARY KEY (order_id, product_id));
INSERT INTO customers VALUES (1,'Aarav Sharma','aarav@example.in','Bengaluru'), (2,'Priya Iyer','priya@example.in','Chennai'), (3,'Rohan Gupta','rohan@example.in','Delhi'), (4,'Sneha Patil','sneha@example.in','Pune'), (5,'Kabir Khan','kabir@example.in',NULL);
INSERT INTO products VALUES (1,'Masala chai 250g','Grocery',24900), (2,'Cotton kurta','Apparel',89900), (3,'Steel bottle 1L','Kitchen',54900), (4,'Wireless earbuds','Electronics',199900), (5,'Basmati rice 5kg','Grocery',64900), (6,'Yoga mat','Fitness',79900);
INSERT INTO orders VALUES (101,1,'delivered','2026-09-02','FESTIVE10'), (102,1,'delivered','2026-09-20',NULL), (103,2,'shipped','2026-10-01',NULL), (104,3,'cancelled','2026-10-03','FESTIVE10'), (105,2,'placed','2026-10-10',NULL), (106,3,'delivered','2026-10-11','WELCOME50');
INSERT INTO order_items VALUES (101,1,2,24900), (101,3,1,54900), (102,4,1,199900), (103,2,2,89900), (103,1,1,24900), (104,5,1,64900), (105,4,1,189900), (106,5,2,64900), (106,1,3,24900);
""")

def show(sql, params=()):
    """Run a query and print the result as an aligned text table (NULL shown as NULL)."""
    cur = db.execute(sql, params)
    cols = [d[0] for d in cur.description]
    rows = [["NULL" if v is None else str(v) for v in r] for r in cur.fetchall()]
    widths = [max(len(x) for x in col) for col in zip(cols, *rows)]
    for line in [cols, *rows]:
        print("  ".join(x.ljust(w) for x, w in zip(line, widths)).rstrip())
    print(f"({len(rows)} row{'' if len(rows) == 1 else 's'})\n")

# 1. Pick columns, filter rows, sort, take the top N
show("""
SELECT name, category, price_paise / 100.0 AS price_rupees
FROM products
WHERE category IN ('Grocery', 'Kitchen') OR price_paise >= 150000
ORDER BY price_paise DESC
LIMIT 3
""")

# 2. A date range (half-open: >= start, < next start) and a pattern
show("""
SELECT id, customer_id, placed_at, coupon
FROM orders
WHERE placed_at >= '2026-10-01' AND placed_at < '2026-11-01'
  AND coupon LIKE 'FEST%'
""")

# 3. Pagination: page 2 of customers, 2 per page, in a stable order
show("SELECT id, name FROM customers ORDER BY id LIMIT 2 OFFSET 2")

# 4. The integer division trap: both operands integers -> integer result
show("SELECT 24900 / 100 AS fine, 24950 / 100 AS wrong, 24950 / 100.0 AS right")

What will this print? Guess first, then check.Show output

name              category     price_rupees
Wireless earbuds  Electronics  1999.0
Basmati rice 5kg  Grocery      649.0
Steel bottle 1L   Kitchen      549.0
(3 rows)

id   customer_id  placed_at   coupon
104  3            2026-10-03  FESTIVE10
(1 row)

id  name
3   Rohan Gupta
4   Sneha Patil
(2 rows)

fine  wrong  right
249   249    249.5
(1 row)

Text

name              category     price_rupees
Wireless earbuds  Electronics  1999.0
Basmati rice 5kg  Grocery      649.0
Steel bottle 1L   Kitchen      549.0
(3 rows)

id   customer_id  placed_at   coupon
104  3            2026-10-03  FESTIVE10
(1 row)

id  name
3   Rohan Gupta
4   Sneha Patil
(2 rows)

fine  wrong  right
249   249    249.5
(1 row)

The single most useful fact about SELECT is its logical order of evaluation, which is not the order you write it in: FROM and JOIN (assemble rows) → WHERE (filter rows) → GROUP BY (form groups) → HAVING (filter groups) → SELECT (compute output columns) → ORDER BY → LIMIT/OFFSET. It explains most errors. A column alias defined in SELECT does not exist yet in WHERE, so standard SQL and Postgres reject WHERE price_rupees > 500; SQLite happens to accept it, which is exactly the kind of leniency that breaks when you move to Postgres. ORDER BY runs after SELECT, so it can use the alias.

Two habits from the outputs: use half-open ranges (>= '2026-10-01' AND < '2026-11-01') for dates, because BETWEEN '2026-10-01' AND '2026-10-31' silently drops '2026-10-31T18:30'; and paginate only with an ORDER BY on a unique column, or rows can repeat or vanish between pages. For deep pages, OFFSET 100000 still reads and discards 100,000 rows; production APIs often switch to keyset pagination (WHERE id > :last_seen_id ORDER BY id LIMIT 20).

JOINs, one shape at a time

A join combines rows from two tables where a condition holds. Think of it as: for each row on the left, find the rows on the right that satisfy ON. What differs between join types is what happens to rows that find no partner.

Python · runs as is

# --- the shop database from above, compacted; each runnable block stands alone ---
import sqlite3

db = sqlite3.connect(":memory:")
db.executescript("""
CREATE TABLE customers (id INTEGER PRIMARY KEY, name TEXT NOT NULL, email TEXT NOT NULL UNIQUE, city TEXT);
CREATE TABLE products (id INTEGER PRIMARY KEY, name TEXT NOT NULL, category TEXT NOT NULL, price_paise INTEGER NOT NULL CHECK (price_paise > 0));
CREATE TABLE orders (id INTEGER PRIMARY KEY, customer_id INTEGER NOT NULL REFERENCES customers(id), status TEXT NOT NULL, placed_at TEXT NOT NULL, coupon TEXT);
CREATE TABLE order_items (order_id INTEGER NOT NULL REFERENCES orders(id), product_id INTEGER NOT NULL REFERENCES products(id), quantity INTEGER NOT NULL, unit_price_paise INTEGER NOT NULL, PRIMARY KEY (order_id, product_id));
INSERT INTO customers VALUES (1,'Aarav Sharma','aarav@example.in','Bengaluru'), (2,'Priya Iyer','priya@example.in','Chennai'), (3,'Rohan Gupta','rohan@example.in','Delhi'), (4,'Sneha Patil','sneha@example.in','Pune'), (5,'Kabir Khan','kabir@example.in',NULL);
INSERT INTO products VALUES (1,'Masala chai 250g','Grocery',24900), (2,'Cotton kurta','Apparel',89900), (3,'Steel bottle 1L','Kitchen',54900), (4,'Wireless earbuds','Electronics',199900), (5,'Basmati rice 5kg','Grocery',64900), (6,'Yoga mat','Fitness',79900);
INSERT INTO orders VALUES (101,1,'delivered','2026-09-02','FESTIVE10'), (102,1,'delivered','2026-09-20',NULL), (103,2,'shipped','2026-10-01',NULL), (104,3,'cancelled','2026-10-03','FESTIVE10'), (105,2,'placed','2026-10-10',NULL), (106,3,'delivered','2026-10-11','WELCOME50');
INSERT INTO order_items VALUES (101,1,2,24900), (101,3,1,54900), (102,4,1,199900), (103,2,2,89900), (103,1,1,24900), (104,5,1,64900), (105,4,1,189900), (106,5,2,64900), (106,1,3,24900);
""")

def show(sql, params=()):
    """Run a query and print the result as an aligned text table (NULL shown as NULL)."""
    cur = db.execute(sql, params)
    cols = [d[0] for d in cur.description]
    rows = [["NULL" if v is None else str(v) for v in r] for r in cur.fetchall()]
    widths = [max(len(x) for x in col) for col in zip(cols, *rows)]
    for line in [cols, *rows]:
        print("  ".join(x.ljust(w) for x, w in zip(line, widths)).rstrip())
    print(f"({len(rows)} row{'' if len(rows) == 1 else 's'})\n")

# A. INNER JOIN: only pairs that match. Customers 4 and 5 have no orders, so they vanish.
show("""
SELECT o.id AS order_id, c.name, o.status
FROM orders AS o
JOIN customers AS c ON c.id = o.customer_id
ORDER BY o.id
""")

# B. LEFT JOIN: every customer, matched or not. No match -> NULLs on the right side.
show("""
SELECT c.name, o.id AS order_id, o.status
FROM customers AS c
LEFT JOIN orders AS o ON o.customer_id = c.id
ORDER BY c.id, o.id
""")

# C. Anti-join: rows with NO match. Which products have never been ordered?
show("""
SELECT p.name
FROM products AS p
LEFT JOIN order_items AS oi ON oi.product_id = p.id
WHERE oi.product_id IS NULL
""")

# D. The classic bug: a filter on the right table in WHERE throws the NULL rows away.
show("""
SELECT c.name, o.id AS order_id
FROM customers AS c
LEFT JOIN orders AS o ON o.customer_id = c.id
WHERE o.status = 'delivered'
ORDER BY c.id
""")

# E. Fixed: the condition goes in ON, so it limits which orders match, not which customers survive.
show("""
SELECT c.name, o.id AS order_id
FROM customers AS c
LEFT JOIN orders AS o ON o.customer_id = c.id AND o.status = 'delivered'
ORDER BY c.id, o.id
""")

# F. Four tables: every line of order 103 with names and line totals
show("""
SELECT o.id AS order_id, c.name AS customer, p.name AS product, oi.quantity,
       oi.quantity * oi.unit_price_paise / 100.0 AS line_rupees
FROM orders AS o
JOIN customers   AS c  ON c.id = o.customer_id
JOIN order_items AS oi ON oi.order_id = o.id
JOIN products    AS p  ON p.id = oi.product_id
WHERE o.id = 103
ORDER BY p.id
""")

What will this print? Guess first, then check.Show output

order_id  name          status
101       Aarav Sharma  delivered
102       Aarav Sharma  delivered
103       Priya Iyer    shipped
104       Rohan Gupta   cancelled
105       Priya Iyer    placed
106       Rohan Gupta   delivered
(6 rows)

name          order_id  status
Aarav Sharma  101       delivered
Aarav Sharma  102       delivered
Priya Iyer    103       shipped
Priya Iyer    105       placed
Rohan Gupta   104       cancelled
Rohan Gupta   106       delivered
Sneha Patil   NULL      NULL
Kabir Khan    NULL      NULL
(8 rows)

name
Yoga mat
(1 row)

name          order_id
Aarav Sharma  101
Aarav Sharma  102
Rohan Gupta   106
(3 rows)

name          order_id
Aarav Sharma  101
Aarav Sharma  102
Priya Iyer    NULL
Rohan Gupta   106
Sneha Patil   NULL
Kabir Khan    NULL
(6 rows)

order_id  customer    product           quantity  line_rupees
103       Priya Iyer  Masala chai 250g  1         249.0
103       Priya Iyer  Cotton kurta      2         1798.0
(2 rows)

Text

A. INNER JOIN                       B. LEFT JOIN
order_id  name          status      name          order_id  status
101       Aarav Sharma  delivered   Aarav Sharma  101       delivered
102       Aarav Sharma  delivered   Aarav Sharma  102       delivered
103       Priya Iyer    shipped     Priya Iyer    103       shipped
104       Rohan Gupta   cancelled   Priya Iyer    105       placed
105       Priya Iyer    placed      Rohan Gupta   104       cancelled
106       Rohan Gupta   delivered   Rohan Gupta   106       delivered
(6 rows)                            Sneha Patil   NULL      NULL
                                    Kabir Khan    NULL      NULL
                                    (8 rows)

C. never ordered    D. filter in WHERE (wrong)    E. filter in ON (right)
name                name          order_id        name          order_id
Yoga mat            Aarav Sharma  101             Aarav Sharma  101
(1 row)             Aarav Sharma  102             Aarav Sharma  102
                    Rohan Gupta   106             Priya Iyer    NULL
                    (3 rows)                      Rohan Gupta   106
                                                  Sneha Patil   NULL
                                                  Kabir Khan    NULL
                                                  (6 rows)

F. four-table join
order_id  customer    product           quantity  line_rupees
103       Priya Iyer  Masala chai 250g  1         249.0
103       Priya Iyer  Cotton kurta      2         1798.0
(2 rows)

(The outputs are laid side by side here to save space; the block prints them one after another.)

Read D and E carefully, because interviewers love it. In D the LEFT JOIN kept Sneha and Kabir with NULL in every order column, and then WHERE o.status = 'delivered' evaluated NULL = 'delivered', which is not true, so they were dropped. Priya was dropped too, because neither of her orders is delivered. The query is now an INNER JOIN wearing a LEFT JOIN's clothes. In E the status test is part of the match itself: Priya's orders fail it, so she keeps one row with NULLs, exactly like Sneha and Kabir. The rule: conditions on the optional (right) side of a LEFT JOIN belong in ON; conditions in WHERE filter the final result.

RIGHT JOIN keeps every row of the right table instead. A RIGHT JOIN B is exactly B LEFT JOIN A, and people find "start from the table you want all of, then LEFT JOIN" easier to read, so RIGHT JOIN is rare in real code. FULL OUTER JOIN keeps unmatched rows from both sides. It is rare because most questions have a natural "main" table, but it is the right tool for one job FDEs meet constantly: reconciliation, comparing two sources that should agree.

Python · runs as is

import sqlite3

db = sqlite3.connect(":memory:")
db.executescript("""
CREATE TABLE our_orders (order_id INTEGER PRIMARY KEY, amount_paise INTEGER NOT NULL);
CREATE TABLE gateway_payments (payment_ref TEXT PRIMARY KEY, order_id INTEGER, amount_paise INTEGER NOT NULL);
INSERT INTO our_orders VALUES (101, 104700), (102, 199900), (103, 204700), (105, 189900);
INSERT INTO gateway_payments VALUES
  ('pay_A1', 101, 104700), ('pay_B2', 102, 199900),
  ('pay_C3', 105, 179900),          -- paid, but the amount differs
  ('pay_D4', 777,  50000);          -- a payment for an order we have never heard of
""")

LABEL = """
    CASE
      WHEN g.payment_ref IS NULL            THEN 'unpaid order'
      WHEN o.order_id IS NULL               THEN 'unknown payment'
      WHEN o.amount_paise <> g.amount_paise THEN 'amount mismatch'
      ELSE 'ok'
    END AS verdict
"""

full_join = f"""
SELECT COALESCE(o.order_id, g.order_id) AS order_id, o.amount_paise AS ours,
       g.amount_paise AS gateway, {LABEL}
FROM our_orders AS o
FULL OUTER JOIN gateway_payments AS g ON g.order_id = o.order_id
ORDER BY 1
"""

# The same result without FULL JOIN (works on any SQLite, and on MySQL, which has no FULL JOIN):
# all left rows with their matches, plus the right rows that matched nothing.
emulated = f"""
SELECT o.order_id AS order_id, o.amount_paise AS ours, g.amount_paise AS gateway, {LABEL}
FROM our_orders AS o LEFT JOIN gateway_payments AS g ON g.order_id = o.order_id
UNION ALL
SELECT g.order_id, NULL, g.amount_paise, 'unknown payment'
FROM gateway_payments AS g LEFT JOIN our_orders AS o ON o.order_id = g.order_id
WHERE o.order_id IS NULL
ORDER BY 1
"""

rows = db.execute(emulated).fetchall()
for r in rows:
    print(r)

version = tuple(map(int, sqlite3.sqlite_version.split(".")))
if version >= (3, 39, 0):                      # RIGHT and FULL JOIN arrived in SQLite 3.39.0
    assert db.execute(full_join).fetchall() == rows
    print(f"FULL OUTER JOIN gives the same rows (SQLite {sqlite3.sqlite_version})")
else:
    print(f"SQLite {sqlite3.sqlite_version} has no FULL JOIN; the UNION ALL version is the way")

What will this print? Guess first, then check.Show output

(101, 104700, 104700, 'ok')
(102, 199900, 199900, 'ok')
(103, 204700, None, 'unpaid order')
(105, 189900, 179900, 'amount mismatch')
(777, None, 50000, 'unknown payment')
FULL OUTER JOIN gives the same rows (SQLite 3.45.1)

Text

(101, 104700, 104700, 'ok')
(102, 199900, 199900, 'ok')
(103, 204700, None, 'unpaid order')
(105, 189900, 179900, 'amount mismatch')
(777, None, 50000, 'unknown payment')
FULL OUTER JOIN gives the same rows (SQLite 3.45.1)

Two more to recognise: a CROSS JOIN pairs every row with every row (a forgotten ON condition does the same, by accident), and a self join joins a table to itself under two aliases, such as employees to their managers.

GROUP BY, HAVING and aggregate functions

GROUP BY collapses rows that share values into one row per group, and aggregate functions summarise each group: COUNT, SUM, AVG, MIN, MAX. WHERE filters rows before grouping; HAVING filters groups after, so only HAVING can test an aggregate.

Python · runs as is

# --- the shop database from above, compacted; each runnable block stands alone ---
import sqlite3

db = sqlite3.connect(":memory:")
db.executescript("""
CREATE TABLE customers (id INTEGER PRIMARY KEY, name TEXT NOT NULL, email TEXT NOT NULL UNIQUE, city TEXT);
CREATE TABLE products (id INTEGER PRIMARY KEY, name TEXT NOT NULL, category TEXT NOT NULL, price_paise INTEGER NOT NULL CHECK (price_paise > 0));
CREATE TABLE orders (id INTEGER PRIMARY KEY, customer_id INTEGER NOT NULL REFERENCES customers(id), status TEXT NOT NULL, placed_at TEXT NOT NULL, coupon TEXT);
CREATE TABLE order_items (order_id INTEGER NOT NULL REFERENCES orders(id), product_id INTEGER NOT NULL REFERENCES products(id), quantity INTEGER NOT NULL, unit_price_paise INTEGER NOT NULL, PRIMARY KEY (order_id, product_id));
INSERT INTO customers VALUES (1,'Aarav Sharma','aarav@example.in','Bengaluru'), (2,'Priya Iyer','priya@example.in','Chennai'), (3,'Rohan Gupta','rohan@example.in','Delhi'), (4,'Sneha Patil','sneha@example.in','Pune'), (5,'Kabir Khan','kabir@example.in',NULL);
INSERT INTO products VALUES (1,'Masala chai 250g','Grocery',24900), (2,'Cotton kurta','Apparel',89900), (3,'Steel bottle 1L','Kitchen',54900), (4,'Wireless earbuds','Electronics',199900), (5,'Basmati rice 5kg','Grocery',64900), (6,'Yoga mat','Fitness',79900);
INSERT INTO orders VALUES (101,1,'delivered','2026-09-02','FESTIVE10'), (102,1,'delivered','2026-09-20',NULL), (103,2,'shipped','2026-10-01',NULL), (104,3,'cancelled','2026-10-03','FESTIVE10'), (105,2,'placed','2026-10-10',NULL), (106,3,'delivered','2026-10-11','WELCOME50');
INSERT INTO order_items VALUES (101,1,2,24900), (101,3,1,54900), (102,4,1,199900), (103,2,2,89900), (103,1,1,24900), (104,5,1,64900), (105,4,1,189900), (106,5,2,64900), (106,1,3,24900);
""")

def show(sql, params=()):
    """Run a query and print the result as an aligned text table (NULL shown as NULL)."""
    cur = db.execute(sql, params)
    cols = [d[0] for d in cur.description]
    rows = [["NULL" if v is None else str(v) for v in r] for r in cur.fetchall()]
    widths = [max(len(x) for x in col) for col in zip(cols, *rows)]
    for line in [cols, *rows]:
        print("  ".join(x.ljust(w) for x, w in zip(line, widths)).rstrip())
    print(f"({len(rows)} row{'' if len(rows) == 1 else 's'})\n")

# Revenue per category from orders that were not cancelled
show("""
SELECT p.category,
       COUNT(*)                                        AS lines,
       SUM(oi.quantity)                                AS units,
       SUM(oi.quantity * oi.unit_price_paise) / 100.0  AS revenue_rupees
FROM order_items AS oi
JOIN orders   AS o ON o.id = oi.order_id
JOIN products AS p ON p.id = oi.product_id
WHERE o.status <> 'cancelled'                 -- WHERE filters rows, before grouping
GROUP BY p.category
ORDER BY revenue_rupees DESC
""")

# Customers who have spent more than ₹3,000: HAVING filters groups, after grouping
show("""
SELECT c.name,
       COUNT(DISTINCT o.id)                            AS orders,
       SUM(oi.quantity * oi.unit_price_paise) / 100.0  AS spent_rupees,
       MIN(o.placed_at)                                AS first_order,
       MAX(o.placed_at)                                AS last_order
FROM customers AS c
JOIN orders      AS o  ON o.customer_id = c.id
JOIN order_items AS oi ON oi.order_id = o.id
WHERE o.status <> 'cancelled'
GROUP BY c.id, c.name
HAVING SUM(oi.quantity * oi.unit_price_paise) > 300000
ORDER BY spent_rupees DESC
""")

# Fan-out: joining to order_items repeats each order once per line item
show("""
SELECT c.name, COUNT(*) AS rows_after_join, COUNT(DISTINCT o.id) AS real_orders
FROM customers AS c
JOIN orders      AS o  ON o.customer_id = c.id
JOIN order_items AS oi ON oi.order_id = o.id
GROUP BY c.id, c.name
ORDER BY c.id
""")

# Every category, including ones with no sales (cancelled orders included here): LEFT JOIN + COALESCE
show("""
SELECT p.category, COALESCE(SUM(oi.quantity), 0) AS units_ordered
FROM products AS p
LEFT JOIN order_items AS oi ON oi.product_id = p.id
GROUP BY p.category
ORDER BY units_ordered DESC, p.category
""")

What will this print? Guess first, then check.Show output

category     lines  units  revenue_rupees
Electronics  2      2      3898.0
Grocery      4      8      2792.0
Apparel      1      2      1798.0
Kitchen      1      1      549.0
(4 rows)

name          orders  spent_rupees  first_order  last_order
Priya Iyer    2       3946.0        2026-10-01   2026-10-10
Aarav Sharma  2       3046.0        2026-09-02   2026-09-20
(2 rows)

name          rows_after_join  real_orders
Aarav Sharma  3                2
Priya Iyer    3                2
Rohan Gupta   3                2
(3 rows)

category     units_ordered
Grocery      9
Apparel      2
Electronics  2
Kitchen      1
Fitness      0
(5 rows)

Text

category     lines  units  revenue_rupees
Electronics  2      2      3898.0
Grocery      4      8      2792.0
Apparel      1      2      1798.0
Kitchen      1      1      549.0
(4 rows)

name          orders  spent_rupees  first_order  last_order
Priya Iyer    2       3946.0        2026-10-01   2026-10-10
Aarav Sharma  2       3046.0        2026-09-02   2026-09-20
(2 rows)

name          rows_after_join  real_orders
Aarav Sharma  3                2
Priya Iyer    3                2
Rohan Gupta   3                2
(3 rows)

category     units_ordered
Grocery      9
Apparel      2
Electronics  2
Kitchen      1
Fitness      0
(5 rows)

Three things to notice. First, Fitness is missing from the first result: an inner join cannot produce a group with no rows, so "show every category, even with zero sales" always means a LEFT JOIN from the dimension table plus COALESCE. Second, the fan-out: once you join orders to their line items, each order appears once per line, so COUNT(*) counts lines, not orders, and summing any order-level column (a delivery fee, say) would multiply it. Use COUNT(DISTINCT ...), or aggregate the child table in a subquery first and join the result. Third, we grouped by c.id, c.name, not just c.name: two customers can share a name, and grouping by the key keeps them apart.

The grouping rule in standard SQL: every column in SELECT must be in GROUP BY or inside an aggregate. Postgres enforces it (except for columns of a table whose primary key you grouped by). SQLite does not: SELECT category, name, MAX(price_paise) FROM products GROUP BY category runs, taking name from the row with the maximum, an SQLite-only behaviour; with other aggregates the value comes from an arbitrary row. Do not rely on it.

NULL: SQL's third truth value

NULL means "unknown" or "not applicable", not zero and not empty string. Any comparison with an unknown is itself unknown, so SQL has three-valued logic: TRUE, FALSE and UNKNOWN. WHERE keeps a row only when its condition is TRUE.

Python · runs as is

# --- the shop database from above, compacted; each runnable block stands alone ---
import sqlite3

db = sqlite3.connect(":memory:")
db.executescript("""
CREATE TABLE customers (id INTEGER PRIMARY KEY, name TEXT NOT NULL, email TEXT NOT NULL UNIQUE, city TEXT);
CREATE TABLE products (id INTEGER PRIMARY KEY, name TEXT NOT NULL, category TEXT NOT NULL, price_paise INTEGER NOT NULL CHECK (price_paise > 0));
CREATE TABLE orders (id INTEGER PRIMARY KEY, customer_id INTEGER NOT NULL REFERENCES customers(id), status TEXT NOT NULL, placed_at TEXT NOT NULL, coupon TEXT);
CREATE TABLE order_items (order_id INTEGER NOT NULL REFERENCES orders(id), product_id INTEGER NOT NULL REFERENCES products(id), quantity INTEGER NOT NULL, unit_price_paise INTEGER NOT NULL, PRIMARY KEY (order_id, product_id));
INSERT INTO customers VALUES (1,'Aarav Sharma','aarav@example.in','Bengaluru'), (2,'Priya Iyer','priya@example.in','Chennai'), (3,'Rohan Gupta','rohan@example.in','Delhi'), (4,'Sneha Patil','sneha@example.in','Pune'), (5,'Kabir Khan','kabir@example.in',NULL);
INSERT INTO products VALUES (1,'Masala chai 250g','Grocery',24900), (2,'Cotton kurta','Apparel',89900), (3,'Steel bottle 1L','Kitchen',54900), (4,'Wireless earbuds','Electronics',199900), (5,'Basmati rice 5kg','Grocery',64900), (6,'Yoga mat','Fitness',79900);
INSERT INTO orders VALUES (101,1,'delivered','2026-09-02','FESTIVE10'), (102,1,'delivered','2026-09-20',NULL), (103,2,'shipped','2026-10-01',NULL), (104,3,'cancelled','2026-10-03','FESTIVE10'), (105,2,'placed','2026-10-10',NULL), (106,3,'delivered','2026-10-11','WELCOME50');
INSERT INTO order_items VALUES (101,1,2,24900), (101,3,1,54900), (102,4,1,199900), (103,2,2,89900), (103,1,1,24900), (104,5,1,64900), (105,4,1,189900), (106,5,2,64900), (106,1,3,24900);
""")

def show(sql, params=()):
    """Run a query and print the result as an aligned text table (NULL shown as NULL)."""
    cur = db.execute(sql, params)
    cols = [d[0] for d in cur.description]
    rows = [["NULL" if v is None else str(v) for v in r] for r in cur.fetchall()]
    widths = [max(len(x) for x in col) for col in zip(cols, *rows)]
    for line in [cols, *rows]:
        print("  ".join(x.ljust(w) for x, w in zip(line, widths)).rstrip())
    print(f"({len(rows)} row{'' if len(rows) == 1 else 's'})\n")

# Comparing with unknown gives unknown (shown as NULL). OR and AND still short-circuit sensibly.
show("SELECT NULL = NULL AS eq, NULL <> 1 AS ne, NULL IS NULL AS is_null, 1 + NULL AS plus, NULL OR 1 AS or_true, NULL AND 0 AS and_false")

# Kabir's city is unknown, so he is in neither "= Delhi" nor "<> Delhi"
show("SELECT name, city FROM customers WHERE city = 'Delhi'")
show("SELECT name, city FROM customers WHERE city <> 'Delhi'")
show("SELECT name, city FROM customers WHERE city <> 'Delhi' OR city IS NULL")

# COUNT(*) counts rows; COUNT(col) counts non-NULL values; DISTINCT also drops duplicates
show("SELECT COUNT(*) AS all_orders, COUNT(coupon) AS with_coupon, COUNT(DISTINCT coupon) AS coupon_codes FROM orders")

# Aggregates skip NULLs. Over zero rows, SUM gives NULL while COUNT gives 0.
show("SELECT SUM(price_paise) AS total, COUNT(*) AS n, COALESCE(SUM(price_paise), 0) AS safe_total FROM products WHERE category = 'Toys'")

# The NOT IN trap. Customers 3 and 5 live in ('Delhi', NULL), so x NOT IN that list is never TRUE.
show("SELECT name FROM customers WHERE city NOT IN ('Delhi', 'Pune')")
show("SELECT name FROM customers WHERE city NOT IN (SELECT city FROM customers WHERE id IN (3, 5))")
# NOT EXISTS has no such trap: customers with no orders at all
show("""
SELECT c.name FROM customers AS c
WHERE NOT EXISTS (SELECT 1 FROM orders AS o WHERE o.customer_id = c.id)
""")

What will this print? Guess first, then check.Show output

eq    ne    is_null  plus  or_true  and_false
NULL  NULL  1        NULL  1        0
(1 row)

name         city
Rohan Gupta  Delhi
(1 row)

name          city
Aarav Sharma  Bengaluru
Priya Iyer    Chennai
Sneha Patil   Pune
(3 rows)

name          city
Aarav Sharma  Bengaluru
Priya Iyer    Chennai
Sneha Patil   Pune
Kabir Khan    NULL
(4 rows)

all_orders  with_coupon  coupon_codes
6           3            2
(1 row)

total  n  safe_total
NULL   0  0
(1 row)

name
Aarav Sharma
Priya Iyer
(2 rows)

name
(0 rows)

name
Sneha Patil
Kabir Khan
(2 rows)

Text

eq    ne    is_null  plus  or_true  and_false
NULL  NULL  1        NULL  1        0
(1 row)

city = 'Delhi'      city <> 'Delhi'           city <> 'Delhi' OR city IS NULL
Rohan Gupta  Delhi  Aarav Sharma  Bengaluru   Aarav Sharma  Bengaluru
(1 row)             Priya Iyer    Chennai     Priya Iyer    Chennai
                    Sneha Patil   Pune        Sneha Patil   Pune
                    (3 rows)                  Kabir Khan    NULL
                                              (4 rows)

all_orders  with_coupon  coupon_codes
6           3            2
(1 row)

total  n  safe_total
NULL   0  0
(1 row)

NOT IN ('Delhi','Pune')   NOT IN (subquery with a NULL)   NOT EXISTS
Aarav Sharma              (0 rows)                         Sneha Patil
Priya Iyer                                                 Kabir Khan
(2 rows)                                                   (2 rows)

(Again, the outputs are condensed side by side.) The rules to carry around:

Test for NULL with IS NULL / IS NOT NULL, never = NULL. Postgres and SQLite (3.39+) also have IS DISTINCT FROM, a NULL-safe "not equal".
NOT IN (...) with any NULL in the list returns no rows, because x <> NULL is unknown. Kabir is also missing from the first NOT IN result: his own city is NULL, and NULL NOT IN (...) is unknown too. Prefer NOT EXISTS for "has no matching row".
COUNT(*) counts rows; COUNT(column) counts non-NULL values. AVG(column) divides by the non-NULL count, so an average over a column with missing values is an average of what is known.
SUM of nothing is NULL. Wrap it in COALESCE(..., 0) when the API should say 0.
Sorting differs: SQLite treats NULL as smaller than everything (first in ascending order); Postgres treats it as larger (last in ascending order). Write ORDER BY city NULLS LAST when it matters; both support it.
UNIQUE allows many NULLs in both SQLite and Postgres by default, because two unknowns are not considered equal.

Indexes, and asking the database for its plan

Without an index, WHERE customer_id = 7 means reading every row of orders and checking each one: a full table scan. An index is a separate, sorted structure (a B-tree) holding the indexed column's values with pointers back to their rows, like the index at the back of a textbook: you jump to "customer_id 7" in a few steps instead of reading every page. The cost is that every insert, update and delete must also update each index, and indexes take disk space. So you index the columns you filter, join and sort on, not every column.

EXPLAIN QUERY PLAN asks SQLite how it intends to run a query. SCAN means it will read everything; SEARCH means it will seek into an index.

Python · runs as is

import random
import sqlite3
import time

db = sqlite3.connect(":memory:")
db.execute("CREATE TABLE orders (id INTEGER PRIMARY KEY, customer_id INTEGER NOT NULL, "
           "status TEXT NOT NULL, placed_at TEXT NOT NULL, total_paise INTEGER NOT NULL)")
rng = random.Random(42)
rows = [(i, rng.randint(1, 20_000), rng.choice(["placed", "shipped", "delivered"]),
         f"2026-{rng.randint(1, 9):02d}-{rng.randint(1, 28):02d}", rng.randint(9_900, 500_000))
        for i in range(1, 100_001)]
db.executemany("INSERT INTO orders VALUES (?, ?, ?, ?, ?)", rows)
db.commit()

def plan(sql: str, params=()) -> None:
    # Each plan row is (id, parent, notused, detail); the detail text is what you read.
    for *_, detail in db.execute("EXPLAIN QUERY PLAN " + sql, params):
        print("   ", detail)

def time_lookups(n: int = 200) -> float:
    start = time.perf_counter()
    for cid in range(1, n + 1):
        db.execute("SELECT id, total_paise FROM orders WHERE customer_id = ?", (cid,)).fetchall()
    return (time.perf_counter() - start) / n * 1000      # milliseconds per query

q = "SELECT id, total_paise FROM orders WHERE customer_id = ?"
print("Before any index:")
plan(q, (7,))
before = time_lookups()

db.execute("CREATE INDEX idx_orders_customer ON orders (customer_id)")
print("After CREATE INDEX idx_orders_customer:")
plan(q, (7,))
after = time_lookups()
print(f"per lookup: {before:.3f} ms -> {after:.3f} ms (about {before / after:.0f}x faster here)\n")

print("Lookup by primary key (always indexed):")
plan("SELECT * FROM orders WHERE id = ?", (12345,))

print("Filter plus sort, single-column index: sorts in a temporary B-tree")
plan("SELECT * FROM orders WHERE customer_id = ? ORDER BY placed_at DESC", (7,))
db.execute("CREATE INDEX idx_orders_customer_date ON orders (customer_id, placed_at)")
print("Same query with a composite index (customer_id, placed_at): no sort step")
plan("SELECT * FROM orders WHERE customer_id = ? ORDER BY placed_at DESC", (7,))

print("Leftmost-prefix rule: placed_at alone cannot SEARCH that index, only SCAN all of it")
plan("SELECT COUNT(*) FROM orders WHERE placed_at >= ?", ("2026-09-01",))

db.execute("CREATE INDEX idx_orders_date ON orders (placed_at)")
print("A function wrapped around the column hides it from the index:")
plan("SELECT COUNT(*) FROM orders WHERE substr(placed_at, 1, 7) = ?", ("2026-09",))
print("The same question as a range on the bare column can use it:")
plan("SELECT COUNT(*) FROM orders WHERE placed_at >= ? AND placed_at < ?", ("2026-09-01", "2026-10-01"))

What will this print? Guess first, then check.Show output

Before any index:
    SCAN orders
After CREATE INDEX idx_orders_customer:
    SEARCH orders USING INDEX idx_orders_customer (customer_id=?)
per lookup: 3.297 ms -> 0.006 ms (about 516x faster here)

Lookup by primary key (always indexed):
    SEARCH orders USING INTEGER PRIMARY KEY (rowid=?)
Filter plus sort, single-column index: sorts in a temporary B-tree
    SEARCH orders USING INDEX idx_orders_customer (customer_id=?)
    USE TEMP B-TREE FOR ORDER BY
Same query with a composite index (customer_id, placed_at): no sort step
    SEARCH orders USING INDEX idx_orders_customer_date (customer_id=?)
Leftmost-prefix rule: placed_at alone cannot SEARCH that index, only SCAN all of it
    SCAN orders USING COVERING INDEX idx_orders_customer_date
A function wrapped around the column hides it from the index:
    SCAN orders USING COVERING INDEX idx_orders_date
The same question as a range on the bare column can use it:
    SEARCH orders USING COVERING INDEX idx_orders_date (placed_at>? AND placed_at<?)

Text

Before any index:
    SCAN orders
After CREATE INDEX idx_orders_customer:
    SEARCH orders USING INDEX idx_orders_customer (customer_id=?)
per lookup: 2.554 ms -> 0.008 ms (about 309x faster here)

Lookup by primary key (always indexed):
    SEARCH orders USING INTEGER PRIMARY KEY (rowid=?)
Filter plus sort, single-column index: sorts in a temporary B-tree
    SEARCH orders USING INDEX idx_orders_customer (customer_id=?)
    USE TEMP B-TREE FOR ORDER BY
Same query with a composite index (customer_id, placed_at): no sort step
    SEARCH orders USING INDEX idx_orders_customer_date (customer_id=?)
Leftmost-prefix rule: placed_at alone cannot SEARCH that index, only SCAN all of it
    SCAN orders USING COVERING INDEX idx_orders_customer_date
A function wrapped around the column hides it from the index:
    SCAN orders USING COVERING INDEX idx_orders_date
The same question as a range on the bare column can use it:
    SEARCH orders USING COVERING INDEX idx_orders_date (placed_at>? AND placed_at<?)

Your timings will differ; the ratio is the point, and it grows with the table. What to take away:

A composite index on (customer_id, placed_at) is sorted by customer, then by date within each customer, like a phone book sorted by surname then first name. It serves "this customer's orders, newest first" with no sort step, but cannot seek on placed_at alone (you cannot find everyone named Priya quickly in a surname-first phone book). That is the leftmost-prefix rule.
A covering index contains every column the query needs, so the table itself is never read.
Wrapping a column in a function (substr(placed_at, 1, 7), lower(email)) prevents a seek. Rewrite as a range on the bare column, or create an index on the expression itself.
Primary keys and UNIQUE constraints get an index automatically. Foreign key columns do not, in SQLite or Postgres, and you almost always want one: joins and "orders for customer X" go through them.
In Postgres, EXPLAIN shows the plan with estimated costs (Seq Scan on orders versus Index Scan using ...), and EXPLAIN ANALYZE actually runs the query and reports real row counts and timings. Because it really runs, wrap an EXPLAIN ANALYZE of an UPDATE or DELETE in BEGIN; ... ROLLBACK;. On a tiny table Postgres will often choose a sequential scan even with an index, correctly, because reading three pages is cheaper than using the index.

Transactions, in one paragraph

A transaction groups statements so they succeed or fail as one unit: placing an order inserts the order and all its line items, and a failure halfway must never leave half an order. Databases promise ACID: atomic (all or nothing), consistent (constraints hold afterwards), isolated (concurrent transactions do not see each other's half-done work, to a degree set by the isolation level) and durable (committed data survives a crash). In SQL you write BEGIN; ...; COMMIT; or ROLLBACK;. In Python's sqlite3, with conn: commits if the block succeeds and rolls back if it raises, but does not close the connection (psycopg 3's with psycopg.connect(...) commits and closes). By default sqlite3 silently opens a transaction before your first INSERT, UPDATE or DELETE, which is why you must commit(); Python 3.12 added an autocommit attribute for explicit control.

Python · runs as is

import sqlite3

db = sqlite3.connect(":memory:")
db.executescript("""
CREATE TABLE orders (id INTEGER PRIMARY KEY, customer_id INTEGER NOT NULL);
CREATE TABLE order_items (order_id INTEGER NOT NULL REFERENCES orders(id), product_id INTEGER NOT NULL,
                          quantity INTEGER NOT NULL CHECK (quantity > 0));
""")

def place_order(customer_id: int, items: list[tuple[int, int]]) -> int:
    with db:   # commits if the block succeeds, rolls back if it raises (it does NOT close db)
        cur = db.execute("INSERT INTO orders (customer_id) VALUES (?)", (customer_id,))
        db.executemany("INSERT INTO order_items VALUES (?, ?, ?)",
                       [(cur.lastrowid, pid, qty) for pid, qty in items])
        return cur.lastrowid

print("placed order", place_order(1, [(1, 2), (3, 1)]))
try:
    place_order(2, [(4, 1), (5, 0)])          # second line breaks the CHECK constraint
except sqlite3.IntegrityError as e:
    print("failed:", e)
print("orders:", db.execute("SELECT COUNT(*) FROM orders").fetchone()[0],
      "| items:", db.execute("SELECT COUNT(*) FROM order_items").fetchone()[0])  # 1 | 2: no half-order

What will this print? Guess first, then check.Show output

placed order 1
failed: CHECK constraint failed: quantity > 0
orders: 1 | items: 2

Parameterised queries, and why string-built SQL is a security bug

If you build SQL by pasting user input into a string, the input can change the meaning of the SQL. That is SQL injection, and it is still one of the most common ways real applications leak data.

Two ways to put user input into SQL

Built with a string

f"... WHERE email = '{email}'"
The input can rewrite the query itself
One stray quote breaks it; a crafted one leaks data

Parameterised

cur.execute("... WHERE email = ?", (email,))
The input is only ever treated as data
The driver handles quoting for every type

Python · runs as is

import sqlite3

db = sqlite3.connect(":memory:")
db.executescript("""
CREATE TABLE customers (id INTEGER PRIMARY KEY, name TEXT NOT NULL, email TEXT NOT NULL UNIQUE, city TEXT);
INSERT INTO customers VALUES (1, 'Aarav Sharma', 'aarav@example.in', 'Bengaluru'),
                             (2, 'Priya Iyer',   'priya@example.in', 'Chennai'),
                             (3, 'Rohan Gupta',  'rohan@example.in', 'Delhi');
""")

def find_by_email_unsafe(email: str) -> list:
    # NEVER do this: the input becomes part of the SQL text
    sql = f"SELECT id, name FROM customers WHERE email = '{email}'"
    print("   SQL sent:", sql)
    return db.execute(sql).fetchall()

def find_by_email(email: str) -> list:
    # The SQL text is fixed; the value travels separately and is only ever data
    return db.execute("SELECT id, name FROM customers WHERE email = ?", (email,)).fetchall()

attack = "x' OR '1'='1"
print("unsafe, normal input:", find_by_email_unsafe("priya@example.in"))
print("unsafe, attack input:", find_by_email_unsafe(attack))     # every customer leaks
print("safe,   attack input:", find_by_email(attack))            # just a strange email: no match

# Named placeholders read better when there are many values
row = db.execute("SELECT name FROM customers WHERE city = :city AND id > :min_id",
                 {"city": "Delhi", "min_id": 1}).fetchone()
print("named placeholders:", row)

# Placeholders are for VALUES only. A column to sort by must come from an allowlist.
SORTABLE = {"name": "name", "city": "city", "newest": "id DESC"}
def list_customers(sort: str) -> list:
    if sort not in SORTABLE:
        raise ValueError(f"cannot sort by {sort!r}; choose from {sorted(SORTABLE)}")
    return db.execute(f"SELECT name FROM customers ORDER BY {SORTABLE[sort]}").fetchall()
print("sorted by newest:", list_customers("newest"))
try:
    list_customers("name; DROP TABLE customers")
except ValueError as e:
    print("rejected:", e)

What will this print? Guess first, then check.Show output

   SQL sent: SELECT id, name FROM customers WHERE email = 'priya@example.in'
unsafe, normal input: [(2, 'Priya Iyer')]
   SQL sent: SELECT id, name FROM customers WHERE email = 'x' OR '1'='1'
unsafe, attack input: [(1, 'Aarav Sharma'), (2, 'Priya Iyer'), (3, 'Rohan Gupta')]
safe,   attack input: []
named placeholders: ('Rohan Gupta',)
sorted by newest: [('Rohan Gupta',), ('Priya Iyer',), ('Aarav Sharma',)]
rejected: cannot sort by 'name; DROP TABLE customers'; choose from ['city', 'name', 'newest']

Text

   SQL sent: SELECT id, name FROM customers WHERE email = 'priya@example.in'
unsafe, normal input: [(2, 'Priya Iyer')]
   SQL sent: SELECT id, name FROM customers WHERE email = 'x' OR '1'='1'
unsafe, attack input: [(1, 'Aarav Sharma'), (2, 'Priya Iyer'), (3, 'Rohan Gupta')]
safe,   attack input: []
named placeholders: ('Rohan Gupta',)
sorted by newest: [('Rohan Gupta',), ('Priya Iyer',), ('Aarav Sharma',)]
rejected: cannot sort by 'name; DROP TABLE customers'; choose from ['city', 'name', 'newest']

With a placeholder, the SQL text and the value reach the database separately: the SQL is parsed first and the value is plugged in as data, so no value can ever become SQL. Hand-escaping quotes is not a substitute. Placeholders work only for values, never for table names, column names or ASC/DESC; those come from your own code via an allowlist, exactly as the FastAPI lesson's PATCH endpoint built its SET clause from the Pydantic model's field names. sqlite3's execute() refuses two statements in one call, which blocks the famous '; DROP TABLE trick, but the OR '1'='1' leak above needs no second statement.

In Postgres with psycopg 3, the placeholder is %s (or %(name)s). It looks like Python string formatting but is not; you pass values as the second argument and the driver sends them separately:

Python

# psycopg 3.3 (pip install "psycopg[binary]"). DATABASE_URL e.g. postgresql://app:secret@localhost:5432/shop
import os

import psycopg

email = "priya@example.in"
with psycopg.connect(os.environ["DATABASE_URL"]) as conn:     # commits and closes on exit
    row = conn.execute("SELECT id, name FROM customers WHERE email = %s", (email,)).fetchone()
    # WRONG, even though it looks similar:  conn.execute("... WHERE email = '%s'" % email)

Where Postgres differs

You can learn SQL on SQLite and keep almost everything. The differences that bite:

Architecture. SQLite is a library writing one file, with one writer at a time; perfect for tests, local tools and small services. Postgres is a server with many concurrent readers and writers, roles and permissions, replication and backups; it is what most clients run in production.
Types are strict. SQLite stores 'abc' in an INTEGER column unless you declare the table STRICT (3.37+); Postgres rejects it. Postgres has real timestamptz, numeric, boolean, uuid and jsonb types; SQLite stores dates as text or numbers.
Auto-increment ids. SQLite: id INTEGER PRIMARY KEY. Postgres: id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY (older code uses SERIAL).
Foreign keys are always enforced in Postgres.
Strictness. Postgres rejects ungrouped columns in grouped queries, aliases in WHERE, and double-quoted strings ("Grocery" is an identifier, never a string). SQLite accepts all three through Python's sqlite3 (its own command-line shell has rejected double-quoted strings by default since 3.41).
LIKE. Case-sensitive in Postgres (use ILIKE for case-insensitive); case-insensitive for ASCII letters in SQLite by default.
NULL ordering is reversed, as above.
Placeholders: ? and :name in sqlite3; %s and %(name)s in psycopg.
Both support INSERT ... RETURNING, ON CONFLICT ... DO UPDATE (upsert), window functions and common table expressions (WITH), which are your next SQL topics after this lesson.

How this shows up in FDE work

A D2C grocery brand in Pune wants their support agent to answer "where is my order?", and their ops lead wants a weekly report of unpaid orders. You get read access to their Postgres. On day one you read the tables and foreign keys and find order_items has no index on order_id, which is why their admin page is slow; you show EXPLAIN ANALYZE before and after a one-line CREATE INDEX. The report is a reconciliation of their orders against the payment gateway's export: a FULL OUTER JOIN with a verdict column, plus COUNT(DISTINCT ...) because their first draft double counted multi-item orders. For the agent, you write one parameterised query per question and expose each as a tool, rather than letting the model write raw SQL against production. When a model does draft SQL for an analyst, it runs under a read-only role, and you check its joins and NULL handling exactly as this lesson taught.

Planning the Week 1 deliverable

The deliverable: a small FastAPI CRUD service (a notes or task API is fine) backed by a real database, tested, pushed to GitHub with a README that explains how to run it. You already have the skeleton from the FastAPI lesson. This week's job is to make the database part as solid as the HTTP part. Plan it in this order.

1. Design the schema on paper first, with a relationship in it. Two related tables make you use keys and joins. For a task API, for example:

Text

projects (id PK, name TEXT NOT NULL UNIQUE, created_at TEXT NOT NULL)
tasks    (id PK, project_id FK -> projects.id NOT NULL, title TEXT NOT NULL,
          status TEXT NOT NULL CHECK (status IN ('todo','doing','done')),
          due_date TEXT NULL, created_at TEXT NOT NULL)
index:   tasks (project_id, status)      -- serves "tasks in project X with status Y"

For every column, write down its type, NULL or NOT NULL and why, and any CHECK or UNIQUE. Decide what deleting a project does: 409 while it has tasks, or ON DELETE CASCADE. Either is defensible; undocumented is not.

2. Decide SQLite or Postgres. SQLite with PRAGMA foreign_keys = ON in your single connect() function is fully acceptable. Postgres in Docker is a stretch goal closer to client reality; if you choose it, read a DATABASE_URL environment variable and keep the tests runnable without manual setup.

3. Plan the endpoints and their SQL. Full CRUD for the main resource (create, list with filter and capped limit, get, PATCH, delete) plus at least one endpoint that needs a JOIN or GROUP BY, such as task counts per status for a project. Every value goes through a placeholder; every write touching two tables runs in one transaction.

4. Plan the tests before writing them. A fresh database per test (the tmp_path fixture from the FastAPI lesson). Cover each happy path, 404s, 422s, a task for a non-existent project (a clean 404 or 422, never a 500 from an IntegrityError), your delete behaviour, the aggregate endpoint on an empty database (zeros, not nulls), and an injection-shaped string treated as plain data.

5. Plan the repository. Branch and pull request, as in the Git lesson. .gitignore covers .venv/, *.db and .env. The README covers what the service does, quickstart, configuration, three curl examples, running the tests, and "Design decisions": your schema, your delete behaviour, and EXPLAIN QUERY PLAN for your main list query showing SEARCH, not SCAN.

Acceptance criteria. You are done when all of these are true:

A stranger can go from git clone to a running server and passing tests by following only the README, in under ten minutes.
pytest passes from a clean checkout, with no database file committed and none required beforehand.
Status codes are deliberate: 201 with Location on create, 204 on delete, 404, 409 or 422 for client mistakes, never a 500 for bad input.
Foreign keys are enforced and a test proves it.
No SQL string anywhere is built from request values; searching your code for execute(f finds only allowlisted cases, each with a comment.
At least one endpoint uses a JOIN or GROUP BY, is tested on empty and non-empty data, and handles NULLs correctly.
The repository has a meaningful commit history, not one "final" commit.

Pitfalls

Forgetting PRAGMA foreign_keys = ON in SQLite. Every REFERENCES clause is decoration until you enable it, on every connection, and orphan rows accumulate silently.
Filtering the right-hand table of a LEFT JOIN in WHERE. The NULL rows you joined for are dropped and the query quietly becomes an INNER JOIN; put that condition in ON.
Writing = NULL, or NOT IN (subquery) where the subquery can return a NULL. Both return nothing without any error. Use IS NULL and NOT EXISTS.
Aggregating after a one-to-many join. Each parent row is repeated per child, so counts and sums of parent-level columns are inflated; use COUNT(DISTINCT ...) or aggregate the child table first.
Storing money as REAL and dividing integers by integers. Floats drift and 24950 / 100 is 249; store integer paise (or NUMERIC in Postgres) and divide by 100.0 only for display.
Building SQL with f-strings or % from any value that came from outside your code. That is SQL injection; values go in placeholders, identifiers come from an allowlist.

Practice

Predict, then run. Against the shop database, write down the result you expect before running each query: (a) the average order value in rupees excluding cancelled orders; (b) for each city, the number of customers and the number of orders, keeping cities with no orders and showing customers with no city as 'Unknown'; (c) products bought by more than one distinct customer; (d) each customer's most recent order date, including customers who never ordered. For every query you predicted wrongly, write one sentence on why.
Add a reviews table: customer, product, rating 1 to 5 enforced by CHECK, optional comment, at most one review per customer per product. Insert a few rows, then write a query listing every product with its average rating and review count, including unreviewed products. Decide whether an unreviewed product should show NULL or 0 as its average, and defend the choice in a comment.
Index experiment. Grow the indexing example's table to 500,000 rows. Measure (a) a lookup by customer_id and (b) inserting 50,000 more rows, each with zero, one and three indexes on the table. Record the plans and timings in a short table in your notes, and write two sentences on the read and write trade-off you measured.
Port the shop to Postgres. Start one with Docker (docker run --name shop-pg -e POSTGRES_PASSWORD=dev -p 5432:5432 -d postgres:18), translate the schema (identity columns, timestamptz, numeric or integer paise), load the same rows with psycopg using placeholders, and run every query from this lesson. List each change you had to make and each query whose result or error differed from SQLite.

Check yourself

Answer in your head or on paper first, then open the answer and be honest with yourself.

Why does SELECT name FROM customers WHERE city <> 'Delhi' not return customers whose city is NULL?

NULL <> 'Delhi' evaluates to UNKNOWN, not TRUE, and WHERE keeps only rows where the condition is TRUE. Add OR city IS NULL if those customers should be included.

A LEFT JOIN from customers to orders with WHERE o.status = 'delivered' returns no customers without orders. Why, and what is the fix?

The unmatched customers have NULL in every order column, and NULL = 'delivered' is not true, so the WHERE filter removes them, turning the query into an inner join. Move the condition into the ON clause.

What is the difference between COUNT(*), COUNT(coupon) and COUNT(DISTINCT coupon) on the orders table?

COUNT(*) counts rows (6), COUNT(coupon) counts rows where coupon is not NULL (3), and COUNT(DISTINCT coupon) counts different non-NULL coupon values (2).

You have an index on (customer_id, placed_at). Which of these can seek into it: WHERE customer_id = 7, WHERE customer_id = 7 AND placed_at >= '2026-09-01', WHERE placed_at >= '2026-09-01'?

The first two, because they constrain the leftmost column. The third does not constrain customer_id, so the index is sorted the wrong way for it and the database must scan.

Why can you not pass a column name as a ? placeholder, and what do you do instead?

Placeholders bind values after the SQL has been parsed, and the column names are part of the SQL's structure, so they must be known at parse time. Map the user's choice to a fixed set of column names in your code (an allowlist) and reject anything else.

Why does unit_price_paise live in order_items when products already has a price?

Because a product's price changes over time, and an order must record what the customer actually paid at the moment of purchase. It is a historical fact about the order line, not a duplicate of the current price.

Use AI for this

AI models write SQL fluently, and that is the danger: the query runs, returns plausible rows, and is wrong in exactly the ways this lesson covered. Write your queries by hand this week, predicting each result before you run it, until joins, grouping and NULLs feel obvious. Then use AI for what it is genuinely good at: explaining a query plan you do not understand, reviewing a schema for missing constraints and indexes, and generating test data or tricky predict-the-result questions. Always give it your real schema, and always run what it gives you against real data before trusting it.

Explain this query plan to me

You are a database engineer teaching me to read query plans. My database is
[SQLite 3.x / PostgreSQL 18]. Here is the schema of the tables involved, including
indexes (paste the CREATE TABLE and CREATE INDEX statements):
[PASTE SCHEMA]

The query:
[PASTE QUERY]

The plan output from [EXPLAIN QUERY PLAN / EXPLAIN ANALYZE]:
[PASTE PLAN]

Approximate row counts: [e.g. orders 2 million, customers 80,000]

1. Walk through the plan line by line in plain English: what is scanned, what is
   searched through which index, where sorting or temporary structures appear.
2. Point to the most expensive step and explain why it is expensive.
3. Suggest at most two index or query changes, and for each one, predict how the
   plan line would change and what it costs on writes.
Only refer to columns and indexes that appear in my schema. If the plan format
depends on the database version, say so.

Review my schema before I build on it

Act as a senior backend engineer reviewing a relational schema for a production
service. The service is: [ONE-PARAGRAPH DESCRIPTION, e.g. "a task API where users
create projects and tasks; tasks have status and due dates"].
Database: [SQLite with PRAGMA foreign_keys = ON / PostgreSQL 18].

Schema:
[PASTE ALL CREATE TABLE AND CREATE INDEX STATEMENTS]

The queries the API will run most often:
[LIST 3-6 QUERIES OR DESCRIBE THEM]

Review for: primary and foreign keys, NOT NULL decisions (for each nullable column,
ask what NULL would mean), CHECK and UNIQUE constraints that are missing, money and
date types, ON DELETE behaviour, and indexes for the listed queries (including
foreign key columns). For each issue give: the table and column, the concrete bug it
allows (a bad row that could be inserted, or a slow query), and the corrected DDL.
Do not redesign the whole thing; list the five most important changes, most
important first.

Quiz me with predict-the-result queries

Quiz me on SQL using this small dataset. First, create 3 or 4 tables of an Indian
e-commerce shop (customers, orders, order_items, products, prices in paise) with at
most 8 rows each, including at least one NULL city, one customer with no orders,
one product never ordered and one cancelled order. Show the data as SQLite
CREATE TABLE and INSERT statements so I can run them myself.

Then ask me one question at a time: show a query and ask me to predict the exact
result rows. Cover INNER vs LEFT JOIN, a filter on the right table of a LEFT JOIN,
GROUP BY with HAVING, COUNT(*) vs COUNT(column), NOT IN with a NULL, fan-out after a
join, and ORDER BY with NULLs. Wait for my answer, then tell me whether I am right,
show the correct result, and explain in two or three sentences. Make the next
question harder when I get one right. Use only standard SQL that SQLite 3.39+
runs, and say so if a question's answer would differ in PostgreSQL.

After the quiz, run the AI's dataset and queries yourself in Python; if its "correct result" disagrees with the real output, you have found a model error, which is a lesson in itself.

Watch out

AI tools invent columns and tables that sound right: they will join on orders.user_id when your column is customer_id, or select products.price when you store price_paise. Paste the real schema every time and run the query; an error is the good outcome, a wrong join that happens to run is the bad one. They also mix dialects freely, writing Postgres ILIKE, ::date casts, SERIAL or NOW() for SQLite, MySQL backtick quoting and LIMIT x, y for Postgres, or SQLite's lenient GROUP BY that Postgres rejects; state the database and version in the prompt and check each function against that database's docs. Most dangerous of all, generated Python often builds SQL with f-strings or % formatting ("for simplicity"), including in tests and scripts that later get copied into production. Reject every query that puts a value into the SQL text, and check that NOT IN, LEFT JOIN filters and aggregates over joins are written the way this lesson showed.

Go deeper

I have done this task, including the practice

SQL from the relational model up: keys, joins, grouping, NULLs and indexes

The mental model: tables are sets of facts, SQL describes the answer

Keys: how rows find each other

SELECT, WHERE, ORDER BY, LIMIT

JOINs, one shape at a time

GROUP BY, HAVING and aggregate functions

NULL: SQL's third truth value

Indexes, and asking the database for its plan

Transactions, in one paragraph

Parameterised queries, and why string-built SQL is a security bug

Where Postgres differs

How this shows up in FDE work

Planning the Week 1 deliverable

Build the deliverable, then keep going.