SQL Quick Reference
-- SELECT / WHERE / ORDER / LIMIT
SELECT id, name FROM users WHERE active = true ORDER BY name LIMIT 20;
-- Aggregation: COUNT, SUM, AVG, MIN, MAX
SELECT dept, COUNT(*) AS n, AVG(salary) AS avg
FROM employees
GROUP BY dept
HAVING AVG(salary) > 60000; -- HAVING filters groups; WHERE filters rows
-- Subquery
SELECT name FROM users WHERE id IN (SELECT user_id FROM orders WHERE total > 1000);
-- INSERT / UPDATE / DELETE
INSERT INTO users (name, email) VALUES ('Alice', 'a@b.com');
UPDATE users SET active = false WHERE last_login < '2024-01-01';
DELETE FROM sessions WHERE expires_at < NOW();
JOIN Types
-- INNER JOIN — only rows matching in BOTH tables
SELECT u.name, o.total FROM users u INNER JOIN orders o ON o.user_id = u.id;
-- LEFT JOIN — all rows from LEFT, NULL if no match on right
SELECT u.name, o.id FROM users u LEFT JOIN orders o ON o.user_id = u.id;
-- use: "all users, including those with no orders"
-- FULL OUTER JOIN — all rows from both; NULL where no match
-- RIGHT JOIN — opposite of LEFT (prefer LEFT for readability)
-- SELF JOIN — table joins itself
SELECT e.name AS emp, m.name AS manager
FROM employees e LEFT JOIN employees m ON m.id = e.manager_id;
| JOIN | Returns |
|---|
| INNER | Only matching rows from both tables |
| LEFT | All left rows + matching right (NULL if no match) |
| RIGHT | All right rows + matching left (NULL if no match) |
| FULL OUTER | All rows from both; NULL where no match |
Window Functions
-- RANK / ROW_NUMBER / DENSE_RANK within partitions
SELECT name, salary, dept,
RANK() OVER (PARTITION BY dept ORDER BY salary DESC) AS rank
FROM employees;
-- Running total
SELECT date, amount,
SUM(amount) OVER (ORDER BY date) AS running_total
FROM transactions;
-- LAG / LEAD — previous / next row value
SELECT date, revenue,
LAG(revenue, 1) OVER (ORDER BY date) AS prev_day
FROM daily_sales;
-- RANK vs DENSE_RANK: RANK skips (1,2,2,4); DENSE_RANK doesn't (1,2,2,3)
ACID Properties
| Letter | Property | Guarantee |
|---|
| A | Atomicity | All operations succeed or all are rolled back |
| C | Consistency | Transaction takes DB from one valid state to another |
| I | Isolation | Concurrent transactions don't see each other's partial work |
| D | Durability | Committed data persists even after crash |
BEGIN;
UPDATE accounts SET balance = balance - 500 WHERE id = 1;
UPDATE accounts SET balance = balance + 500 WHERE id = 2;
COMMIT; -- both succeed together, or ROLLBACK reverts both
Isolation Levels
| Level | Dirty Read | Non-repeatable | Phantom |
|---|
| Read Uncommitted | Yes | Yes | Yes |
| Read Committed | No | Yes | Yes |
| Repeatable Read | No | No | Yes |
| Serializable | No | No | No |
Normalization Forms
| Form | Rule |
|---|
| 1NF | Atomic values, no repeating groups, unique rows |
| 2NF | 1NF + no partial dependencies (every non-key column depends on the whole PK) |
| 3NF | 2NF + no transitive dependencies (non-key column can't depend on another non-key column) |
| BCNF | 3NF + every determinant is a candidate key |
Indexing Rules
CREATE INDEX idx_email ON users(email);
CREATE INDEX idx_user_date ON orders(user_id, created_at); -- composite
-- Composite index: leftmost prefix rule
-- (a, b, c) helps: WHERE a=? | WHERE a=? AND b=?
-- (a, b, c) won't help: WHERE b=? alone
-- Index on: WHERE columns, JOIN columns, ORDER BY columns
-- Don't index: low-cardinality columns (boolean), rarely-queried columns
-- Cost: faster reads, slower writes (index maintained on every insert/update)
-- B-tree (default): good for = and range queries
-- Hash index: O(1) equality only, useless for BETWEEN/ORDER BY
EXPLAIN ANALYZE SELECT * FROM orders WHERE user_id = 42; -- check plan
NoSQL Types
| Type | Examples | Best for |
|---|
| Document | MongoDB, Firestore | Flexible schema, nested/hierarchical data |
| Key-Value | Redis, DynamoDB | Caching, sessions, rate limiting, leaderboards |
| Column-Family | Cassandra, HBase | Write-heavy, time-series, wide column tables |
| Graph | Neo4j, Neptune | Social graphs, recommendations, fraud detection |
Key Rules
- Use LEFT JOIN to find rows with no match (WHERE right_table.id IS NULL).
- 3NF is sufficient for most OLTP systems; denormalize for read-heavy analytics.
- Default isolation in PostgreSQL = Read Committed; in MySQL InnoDB = Repeatable Read.
- Composite index: always filter by the leftmost column first, or the index won't be used.
- NoSQL doesn't replace SQL — pick based on data model, access pattern, and consistency needs.