# Databases & Storage Reliability — Part 4: SQL Language Fundamentals > **Series:** Databases & Storage Reliability (4 of 8) > **Part 1:** `01-replication-and-failover.md` — Replication & Failover > **Part 2:** `02-sharding-and-partitioning.md` — Sharding & Partitioning > **Part 3:** `03-backup-recovery-and-durability.md` — Backup, Recovery & Durability > **Part 4:** This file — SQL Language Fundamentals > **Part 5:** `05-nosql-database-types.md` — NoSQL Database Types > **Part 6:** `06-mysql-in-depth.md` — MySQL In Depth > **Part 7:** `07-dynamodb-in-depth.md` — DynamoDB In Depth > **Part 8:** `08-mongodb-in-depth.md` — MongoDB In Depth > **Questions:** `questions.md` ## Table of Contents 1. [Why an SRE Needs to Read SQL, Even Without Writing Much of It](#why-an-sre-needs-to-read-sql-even-without-writing-much-of-it) 2. [The Relational Model, in Plain English](#the-relational-model-in-plain-english) 3. [SELECT — Reading Data](#select--reading-data) 4. [Filtering, Sorting, and Limiting](#filtering-sorting-and-limiting) 5. [INSERT, UPDATE, DELETE — Changing Data](#insert-update-delete--changing-data) 6. [JOINs — Combining Data From Multiple Tables](#joins--combining-data-from-multiple-tables) 7. [The Four JOIN Types, Visually](#the-four-join-types-visually) 8. [Aggregation — GROUP BY and Aggregate Functions](#aggregation--group-by-and-aggregate-functions) 9. [Subqueries and CTEs](#subqueries-and-ctes) 10. [Normalization — Why Tables Are Split Up At All](#normalization--why-tables-are-split-up-at-all) 11. [Indexes — Why Some Queries Are Fast and Others Are Slow](#indexes--why-some-queries-are-fast-and-others-are-slow) 12. [Reading an EXPLAIN Plan](#reading-an-explain-plan) 13. [Transactions in Practice](#transactions-in-practice) 14. [Common SQL Mistakes That Cause Real Production Incidents](#common-sql-mistakes-that-cause-real-production-incidents) 15. [Common Mistakes](#common-mistakes) 16. [Worked Practice Problems](#worked-practice-problems) 17. [Summary and What's Next](#summary-and-whats-next) --- ## Why an SRE Needs to Read SQL, Even Without Writing Much of It Parts 1-3 of this series covered how relational databases replicate, shard, and recover — all of it assumed familiarity with the language actually used to interact with the data itself: **SQL (Structured Query Language)**. An SRE rarely writes complex application queries, but reading and reasoning about SQL is a genuinely everyday skill — diagnosing a slow query during an incident, reviewing a risky migration before it ships, or understanding why a specific query is locking a table all require real SQL fluency, not just conceptual database knowledge. --- ## The Relational Model, in Plain English A relational database organizes data into **tables** (rows and columns), where each table represents one kind of "thing," and relationships between things are expressed by referencing one table's row from another. ```mermaid graph TD Users["users TABLE
id, name, email"] -->|"orders.user_id
REFERENCES users.id"| Orders["orders TABLE
id, user_id, total, created_at"] ``` **Simple analogy:** think of a table as a spreadsheet tab — `users` is one tab, `orders` is another. Each row is one specific record (one specific user, one specific order). A **foreign key** (`orders.user_id`) is simply a column in one tab that points to a specific row's ID in another tab — exactly like a cell that says "see row 42 on the Users tab" instead of copying that user's entire information into every single order row. --- ## SELECT — Reading Data The single most common SQL statement — retrieving data from a table. ```sql -- Get every column, every row (rarely a good idea on a big table) SELECT * FROM users; -- Get specific columns only SELECT id, name, email FROM users; ``` **A genuinely important, practical habit worth stating explicitly: avoid `SELECT *` in real application code and especially in production migrations.** It retrieves every column whether needed or not (wasted bandwidth/memory), and — more dangerously — if someone later adds a new column to the table, `SELECT *`-based code can silently start behaving differently without any code change at all, since it's now pulling back data it never explicitly asked for. --- ## Filtering, Sorting, and Limiting ```sql -- WHERE filters which ROWS are returned SELECT name, email FROM users WHERE created_at > '2026-01-01'; -- ORDER BY controls the RESULT order SELECT name FROM users ORDER BY created_at DESC; -- LIMIT caps how many rows come back SELECT name FROM users ORDER BY created_at DESC LIMIT 10; ``` ```mermaid graph LR Table["Full table:
1,000,000 rows"] --> Where["WHERE filters down
to matching rows"] Where --> Order["ORDER BY sorts
the matches"] Order --> Limit["LIMIT caps the
final result count"] ``` **A genuinely important, real-world operational habit worth stating explicitly, tying directly to production safety: always use `LIMIT` when exploring data in a production database, and always double- and triple-check a `WHERE` clause exists before running any `UPDATE` or `DELETE`** — this connects directly to the DevSecOps series' pipeline-safety discussion and the "unscoped DELETE" disaster scenario from Part 3 of this very series. --- ## INSERT, UPDATE, DELETE — Changing Data ```sql -- Add a new row INSERT INTO users (name, email) VALUES ('Alice', 'alice@example.com'); -- Modify existing rows — ALWAYS scope with WHERE UPDATE users SET email = 'newemail@example.com' WHERE id = 42; -- Remove rows — ALWAYS scope with WHERE DELETE FROM users WHERE id = 42; ``` ```mermaid graph TD Danger["UPDATE or DELETE with NO
WHERE clause"] --> DangerNote["🚨 Applies to EVERY SINGLE
ROW in the table — exactly
the mistake that caused the
full disaster recovery
scenario walked through in
Part 3 of this series"] ``` **A concrete, practical safety habit worth citing explicitly: before running any real `UPDATE`/`DELETE` in production, first run the equivalent `SELECT` with the exact same `WHERE` clause to confirm precisely which rows would actually be affected** — catching a wrong or missing filter before it does real, permanent damage, rather than after. --- ## JOINs — Combining Data From Multiple Tables A **JOIN** combines rows from two (or more) tables based on a related column — this is the entire point of splitting data into separate tables in the first place (covered fully in the Normalization section below). ```sql SELECT users.name, orders.total FROM users JOIN orders ON users.id = orders.user_id; ``` **In plain English:** "for every order, look up the matching user (using the `user_id` reference) and show me both the user's name and that order's total, side by side." --- ## The Four JOIN Types, Visually This is one of the single most commonly asked SQL interview topics — genuinely worth being able to draw and explain each one from memory. ```mermaid graph TD Inner["INNER JOIN:
only rows that MATCH in
BOTH tables"] --> InnerNote["A user with ZERO orders
is EXCLUDED entirely.
An order with no matching
user (shouldn't happen
with good data, but could
with a broken foreign key)
is ALSO excluded"] Left["LEFT JOIN:
ALL rows from the LEFT
table, matched data from
the right where it exists
(NULL where it doesn't)"] --> LeftNote["EVERY user is included,
even ones with ZERO
orders — their order
columns just show NULL"] Right["RIGHT JOIN:
the MIRROR of LEFT JOIN —
ALL rows from the RIGHT
table"] --> RightNote["Rarely used in practice —
almost always rewritten as
a LEFT JOIN with the table
order swapped instead,
for readability"] Full["FULL OUTER JOIN:
ALL rows from BOTH tables,
matched where possible,
NULL on whichever side
doesn't have a match"] --> FullNote["Rarer still — useful for
finding MISMATCHES between
two tables (e.g. auditing
data integrity)"] ``` **A worked, concrete example showing exactly why the choice matters, worth being able to reproduce:** "Show me every user, and how many orders they've placed, including users who've never ordered anything." ```sql -- INNER JOIN would be WRONG here — it silently EXCLUDES -- users with zero orders, undercounting your actual user base SELECT users.name, COUNT(orders.id) AS order_count FROM users INNER JOIN orders ON users.id = orders.user_id GROUP BY users.name; -- Users with NO orders simply don't appear in the results at all! -- LEFT JOIN is CORRECT here — every user appears, with -- order_count = 0 for anyone who's never ordered SELECT users.name, COUNT(orders.id) AS order_count FROM users LEFT JOIN orders ON users.id = orders.user_id GROUP BY users.name; ``` **Why this specific example is such a strong interview answer: it demonstrates that choosing the wrong JOIN type doesn't cause an error — it causes a silently, subtly WRONG result** (undercounting the user base by excluding non-ordering users entirely), which is exactly the kind of bug that's hard to catch in casual testing and can quietly corrupt business-critical reports or dashboards for a long time before anyone notices. --- ## Aggregation — GROUP BY and Aggregate Functions ```sql -- Count orders PER user SELECT user_id, COUNT(*) AS order_count FROM orders GROUP BY user_id; -- Common aggregate functions SELECT COUNT(*) AS total_orders, SUM(total) AS revenue, AVG(total) AS avg_order_value, MAX(total) AS biggest_order, MIN(total) AS smallest_order FROM orders; ``` **A genuinely common, important gotcha worth naming explicitly: `WHERE` filters rows BEFORE grouping; `HAVING` filters groups AFTER aggregation** — you cannot use an aggregate function like `COUNT(*)` inside a `WHERE` clause, because `WHERE` runs before the aggregation even happens. ```sql -- Find users with MORE THAN 5 orders — needs HAVING, not WHERE, -- because it's filtering on an AGGREGATED value SELECT user_id, COUNT(*) AS order_count FROM orders GROUP BY user_id HAVING COUNT(*) > 5; ``` --- ## Subqueries and CTEs A **subquery** is a query nested inside another query; a **CTE (Common Table Expression)** is a named, reusable subquery defined up front with `WITH`, usually far more readable for anything non-trivial. ```sql -- Subquery: find users who have placed at least one order over $1000 SELECT name FROM users WHERE id IN (SELECT user_id FROM orders WHERE total > 1000); -- The SAME logic, as a CTE — genuinely easier to read for -- anything with multiple steps WITH big_spenders AS ( SELECT user_id FROM orders WHERE total > 1000 ) SELECT name FROM users WHERE id IN (SELECT user_id FROM big_spenders); ``` **Why CTEs are worth knowing specifically, beyond just "another way to write a subquery": they let a complex query be broken into clearly-named, logical steps, read top to bottom, exactly like naming intermediate variables in application code instead of writing one giant, deeply nested expression** — genuinely valuable both for humans reading the query later and for a database's own query planner, which can sometimes optimize a CTE more effectively than an equivalent deeply-nested subquery. --- ## Normalization — Why Tables Are Split Up At All This directly connects to *why* JOINs are even necessary — worth understanding the underlying design principle, not just the mechanics of writing queries. ```mermaid graph TD Bad["UN-normalized: one giant
'orders' table with the
user's NAME and EMAIL
copied into EVERY SINGLE
order row"] --> BadProb["❌ If a user changes their
email, EVERY past order row
needs updating too — real
risk of INCONSISTENT data
(some rows updated, some
not) and wasted storage
from massive duplication"] Good["NORMALIZED: user info
lives ONCE, in the 'users'
table. Orders reference it
via user_id"] --> GoodNote["✅ A user's email is
stored in exactly ONE
place — update it once,
every order automatically
reflects the current value
via the JOIN"] ``` **The formal levels worth knowing by name, at a conceptual level (deep, rigorous coverage isn't necessary for most SRE interviews, but recognizing the names and the core idea is):** **1NF** (each column holds a single, atomic value — no lists crammed into one field), **2NF** (every non-key column depends on the *whole* primary key, not just part of it), **3NF** (every non-key column depends *only* on the primary key, not on another non-key column) — each level is about progressively eliminating a specific category of data duplication and the inconsistency risk that comes with it. **A genuinely important, balanced point worth stating explicitly: normalization isn't free — it means more JOINs to reconstruct a full picture, which costs real query performance.** This is precisely why **denormalization** (deliberately duplicating some data back in, for read performance) is a real, common, deliberate tradeoff in high-read-volume systems — directly connecting to the caching discussion from the Capacity Planning & Performance series: sometimes the fastest "join" is simply not needing to do one at all, because the data was already duplicated where it's read. --- ## Indexes — Why Some Queries Are Fast and Others Are Slow Already touched on conceptually elsewhere in this course — here's the concrete, SQL-level mechanics. ```mermaid graph TD NoIndex["NO index on a column"] --> TableScan["A query filtering on that
column must check EVERY
SINGLE ROW, one by one
('full table scan') —
SLOW on a large table"] Index["An INDEX on a column"] --> IndexScan["The database maintains a
SEPARATE, SORTED lookup
structure (commonly a
B-TREE) — finding matching
rows becomes dramatically
faster, similar to using a
book's INDEX instead of
reading every single page"] ``` ```sql -- Create an index on a frequently-queried column CREATE INDEX idx_orders_user_id ON orders (user_id); -- A composite (multi-column) index — order of columns matters! CREATE INDEX idx_orders_user_date ON orders (user_id, created_at); ``` **Why indexes aren't a free "add it everywhere and everything gets faster" win, worth stating explicitly, and a genuinely common interview follow-up:** every index has to be updated on every `INSERT`/`UPDATE`/`DELETE` to the table, meaning **more indexes make writes slower**, and every index also consumes real disk space. **The real, practical skill is choosing indexes deliberately, based on the actual, real query patterns the application runs most often** — not indexing every column defensively. **Why column order in a composite index matters, a genuinely sharp, commonly-tested detail:** an index on `(user_id, created_at)` efficiently supports queries filtering by `user_id` alone, or by `user_id` AND `created_at` together — but it does **NOT** efficiently support a query filtering by `created_at` alone, since the index is fundamentally structured (sorted) by `user_id` first. This is exactly analogous to a phone book sorted by last name, then first name — you can efficiently find "all the Smiths" or "John Smith specifically," but you can't efficiently find "everyone named John" without scanning the whole book, since the book isn't sorted by first name at the top level. --- ## Reading an EXPLAIN Plan **`EXPLAIN`** shows exactly *how* the database intends to actually execute a given query — the single most important diagnostic tool for understanding why a specific query is slow. ```sql EXPLAIN SELECT * FROM orders WHERE user_id = 42; ``` ``` Seq Scan on orders (cost=0.00..18584.00 rows=1 width=64) Filter: (user_id = 42) ``` ```sql -- After adding the index from earlier: EXPLAIN SELECT * FROM orders WHERE user_id = 42; ``` ``` Index Scan using idx_orders_user_id on orders (cost=0.42..8.44 rows=1 width=64) Index Cond: (user_id = 42) ``` **Why the difference between "Seq Scan" and "Index Scan" is such a genuinely important, concrete thing to recognize, worth stating explicitly: `Seq Scan` (sequential/full table scan) means the database is checking every single row — exactly the slow path an index exists to avoid. `Index Scan` means it's using the sorted index structure to jump directly to matching rows.** Seeing an unexpected `Seq Scan` on a large table, where an `Index Scan` was expected, is one of the single most common, most actionable findings when diagnosing a slow production query during a real incident. --- ## Transactions in Practice Directly connecting to the ACID discussion from Part 3 of this series — here's the concrete SQL syntax. ```sql BEGIN; UPDATE accounts SET balance = balance - 100 WHERE id = 1; UPDATE accounts SET balance = balance + 100 WHERE id = 2; COMMIT; -- If anything went wrong instead: ROLLBACK; ``` ```mermaid sequenceDiagram participant App participant DB as Database App->>DB: BEGIN App->>DB: UPDATE accounts SET
balance = balance - 100
WHERE id = 1 App->>DB: UPDATE accounts SET
balance = balance + 100
WHERE id = 2 alt Both succeed App->>DB: COMMIT - BOTH changes
become permanent TOGETHER else Anything fails App->>DB: ROLLBACK - NEITHER
change is applied at all end ``` **Why wrapping both updates in a single transaction is essential here, directly reusing Atomicity from Part 3's ACID discussion: without a transaction, if the SECOND update failed after the FIRST one already succeeded, money would simply vanish — debited from account 1 but never credited to account 2. The transaction guarantees these two statements succeed or fail as one indivisible unit, exactly the Atomicity guarantee ACID promises.** --- ## Common SQL Mistakes That Cause Real Production Incidents A concrete, memorable list worth having ready — this section deliberately mirrors the "common mistakes" pattern from every other tutorial in this course, but focused specifically on genuinely common, real SQL-authoring incidents. ```mermaid graph TD Mistakes["Real-World SQL Incident
Patterns"] --> M1["Forgetting WHERE on an
UPDATE/DELETE — affects
EVERY row (Part 3's
disaster scenario)"] Mistakes --> M2["An unindexed column used
in WHERE on a huge table —
a full table scan that
locks/slows the table for
a long time"] Mistakes --> M3["A JOIN missing its ON
condition — accidentally
produces a CARTESIAN
PRODUCT (every row
matched with EVERY other
row) instead of the
intended pairing"] Mistakes --> M4["A long-running, UN-
COMMITTED transaction
holding LOCKS, blocking
other queries indefinitely"] ``` **A worked example of the "missing JOIN condition" disaster, worth being able to explain concretely:** ```sql -- MISSING the ON clause entirely (a genuine, real typo risk) SELECT users.name, orders.total FROM users, orders; -- Produces a CARTESIAN PRODUCT: EVERY user paired with EVERY -- order, regardless of whether they actually belong together. -- 1,000 users × 10,000 orders = 10,000,000 nonsense result rows! ``` **Why this is dangerous beyond just "returns wrong data": on a large enough table, a cartesian product can generate an enormous, unexpected result set that consumes massive memory/disk, potentially degrading or crashing the database entirely — a small, easy-to-make syntax mistake with an outsized, genuinely severe operational impact.** --- ## Common Mistakes | Mistake | Why It's Wrong | Fix | |---|---|---| | Using `SELECT *` in application code | Wastes bandwidth, and silently changes behavior if columns are later added to the table | Explicitly select only the columns actually needed | | Running `UPDATE`/`DELETE` without first confirming the `WHERE` clause with an equivalent `SELECT` | Risks affecting every row in the table if the filter is wrong or missing | Always test the exact same `WHERE` clause with `SELECT` first, in production especially | | Using `INNER JOIN` when rows without a match should still be included | Silently, subtly undercounts/excludes data with no error at all | Use `LEFT JOIN` when the "no match" case still needs to be represented | | Trying to filter on an aggregate with `WHERE` instead of `HAVING` | `WHERE` runs before aggregation happens — the aggregate value doesn't exist yet at that point | Use `HAVING` to filter on aggregated/grouped values | | Adding an index to every column "just in case" | Every index slows down writes and consumes real storage | Index deliberately, based on actual, observed query patterns | | Writing a multi-table query with a comma-separated `FROM` and no `ON`/`WHERE` join condition | Produces a cartesian product — every row from one table paired with every row from the other | Always use explicit `JOIN ... ON` syntax, and double-check every join has its condition | --- ## Worked Practice Problems **Problem 1:** A report is supposed to show every product and its total sales, including products that have never sold. The current query uses `INNER JOIN` between `products` and `order_items`, and management notices the report is missing several known products. What's wrong, and how do you fix it? *Answer:* `INNER JOIN` only returns rows that match in BOTH tables — a product with zero rows in `order_items` (never sold) has nothing to match, so it's silently excluded from the results entirely, exactly explaining the missing products. The fix: use `LEFT JOIN` from `products` to `order_items` instead, so every product appears regardless of whether it has any matching sales, with `SUM(order_items.total)` naturally coming out as 0 (or NULL, depending on how it's aggregated) for products with no sales. **Problem 2:** A query filtering `WHERE created_at = '2026-06-01'` on a 50-million-row table is taking 30 seconds, and `EXPLAIN` shows `Seq Scan`. There's already an index on `(user_id, created_at)`. Why isn't the index helping, and what would you recommend? *Answer:* The existing composite index is structured (sorted) by `user_id` FIRST, then `created_at` — it can efficiently support queries filtering by `user_id` alone or by `user_id` AND `created_at` together, but it can't efficiently support a query filtering by `created_at` alone, since the index isn't sorted by that column at the top level (exactly like a phone book sorted by last name can't efficiently answer "everyone with a given first name"). The fix, assuming this specific access pattern (filtering purely by date) is common enough to justify it: create a separate, dedicated index specifically on `created_at` alone (or reconsider whether this query pattern is common enough to warrant one, weighed against the write-performance cost of an additional index). **Problem 3:** During an incident, a query is found to be running for over 10 minutes, and other queries against the same table are timing out. Investigation shows the slow query is a multi-table `SELECT` using old-style comma-separated `FROM users, orders, products` syntax with a `WHERE` clause that appears incomplete. What's the likely root cause, and why is it also causing OTHER queries to fail? *Answer:* This has the hallmark signature of a cartesian product — the incomplete `WHERE` clause likely isn't actually joining the tables correctly (missing the equivalent of an `ON` condition matching foreign keys), causing every row in one table to be paired with every row in the others, producing a massive, unintended result set. This explains both the extreme runtime (processing millions of nonsense combined rows instead of the intended, much smaller matched set) and why other queries are timing out — the runaway query is likely consuming enormous memory/disk I/O and potentially holding locks on the involved tables for its entire, abnormally long execution, starving other legitimate queries of the same resources. --- ## Summary and What's Next - SQL organizes data into **tables**, with relationships expressed via **foreign keys** referencing another table's rows — **JOINs** reconstruct the full picture across related tables. - The four JOIN types — **INNER** (only matches), **LEFT** (all of the left table, matched or not), **RIGHT** (mirror of LEFT), **FULL OUTER** (all of both) — pick the wrong one and you get a silently, subtly WRONG result, not an error. - **`WHERE`** filters rows before aggregation; **`HAVING`** filters groups after aggregation — you can't use an aggregate function inside `WHERE`. - **Normalization** eliminates data duplication and inconsistency risk by storing each fact in exactly one place — at the real cost of needing more JOINs, which is exactly why deliberate **denormalization** is a legitimate, common performance tradeoff. - **Indexes** turn a slow, full **table scan** into a fast, targeted **index scan** — but every index costs write performance and storage, so they should be added deliberately, based on real query patterns, and **column order in a composite index genuinely matters.** - **`EXPLAIN`** is the single most important diagnostic tool for understanding why a query is slow — spotting an unexpected `Seq Scan` on a large table is one of the most common, actionable findings during a real production slow-query incident. - **Transactions** (`BEGIN`/`COMMIT`/`ROLLBACK`) provide the Atomicity guarantee from ACID (Part 3) — multiple statements succeed or fail together, as one indivisible unit. - Real, common SQL-authoring mistakes — an unscoped `DELETE`, a missing `JOIN` condition producing a cartesian product, an unindexed column on a huge table — are genuine, recurring causes of real production incidents, not just academic concerns. **Continue to Part 5** (`05-nosql-database-types.md`) to see how NoSQL databases deliberately trade away some of the relational model's structure (and SQL itself) for different scalability and flexibility tradeoffs.