← All handbooks

SQL Handbook

Structured Query Language end to end — from a single SELECT to window functions, recursive CTEs, transactions, indexing, and the analytics patterns interviews love. ANSI SQL with notes where PostgreSQL, SQL Server, MySQL, Snowflake and BigQuery diverge.

01 What SQL is

SQL is a declarative language: you state what result you want, the engine’s query planner decides how to get it (which indexes, join order, algorithms). It runs over a relational database — data in tables (relations) of rows and columns, linked by keys.

The sub-languages

Keys

• Primary key — unique, non-null row identifier (one per table).
• Foreign key — column referencing a PK in another table; enforces referential integrity.
• Composite key — key spanning multiple columns. Surrogate key — meaningless generated id (identity/sequence) vs natural key from the data.

02 Logical query processing order

You write SELECT first, but the engine evaluates in this order. This explains why you can’t use a SELECT alias in WHERE, but can in ORDER BY.

-- written order vs evaluation order
FROM        -- 1  pick tables, resolve joins
WHERE       -- 2  filter rows (no aggregates yet)
GROUP BY    -- 3  collapse into groups
HAVING      -- 4  filter groups (aggregates allowed)
SELECT      -- 5  projection + window functions + aliases born here
DISTINCT    -- 6  dedupe
ORDER BY    -- 7  sort (can use SELECT aliases)
LIMIT/OFFSET-- 8  slice

03 SELECT & projection

SELECT customer_id,
       amount * 1.1 AS amount_with_tax,
       UPPER(status) AS status
FROM orders;

• SELECT * is fine to explore, bad in production code — it breaks when columns change and reads extra data. List columns explicitly.
• Aliases rename output (AS optional). Quote with double quotes for spaces/case: "Total Sales".
• DISTINCT dedupes the whole row. COUNT(DISTINCT col) counts unique values.

04 Filtering & NULL logic

SELECT * FROM orders
WHERE status IN ('paid','shipped')
  AND amount BETWEEN 100 AND 500
  AND created_at >= '2026-01-01'
  AND email LIKE '%@gmail.com';

NULL is not a value — it’s “unknown”

• Three-valued logic: comparisons with NULL yield UNKNOWN, not true/false. WHERE x = NULL never matches — use IS NULL / IS NOT NULL.
• NULL = NULL → UNKNOWN. COUNT(col) ignores NULLs; COUNT(*) counts rows.
• Coalesce defaults: COALESCE(discount, 0). NULLIF(a,b) returns NULL when equal (guard divide-by-zero: a / NULLIF(b,0)).
• NOT IN (subquery) with any NULL in the list returns no rows — prefer NOT EXISTS.

05 Sorting & paging

SELECT * FROM products
ORDER BY category ASC, price DESC NULLS LAST
LIMIT 20 OFFSET 40;   -- page 3 of 20

• SQL Server uses OFFSET 40 ROWS FETCH NEXT 20 ROWS ONLY (or TOP n).
• Keyset pagination beats OFFSET for deep pages: WHERE (price,id) < (:last_price,:last_id) ORDER BY price DESC,id DESC LIMIT 20 — O(1) instead of scanning skipped rows.
• Always include a tiebreaker column in ORDER BY or row order is non-deterministic.

06 CASE & conditionals

SELECT order_id,
  CASE WHEN amount >= 500 THEN 'large'
       WHEN amount >= 100 THEN 'medium'
       ELSE 'small' END AS bucket,
  SUM(CASE WHEN status='paid' THEN amount ELSE 0 END) AS paid_total
FROM orders GROUP BY order_id;

Conditional aggregation (the SUM(CASE...) trick) is how you pivot and compute filtered metrics in one pass — learn it cold.

07 Joins

SELECT o.id, c.name, p.title
FROM orders o
JOIN      customers c ON c.id = o.customer_id
LEFT JOIN products  p ON p.id = o.product_id
WHERE c.country = 'IN';

Semi / anti joins

-- customers WITH an order (semi-join)
SELECT * FROM customers c
WHERE EXISTS (SELECT 1 FROM orders o WHERE o.customer_id=c.id);

