← All Chapters
14 topics

Databases and SQL

Joins, indexes, query plans and transactions, watch queries run against live tables.

  1. 32Tables and the Relational ModelData split across tables and linked by keys: hover a row to see the primary-to-foreign-key link
  2. 33SELECT, WHERE and CASEChoose columns, filter rows live, and bucket them with a CASE expression
  3. 34JoinsMatch rows across tables, and watch INNER, LEFT, RIGHT, and FULL change the result
  4. 35GROUP BY and AggregationCollapse rows into one summary per group, and see how COUNT, DISTINCT, and HAVING behave
  5. 36Window FunctionsRank, number, and total across rows without collapsing them, and see how ties split the ranks
  6. 37NormalizationWatch one wide table that stores each fact many times step into first normal form, hit an update anomaly, then split into three linked tables so every fact lives in exactly one place
  7. 38IndexesRun the same query with the index off then on, and watch rows examined collapse from the whole table to a handful
  8. 39Query PlanningSlide selectivity to watch the planner flip between an index and a full scan, then compare a good and a bad join order
  9. 40Funnel AnalysisCount distinct sessions through each step and watch where users drop off
  10. 41SQL vs NoSQL ModelingModel the same users, plans, and orders as normalized tables and as one document per user, then run three queries and watch which shape touches fewer places as the access pattern changes
  11. 42AtomicityTransfer money between two accounts as one transaction, trigger a failure between the debit and the credit, and watch the rollback restore the balances so the total never changes
  12. 43ConcurrencyRun two transactions that both add to one balance, watch a stale read make an update vanish, then switch on locking so the second waits and the total comes out right
  13. 44Isolation LevelsTurn the isolation dial and pick a concurrency phenomenon, then watch a two-transaction timeline show whether PostgreSQL lets it happen or prevents it
  14. 45Finding Similar RowsTurn each row into a bag of values and ask which rows sit closest