SQL — Cradle to Mastery

SQL traces back to a 1970 paper by Edgar Codd proposing the relational model, and to IBM's System R project that turned it into a working query language soon after. Unlike every other language in this course, SQL is declarative: you describe the result you want, not the steps to compute it, and the database engine decides how.

There is no single “SQL runtime” to install the way there is a Python or Go compiler. You pick a database engine (this course uses PostgreSQL for examples) and a client to talk to it. This lesson gets a real database running locally so every later lesson can be typed and run, not just read.

Every query starts by naming which columns you want and which table they come from. This sounds trivial, but the exact order SQL evaluates a query in (FROM before SELECT, conceptually) explains several beginner surprises you will hit later, like why you cannot reference a computed column alias everywhere you might expect to.

WHERE is the workhorse of nearly every query: it decides which rows survive before anything else happens to them. This lesson covers comparison operators, combining conditions with AND/OR, ranges with BETWEEN, and pattern matching with LIKE.

Without ORDER BY, a database makes no promise about what order rows come back in at all — an easy beginner assumption to get wrong. LIMIT (and its engine-specific cousins) then caps the result to a manageable size, essential for pagination.

NULL represents the absence of a value, and SQL's three-valued logic (true, false, unknown) trips up nearly everyone once: `WHERE column = NULL` never matches anything, even a row where the column really is NULL. You need `IS NULL` instead.

Unlike a programming language with one type system, SQL's types genuinely differ between PostgreSQL, MySQL, and SQLite — SQLite in particular uses “type affinity” rather than strict typing, which is the strangest case among the three. Knowing your engine's actual behavior matters here more than almost anywhere else in SQL.

This lesson is the synthesis for this stage: seeing SELECT, WHERE, ORDER BY, CASE expressions, a first look at subqueries, and NULL-aware logic combined into the kind of query you would actually write against a real table, rather than each clause in isolation.

CREATE TABLE is where a schema becomes real: every column's name, type, and rules are declared before a single row exists. ALTER TABLE lets you change it afterward, but on a table with real data and real traffic, that has to be done carefully, not casually.

A constraint is a rule the database itself enforces, rejecting any row that would break it, rather than trusting application code to always get validation right. This is one of SQL's biggest structural advantages over storing data as plain files or documents.

A primary key is a promise: this column (or combination of columns) is unique for every row, forever. A foreign key is a different promise: this value must exist as a primary key somewhere else, which is exactly what stops you from ever having an order that points at a customer who does not exist.

Normalization is a series of “normal forms,” each one eliminating a specific kind of redundancy that would otherwise let the same fact get stored in two places and quietly disagree with itself. You do not need to memorize the formal definitions to internalize the actual habit: each fact lives in exactly one place.

This lesson is the synthesis for this stage: taking normalization, primary/foreign keys, and a first look at indexing and actually designing a schema for a realistic small system, before any application code exists to use it.

An INNER JOIN only returns rows that match on both sides — which quietly drops data if that is not what you meant. An OUTER JOIN is how you deliberately keep the customers with zero orders, or the products that have never sold, showing NULLs for the side with no match.

GROUP BY collapses many rows into one per group, and aggregate functions like COUNT and SUM summarize each group. The classic confusion is WHERE vs HAVING: WHERE runs before grouping and cannot reference an aggregate, HAVING runs after and can.

A subquery is a complete SELECT living inside a larger query, and it comes in three flavors that behave quite differently: a scalar subquery in WHERE, a derived table in FROM, and a correlated subquery that re-runs once per outer row, which is easy to write by accident and expensive to run.

A CTE, written with `WITH`, names a subquery so you can reference it by a readable name instead of nesting parentheses several layers deep. A recursive CTE goes further, referencing itself to walk a tree or graph structure, like an employee-to-manager hierarchy.

A window function computes something across a set of related rows — a running total, a rank, the previous row's value — without collapsing those rows into one, the way GROUP BY does. This is the feature that finally makes “rank each product within its category” a single clause instead of a self-join.

These three operators treat query results the way you would treat mathematical sets: UNION combines them, INTERSECT keeps only what is in both, and EXCEPT keeps what is in the first but not the second. Each requires both queries to return the same number of compatible columns.

This lesson is the synthesis for this stage: combining joins across several tables with aggregation, a subquery, and a window function in the kind of query a real report or dashboard would actually need — seeing how the pieces you just learned individually compose in practice.

A transaction groups multiple statements so that either all of them take effect or none do — transferring money between two accounts is the classic example, since a crash between the debit and the credit must not leave the money in neither account or both.

ACID names the four properties a transactional database promises: Atomicity (all-or-nothing), Consistency (never leaving the data in an invalid state), Isolation (concurrent transactions do not corrupt each other), and Durability (once committed, it survives a crash).

An index lets the database find matching rows without scanning the whole table, the same way a book's index beats reading every page. But every index also has to be updated on every INSERT/UPDATE/DELETE, so adding indexes freely is a real tradeoff, not a pure win.

EXPLAIN (and EXPLAIN ANALYZE) shows you the actual plan the database chose: which indexes it used, in what order it joined tables, and how many rows it expects at each step. Reading one is how you stop guessing why a query is slow and start knowing.

Most real-world slow queries share a small set of causes: a missing index, a function applied to a column that blocks index use, a correlated subquery that should have been a join, or a SELECT * pulling far more than is needed. This lesson is a checklist built from those recurring causes.

A view wraps a query behind a name you can SELECT from like a table, which is useful for hiding complexity or restricting which columns a user can see. A regular view re-runs its query every time; a materialized view caches the result and has to be refreshed on purpose.

A stored procedure keeps logic next to the data it operates on, callable from any client without duplicating the logic in every application. This is also the topic where engines diverge the most — PostgreSQL's PL/pgSQL, MySQL's syntax, and SQL Server's T-SQL are not interchangeable.

A trigger fires automatically in response to a data change, useful for enforcing rules an ordinary constraint cannot express or for keeping an audit log. The risk is real too: logic hidden inside a trigger is easy to forget exists, and can surprise anyone debugging unexpected side effects later.

SQL injection happens when untrusted input gets concatenated directly into a query string, letting an attacker change what the query actually does. The fix is not more careful string escaping — it is never building a query by concatenation in the first place: parameterized queries separate code from data entirely.

This lesson is the course's final synthesis: recursive CTEs, schema changes with ALTER, a transaction wrapping several statements, reading its EXPLAIN ANALYZE output, and a look at PostgreSQL's JSONB type for when a column needs to hold semi-structured data — the advanced end of everything this course has covered, working together.