-- customers WITHOUT an order (anti-join)
SELECT * FROM customers c
WHERE NOT EXISTS (SELECT 1 FROM orders o WHERE o.customer_id=c.id);

Self-join: join a table to itself (e.g. employee → manager). Join order doesn’t change results for inner joins (planner reorders), but does for outer joins.

08 Set operations

• UNION — stack rows, remove duplicates (sorts → cost). UNION ALL keeps dupes and is much faster — use it unless you need dedup.
• INTERSECT — rows in both. EXCEPT (MySQL: EXCEPT 8.0+ / else anti-join) — rows in first not second.
• Column count and compatible types must match across both sides.

09 Subqueries

• Scalar — returns one value, usable in SELECT/WHERE: WHERE amount > (SELECT AVG(amount) FROM orders).
• Correlated — references the outer row, runs per row (can be slow): see EXISTS above.
• Derived table — subquery in FROM: FROM (SELECT ...) t.
• IN vs EXISTS: EXISTS short-circuits and handles NULLs safely; prefer it for existence checks.

10 CTEs & recursion

A Common Table Expression (WITH) names a subquery for readability and reuse. Chain several to build a pipeline top-to-bottom.

WITH paid AS (
  SELECT customer_id, SUM(amount) AS spend
  FROM orders WHERE status='paid'
  GROUP BY customer_id
)
SELECT c.name, paid.spend
FROM paid JOIN customers c ON c.id=paid.customer_id
WHERE paid.spend > 1000;

Recursive CTE (hierarchies, sequences)

WITH RECURSIVE tree AS (
  SELECT id, manager_id, name, 1 AS lvl
  FROM employees WHERE manager_id IS NULL   -- anchor
  UNION ALL
  SELECT e.id, e.manager_id, e.name, t.lvl+1
  FROM employees e JOIN tree t ON e.manager_id=t.id  -- recurse
)
SELECT * FROM tree ORDER BY lvl;

11 GROUP BY & HAVING

SELECT category, COUNT(*) orders, SUM(amount) revenue, AVG(amount) aov
FROM sales
GROUP BY category
HAVING SUM(amount) > 10000      -- filter groups, not rows
ORDER BY revenue DESC;

• Every non-aggregated SELECT column must be in GROUP BY (Postgres/SQL Server strict; MySQL lax via ONLY_FULL_GROUP_BY).
• WHERE filters rows before grouping; HAVING filters after.
• Roll-ups: GROUP BY ROLLUP(region, product) adds subtotal + grand-total rows; GROUPING SETS / CUBE for multi-dimension totals.
• Aggregates: COUNT SUM AVG MIN MAX, plus STRING_AGG/ARRAY_AGG/LISTAGG to concatenate, and statistical STDDEV/PERCENTILE_CONT.

12 Window functions

Window functions compute across a set of rows related to the current row — without collapsing them like GROUP BY. The single most important advanced SQL skill.

function() OVER (
  PARTITION BY col     -- groups (optional)
  ORDER BY col          -- order within group
  ROWS BETWEEN ...      -- frame (optional)
)

Ranking & row numbers

SELECT name, dept, salary,
  ROW_NUMBER() OVER(PARTITION BY dept ORDER BY salary DESC) rn,
  RANK()       OVER(PARTITION BY dept ORDER BY salary DESC) rnk,
  DENSE_RANK() OVER(PARTITION BY dept ORDER BY salary DESC) drnk
FROM employees;

• ROW_NUMBER always unique (1,2,3); RANK leaves gaps after ties (1,1,3); DENSE_RANK no gaps (1,1,2).
• Top-N per group: wrap in a subquery and filter WHERE rn <= 3.

Offsets & running totals

