SQL: The Language of Relational Data

SQL (Structured Query Language) is the declarative language for working with relational databases. You describe what you want — not how to get it. The database’s query planner figures out the how. This declarative contract is the reason SQL has outlived every database engine that implements it: the language is portable even as the engines underneath it change.

Tables, Rows, and Types

A relational database stores data in tables — a table is a set of rows (records), and each row has columns (attributes) with declared types:

CREATE TABLE users (
  id         BIGINT PRIMARY KEY,
  email      VARCHAR(255) NOT NULL UNIQUE,
  name       TEXT NOT NULL,
  created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

• PRIMARY KEY — uniquely identifies a row; the index that backs it makes lookups fast.
• NOT NULL — the column must hold a value.
• UNIQUE — no two rows may share the value.
• DEFAULT — value used when the insert omits the column.

Types matter: VARCHAR(n) vs TEXT, INTEGER vs BIGINT, TIMESTAMPTZ vs TIMESTAMP. Choosing a type is choosing a storage contract — TIMESTAMPTZ stores an instant in time; TIMESTAMP stores wall-clock with no timezone. Pick wrong and you get 2 AM bugs.

CRUD

The four verbs every developer uses daily:

INSERT INTO users (email, name) VALUES ('a@x.com', 'Ada');          -- create
SELECT id, email FROM users WHERE name = 'Ada';                     -- read
UPDATE users SET name = 'Ada Lovelace' WHERE id = 1;                -- update
DELETE FROM users WHERE id = 1;                                     -- delete

Every statement is worth reading carefully: UPDATE/DELETE without a WHERE affects every row — the classic “whoops I dropped the whole table” is DELETE FROM users;. Always write the WHERE first in your head, then add the statement around it.

SELECT: The Heart of the Language

The SELECT statement’s clause order and execution order differ — and the execution order is the mental model that prevents bugs:

1. FROM — start from the source table(s).
2. JOIN — combine tables.
3. WHERE — filter rows (before grouping).
4. GROUP BY — collapse rows into groups.
5. HAVING — filter groups (post-aggregation).
6. SELECT — compute output expressions.
7. ORDER BY — sort results.
8. LIMIT — take a slice.

The classic mistake is putting an aggregate filter in WHERE — it can’t see aggregates yet. HAVING COUNT(*) > 5 lives at step 5, not step 3.

JOINs

Joins combine rows from two tables on a join key:

LEFT JOIN is the workhorse: “list every user with their orders” where a user with no orders still appears (with NULL order columns). If you reach for a RIGHT JOIN, most style guides suggest flipping it to a LEFT JOIN for readability. FULL OUTER JOIN is rare and usually signals a modeling question worth revisiting.

Normalization

Normalization eliminates redundant data and update anomalies. The three forms that matter:

• 1NF — one value per cell; rows are unordered and unique (has a key).
• 2NF — 1NF + no partial dependency on part of a composite key (every column depends on the whole key).
• 3NF — 2NF + no transitive dependency (non-key columns depend only on the key, not on other non-key columns).

A concrete pass: instead of storing author_name on every book row, keep an authors table and store author_id — the author’s name lives in exactly one place, so a rename is one UPDATE, not a full-table sweep. You usually want 3NF in a transactional database; you sometimes deliberately denormalize (read-heavy reporting, caching columns) — but that is a conscious trade, not an accident.

Relationships & Foreign Keys

Relationships between tables are expressed with foreign keys:

• One-to-many — orders.user_id → users.id. The many side holds the FK.
• Many-to-many — needs a join table: order_items(order_id, product_id).
• One-to-one — rare; often a sign of a column that should just be in the same table (or a security/extension split).

FOREIGN KEY (user_id) REFERENCES users(id) enforces referential integrity at the database level — you can’t insert an order for a user that doesn’t exist, and ON DELETE CASCADE can clean up children when a parent is removed. Enforce integrity in the database, not “in the application layer later.”

Window Functions

GROUP BY collapses rows — you get one output row per group, and the individual rows disappear. Window functions compute an aggregate without collapsing: every input row survives, and each row sees a window of related rows it can rank or aggregate over. The syntax is:

SELECT
  user_id,
  amount,
  ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY amount DESC) AS rank
FROM orders;

Three parts to unpack:

• The function — ROW_NUMBER(), RANK(), DENSE_RANK(), LAG()/LEAD(), SUM() OVER, AVG() OVER, NTILE(), running totals, and more.
• PARTITION BY — splits rows into independent groups; the window function restarts at each partition boundary (omit it and the whole result set is one partition).
• ORDER BY (inside the OVER clause) — orders rows within the partition and defines the frame direction (for a running total it’s “up to and including the current row”).

Contrast with plain aggregates: SELECT user_id, COUNT(*) FROM orders GROUP BY user_id returns one row per user. SELECT user_id, order_id, ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY created_at) FROM orders returns every order, each tagged with its rank within its user.

A classic pattern is top-N-per-group — “each author’s most recent post”:

SELECT * FROM (
  SELECT
    p.*,
    ROW_NUMBER() OVER (PARTITION BY author_id ORDER BY posted_at DESC) AS rn
  FROM posts p
) ranked
WHERE rn = 1;

LAG/LEAD let you compare a row to its neighbor (“order total vs previous order total”) without a self-join — the workhorse of growth/churn analysis. The mental model: a window function runs after GROUP BY/HAVING and sees the already-grouped rows, which is why window references like PARTITION BY compose naturally with a final WHERE rn = 1 wrapper.

Practice Trajectory

1. Design a small schema for a blog (posts, authors, tags, comments) — write the CREATE TABLE statements with proper types and foreign keys.
2. Write queries for “posts by author X in the last month” using WHERE, JOIN, and ORDER BY; check the results by hand.
3. Produce “each author’s most recent post” with GROUP BY + MAX(posted_at), then re-derive it with a window function.
4. Normalize a denormalized CSV (name repeated per order) into 2NF/3NF tables and write the insert queries.
5. Explain the execution order of SELECT DISTINCT name, COUNT(*) ... HAVING COUNT(*) > 1 clause by clause.

When It’s the Right Tool