Prerequisites
Basic computer literacy and a willingness to document experiments.
Learning objectives
- Explain the stage-specific model and its limits
- Apply the listed concepts using a controlled exercise
- Record results, failure cases and a reproducible evidence trail
Topics to learn
- Entities, keys and relationships
- Normal forms and controlled denormalisation
- Nullability and uniqueness
- ER diagrams and constraints
- SQL specialization: SQL: focus on relational modelling, joins, windows, query plans, transactions, indexing and safe migrations.
Tools
PostgreSQL or SQLiteER diagram toolSQL client
Hands-on exercise
Model a small domain and identify duplicate, missing and inconsistent data cases. Context for this route: SQL: focus on relational modelling, joins, windows, query plans, transactions, indexing and safe migrations.
Suggested project
A schema with constraints and sample data. The outcome must directly demonstrate SQL, not merely list tools.
Learning resources
Completion criteria
The database enforces key business rules under invalid writes. Evidence must be reproducible and tied to SQL.
Next: Query composition & joins
Prerequisites
Completion of the previous stage and a working local practice environment.
Learning objectives
- Explain the stage-specific model and its limits
- Apply the listed concepts using a controlled exercise
- Record results, failure cases and a reproducible evidence trail
Topics to learn
- SELECT, WHERE and ORDER BY
- Inner/outer joins and set operations
- GROUP BY and HAVING
- NULL and three-valued logic
- SQL specialization: SQL: focus on relational modelling, joins, windows, query plans, transactions, indexing and safe migrations.
Tools
PostgreSQLSQL client
Hands-on exercise
Answer a set of business questions using joins and verify totals against hand-calculated cases. Context for this route: SQL: focus on relational modelling, joins, windows, query plans, transactions, indexing and safe migrations.
Suggested project
A query notebook of documented analytical questions. The outcome must directly demonstrate SQL, not merely list tools.
Learning resources
Completion criteria
Queries return correct results on edge cases and NULL values. Evidence must be reproducible and tied to SQL.
Next: CTEs, windows & analytical SQL
Prerequisites
Completion of the previous stage and a working local practice environment.
Learning objectives
- Explain the stage-specific model and its limits
- Apply the listed concepts using a controlled exercise
- Record results, failure cases and a reproducible evidence trail
Topics to learn
- CTEs and recursive queries where applicable
- Window functions and ranking
- Conditional aggregation
- Date/time and cohort analysis
- SQL specialization: SQL: focus on relational modelling, joins, windows, query plans, transactions, indexing and safe migrations.
Tools
PostgreSQL window functionsExplain output
Hands-on exercise
Build a cohort or ranking report and compare it with a simpler query. Context for this route: SQL: focus on relational modelling, joins, windows, query plans, transactions, indexing and safe migrations.
Suggested project
A reusable analytical query pack with expected results. The outcome must directly demonstrate SQL, not merely list tools.
Learning resources
Completion criteria
The query logic is readable and tested against known fixtures. Evidence must be reproducible and tied to SQL.
Next: Indexes, plans & query optimisation
Prerequisites
Completion of the previous stage and a working local practice environment.
Learning objectives
- Explain the stage-specific model and its limits
- Apply the listed concepts using a controlled exercise
- Record results, failure cases and a reproducible evidence trail
Topics to learn
- B-tree and composite index trade-offs
- EXPLAIN/ANALYZE
- Selectivity and cardinality
- Sorts, scans and join strategies
- SQL specialization: SQL: focus on relational modelling, joins, windows, query plans, transactions, indexing and safe migrations.
Tools
EXPLAINDatabase statisticsBenchmark fixtures
Hands-on exercise
Capture a plan before and after adding a candidate index, then compare execution. Context for this route: SQL: focus on relational modelling, joins, windows, query plans, transactions, indexing and safe migrations.
Suggested project
A query tuning report with plan screenshots and workload notes. The outcome must directly demonstrate SQL, not merely list tools.
Learning resources
Completion criteria
Optimisation claims include the data size and measured plan changes. Evidence must be reproducible and tied to SQL.
Next: Transactions, concurrency & integrity
Prerequisites
Completion of the previous stage and a working local practice environment.
Learning objectives
- Explain the stage-specific model and its limits
- Apply the listed concepts using a controlled exercise
- Record results, failure cases and a reproducible evidence trail
Topics to learn
- ACID and isolation levels
- Locks and deadlocks
- Optimistic vs pessimistic concurrency
- Idempotent writes and recovery
- SQL specialization: SQL: focus on relational modelling, joins, windows, query plans, transactions, indexing and safe migrations.
Tools
Database transaction toolsConcurrent test script
Hands-on exercise
Run concurrent update tests and demonstrate a race condition prevented by a constraint or transaction. Context for this route: SQL: focus on relational modelling, joins, windows, query plans, transactions, indexing and safe migrations.
Suggested project
A transactional workflow with concurrency tests. The outcome must directly demonstrate SQL, not merely list tools.
Learning resources
Completion criteria
The invariants hold under repeated and parallel requests. Evidence must be reproducible and tied to SQL.
Next: Migrations, backup & operations
Prerequisites
Completion of the previous stage and a working local practice environment.
Learning objectives
- Explain the stage-specific model and its limits
- Apply the listed concepts using a controlled exercise
- Record results, failure cases and a reproducible evidence trail
Topics to learn
- Additive migrations and version history
- Backup and restore validation
- WAL/checkpoint basics where relevant
- Access control and audit
- SQL specialization: SQL: focus on relational modelling, joins, windows, query plans, transactions, indexing and safe migrations.
Tools
Migration toolDatabase backup toolMonitoring
Hands-on exercise
Apply a migration to a copy of a legacy schema and restore from backup. Context for this route: SQL: focus on relational modelling, joins, windows, query plans, transactions, indexing and safe migrations.
Suggested project
A versioned migration set and verified recovery procedure. The outcome must directly demonstrate SQL, not merely list tools.
Learning resources
Completion criteria
The migration is repeatable, preserves existing rows and has a rollback/restore plan. Evidence must be reproducible and tied to SQL.
Next: Performance, security & governance
Prerequisites
Completion of the previous stage and a working local practice environment.
Learning objectives
- Explain the stage-specific model and its limits
- Apply the listed concepts using a controlled exercise
- Record results, failure cases and a reproducible evidence trail
Topics to learn
- Least-privilege roles
- Parameterised statements and injection prevention
- Connection limits and maintenance
- Retention and sensitive-data handling
- SQL specialization: SQL: focus on relational modelling, joins, windows, query plans, transactions, indexing and safe migrations.
Tools
DB rolesQuery statsSecurity checklist
Hands-on exercise
Review an application data path and verify parameter binding and privilege boundaries. Context for this route: SQL: focus on relational modelling, joins, windows, query plans, transactions, indexing and safe migrations.
Suggested project
A database operations checklist and access matrix. The outcome must directly demonstrate SQL, not merely list tools.
Learning resources
Completion criteria
No application query relies on interpolated untrusted input. Evidence must be reproducible and tied to SQL.
Next: Capstone: analytics-ready data model
Prerequisites
Completion of the previous stage and a working local practice environment.
Learning objectives
- Explain the stage-specific model and its limits
- Apply the listed concepts using a controlled exercise
- Record results, failure cases and a reproducible evidence trail
Topics to learn
- Business questions and dimensional choices
- Schema and query design
- Concurrency and performance evidence
- Migrations and disaster recovery
- SQL specialization: SQL: focus on relational modelling, joins, windows, query plans, transactions, indexing and safe migrations.
Tools
SQL databaseVisualisation toolGit
Hands-on exercise
Build the schema, ingest fixtures, answer analytics questions and test restore. Context for this route: SQL: focus on relational modelling, joins, windows, query plans, transactions, indexing and safe migrations.
Suggested project
A documented database project with schema, query pack and benchmark report. The outcome must directly demonstrate SQL, not merely list tools.
Learning resources
Completion criteria
Data correctness, query performance and recovery are independently verifiable. Evidence must be reproducible and tied to SQL.
End of roadmap: Complete the final project and build your portfolio.