§ 02 — Eras · 1974

SQL

say what you want, not how to find it

Left: era context, features, and the 101 hello. Right: 3 core concepts, 3 deeper 201 lessons, the factorial specimen, and where SQL lives today — each column scrolls on its own.

see this language in the feature matrix

creators: Donald Chamberlin & Raymond Boyce, IBM, 1974
status: alive (ubiquitous) · every database, every analytics tool

The problem it solves

Data sat in files nobody but a programmer could interrogate. SQL let the question be asked in something close to English.

The problem it carries

You own the question; the optimizer owns the answer. Plus NULL logic that betrays both, and injection for every string-built query.

Key features

declarative queries: You state the shape of the answer; the planner decides how to find it.
the relational model: Tables, keys and relations — data described independently of the program that reads it.
joins: Relations combine by value rather than by pointer, so unrelated data still lines up.
aggregation: GROUP BY folds rows into summaries; HAVING and ORDER BY shape the folded result.

101 — hello, world

Session one is the smallest program that still counts as speaking SQL: what every tutorial prints first, in the dialect that mattered when the language arrived (1974).

the first program1974

SELECT 'Hello, world';

101First contact — hello world and 3 ideas that define how SQL reads. 201 — 3 lessons on mechanisms you meet after the factorial specimen: the sharp edges practitioners actually use.

101 — core concepts

3 fragments, each naming a habit of mind in SQL. Read the note, then the code — together they are the 101 in one sitting.

01 — SELECT … WHERE

Describe the result; the optimizer picks the path — indexes, joins, everything.

SELECT name, wage FROM crew
WHERE  wage > 5000;

02 — JOIN

Relations combined by value, not by pointer — the idea behind every ORM.

SELECT s.name, p.port
FROM   ships s
JOIN   portcalls p ON p.ship = s.id;

03 — GROUP BY

Folding rows into summaries, sorted, in one declarative sentence.

SELECT port, COUNT(*) AS calls
FROM   portcalls
GROUP  BY port
ORDER  BY calls DESC;

201 — going deeper

The 201 assumes you have read the 101 and the specimen. Each lesson is one mechanism — what it is for, how it looks in code, and the trap or payoff that makes SQL distinctive at scale.

window functions
recursive CTEs
plans and indexes

window functions

Ranking, running totals and lag/lead are computed across rows without collapsing them — the modern way to do reporting that SQL aggregations cannot express.

SELECT ship, port, ts,
       RANK() OVER (PARTITION BY ship ORDER BY ts) AS call_no,
       LAG(port) OVER (PARTITION BY ship ORDER BY ts) AS prev_port
FROM portcalls;

recursive CTEs

A CTE that refers to itself walks trees and graphs — closure tables, org charts and route finding, all in declarative SQL.

WITH RECURSIVE chain (id, parent, depth) AS (
  SELECT id, parent, 0 FROM node WHERE id = 1
  UNION ALL
  SELECT n.id, n.parent, c.depth + 1
  FROM node n JOIN chain c ON n.parent = c.id
)
SELECT * FROM chain;

plans and indexes

EXPLAIN shows the path the planner chose. Without a usable index the same query degrades from milliseconds to minutes — the classic SQL performance story.

EXPLAIN ANALYZE
SELECT * FROM portcalls WHERE ship = 42;

The specimen — factorial

The query: describe the result you want — the database finds the path. Declarative computing for the office, and the longest-lived success among all “English-like” languages.

the query1974

SELECT name, wage FROM crew
WHERE wage > 5000
ORDER BY wage DESC;

Run it today

runsqlite3 < crew.sql

Where it is now

Behind every checkout, login and dashboard you touch. It has outlived every “SQL-killer” sent against it.