# SQL essentials

Source: https://codewiki.com/cheatsheets/sql/

## Read and shape rows

- `SELECT order_id, total FROM orders;` — return only the named columns from every row
- `SELECT DISTINCT customer_id FROM orders;` — remove duplicate projected rows
- `SELECT customer_id, total AS order_total FROM orders;` — name a result column explicitly
- `SELECT * FROM orders ORDER BY total DESC NULLS LAST, order_id;` — sort descending, place nulls last, and break ties stably
- `SELECT * FROM orders ORDER BY created_at DESC, order_id DESC LIMIT 20 OFFSET 40;` — skip the first 40 ordered rows, then return at most 20
- `SELECT CAST(total AS REAL) FROM orders;` — convert each value to SQLite's real storage class

## Filter and handle nulls

- `SELECT * FROM orders WHERE status = 'paid' AND total >= ?1;` — filter with a bound minimum instead of interpolated SQL
- `SELECT * FROM orders WHERE shipped_at IS NULL;` — match missing values with `IS NULL`
- `SELECT * FROM orders WHERE customer_id IN (1, 3, 5);` — match any value in an explicit list
- `SELECT * FROM orders WHERE created_at >= '2026-09-01' AND created_at < '2026-10-01';` — use a half-open range for September timestamps
- `SELECT COALESCE(discount, 0) FROM orders;` — replace null discounts in the result
- `SELECT CASE WHEN total >= 100 THEN 'large' ELSE 'small' END FROM orders;` — map each row through ordered conditions

## Join and test existence

- `SELECT o.order_id, c.name FROM orders AS o JOIN customers AS c ON c.customer_id = o.customer_id;` — keep orders with a matching customer
- `SELECT c.name, o.order_id FROM customers AS c LEFT JOIN orders AS o ON o.customer_id = c.customer_id;` — keep every customer even without an order
- `SELECT * FROM colors CROSS JOIN sizes;` — produce every color-size combination
- `SELECT * FROM orders JOIN customers USING (customer_id);` — join on an equally named column and project it once
- `SELECT * FROM customers AS c WHERE EXISTS (SELECT 1 FROM orders AS o WHERE o.customer_id = c.customer_id);` — keep customers that have at least one order
- `SELECT * FROM customers AS c WHERE NOT EXISTS (SELECT 1 FROM orders AS o WHERE o.customer_id = c.customer_id);` — keep customers with no matching order, even when the inner key is nullable

## Group and summarize

- `SELECT COUNT(*) FROM orders;` — count rows, including rows whose columns contain nulls
- `SELECT COUNT(shipped_at) FROM orders;` — count only rows with a non-null shipping time
- `SELECT COUNT(DISTINCT customer_id) FROM orders;` — count distinct non-null customer identifiers
- `SELECT customer_id, SUM(total) FROM orders GROUP BY customer_id;` — return one total per customer
- `SELECT customer_id, SUM(total) AS spent FROM orders GROUP BY customer_id HAVING SUM(total) >= 1000;` — filter groups after aggregation
- `SELECT COUNT(*) FILTER (WHERE status = 'paid') FROM orders;` — count matching rows without removing other aggregate inputs

## Combine and nest queries

- `SELECT email FROM leads UNION SELECT email FROM customers;` — combine results and remove duplicate rows
- `SELECT email FROM leads UNION ALL SELECT email FROM customers;` — combine results and preserve duplicate rows
- `SELECT email FROM leads INTERSECT SELECT email FROM customers;` — keep rows present in both results
- `SELECT email FROM leads EXCEPT SELECT email FROM customers;` — keep left-side rows absent from the right result
- `SELECT * FROM orders WHERE total > (SELECT AVG(total) FROM orders);` — compare each row with one scalar aggregate
- `SELECT * FROM (SELECT * FROM orders WHERE status = 'paid') AS paid_orders;` — query a derived table named in `FROM`

## Stage and recurse