SELECT day, revenue,
  LAG(revenue) OVER(ORDER BY day)               AS prev_day,
  revenue - LAG(revenue) OVER(ORDER BY day)   AS dod_change,
  SUM(revenue) OVER(ORDER BY day
      ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS running_total,
  AVG(revenue) OVER(ORDER BY day
      ROWS BETWEEN 6 PRECEDING AND CURRENT ROW)        AS ma7
FROM daily;

Also: LEAD, FIRST_VALUE/LAST_VALUE, NTILE(4) for quartiles, PERCENT_RANK, CUME_DIST. Reuse a window via WINDOW w AS (...).

13 Types, strings & dates

• Numeric: INT/BIGINT, exact DECIMAL(p,s) for money (never FLOAT for currency — rounding errors), approximate FLOAT/DOUBLE.
• Text: VARCHAR(n), TEXT. Temporal: DATE, TIMESTAMP, TIMESTAMPTZ (store UTC, convert at edges).
• Cast: CAST(x AS INT) or Postgres x::int.

-- dates
DATE_TRUNC('month', created_at)            -- bucket to month
created_at + INTERVAL '7 days'
EXTRACT(YEAR FROM created_at)
DATEDIFF(day, a, b)                       -- SQL Server
-- strings
CONCAT(first,' ',last)  ||  SUBSTRING(s,1,3)  ||  TRIM(s)  ||  REPLACE(s,'-','')

14 INSERT / UPDATE / DELETE / MERGE

INSERT INTO orders (customer_id, amount) VALUES (1, 99.50);
INSERT INTO archive SELECT * FROM orders WHERE created_at < '2024-01-01';

UPDATE orders SET status='shipped' WHERE id=42;
DELETE FROM orders WHERE status='cancelled';

Upsert / MERGE

-- Postgres upsert
INSERT INTO stock (sku, qty) VALUES ('A1', 5)
ON CONFLICT (sku) DO UPDATE SET qty = stock.qty + EXCLUDED.qty;

-- ANSI / SQL Server / Snowflake MERGE
MERGE INTO stock t USING staging s ON t.sku=s.sku
WHEN MATCHED THEN UPDATE SET t.qty=s.qty
WHEN NOT MATCHED THEN INSERT (sku,qty) VALUES (s.sku,s.qty);

TRUNCATE empties a table fast (no per-row delete, often non-logged, resets identity) but can’t be filtered and may not be transactional everywhere.

15 DDL & constraints

CREATE TABLE orders (
  id          BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  customer_id BIGINT NOT NULL REFERENCES customers(id),
  amount      DECIMAL(10,2) NOT NULL CHECK (amount >= 0),
  status      VARCHAR(20) NOT NULL DEFAULT 'pending',
  created_at  TIMESTAMPTZ NOT NULL DEFAULT now(),
  UNIQUE(customer_id, created_at)
);

• Constraints: PRIMARY KEY, FOREIGN KEY, UNIQUE, NOT NULL, CHECK, DEFAULT. Let the DB enforce integrity — don’t rely on app code alone.
• Views — saved query, always fresh, no storage. Materialized views — stored result, fast reads, must REFRESH.
• ALTER TABLE ... ADD COLUMN / ADD CONSTRAINT; mind locks on big tables in production.

16 Transactions & ACID

BEGIN;
  UPDATE accounts SET bal = bal - 100 WHERE id=1;
  UPDATE accounts SET bal = bal + 100 WHERE id=2;
COMMIT;   -- or ROLLBACK to undo both

• ACID: Atomic (all-or-nothing), Consistent (constraints hold), Isolated (concurrent txns don’t corrupt), Durable (committed = persisted).

Higher isolation = fewer anomalies, more contention. Watch for deadlocks (two txns wait on each other) — the engine kills one; retry with consistent lock ordering.

17 Indexing

An index is a sorted side structure (usually a B-tree) that turns a full scan into a lookup. Indexes speed reads, slow writes, and cost storage — add deliberately.

• Index columns used in WHERE, JOIN, ORDER BY. A composite index (a,b) serves filters on a and a,b — left-prefix rule; not b alone.
• Covering index includes all columns a query needs → index-only scan, no table fetch.
• Unique index enforces uniqueness. Partial index (WHERE active) indexes a subset. Clustered index defines physical row order (SQL Server PK default; one per table).
• Other types: Hash (equality), GIN/GiST (Postgres JSON/full-text/arrays), bitmap (low-cardinality, OLAP).

18 Query performance

EXPLAIN ANALYZE SELECT ... ;   -- see the actual plan + timings

• Read the plan: Seq Scan (full table) vs Index Scan/Seek; join type (Nested Loop for small, Hash Join for big unsorted, Merge Join for sorted); look for the row-count estimate vs actual gap → stale stats.
• Keep statistics fresh (ANALYZE / auto-stats) so the planner estimates well.
• Filter early and narrow; avoid SELECT *; push predicates down; avoid functions on indexed columns; prefer EXISTS over IN(subquery); replace OR with UNION ALL or IN when it blocks index use.
• Partitioning (range/list) prunes large tables by date/key. Batch big DML to limit lock time and log growth.
• N+1 in app code → one set-based query beats a loop of point queries.

19 Normalization

Normalize OLTP (write-heavy apps) to kill update anomalies and redundancy. Denormalize analytics/reporting (read-heavy) for speed — fewer joins. The eternal trade-off: normalization optimizes writes & integrity; denormalization optimizes reads.

20 Schema patterns

• Star schema — central fact (events, measures + FKs) surrounded by denormalized dimensions (date, product, customer). The analytics default.
• Snowflake schema — dimensions normalized into sub-tables. Less redundancy, more joins.
• Slowly Changing Dimensions: Type 1 overwrite (no history), Type 2 add a new row with validity dates + current flag (full history), Type 3 add a “previous” column.
• Surrogate keys in dimensions decouple the warehouse from source natural keys and enable Type-2 history.

21 Pivot, JSON & procedures

Pivot (conditional aggregation works everywhere)

SELECT product,
  SUM(CASE WHEN region='N' THEN amt END) AS north,
  SUM(CASE WHEN region='S' THEN amt END) AS south
FROM sales GROUP BY product;

JSON

-- Postgres
SELECT data->>'email', data#>>'{addr,city}' FROM events;
SELECT * FROM events WHERE data @> '{"active":true}';   -- GIN-indexable

• Stored procedures / functions encapsulate logic server-side; functions return values and can be used in queries, procedures run side effects (and can manage transactions).
• Triggers fire on INSERT/UPDATE/DELETE (audit, derived columns) — powerful but easy to hide logic in; use sparingly.
• Temp tables (#tmp / CREATE TEMP TABLE) persist within a session for staging multi-step work; CTEs don’t persist.

22 Dialect cheat-sheet

ANSI window functions, CTEs and standard joins work across all modern engines — learn the standard, look up the dialect quirk when needed.

23 Analytics patterns to memorize

Deduplicate, keep latest

WITH r AS (SELECT *, ROW_NUMBER() OVER(PARTITION BY email ORDER BY updated_at DESC) rn FROM users)
SELECT * FROM r WHERE rn=1;

Top-N per group

WITH r AS (SELECT *, ROW_NUMBER() OVER(PARTITION BY dept ORDER BY sales DESC) rn FROM reps)
SELECT * FROM r WHERE rn<=3;

Running total & month-over-month

SELECT mth, rev,
  SUM(rev) OVER(ORDER BY mth ROWS UNBOUNDED PRECEDING) AS cumulative,
  rev/NULLIF(LAG(rev) OVER(ORDER BY mth),0)-1 AS mom
FROM monthly;

Gaps & islands / sessionization

-- group consecutive activity: subtract row_number from the ordering key
SELECT user_id, MIN(ts) start_ts, MAX(ts) end_ts
FROM (SELECT *, ROW_NUMBER() OVER(PARTITION BY user_id ORDER BY ts)
       - ROW_NUMBER() OVER(PARTITION BY user_id, active ORDER BY ts) grp
      FROM events) s
GROUP BY user_id, grp;

Also worth knowing cold: cohort retention (first-event month vs activity month), funnel (conditional aggregation over step events), YoY via self-join or LAG(...,12), and median via PERCENTILE_CONT(0.5).

24 Interview drills

• 2nd-highest salary (and per department) — DENSE_RANK or OFFSET.
• Find duplicate rows & delete all but one — ROW_NUMBER.
• Customers with no orders — NOT EXISTS / LEFT JOIN ... IS NULL.
• Running total / 7-day moving average — window frames.
• Pivot rows to columns — conditional aggregation.
• Nth highest, consecutive-days streaks, department-wise top earners, MoM growth.
• Explain WHERE vs HAVING; RANK vs DENSE_RANK vs ROW_NUMBER; UNION vs UNION ALL; index vs no index; what makes a query non-sargable.