SQL Databases
Learn SQL as a cumulative local course: define what each row means, predict exact results, run queries against a deterministic SQLite fixture, protect invariants, inspect evidence, and finish with a restorable enrollment ledger.
Relational models and SELECT queries
Objective Create one local relation, state its row grain, predict a SELECT result, and verify exact ordered rows.
Core explanation
SQL, or Structured Query Language, is a language for stating questions and changes over relational data. A database is an organized collection of stored facts, a table names one kind of fact, a row records one fact, a column names one attribute of that fact, and a schema declares columns and rules. The grain sentence says what exactly one row represents; here one courses row represents one identified course. A primary key is the column or column combination that uniquely identifies a row, and the id column supplies that identity. SELECT names the output columns, FROM names the source table, WHERE keeps only rows whose condition is TRUE, ORDER BY alone guarantees result sequence, and LIMIT bounds the returned rows. The query result has its own grain: one published course per row, with id, title, and chapter_count columns. The question-mark symbols are parameter placeholders whose values are bound separately, so data is not pasted into SQL text. SQL is declarative because the database may choose any equivalent access method while preserving the promised rows and order. The first laboratory uses Python’s built-in sqlite3 module and an in-memory database that disappears when the process exits; SQLite is the local teaching engine, not proof of PostgreSQL, MySQL, concurrency, persistence, backup, or production operation.
Model a relation as typed facts with a declared grain
A relation has attributes and tuples under the mathematical model; a practical SQL table has named typed columns, rows, constraints, and engine-specific storage. Before querying, state what one row represents. In users, one row may mean one registered account; in course_daily_metrics, one row may mean one course on one UTC date. Grain determines the candidate key and prevents interpreting two rows as one fact or one row as several facts. Column names, units, time zone, and null policy belong to the model.
A primary key identifies each row and should be stable, minimal, and never reused for another entity. A natural key may express a real-world uniqueness rule, while a surrogate key simplifies references but does not replace domain uniqueness. Tables are unordered unless a query includes ORDER BY. Physical row placement, insertion order, or a previous result cannot supply presentation order. Deterministic pagination needs a complete sort key with a unique tie breaker.
Read SELECT as a logical query pipeline
A useful logical order is FROM and JOIN, WHERE, GROUP BY, aggregate calculation, HAVING, window calculation, SELECT, DISTINCT, set ordering, and LIMIT or FETCH, although the optimizer may execute an equivalent physical plan differently. This explains why a SELECT alias is often unavailable in WHERE and why filtering before aggregation differs from HAVING. SQL is declarative: state the desired relation and let the engine choose an equivalent access plan under its semantics.
List required columns rather than SELECT * in durable code. An explicit projection documents the response shape, avoids accidental private or large fields, reduces transfer, and resists schema additions that break positional consumers. Qualify ambiguous columns with table aliases and give derived expressions stable names. A query can execute successfully while returning the wrong grain, order, or units, so predict the columns, row count bounds, uniqueness, and representative rows before running it.
Use expressions, types, aliases, and ordering deliberately
SELECT expressions can calculate arithmetic, concatenate text, branch with CASE, convert with CAST, and call deterministic or context-sensitive functions. Type coercion differs among engines and can lose precision or prevent index use. Store money under an explicit decimal or minor-unit policy, timestamps with a time-zone contract, and identifiers in their real type. Avoid formatting authoritative numbers or dates into display strings inside reusable data queries unless the output boundary explicitly requires it.
ORDER BY can reference expressions and declares ascending or descending direction plus a null-ordering policy where the dialect supports it. Always add a stable unique tie breaker for reproducible results. LIMIT without complete ordering selects an arbitrary qualifying subset. DISTINCT removes duplicate projected rows, which may hide a wrong join and adds work; use it only when duplicate elimination is part of the result contract. Explain why duplicates can exist before removing them.
Laboratory: run one complete local SELECT before adding more clauses
Save the displayed source as sql_chapter_01.py and predict its four output lines before running python3 sql_chapter_01.py. Draw the courses table as four named columns and three rows, underline id as the primary key, and write the grain sentence “one identified course per row.” Then trace FROM, WHERE published = 1, the three-column SELECT projection, ORDER BY title and id, and LIMIT 10 in logical-result terms.
Run the file and reconcile the complete tuple assertion before reading the printout. Change only the bound published value to 0, predict the draft row, and update only the intentionally changed assertion; do not paste the value into SQL. Restore the baseline, then add a private notes column without selecting it and confirm that the four public lines remain unchanged. The exercise proves one local SQLite result contract and parameter boundary, not another dialect, permanent storage, or production operation.