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 database serves many clients at once. Two customers buy the last item in stock. A report runs while an order is being processed. An analyst sums account balances while a transfer moves money between them. These concurrent operations are the normal state of any production database, and without careful control they produce results that…
A table without an index is a book without a table of contents. Every query that needs to find a specific row must read the entire table from the first row to the last. For a table with a thousand rows, that is unnoticeable. For a table with a hundred million rows, that is a…
An index is only as good as the data structure underneath it. The previous chapter established why indexes exist and how selectivity drives the optimizer’s decisions. This chapter goes one level deeper and examines the three fundamental index structures that relational databases actually implement: the B-tree, the hash table, and the bitmap. Each structure makes…
A single-column index answers one question efficiently: which rows have this value? Real queries rarely ask only one question. They filter by customer and sort by date, look up a user by email and require that email to be unique, or query a queue for the oldest pending job while ignoring the millions of completed…
Indexes are created for queries, but they live with writes. Every index you add to a table becomes a permanent obligation: the database must update it on every insert, every update that touches an indexed column, and every delete. Over months and years, indexes accumulate bloat from dead entries, statistics drift out of date, and…
A SQL query is a declaration of intent. The database is free to satisfy it however it chooses: scanning a table, walking an index, joining in one order or another, sorting in memory or on disk. The execution plan is the database’s chosen strategy, and reading it is the only reliable way to understand why…
Every query that filters rows makes the same fundamental choice: read the whole table and check each row, or use an index to jump directly to the rows that match. The optimizer makes this decision on every query, and the choice determines whether the query takes milliseconds or minutes. Three access methods dominate: the table…
Normalization is the process of organizing a database’s columns and tables to reduce redundancy and eliminate the anomalies that redundancy causes. The theory is older than most of the databases that use it, but the practice is as relevant as ever: a table that stores a customer’s address on every order will eventually have two…
The first three normal forms eliminate the anomalies that appear in most schemas. Beyond 3NF, three more forms exist: Boyce-Codd Normal Form, Fourth Normal Form, and Fifth Normal Form. Each addresses a narrower class of problem. BCNF handles tables with multiple candidate keys where a non-key column determines part of a key. 4NF handles multi-valued…
Normalization is the default. It eliminates redundancy, prevents the anomalies that redundancy causes, and keeps every fact in one place. But normalization has a cost: joins. A fully normalized schema spreads a single logical entity across many tables, and every query that reads the entity pays for the joins that reconstruct it. On a read-heavy…