Databases & SQL Cheatsheet

Query Structure

Clause & Evaluation Order

SELECT   col, agg(...) AS a   -- 5
FROM     t                    -- 1
JOIN     u ON u.tid = t.id
WHERE    predicate            -- 2  (per-row, pre-group)
GROUP BY col                  -- 3
HAVING   agg(...) > x         -- 4  (per-group, post-agg)
ORDER BY a DESC               -- 6
LIMIT    n OFFSET m;          -- 7

WHERE Predicates

col IN ('a','b')           col BETWEEN 1 AND 9
col LIKE 'abc%'            col ILIKE 'x%'   -- case-insensitive
col <> 'x'                 col IS NULL / IS NOT NULL
a AND b   a OR b   NOT a   col = ANY(ARRAY[...])

JOINs

SELECT c.name, o.total_cents
FROM   customers c
LEFT JOIN orders o ON o.customer_id = c.id;

-- Anti-join: rows with NO match
LEFT JOIN orders o ON o.customer_id = c.id
WHERE o.id IS NULL;

Aggregates & Grouping

COUNT(*)  COUNT(col)  SUM(col)  AVG(col)
MIN(col)  MAX(col)    STRING_AGG(col, ',')

SELECT customer_id, COUNT(*) n, SUM(total_cents) s
FROM   orders
GROUP  BY customer_id
HAVING COUNT(*) > 5;

-- Window function (no collapse)
SELECT id, total_cents,
  RANK() OVER (ORDER BY total_cents DESC) AS rnk,
  SUM(total_cents) OVER (PARTITION BY customer_id) AS cust_total
FROM orders;

CTEs & Subqueries

WITH big AS (
  SELECT customer_id, SUM(total_cents) s
  FROM orders GROUP BY customer_id HAVING SUM(total_cents) > 1000
)
SELECT c.name, big.s FROM big JOIN customers c ON c.id = big.customer_id;

-- Recursive
WITH RECURSIVE t AS (
  SELECT id, parent_id FROM nodes WHERE id = 1
  UNION ALL
  SELECT n.id, n.parent_id FROM nodes n JOIN t ON n.parent_id = t.id
) SELECT * FROM t;

-- EXISTS vs IN
WHERE EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.id);

DDL & Keys

CREATE TABLE t (
  id     BIGSERIAL PRIMARY KEY,
  ref_id BIGINT REFERENCES other(id) ON DELETE CASCADE,
  email  TEXT NOT NULL UNIQUE,
  qty    INT  NOT NULL CHECK (qty >= 0)
);

ALTER TABLE t ADD COLUMN note TEXT;
ALTER TABLE t ADD CONSTRAINT uq UNIQUE (email);

DML

INSERT INTO t (email, qty) VALUES ('a@x.io', 3);
UPDATE t SET qty = qty + 1 WHERE id = 5;
DELETE FROM t WHERE id = 5;

-- Upsert (Postgres)
INSERT INTO t (id, qty) VALUES (1, 4)
ON CONFLICT (id) DO UPDATE SET qty = EXCLUDED.qty;

Indexes

CREATE INDEX i1 ON t(customer_id);              -- B-tree
CREATE INDEX i2 ON t(status, created_at);       -- composite
CREATE INDEX i3 ON t(customer_id) INCLUDE (qty);-- covering
CREATE INDEX i4 ON t(created_at) WHERE status='open'; -- partial
CREATE UNIQUE INDEX i5 ON t(email);

EXPLAIN ANALYZE SELECT * FROM t WHERE customer_id = 42;

Transactions & Isolation

BEGIN;
  UPDATE accounts SET balance = balance - 100 WHERE id = 1;
  UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;   -- or ROLLBACK;

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
SELECT * FROM accounts WHERE id = 1 FOR UPDATE; -- row lock

ACID: Atomicity, Consistency, Isolation, Durability.

Normalization & SQL vs NoSQL