- `WITH paid_orders AS (SELECT * FROM orders WHERE status = 'paid') SELECT * FROM paid_orders;` — name one query result for the following statement
- `WITH paid AS (SELECT * FROM orders WHERE status = 'paid'), totals AS (SELECT customer_id, SUM(total) AS spent FROM paid GROUP BY customer_id) SELECT * FROM totals;` — feed one CTE into the next
- `WITH order_totals AS (SELECT customer_id, SUM(total) AS spent FROM orders GROUP BY customer_id) SELECT c.name, t.spent FROM order_totals AS t JOIN customers AS c USING (customer_id);` — join a summarized CTE to detail data
- `WITH RECURSIVE sequence(n) AS (VALUES (1) UNION ALL SELECT n + 1 FROM sequence WHERE n < 5) SELECT n FROM sequence;` — generate integers one through five with a stopping condition
- `WITH RECURSIVE tree(id, depth) AS (SELECT category_id, 0 FROM categories WHERE parent_id IS NULL UNION ALL SELECT c.category_id, t.depth + 1 FROM categories AS c JOIN tree AS t ON c.parent_id = t.id) SELECT * FROM tree;` — walk a category hierarchy from its roots
- `VALUES (1, 'draft'), (2, 'paid');` — construct a two-row, two-column result directly

## Calculate over windows

- `SELECT order_id, ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY created_at, order_id) AS position FROM orders;` — assign a stable position inside each customer partition
- `SELECT order_id, RANK() OVER (ORDER BY total DESC) AS place FROM orders;` — give equal totals the same rank and leave gaps
- `SELECT order_id, DENSE_RANK() OVER (ORDER BY total DESC) AS tier FROM orders;` — give equal totals the same rank without gaps
- `SELECT order_id, LAG(total) OVER (PARTITION BY customer_id ORDER BY created_at, order_id) AS previous_total FROM orders;` — read the preceding ordered value in each partition
- `SELECT order_id, LEAD(total) OVER (PARTITION BY customer_id ORDER BY created_at, order_id) AS next_total FROM orders;` — read the following ordered value in each partition
- `SELECT order_id, SUM(total) OVER (PARTITION BY customer_id ORDER BY created_at, order_id ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS running_total FROM orders;` — calculate a running total with an explicit row frame

## Insert, update, and delete

- `INSERT INTO customers (customer_id, name, email) VALUES (?1, ?2, ?3);` — insert one row with bound values and an explicit column list
- `INSERT INTO statuses (status_id, name) VALUES (1, 'draft'), (2, 'paid');` — insert multiple rows in one statement
- `INSERT INTO archived_orders SELECT * FROM orders WHERE created_at < '2026-01-01';` — insert the rows returned by a query
- `INSERT INTO customers (customer_id, name, email) VALUES (?1, ?2, ?3) ON CONFLICT(customer_id) DO UPDATE SET name = excluded.name, email = excluded.email;` — insert or update on a specified uniqueness conflict
- `UPDATE orders SET status = 'paid' WHERE order_id = ?1 RETURNING order_id, status;` — update matching rows and return their new values
- `DELETE FROM orders WHERE order_id = ?1 RETURNING order_id;` — delete matching rows and return their identifiers

## Define schema and indexes

- `CREATE TABLE users (user_id INTEGER PRIMARY KEY, email TEXT NOT NULL UNIQUE);` — create a table with identity, presence, and uniqueness constraints
- `CREATE TABLE order_items (order_id INTEGER REFERENCES orders(order_id), sku TEXT, quantity INTEGER CHECK (quantity > 0), PRIMARY KEY (order_id, sku));` — declare a composite key, foreign key, and positive quantity
- `ALTER TABLE users ADD COLUMN active INTEGER NOT NULL DEFAULT 1;` — add a non-null column with a default for existing rows
- `CREATE INDEX orders_customer_created_idx ON orders (customer_id, created_at);` — index a common equality-and-ordering access path
- `CREATE UNIQUE INDEX customers_email_idx ON customers (email) WHERE email IS NOT NULL;` — enforce uniqueness only for non-null email values
- `DROP INDEX IF EXISTS orders_customer_created_idx;` — remove the index only when it exists

## Control transactions

- `BEGIN IMMEDIATE;` — start a SQLite write transaction immediately
- `COMMIT;` — commit and end the current transaction
- `ROLLBACK;` — undo the current transaction
- `SAVEPOINT before_import;` — mark a nested rollback point
- `ROLLBACK TO before_import;` — undo work after the savepoint while keeping it active
- `RELEASE before_import;` — remove the savepoint without ending an outer transaction

## Inspect and maintain

- `EXPLAIN QUERY PLAN SELECT * FROM orders WHERE customer_id = ?1;` — show the high-level query plan without running the query
- `ANALYZE;` — refresh planner statistics for all attached schemas
- `PRAGMA optimize;` — run recommended optimizer maintenance for the current workload
- `PRAGMA foreign_keys = ON;` — enable foreign-key enforcement for the current connection
- `PRAGMA table_info('orders');` — list the table's normal columns and core attributes
- `PRAGMA integrity_check;` — scan the database for structural and constraint errors