Databases & SQL

The Relational Model

A relational database stores data in tables (relations). Each table has columns (attributes) with a defined type, and rows (tuples) that hold the actual records. The power of the model comes from relationships between tables expressed through shared key values, and a declarative query language — SQL — that lets you describe what you want without specifying how to fetch it.

The database engine's query planner turns your SQL into an execution plan, using statistics and indexes to run it efficiently. Your job is to write correct, clear SQL and design tables that make the right queries cheap.

Tables, Keys & Constraints

A primary key uniquely identifies each row and cannot be NULL. A foreign key references the primary key of another table, enforcing referential integrity — you cannot insert an order for a customer that doesn't exist.

CREATE TABLE customers (
  id         BIGSERIAL PRIMARY KEY,
  email      TEXT NOT NULL UNIQUE,
  name       TEXT NOT NULL,
  created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE TABLE orders (
  id          BIGSERIAL PRIMARY KEY,
  customer_id BIGINT NOT NULL REFERENCES customers(id) ON DELETE CASCADE,
  total_cents INTEGER NOT NULL CHECK (total_cents >= 0),
  status      TEXT NOT NULL DEFAULT 'pending',
  created_at  TIMESTAMPTZ NOT NULL DEFAULT now()
);

SELECT — The Core Query

The clauses of a query are written in one order but evaluated in another. This is the single most important thing to internalize — it explains why you cannot use a column alias in WHERE but can in ORDER BY.

-- Written order
SELECT   status, COUNT(*) AS n
FROM     orders
WHERE    created_at >= '2026-01-01'
GROUP BY status
HAVING   COUNT(*) > 10
ORDER BY n DESC
LIMIT    5;

-- Logical evaluation order:
-- FROM -> WHERE -> GROUP BY -> HAVING -> SELECT -> ORDER BY -> LIMIT

WHERE filters individual rows before grouping; HAVING filters groups after aggregation. Use WHERE whenever possible — it reduces rows earlier and is cheaper.

-- Common WHERE predicates
WHERE status IN ('paid', 'shipped')
WHERE total_cents BETWEEN 1000 AND 5000
WHERE email LIKE '%@gmail.com'
WHERE status <> 'cancelled'        -- SQL not-equal
WHERE deleted_at IS NULL            -- never use = NULL
WHERE name ILIKE 'jane%'            -- case-insensitive (Postgres)

Aggregate Functions & GROUP BY

Aggregates collapse many rows into one summary value. When you mix aggregated and non-aggregated columns in SELECT, every non-aggregated column must appear in GROUP BY.

SELECT customer_id,
       COUNT(*)              AS order_count,
       SUM(total_cents)      AS lifetime_cents,
       AVG(total_cents)::int AS avg_cents,
       MIN(created_at)       AS first_order,
       MAX(created_at)       AS last_order
FROM   orders
GROUP  BY customer_id
HAVING SUM(total_cents) > 50000
ORDER  BY lifetime_cents DESC;

JOINs

A JOIN combines rows from two tables based on a related column. Choosing the right join type is about which unmatched rows you want to keep.

-- INNER JOIN: only rows with a match in BOTH tables
SELECT c.name, o.total_cents
FROM   customers c
JOIN   orders o ON o.customer_id = c.id;

-- LEFT JOIN: all customers, even those with no orders (o.* is NULL)
SELECT c.name, o.id AS order_id
FROM   customers c
LEFT JOIN orders o ON o.customer_id = c.id;

-- Find customers who have NEVER ordered (anti-join)
SELECT c.name
FROM   customers c
LEFT JOIN orders o ON o.customer_id = c.id
WHERE  o.id IS NULL;

-- FULL OUTER JOIN: all rows from both sides, matched where possible
SELECT c.name, o.id
FROM   customers c
FULL OUTER JOIN orders o ON o.customer_id = c.id;

RIGHT JOIN is just a LEFT JOIN with the tables swapped — most teams standardize on LEFT for readability. Watch where you put a condition on the joined table: putting it in ON keeps unmatched left rows, but putting it in WHERE silently turns a LEFT JOIN back into an INNER JOIN.

Subqueries & CTEs

A subquery is a query nested inside another. A CTE (Common Table Expression, the WITH clause) names a subquery, making complex queries readable and reusable within a single statement.

-- Scalar subquery in WHERE
SELECT name FROM customers
WHERE id IN (SELECT customer_id FROM orders WHERE total_cents > 10000);

-- CTE: readable, reusable
WITH high_value AS (
  SELECT customer_id, SUM(total_cents) AS spent
  FROM   orders
  GROUP  BY customer_id
  HAVING SUM(total_cents) > 100000
)
SELECT c.name, hv.spent
FROM   high_value hv
JOIN   customers c ON c.id = hv.customer_id
ORDER  BY hv.spent DESC;

-- Recursive CTE: walk an org hierarchy
WITH RECURSIVE chain AS (
  SELECT id, manager_id, name FROM employees WHERE id = 1
  UNION ALL
  SELECT e.id, e.manager_id, e.name
  FROM   employees e JOIN chain ON e.manager_id = chain.id
)
SELECT * FROM chain;

Indexes

An index is an auxiliary data structure that lets the engine find rows without scanning the whole table. The default is a B-tree: a balanced, sorted tree giving O(log n) lookups. It accelerates equality (=), range (<, >, BETWEEN), prefix LIKE 'abc%', and ORDER BY on the indexed columns.

-- Single-column index for frequent filters
CREATE INDEX idx_orders_customer ON orders(customer_id);

-- Composite index: order matters. Serves WHERE on status,
-- or (status, created_at) — but NOT created_at alone.
CREATE INDEX idx_orders_status_date ON orders(status, created_at);

-- Covering index: query answered from the index alone (index-only scan)
CREATE INDEX idx_orders_cover ON orders(customer_id) INCLUDE (total_cents);

-- Partial index: smaller, only indexes rows you actually query
CREATE INDEX idx_active ON orders(created_at) WHERE status = 'pending';

-- See what the planner does
EXPLAIN ANALYZE SELECT * FROM orders WHERE customer_id = 42;

Indexes are not free. Every INSERT, UPDATE, and DELETE must also update every affected index, and indexes consume disk and memory. Index the columns you filter, join, and sort on frequently — not every column. A leftmost-prefix rule governs composite indexes: an index on (a, b) helps WHERE a = ? and WHERE a = ? AND b = ? but not WHERE b = ? alone.

Transactions & ACID

A transaction groups multiple statements into an all-or-nothing unit. The classic example: transferring money must debit one account and credit another together, or neither.

BEGIN;
  UPDATE accounts SET balance = balance - 100 WHERE id = 1;
  UPDATE accounts SET balance = balance + 100 WHERE id = 2;
  -- if anything fails, ROLLBACK undoes both updates
COMMIT;

Isolation Levels

Higher isolation prevents more anomalies but reduces concurrency. Anomalies: a dirty read sees uncommitted data; a non-repeatable read gets different values for the same row twice; a phantom read gets a different set of rows for the same query.

Read Committed is the default in PostgreSQL and Oracle; MySQL/InnoDB defaults to Repeatable Read. Reach for Serializable only when correctness demands it, since it increases the chance of serialization conflicts that force retries.

Normalization

Normalization organizes columns to reduce redundancy and update anomalies. The first three normal forms cover most real designs:

• 1NF — atomic values only: no repeating groups or comma-separated lists in a column; each cell holds a single value.
• 2NF — 1NF plus every non-key column depends on the whole primary key (removes partial dependencies in composite keys).
• 3NF — 2NF plus no non-key column depends on another non-key column (removes transitive dependencies; e.g., store zip and derive city via a lookup rather than duplicating city on every row).

Normalize for write correctness; denormalize deliberately (caching a computed total, duplicating a rarely-changing name) only when measured read performance requires it.

SQL vs NoSQL

In 2026 the line is blurry: PostgreSQL has excellent JSONB support and NoSQL engines add transactions. Default to a relational database for most applications — reach for NoSQL when a specific access pattern (huge scale, flexible schema, key-value caching) clearly justifies it.

Practice Exercises

1. Given customers and orders, write a query returning each customer's name and total lifetime spend, including customers with zero orders (spend 0).
2. Find the top 3 customers by number of orders in the last 90 days using GROUP BY, ORDER BY, and LIMIT.
3. Write a query that finds customers who have never placed an order using a LEFT JOIN anti-join, then rewrite it with NOT EXISTS.
4. Use a CTE to compute each customer's average order value, then select only those above the overall average.
5. Given a slow query filtering on status and sorting by created_at, design the composite index and confirm it with EXPLAIN ANALYZE.
6. Write a transaction that transfers stock between two warehouses and rolls back if either warehouse would go negative (use a CHECK constraint or an explicit guard).