SQL and DBMS in one line per lesson

Module 1: What a database is

Why a database, not a spreadsheet: A database keeps one checked copy of the data in tables (relations) of rows (tuples) and columns (attributes); SELECT FROM t shows a table.
Keys and relationships: A primary key is unique and never NULL; a foreign key must match another table's key (or be NULL); many-to-many needs a junction table with a composite key.
DBMS, RDBMS and SQL: A DBMS handles concurrency, integrity, security and recovery; three levels (external, conceptual, internal) give data independence; SQL = DDL + DML + DCL + TCL.

Module 2: Your first queries

SELECT: columns, expressions, aliases, DISTINCT: SELECT picks columns and computes expressions; AS names them; DISTINCT removes rows equal in every selected column.
WHERE: filtering rows: WHERE keeps rows where the condition is true; AND binds before OR, so bracket them; BETWEEN includes both ends; ILIKE ignores case.
NULL: the value that isn't there: NULL means unknown: any comparison with it is unknown, WHERE drops unknown rows, so test with IS NULL and fill gaps with COALESCE.
ORDER BY, LIMIT and pages: ORDER BY sorts (ASC default; Postgres puts NULLs last in ASC, first in DESC); add a tie-breaker; LIMIT n OFFSET (p−1)×n gives page p.

Module 3: Functions and expressions

Text functions: Text functions change values per row: UPPER, TRIM, REPLACE, SPLITPART, LEFT/RIGHT; || with NULL gives NULL, CONCAT skips it.
Numbers and dates: Integer / integer drops the decimals: cast or use 100.0 first; EXTRACT, DATETRUNC, TOCHAR and INTERVAL handle dates.
CASE, COALESCE and NULLIF: CASE WHEN … THEN … ELSE … END labels rows (first true WHEN wins); COALESCE fills NULLs; x / NULLIF(y, 0) divides safely.

Module 4: Aggregates and GROUP BY

COUNT, SUM, AVG, MIN, MAX: COUNT() counts rows, COUNT(col) skips NULLs, COUNT(DISTINCT col) counts different values; AVG ignores NULLs; SUM of no rows is NULL.
GROUP BY one and many columns: GROUP BY makes one row per bucket; every plain column in SELECT must be in GROUP BY; NULLs form one group.
HAVING vs WHERE, and the order a query runs in: WHERE filters rows before grouping; HAVING filters groups after; the logical order is FROM, WHERE, GROUP BY, HAVING, SELECT, ORDER BY, LIMIT.
Conditional aggregation: SUM(CASE WHEN … THEN 1 ELSE 0 END) or COUNT() FILTER (WHERE …) counts some rows per group; one per column makes a pivot.

Module 5: Joins

INNER JOIN: matching rows across tables: JOIN t ON key = key keeps only matching pairs: rows with no match, or a NULL key, disappear.
LEFT, RIGHT and FULL joins: keeping rows with no match: LEFT JOIN keeps every left row; WHERE right.pk IS NULL finds rows with no match; filters on the right table go in ON.
SELF and CROSS joins: A self join uses one table in two roles (c and r); CROSS JOIN makes every pair, m × n rows.
Joins with GROUP BY, and the double-counting trap: Count the right table with COUNT(right.pk); a one-to-many join repeats parent rows, so sum parent columns before joining.

Module 6: Subqueries and set operations

Scalar and IN subqueries: A scalar subquery (SELECT AVG(…) …) is one value (NULL if empty, error if many); use IN (SELECT …) for a list.
Correlated subqueries, EXISTS and the NOT IN trap: A correlated subquery uses the outer row; use NOT EXISTS for "never", because NOT IN returns nothing if the list has a NULL.
Subqueries in FROM: derived tables: A subquery in FROM (with an alias) is a temporary table: summarise inside, then filter, join or aggregate outside.
UNION, INTERSECT and EXCEPT: UNION stacks and de-duplicates (UNION ALL keeps duplicates); INTERSECT = in both, EXCEPT = in first only; one ORDER BY at the end.

Module 7: Creating tables

