READOS / Technology
SQL, Queries and Data Correctness
Writing queries that return the right answer and designing rules, keys and tests that keep data correct.
Start reading →Contents
- 01SQL for managers and analysts: reading and writing simple queries to answer business questionsA little SQL lets managers and analysts answer questions directly from business data. The core of the language, how to avoid common traps and how to check results before trusting them.
- 02SQL queries for meeting every requirementUse relational division to find records that satisfy every required condition, with clear treatment of missing evidence, duplicate records and empty sets.
- 03SQL NULL and the meaning of missing dataUnderstand how missing values affect SQL filters, totals and business decisions, with practical ways to distinguish unknown, absent and inapplicable data.
- 04SQL set operations and the meaning of duplicatesChoose union, intersection and difference by defining record identity and multiplicity, then validate combined datasets without hiding legitimate repeated events.
- 05Preventing double counting in SQL joinsLearn why valid SQL joins can inflate business totals, how to define the grain of a report, and how to reconcile aggregates before trusting the result.
- 06Paging database results without skips or duplicatesChoose a pagination strategy with explicit ordering, continuation and consistency rules, and test how inserts, deletes and changing sort values affect traversal.
- 07Parameterised queries and safe dynamic searchHow to keep search values separate from SQL instructions, handle optional filters and sort choices, and test database queries without breaking legitimate customer data.
- 08Recursive queries for component structuresHow to traverse multi-level component relationships, multiply quantities along each path, preserve shared components and detect cycles before trusting a materials total.
- 09Time intervals, overlaps and gaps in databasesModel database time intervals with clear boundaries, detect overlaps and coverage gaps, and distinguish business-effective periods from recorded history.
- 10Text encoding, collation and reliable record matchingWhy identical-looking names can compare differently, how text encoding and collation affect database matches, and how to migrate comparison rules without merging distinct records.
- 11Updating sets of database records correctlyHow to define a multi-record database change, select the right population, avoid ambiguous update joins, and verify the result without relying on row-by-row assumptions.
- 12Enforcing business rules with database constraintsTranslate business rules into database constraints, distinguish state rules from transition rules, and test enforcement across imports and concurrent updates.
- 13Designing database triggers with predictable effectsKeep database triggers understandable by defining event scope, transaction effects, bulk behaviour, recursion, concurrency and the evidence needed to test them.
- 14Derived values and reproducible database calculationsHow to decide when to calculate, store or freeze a derived value, maintain its dependencies and explain why a saved result differs from a calculation performed today.
- 15Identifying dependent records within their ownerHow to model records whose identity depends on a parent, preserve local numbering rules, and handle references, transfers and deletion without confusing ownership.
- 16Soft deletion, uniqueness and record lifecycleDesign logical deletion around record identity, active uniqueness, historical relationships, restoration and eventual removal instead of relying on a flag alone.
- 17Replacing overloaded status codes in databasesHow to separate independent business facts hidden inside one status code, choose flags or related records appropriately, and migrate old meanings without inventing missing information.
- 18Database views as stable data contractsUse database views to expose deliberate data contracts, with clear row meaning, types, update rules, access behaviour and compatibility tests as storage changes.
- 19Testing data models with facts and counterexamplesValidate a conceptual data model using precise sentences, small example populations and counterexamples that expose hidden uniqueness and participation assumptions.
- 20Building repeatable test data for database behaviourHow to create small, controlled database fixtures that expose incorrect joins, missing constraints and migration errors without depending on shared or confidential production data.
These topic-based reading collections are organised alphabetically; chapters may be read in any order.
← All books