SQL in 10 Minutes

A database is a set of tables — spreadsheets with rules. Each row is a thing, each column is a fact about it, and tables refer to each other by ID. SQL's one big idea: you declare what you want, and the database figures out how. You never write a loop.

-- users                          -- orders
-- id | name  | country           -- id | user_id | amount
-- 1  | Ada   | UK                -- 1  | 1       | 19.99
-- 2  | Lin   | SG                -- 2  | 1       | 5.00
-- 3  | Sam   | UK                -- 3  | 2       | 42.50

-- "orders.user_id points at users.id" — that link is the
-- entire secret of relational databases. hold that thought.

Four clauses cover most queries you'll ever write: which columns, which table, which rows, what order. One habit worth forming on day one: name your columns instead of SELECT * — star queries break silently when tables change, and they drag megabytes you didn't need.

SELECT name, country        -- which facts
FROM users                  -- which table
WHERE country = 'UK'        -- which rows
ORDER BY name               -- what order
LIMIT 10;                   -- how many (always LIMIT while exploring!)

WHERE filters rows with familiar operators — plus LIKE for patterns and IN for lists. And then there's NULL, the missing-value marker that breaks normal logic: NULL isn't equal to anything, including itself. Every SQL developer loses an hour to this exactly once. Yours is refunded here.

WHERE amount > 20
WHERE country IN ('UK', 'SG', 'DE')
WHERE name LIKE 'A%'          -- starts with A (% = anything)

-- the trap:
WHERE email = NULL            -- ⚠ returns NOTHING. always. silently.
WHERE email IS NULL           -- ✓ the only way to ask
WHERE email IS NOT NULL       -- ✓ and its opposite

Forget Venn diagrams — here's the model that works. A JOIN builds a wider table by matching rows: for each row on the left, find rows on the right where the ON condition is true, and glue them side by side. The only real question is: what happens to left rows with no match? INNER throws them away. LEFT keeps them (with NULLs filling the right side).

-- "every order, with the customer's name attached"
SELECT orders.id, users.name, orders.amount
FROM orders
JOIN users ON orders.user_id = users.id;

-- "every USER and their orders — including users
--  who never bought anything" → LEFT JOIN
SELECT users.name, orders.amount
FROM users
LEFT JOIN orders ON orders.user_id = users.id;
-- Sam bought nothing → one row: Sam | NULL

GROUP BY collapses rows into buckets — one output row per bucket — and aggregate functions (COUNT, SUM, AVG, MAX) summarize each bucket. This is the clause that turns a table of raw events into an actual report.

-- "revenue and order count per user"
SELECT user_id,
       COUNT(*)     AS orders,
       SUM(amount)  AS revenue
FROM orders
GROUP BY user_id;

-- user_id | orders | revenue
-- 1       | 2      | 24.99
-- 2       | 1      | 42.50

Both filter — the difference is when. WHERE filters rows before they're grouped; HAVING filters buckets after aggregation. The tell: if your condition contains COUNT() or SUM(), it can't go in WHERE — those numbers don't exist yet when WHERE runs.

-- "big-spender customers: only count 2026 orders,
--  and only show customers who spent 100+"
SELECT user_id, SUM(amount) AS total
FROM orders
WHERE created_at >= '2026-01-01'   -- filter ROWS first
GROUP BY user_id
HAVING SUM(amount) >= 100;         -- then filter BUCKETS

Worth internalizing once: the database runs clauses in this order — FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY. That's also why you can't use a SELECT alias inside WHERE: the alias hasn't been born yet.

Writing data is three verbs. Two of them — UPDATE and DELETE — share the most infamous foot-gun in databases: without a WHERE clause, they hit every row in the table. No confirmation. No "are you sure". Every DBA has a story; the good ones made it a habit instead of a memory.

INSERT INTO users (name, country) VALUES ('Kai', 'JP');

UPDATE users SET country = 'DE' WHERE id = 2;   -- one row ✓
UPDATE users SET country = 'DE';                -- ⚠ EVERY row. gone.

-- the professional habit: wrap risky writes in a transaction
BEGIN;
DELETE FROM orders WHERE created_at < '2020-01-01';
-- check: SELECT COUNT(*) FROM orders;  happy?
COMMIT;      -- or ROLLBACK; to undo everything

Without an index, WHERE email = '…' reads every row in the table — a full scan. An index is a sorted phone book for one column: the database jumps straight to the entry. When a query is mysteriously slow, the answer is an index about 90% of the time.

CREATE INDEX idx_users_email ON users (email);

-- before: WHERE email = 'ada@x.com'  → scans 10,000,000 rows
-- after:  same query                 → ~3 lookups

-- see what the database ACTUALLY does:
EXPLAIN SELECT * FROM users WHERE email = 'ada@x.com';
-- "Seq Scan" = full read, add an index. "Index Scan" = ✓

The queries you'll actually reuse, keyed by intent.

-- explore an unknown table
SELECT * FROM t LIMIT 10;

-- top 10 by revenue
SELECT user_id, SUM(amount) AS total
FROM orders GROUP BY user_id
ORDER BY total DESC LIMIT 10;

-- rows in A with no match in B
SELECT a.* FROM a LEFT JOIN b ON b.a_id = a.id
WHERE b.id IS NULL;

-- find duplicates
SELECT email, COUNT(*) FROM users
GROUP BY email HAVING COUNT(*) > 1;

-- safe destructive write
BEGIN;  -- ...UPDATE/DELETE with WHERE...  COMMIT; -- or ROLLBACK;

-- why is it slow?
EXPLAIN <your query>;   -- "Seq Scan" on a big table = missing index

That's working SQL. The dialects (Postgres, MySQL, SQLite) differ in the corners, but everything on this page runs everywhere. Next in the stack: Git if you haven't, and the command line, where both of them live.