🧬SQL introduction
How to use this fundamentals track
Later lessons assume you already picture where data lives (instance → database → schema → table), what a row means (tuple grain + keys), and why clause order in your head differs from the order you type SELECT. Read these fundamentals once, then come back when joins or aggregates feel fuzzy, gaps usually trace to this layer.
Why the examples look like a lab stack
Studies, samples, assays, and plates are stand-ins for any domain: the SQL is the same as e-commerce or ledgers. Keeping a coherent mini-schema makes copy-paste exercises in a scratch database feel grounded without importing your real PHI.
What SQL actually is
SQL (Structured Query Language) is the lingua franca of relational databases. You declare sets of rows you want, filters, joins, groups, and the engine chooses indexes, join algorithms, and parallelism. That separation (your intent vs. physical plan) is why the same query can be fast or slow after a statistics refresh or a schema tweak.
🏗️The 4 Layers of a Database Engine
SQL is just the interface; these layers do the heavy lifting.
| Layer | Mechanical Responsibility | Engineering Goal |
|---|---|---|
| 1. Storage Layer | Physical 8KB pages on SSD; WAL logs. | Durability and minimal random I/O. |
| 2. Buffer Pool | RAM cache of data pages. | Minimize Disk I/O (Cache Hit Ratio > 99%). |
| 3. Execution Engine | Operators (Hash Join, Seq Scan, Sort). | CPU and Memory efficiency. |
| 4. Optimizer (CBO) | Statistics + Cost Modeling. | Picking the 'Search Path' with lowest cost. |
The Declarative Contract
In SQL, you define the WHAT (I want the top 10 users by spend), and the engine defines the HOW (Scan the user index, join the orders table, sort in memory). This track teaches you to think like the Engine so you can write SQL that respects the physical reality of hardware.
📚What you can do with SQL
Four families of statements you will mix daily.
| Category | Typical statements | Purpose |
|---|---|---|
| Query (DQL) | SELECT … | Project, filter, join, aggregate, sort, build derived relations. |
| Change data (DML) | INSERT, UPDATE, DELETE | Mutate rows under transaction boundaries and constraints. |
| Define structure (DDL) | CREATE, ALTER, DROP | Tables, indexes, views, partitions, shape how the engine stores truth. |
| Control (DCL / TCL) | GRANT, REVOKE, BEGIN, COMMIT | Permissions and atomic units of work, partners to ACID. |
🌍ANSI SQL vs database dialects
Portable mental models first; then learn the 10% of syntax that pays rent on your chosen engine.
| Feature | PostgreSQL | MySQL | SQL Server |
|---|---|---|---|
| Limit first N rows | LIMIT n OFFSET m | LIMIT n OFFSET m | TOP n / OFFSET-FETCH |
| Boolean type | native BOOLEAN | TINYINT(1) convention | BIT / CHECK patterns |
| String concat | || or CONCAT | CONCAT() | CONCAT / + (careful) |
| Upsert idiom | ON CONFLICT | ON DUPLICATE KEY | MERGE |
Interview tip
When a question is silent on dialect, state assumptions aloud: “I will use PostgreSQL-style LIMIT and RETURNING; on SQL Server I would use OFFSET-FETCH and OUTPUT.”
Your first readable query
SELECT product_id, name, unit_price_cents
FROM products
WHERE is_active = TRUE
ORDER BY name;| product_id | name | unit_price_cents |
|---|---|---|
| 101 | Alginate scaffold | 4999 |
| 102 | CRISPR kit | 12900 |
| 103 | Pipette tips (rack) | 899 |
🧠Mental model: relations all the way down
Every SELECT is a new table, compose fearlessly.
Spreadsheet thinking
Expecting stable row order without ORDER BY, or treating NULL like zero in business rules. Both create flaky tests and silent analytics bugs.
Relational thinking
Think in relations (tables), tuples (rows), and attributes (columns). Nest queries, pipe into CTEs, feed subqueries into INSERT: it is all algebra with great ergonomics.
🧰What to learn next (concrete path)
Skim once, then revisit each topic when real queries break, depth beats passive reading.
Core query flow
SELECT basics → WHERE / NULL → joins → aggregates (GROUP BY / HAVING) → subqueries / CTEs → windows.Integrity & safety
Before you call a query “done”
- Row grain is explicit (one row = one business fact).
- ORDER BY is deterministic when order matters (tie-break with PK).
- Join keys are indexed; outer join predicates are in ON vs WHERE intentionally.
- Aggregates match the grain you intend (no silent fan-out from joins).
First-week FAQ
Questions that unblock beginners before they fight the optimizer.
Why does my query return no rows when I “know” the data exists?
Most often: NULL comparisons (WHERE col = NULL is always UNKNOWN), wrong join type (INNER vs OUTER), or filters that accidentally null out outer-join rows in WHERE. Test with a tiny repro table and SELECT * before adding complexity.
Do I need to memorize vendor syntax?
Memorize the relational pipeline and portability patterns; keep a cheat sheet for limits, booleans, and upsert idioms per engine you ship on. Interview answers should state assumptions aloud.
When should I normalize vs denormalize?
Normalize OLTP for write integrity and small updates; denormalize selectively for read-heavy dashboards or warehouses, often via materialized views or ETL, not by copying columns “just in case” without a query that proves the win.
TL;DR
SQL is the declarative language of relational databases: you describe sets of rows with SELECT/WHERE/JOIN/GROUP BY, while the engine chooses plans. Dialects differ (limits, types, functions), but the relational model, keys, constraints, transactions, is portable. Learn clause order logically (filters before aggregates) and treat SQL as part of your system's reliability story, not a string templating afterthought.
🛰️Operational checklist
What teams review after the syntax is correct, same rhythm as system design reviews.
Observability
Safety
Concurrency
Scale path
📐How engines reuse this idea
Different lesson topics map to the same physical story.
| Layer | You author… | Engine decides… |
|---|---|---|
| Parser / binder | Identifiers, aliases, view expansion | Reserved words, name resolution, object privileges |
| Optimizer | Predicates, joins, aggregates | Join order, access paths, memory grants, parallelism |
| Executor | Result shape | Spills to disk, batch sizes, prefetch, vectorization |
| Storage | Keys & types | Page layout, WAL, checkpoints, compression |
Engine & operations FAQ
Quick answers teams rehearse before incidents and design reviews.
Why did my query get slower after a “harmless” migration?
Statistics drift, new indexes changing join order, implicit casts from type widening, or hotter lock contention. Compare EXPLAIN (ANALYZE) before/after with the same parameters and row counts.
When is a read replica stale enough to matter?
Async replication lag is normal. If users read their own writes, route those reads to the primary or use session stickiness / causal read APIs where the product promises freshness.
Should I fix cardinality with DISTINCT?
Rarely. DISTINCT removes duplicate rows after the fact; wrong join grain usually means a missing bridge table, wrong key, or predicate in WHERE instead of ON on an outer join. Fix the graph first.
Pre-deploy SQL review
- Parameterized queries (no string-built predicates with user input).
- Indexes match WHERE/JOIN/ORDER BY leading columns; no redundant overlapping indexes.
- Migrations: backfill + NOT NULL/VALIDATE plan for large tables; lock expectations documented.
- EXPLAIN on worst-case parameters, not only literals used in dev.
- Rollback tested: down migration or feature flag path.
Interview / design prompt
When discussing any SQL feature, close the loop: correctness (constraints + transactions), performance (indexes + plans + data volume), and operability (rollbacks, monitoring, blast radius). That triad matches how senior engineers talk about databases in system design.