CREATE TABLE and data types: Pick types on purpose: INT/BIGINT for ids, NUMERIC(p,s) for money (never FLOAT), VARCHAR/TEXT for text and phone numbers, DATE/TIMESTAMP, BOOLEAN.
Constraints: PRIMARY KEY, NOT NULL, UNIQUE, CHECK, DEFAULT: Constraints are rules Postgres enforces on every write: PRIMARY KEY, NOT NULL, UNIQUE (allows NULLs), CHECK (NULL passes), DEFAULT.
Foreign keys and ON DELETE: REFERENCES parent(col) keeps children pointing at real parents; ON DELETE decides: refuse (default/RESTRICT), CASCADE, or SET NULL.
ALTER, DROP, TRUNCATE, identity columns and sequences: ALTER TABLE changes a live table; TRUNCATE empties it, DROP removes it; identity columns number rows for you, with gaps.

Module 8: Changing data

INSERT: one row, many rows, from a SELECT, with RETURNING: INSERT INTO t (cols) VALUES (…), (…) or INSERT INTO t (cols) SELECT …; name the columns, and add RETURNING to get the new rows back.
UPDATE and DELETE: UPDATE t SET col = … WHERE … and DELETE FROM t WHERE …: always WHERE, check with SELECT first, delete children before parents.
Upsert with ON CONFLICT, and MERGE: INSERT … ON CONFLICT (key) DO NOTHING | DO UPDATE SET col = EXCLUDED.col is an atomic upsert; MERGE applies a whole source table.

Module 9: ER modelling

Entities, attributes and relationships: Entities become tables, composite attributes become columns, multivalued ones get their own table, derived ones are computed, and a weak entity is keyed by owner key + partial key.
Cardinality and participation: 1:N puts the FK on the many side, 1:1 adds UNIQUE, M:N needs a junction table, and total participation means NOT NULL.
From ER diagram to tables: Entities → tables, 1:N → FK on the many side, 1:1 → FK + UNIQUE, M:N and ternary → their own table, multivalued → its own table, total participation → NOT NULL.

Module 10: Normalization

Redundancy and the three anomalies: Redundancy stores one fact many times, which causes update, insert and delete anomalies; normalization stores each fact once.
Functional dependencies, closure and keys: X → Y: same X, same Y. Closure X⁺ finds what X decides; a minimal X with X⁺ = R is a candidate key. Check an FD in data with GROUP BY X HAVING COUNT(DISTINCT Y) 1.
1NF, 2NF and 3NF: 1NF: atomic cells. 2NF: no non-prime attribute depends on part of a key. 3NF: for every X → A, X is a superkey or A is prime.
BCNF, lossless joins and when to denormalise: BCNF: every determinant is a superkey. A split is lossless when the common columns are a key of one side; 3NF always keeps dependencies, BCNF may not. Denormalise only on purpose.

Module 11: Window functions

OVER and PARTITION BY: windows vs GROUP BY: f(...) OVER (PARTITION BY x) adds a group value to every row without collapsing rows; it runs after WHERE, so filter on it from an outer query.
ROW_NUMBER, RANK, DENSE_RANK and top-N per group: ROWNUMBER never ties, RANK ties and skips, DENSERANK ties without gaps; top-N per group = rank with PARTITION BY in a subquery, filter outside.
LAG, LEAD and FIRST_VALUE: match-to-match change: LAG(x) / LEAD(x) read the previous / next row in the window order (NULL at the edges); always PARTITION BY the entity you are following.
Running totals and moving averages: frames: SUM(x) OVER (ORDER BY d) is a running total; the default frame is RANGE up to the current row and its peers, so use ROWS for one-row steps and a full frame for LASTVALUE.

Module 12: CTEs and recursive queries

WITH: multi-step queries you can read: WITH name AS (query) names a step for one statement; chain steps with commas and read the query top to bottom.
Recursive CTEs: org charts, paths and generated rows: WITH RECURSIVE = anchor rows UNION ALL a part that joins the CTE to find the next rows; it repeats until a round adds nothing, so always make sure it can stop.
Reports: ROLLUP, STRING_AGG and pivots: STRINGAGG lists values in one cell; ROLLUP / GROUPING SETS / CUBE add subtotal and total rows (spot them with GROUPING()); FILTER or SUM(CASE) pivots rows into columns.

Module 13: Transactions and concurrency

