Databases & SQL
Relational and non-relational models, and the SQL clauses that actually run against them.
About this track
SQL is declarative, which is exactly why it is hard to learn: you write what you want and the engine decides how. These pages show the how. Run a join and watch rows pair up; run a GROUP BY and watch rows collapse into one.
The track covers the relational model and its NoSQL alternative, then works through the clauses in the order a query is actually evaluated, ending on window functions and CTEs.
SQL is declarative, which is exactly what makes it hard to learn: you write what you want and the engine decides how. These pages show the how - which rows survive each clause, in the order the database actually applies them.
What you will be able to do
- Write queries with confidence about which clause runs when, and why that decides where an alias is legal.
- Choose the right join, and predict how many rows come back before running it.
- Tell WHERE from HAVING by what each one filters, and pick the faster of the two.
- Explain what an index costs on write and what it saves on read.
- Say what a transaction guarantees, and what normalisation is trading away.
How the track is ordered
The relational model comes first, along with its non-relational alternative, so the later clauses have something to act on. Then SQL itself, worked through in the order a query is actually evaluated - FROM and JOIN, WHERE, GROUP BY, HAVING, SELECT, ORDER BY - which is deliberately not the order it is written in, and explains most of the errors beginners hit. Subqueries, CTEs and window functions follow, and the track ends on the engine's own concerns: indexes, transactions and normalisation. No prior database experience is needed.
Where this leads
Indexes are B-trees, and query execution order is a plan over set operations, so the algorithms track explains the machinery underneath. If you are heading toward machine learning, this is where the training data comes from and where most feature engineering actually happens.
All 42 modules, in teaching order
- 01What are Relational Databases?Store data in normalised tables linked by foreign keys and see how one update in one row propagates correctly through every join.
- 02What are Non Relational Databases?Compare document, key-value, wide-column and graph databases and see which relational guarantee each one drops to gain something else.
- 03Datatypes in SQLChoose between INT, BIGINT, DECIMAL and VARCHAR, and see why storing money in FLOAT silently corrupts your arithmetic.
- 04DDL in SQLCreate, alter and drop tables with DDL, and see how constraints — NOT NULL, UNIQUE, FOREIGN KEY — enforce data quality at the edge.
- 05Primary and Foreign KeysDefine a primary key and a foreign key, attempt an insert that violates each, and watch the database enforce referential integrity for you.
- 06Constraints: UNIQUE, CHECK and NOT NULLAdd UNIQUE, CHECK and NOT NULL constraints to a table and watch the database reject every row that violates a rule you wrote once.
- 07Normalization (1NF, 2NF, 3NF) in SQLOne table with a multi-valued column, split step by step into 1NF, 2NF and 3NF. Watch the redundant cells that cause update anomalies drop to zero.
- 08DML in SQLRun INSERT, UPDATE, DELETE and SELECT on a live table and see why a missing WHERE clause can wipe every row in one statement.
- 09Transactions and ACID in SQLStep a transfer through BEGIN, two UPDATEs and COMMIT or ROLLBACK, with a second session watching. Fail it halfway and watch atomicity undo both writes, not one.
- 10Where Clause in SQLFilter rows one at a time with WHERE and see how NULL turns a comparison into UNKNOWN — the trap behind most wrong SQL results.
- 11NULL Handling and COALESCE in SQLNULL is not a value, so nothing about it compares the way you expect. Pick an expression and watch COALESCE, NULLIF and IS NULL treat the same rows differently.
- 12ORDER BY in SQLWatch rows slide into their new order as you change the sort key, direction, tie-breaker and NULLS placement, with the query building live.
- 13Limit and Offset in SQLPage through a result set with LIMIT and OFFSET, and hit the two traps: missing ORDER BY and the linear scan at high page numbers.
- 14CASE and Views in SQLBuild a CASE expression that turns raw numbers into labels, then save the whole query as a view and watch it stay a live query, not a snapshot.
- 15Aggregate Functions and the NULL TrapRun COUNT, SUM, AVG, MIN and MAX on real rows, see how each one handles NULLs, and fix the silent mistake that changes your totals.
- 16SQL GroupBy VisualizerGroup rows by a column and watch COUNT, SUM and AVG collapse many rows into one row per group, live against a sample table.
- 17HAVING in SQLRows become groups become filtered groups. Move the same condition between WHERE and HAVING and watch the answer change, one stage at a time.
- 18Query Execution Order in SQLSQL reads top to bottom but runs FROM first and SELECT second-to-last. Step through the real order and watch the row count change at every stage.
- 19SQL Joins VisualizerWatch INNER, LEFT, RIGHT and FULL joins pull rows from two tables, and see exactly which rows each variant keeps.
- 20Self-JoinsJoin a table to itself, match employees to managers in the same rows, and walk through the three query patterns that need a self-join.
- 21UNION, INTERSECT and EXCEPT in SQLCombine two result sets instead of two tables. Pick an operator and watch which rows survive, and see UNION ALL keep the duplicate UNION would quietly drop.
- 22Subqueries in SQLThe inner query runs first and hands its answer to the outer one. Scalar, IN-list, derived table, and a correlated subquery re-running once per row.
- 23EXISTS, IN and JOINWrite the same question with EXISTS, IN and JOIN, compare the execution plans, and find the version that quietly returns wrong rows.
- 24Common Table Expressions in SQLName a subquery with WITH so the logic reads top to bottom, and see how a recursive CTE walks a tree row by row.
- 25Recursive CTEsWrite a recursive CTE that walks an org chart row by row, follow each iteration of the loop, and see how SQL follows a chain of unknown length.
- 26SQL Window FunctionsRank, total and compare across rows without collapsing them, using OVER and PARTITION BY on a live result set.
- 27Regular Expressions in SQLMatch patterns that LIKE cannot express — character classes, quantifiers and anchors — applied directly inside a SQL WHERE clause.
- 28Indexes and Query PerformanceWalk a B-tree instead of reading every row and watch rows-examined collapse. Then meet the queries an index cannot help, and what it costs on writes.
- 29Composite and Covering IndexesBuild a composite index, reorder the columns, and watch the query plan switch between an index scan and a full table scan on the same query.
- 30EXPLAIN and Query PlansRun EXPLAIN on a slow query, read the plan node by node, and spot the sequential scan or nested loop that accounts for the missing time.
- 31Isolation LevelsStep through read uncommitted, read committed, repeatable read and serializable with two concurrent transactions to see what each one allows.
- 32DeadlocksSet up two transactions that lock rows in opposite order, watch the deadlock form, and see the one change in lock order that prevents it.
- 33MVCC: How Readers Avoid Blocking WritersWatch MVCC serve a reader an older row version while a writer updates the same row, and see why neither transaction has to wait for the other.
- 34Partitioning by Range and HashPartition a table by range and by hash, run the same query on each layout, and see how the planner skips every partition it does not need.
- 35ShardingShard a table by a key, route a query to the right partition, and see the four capabilities you lose when data lives on separate machines.
- 36Replication, Read Replicas and LagSet up a primary and a read replica, write to one and read from the other, and measure the lag window where a query returns stale data.
- 37The CAP TheoremWalk through the CAP theorem with a two-node example, see which two guarantees you keep, and learn what the theorem does not actually say.
- 38The Document Model against RowsStore the same order as a nested document and as normalised rows, then run the queries each shape handles well and the ones it does not.
- 39Key-Value and Graph ModelsCompare key-value and graph models against relational tables, see the queries each answers in one hop, and learn when leaving SQL pays off.
- 40OLTP, OLAP and Columnar StorageCompare row storage and columnar storage on the same table, run an analytical query on each, and see why dashboards need a different engine.
- 41Star Schema: Facts and DimensionsBuild a star schema with one fact table and three dimensions, run an analytical query, and see why columnar engines prefer this layout.
- 42SQL Injection and Parameterised QueriesBuild a query with string concatenation, inject a payload that drops a table, then switch to a parameterised query that blocks it completely.