Structured Query Language, the standard language for interacting with relational databases. It covers querying data with SELECT, modifying it with INSERT, UPDATE, and DELETE, defining schemas with CREATE and ALTER, and controlling access with GRANT and REVOKE. SQL is the universal interface to databases like PostgreSQL, MySQL, SQLite, and SQL Server.
A window function computes a value across a set of rows related to the current row. The OVER clause defines that set. In the simplest form, the set is the entire partition, and the function sees every row in the group. But many analytical questions require a more precise definition: “the running total so far,”…
A GROUP BY clause with a single set of columns produces one row per distinct combination of those columns. That is the standard aggregation behavior, and it answers a specific question: “what is the total for each group?” But analytical queries often need more than one answer at once. A report might need the total…
A Common Table Expression is a named temporary result set that exists for the duration of a single query. It is defined with the WITH keyword, and it can be referenced by name anywhere in the query that follows. The CTE is not a view — it is not stored in the database schema —…
A single Common Table Expression is useful. Two CTEs that reference each other are a pipeline. The first CTE produces an intermediate result, the second consumes it and produces another, and the main query consumes the second. This chaining is what turns a CTE from a readability convenience into a query-organization tool. Each CTE is…
Hierarchical data is data that references itself. An employee has a manager, who is also an employee. A category has a parent category, which is also a category. A part is made of components, which are themselves parts. A comment has a parent comment, which is also a comment. The relationship is recursive: the same…
A view is a named query that behaves like a table. It does not store data — it stores the query — and when it is referenced, the database executes the underlying query and returns the result. The view is the abstraction that hides the complexity of the joins, the aggregations, and the filtering behind…
A regular view is a stored query. Every time it is referenced, the database re-executes the query against the underlying tables. The result is always current, but the cost is paid on every read. A materialized view is a stored result. The query runs once, at the creation, and its output is written to disk…
A transaction is a group of SQL statements that execute as a single unit. Either all of the statements succeed and their changes are permanently written, or one of them fails and every change is undone as if nothing happened. There is no partial state: the database moves from one consistent state to another, and…
The previous chapter described what a transaction is and what it guarantees. This chapter is about the commands that control it: BEGIN, COMMIT, ROLLBACK, and the SAVEPOINT that sits between them. These four statements are the entire user-facing API for transactions. The BEGIN opens the transaction, the COMMIT closes it and makes the changes permanent,…
A savepoint is a named marker inside a transaction. It is set with the SAVEPOINT statement, and it records the state of the transaction at that point. When a later statement fails, the ROLLBACK TO SAVEPOINT returns the transaction to the marker, undoing the changes that came after it while keeping the changes that came…