Transactions and ACID: BEGIN … COMMIT makes statements all-or-nothing; ROLLBACK undoes them; SAVEPOINT lets you undo part; an error aborts the whole transaction.
Concurrency problems: lost updates and friends: Dirty read, non-repeatable read, phantom and lost update; Postgres never allows dirty reads, and relative or guarded UPDATEs stop lost updates.
Isolation levels and MVCC: READ COMMITTED (default) takes a snapshot per statement, REPEATABLE READ one per transaction, SERIALIZABLE also stops write skew; MVCC keeps row versions so readers and writers never block.
Locks, FOR UPDATE and deadlocks: Writes lock rows until commit; FOR UPDATE locks what you read, SKIP LOCKED skips busy rows, a fixed lock order prevents deadlocks, and a version column gives optimistic locking.

Module 14: Indexes and query plans

Why indexes, and the B+ tree idea: An index is a sorted B+ tree of values and row addresses: a lookup reads 3-4 pages instead of the whole table; PK and UNIQUE are indexed for you, foreign keys are not.
Reading EXPLAIN: scans and joins: Read plans inside-out; Index/Index Only/Bitmap scans use indexes, Seq Scan reads everything; nested loop for small outer sides, hash for big unsorted inputs, merge for sorted ones.
Composite and covering indexes: A composite index (a, b) is sorted by a then b: it serves a and a + b but not b alone; equality columns first, ranges last; INCLUDE makes it covering for Index Only Scans.
When an index can't help: Keep the indexed column bare (no functions, casts, maths or leading %); low-selectivity filters skip indexes; every index slows writes; use expression and partial indexes for the queries you really run.

Module 15: Views, functions and security

Views and materialised views: A view stores a query and is always fresh; a materialised view stores rows and is stale until REFRESH MATERIALIZED VIEW.
Functions and procedures in PL/pgSQL: Functions return values and are used in queries (SELECT f(x)); procedures do work and are run with CALL p(x).
Triggers: audit logs, updated_at and rules: A trigger runs a function on INSERT/UPDATE/DELETE: BEFORE to change or block the row (RETURN NEW), AFTER for side effects like audit logs.
Roles, GRANT/REVOKE and SQL injection: Grant each role only what it needs (GRANT/REVOKE, test with SET ROLE), and never glue user input into SQL: pass it as a parameter.

Module 16: Inside the DBMS

Relational algebra and its SQL: σ = WHERE, π = SELECT DISTINCT, ⋈ = JOIN, ∪ ∩ − = UNION INTERSECT EXCEPT, and ÷ ("for every") = double NOT EXISTS.
Storage: pages, B+ tree maths and hashing: bfr = ⌊B/R⌋, blocks = ⌈r/bfr⌉; B+ tree order p = ⌊(B+K)/(K+P)⌋, leaf = ⌊(B−P)/(K+Pr)⌋; lookup = levels + 1 reads; hashing is for equality only.
Serializability, two-phase locking and timestamps: Conflict = different transactions, same item, at least one write; precedence graph with no cycle = conflict serializable; 2PL guarantees it (but can deadlock), strict 2PL also stops cascading aborts.
Logging and recovery: WAL, checkpoints, undo and redo: WAL: log before data, commit = commit record on disk; after a crash redo what committed (after the checkpoint), undo what didn't, newest record first.

Module 17: Beyond one database

Replication and backups: Replicas keep you running when a server dies; backups with point-in-time recovery save you from bad queries. RPO = data you can lose, RTO = time you can be down.
Partitioning and sharding: Partitioning splits a table on one server (RANGE, LIST, HASH; TO is exclusive); sharding splits rows across servers by a shard key.
SQL vs NoSQL, CAP and JSONB: Relational for related data and transactions, NoSQL families for one job at scale; CAP is C or A during a partition; JSONB gives Postgres documents (- JSON, - text, @ contains).

Interview pack: SQL interview problems

Salary classics: Second/Nth highest: MAX below MAX or DENSERANK, wrapped so "none" is one NULL row; manager problems are self-joins on e.managerid = m.empid.
Ranking and top-N: One per group: ROWNUMBER with tie-breakers; ties share a place: RANK (gaps) or DENSERANK (levels); rank in a subquery, filter outside.
Dates and streaks: Streaks: DISTINCT days, then date − ROWNUMBER() groups each island; missing days come from generateseries; growth = LAG over per-month rows.
Joins and anti-joins: Anti-join with NOT EXISTS (never NOT IN over a nullable column); pairs with a.id < b.id; dedupe with DELETE … USING on a lower id; count DISTINCT after a fan-out.
Tricky reports: Pivot with COUNT() FILTER; median with PERCENTILECONT(0.5); swap seats by changing ids; sessions = LAG flag + running SUM.