This chapter explains the four basic database operations (CRUD), and the transactions and ACID properties (atomicity, consistency, isolation, durability) that handle multiple operations as a unit, using examples like bank transfers.
This chapter explains, for junior engineers, the basic concept of a relational database (RDB), the benefits and trade-offs of managing data as "multiple related tables" instead of "one giant table," and how to choose between RDB and NoSQL.
This chapter explains, for junior engineers, the role of a DBMS (SQL parsing, transaction management, permissions, optimization), the four categories of SQL (DDL/DML/DCL/TCL), and the characteristics and trade-offs of major RDBMS products (PostgreSQL / MySQL / SQLite / Oracle / SQL Server).
This chapter explains how indexes speed up lookups and the cost they impose on writes. It covers column order in composite indexes, patterns that stop indexes from being used, and how they differ from sharding and hashing.
This chapter covers reading and writing queries that join and aggregate across tables. It covers the evaluation order of SELECT, the difference between inner and outer joins captured as row-count changes, one-to-many increasing parent rows, GROUP BY granularity, the difference between COUNT(*) and COUNT(column), NULL three-valued logic, and triaging "the count doesn't add up".
This chapter covers the three positions where subqueries fit, correlated subqueries and EXISTS, writing in reading order with CTEs, and how window functions aggregate without folding rows. It covers the differences between ROW_NUMBER, RANK, and DENSE_RANK, running totals, top-N per group, and support versions per database.
This chapter covers the three basic operations of a transaction (begin, commit, rollback) and the four ACID properties. It explains that rollback affects only your own transaction, that commits can't be undone, and when syntax errors are detected.