Offered by Dead Brands
The working mental model of relational databases: how data is structured with keys, how to query it fluently with SELECT/JOIN/GROUP BY, why indexes make reads fast (and writes slower), and how transac
Use it over MCP: connect to https://mcp.deadbrands.co/hub/mcp and call skills_get with slug databases-and-sql-basics.
# Databases and SQL Basics
Credit: SteTy — published free in the Skills Hub
The working mental model of relational databases: how data is structured with keys, how to query it fluently with SELECT/JOIN/GROUP BY, why indexes make reads fast (and writes slower), and how transactions, ACID, and isolation levels keep concurrent work from corrupting data — plus the N+1 problem and when NoSQL actually fits.
## Overview
A relational database stores data in **tables** (relations): rows are records, columns are typed attributes. The model, from Codd (1970), rests on one idea — describe data by its values and relationships, and let the engine decide how to fetch it. You write *declarative* SQL ("what I want"), the planner picks an execution plan ("how"). Almost everything practical follows from three skills: structuring tables so facts are stored once, querying across tables with joins and aggregation, and controlling what happens when many transactions touch the same rows at the same time. SQL is the default because most real data has relationships, integrity rules, and ad-hoc questions that only show up after launch.
## Core Principles
- **Store each fact once (normalization).** A customer's email lives in `customers`, not copied onto every order. Redundant copies drift apart; one copy can be constrained. Denormalize later, deliberately, for a measured read win.
- **Keys are identity and relationships.** A **primary key** uniquely identifies a row (never NULL, never reused). A **foreign key** is a column holding another table's primary key; the engine can enforce *referential integrity* — rejecting an order that points at a nonexistent customer.
- **Constraints are free correctness.** `NOT NULL`, `UNIQUE`, `CHECK`, and foreign keys make whole classes of bugs impossible at the storage layer, where no buggy code path can bypass them. An application-level check is a hope; a unique constraint is a guarantee.
- **ACID is the transaction contract.** **Atomicity**: all statements in a transaction apply or none do. **Consistency**: a transaction moves the database from one constraint-valid state to another. **Isolation**: concurrent transactions don't see each other's half-finished work (with graded levels — see Techniques). **Durability**: once committed, data survives a crash (it's in the write-ahead log on disk).
- **Reads and writes trade against each other.** Every structure that speeds a read (an index) is a structure every write must also maintain. There is no free performance, only moved cost.
## Techniques
**Core SQL fluency.** `SELECT ... FROM ... WHERE ... GROUP BY ... HAVING ... ORDER BY ... LIMIT` executes conceptually in that order: filter rows (WHERE), group them, filter groups (HAVING), then sort and cut. Joins combine tables on key equality: `INNER JOIN` keeps only matches; `LEFT JOIN` keeps all left rows, padding unmatched right columns with NULL — the tool for "orders, including ones with no shipments." Aggregation collapses groups: `COUNT`, `SUM`, `AVG`, `MIN`, `MAX`. Example: revenue per customer, biggest first:
```sql
SELECT c.name, SUM(o.total) AS revenue
FROM customers c
JOIN orders o ON o.customer_id = c.id
WHERE o.status = 'paid'
GROUP BY c.name
HAVING SUM(o.total) > 1000
ORDER BY revenue DESC;
```
**Indexes are sorted copies.** The default index is a **B-tree**: a self-balancing sorted tree giving O(log n) lookup — about 20 page reads to find one row in a million, versus scanning all million. Because it's sorted, one index serves equality lookups, ranges (`BETWEEN`, `>`), and `ORDER BY` on the same column order. Rules that matter:
- **Left-prefix rule**: a composite index on `(status, customer_id)` helps queries filtering on `status`, or `status` + `customer_id`, but *not* `customer_id` alone — like a phone book sorted by last name being useless if you only know the first name. Order columns: equality filters first, range/sort columns last.
- **Covering indexes**: adding the columns a query returns (Postgres `INCLUDE (email, role)`) lets the engine answer entirely from the index — an "Index Only Scan" — skipping the trip to the table row entirely.
- **Selectivity decides**: an index on a boolean or a 3-value status column rarely helps; if a query returns a third of the table, a sequential scan is genuinely faster, and the planner will (correctly) ignore your index.
- **Functions break plain indexes**: `WHERE LOWER(email) = ...` can't use an index on `email`; use an expression index on `LOWER(email)` or store the value normalized.
**Read the plan, don't guess.** `EXPLAIN ANALYZE` runs the query and shows the real plan with actual timings. Red flags: `Seq Scan` on a large table with many "Rows Removed by Filter" (missing index), and estimated rows wildly off from actual rows (stale statistics — run `ANALYZE`). On big production tables, build indexes without locking writes (`CREATE INDEX CONCURRENTLY` in Postgres).
**Transactions and isolation anomalies.** Wrap multi-statement changes in `BEGIN ... COMMIT`; any error, `ROLLBACK`. Isolation levels trade safety for concurrency, defined by which **anomalies** they allow:
- **Dirty read**: reading another transaction's *uncommitted* change, which may roll back — you've read data that never existed.
- **Non-repeatable read**: re-reading a row in one transaction returns a different value because someone committed in between.
- **Phantom read**: re-running a filtered query returns different *rows* (inserts/deletes), not just different values.
- **Write skew**: two transactions each read a shared invariant and update disjoint rows, jointly breaking it (two on-call doctors both see "at least one doctor on duty," both take leave). Snapshot isolation allows it; only true serializable prevents it.
| Level | Dirty | Non-rep. | Phantom | Notes |
|---|---|---|---|---|
| Read Uncommitted | possible | possible | possible | Rarely truly offered |
| Read Committed | no | possible | possible | Postgres/Oracle default; fresh snapshot per statement |
| Repeatable Read | no | no | standard allows; Postgres/InnoDB prevent | One snapshot per transaction (MVCC) |
| Serializable | no | no | no | May abort with a serialization error — **your code must retry** |
Defaults differ by engine (MySQL InnoDB defaults to Repeatable Read; Postgres to Read Committed) — never assume. Most engines implement isolation with **MVCC**: UPDATE writes a new row version instead of overwriting, readers use a snapshot, so readers don't block writers. The debris (dead versions) is reclaimed by Postgres `VACUUM` — skipped maintenance is why update-heavy tables bloat.
**The N+1 problem.** An ORM fetches 100 orders (1 query), then lazily fetches each order's customer (100 more queries). Each query is indexed and fast; the page is slow anyway. It's an application bug, not a database bug: fix by eager loading — one `JOIN`, or one `WHERE customer_id IN (...)` batch query. Detection: 101 near-identical queries in the log for one request.
**When NoSQL fits.** Document stores (MongoDB) fit self-contained aggregates with variable shape and no cross-document joins. Key-value stores fit pure lookup by ID at extreme scale. Wide-column/LSM stores (Cassandra) fit append-heavy, write-dominated workloads with known query patterns. The trade: you give up joins, ad-hoc queries, and cross-record transactions — so the moment requirements say "and also report by…", the relational model usually wins. Choose NoSQL for a *specific measured* property, not as a default.
## Worked Examples
1. **Slow page, fast queries.** An order-history page takes 1.8s. `EXPLAIN ANALYZE` on the main query shows a Seq Scan over 2M orders with "Rows Removed by Filter: 1,999,980". A composite index on `(customer_id, created_at)` turns it into an Index Scan: 1.8s → ~4ms. No new hardware, no rewrite.
2. **The index that made it slower.** An index on `orders(seller_id)` alone made a seller report *slower* (160ms → 228ms): the index found ~72k row pointers fast, then the engine made 72k scattered trips to the table to fetch the other columns — worse than one sequential pass. A covering index `INCLUDE (order_id, product_id, price)` answered from the index alone: 108ms. Lesson: an index that doesn't cover the query is a pointer-chasing tax on wide result sets.
3. **Race the constraint wins.** Two concurrent signups with the same email both `SELECT` (no row found), both `INSERT`. Without a `UNIQUE` constraint on email you get duplicates; with one, the second insert fails cleanly and you catch it. Check-then-insert in code is never a substitute for the constraint.
## Common Pitfalls
- Indexing every column "just in case" — writes slow to a crawl and the planner ignores most of them anyway.
- Trusting `EXPLAIN` estimates without `ANALYZE`; the planner is only as good as its statistics.
- `SELECT *` in production code: breaks when columns change and defeats covering indexes.
- Using Serializable everywhere (throughput collapses, and unhandled serialization aborts become 500s) — or Read Committed while assuming repeat reads are stable.
- Long-running transactions: they hold snapshots, block vacuum/cleanup, and turn into lock pile-ups.
- Modeling in NoSQL by copying a relational schema table-for-table, then reimplementing joins badly in application code.
## Practice Drills
1. Take a slow query, run `EXPLAIN ANALYZE`, and name the exact red flag (scan type, rows removed, estimate mismatch) before changing anything.
2. Given queries filtering on `customer_id` alone, `status` alone, and both, design the minimal index set using the left-prefix rule.
3. Reproduce a phantom: in two sessions at Read Committed, re-run a `COUNT(*)` range query across a concurrent insert; then repeat at Serializable and catch the serialization error.
4. Find an N+1 in an ORM query log and rewrite it as a single JOIN; count queries before and after.
## When to Use and Limits
- **Use it when:** a task calls for databases and sql basics, as laid out in the Techniques and Worked Examples above.
- **Watch first for:** (see Common Pitfalls).
- **Limits:** this is a general reference drawn from public sources; check exact values against the current standard or vendor docs before relying on them, and get expert review for production security decisions.
## Quick Reference
- Filter order: WHERE → GROUP BY → HAVING → ORDER BY → LIMIT.
- Index = sorted B-tree copy: O(log n) reads, extra cost on every write.
- Composite index: equality columns first, range last; left prefix only.
- Anomaly ladder: dirty < non-repeatable < phantom < write skew; each isolation level buys them off in order.
- N+1: batch it or join it — never loop queries per row.