SQL & Database Design: Beyond the Basics
About this course
For developers who already write joins and GROUP BY, and keep hitting the ceiling above them. Window functions, recursive CTEs, how the query planner actually chooses, why your index is being ignored, what your isolation level really guarantees, and schema design that survives three years of requirement changes. Two PostgreSQL practice databases are included to download — a normalised bookshop and a 118,000-row telemetry stream — so every technique is something you run, not something you read.
What you'll learn
- Design schemas to 3NF and know precisely when to denormalise instead
- Reason about NULL, join semantics and the difference between EXISTS, IN and JOIN
- Use window functions fluently: ranking, running totals, frames, LAG and LEAD
- Solve sessionization and gaps-and-islands problems that defeat plain GROUP BY
- Read EXPLAIN ANALYZE and explain why the planner ignored your index
- Choose composite index column order correctly, and know when a partial or covering index wins
- Pick an isolation level deliberately and defend against the anomalies it still allows
- Model time, history and semi-structured data without painting yourself into a corner
Stuck on something? Ask
Every lesson has an assistant that can see the lesson you are reading. Ask in your own words and it answers using the same examples and table names in front of you — no switching to a search engine and losing your place.
Why does WHERE come after FROM?
Because the database reads the rows first, then decides which to keep. That order is also why WHERE cannot see a SELECT alias.
It will not give you assessment answers — it explains the idea they test, so the certificate still means something.
Not ready yet?
One email when something new is published. Nothing else.
Course content
Setup, and Design That Holds Up 6 lessons
- Who this course is for Preview
- Practice database 1: bookshop.sql Preview
- Practice database 2: telemetry.sql Preview
- Normalisation, keys and constraints
- Section 1 assessment: relational design
- Section 1 handout (PDF)
Joins, NULL and Three-Valued Logic 4 lessons
- Joins as set operations, and what they do to row counts
- NULL: the part that bites
- Section 2 assessment: joins and NULL
- Section 2 handout (PDF)
Aggregation and Window Functions 4 lessons
- Aggregation properly: grouping sets, FILTER and the order of evaluation
- Window functions: ranking, running totals and frames
- Section 3 assessment: aggregation and windows
- Section 3 handout (PDF)
CTEs, Recursion and the Patterns That Defeat GROUP BY 4 lessons
- CTEs, LATERAL and recursion
- Sessionization, gaps and islands, and calendar joins
- Section 4 assessment: CTEs and query patterns
- Section 4 handout (PDF)
Indexes and How the Planner Thinks 4 lessons
- Reading EXPLAIN ANALYZE, and finding the missing index
- Index design: column order, covering, partial and the ones that hurt
- Section 5 assessment: indexing and the planner
- Section 5 handout (PDF)
Transactions, Isolation and Concurrency 4 lessons
- Isolation levels and what each one still allows
- Locking, lost updates and practical concurrency
- Section 6 assessment: transactions and isolation
- Section 6 handout (PDF)
Modelling for Change: Time, History and Semi-Structured Data 4 lessons
- Time, history and soft deletes
- JSONB, partitioning and migrations that do not need downtime
- Section 7 assessment: modelling for change
- Section 7 handout (PDF)
Final Exam 2 lessons
- Before you sit the exam
- Final exam: SQL & Database Design (35 questions, 60 minutes)
Instructor
Fepiq
Practical engineering courses written for people who prefer reading to watching.
We'll send your receipt and a sign-in code here. No password needed.
Full lifetime access · Certificate on completion · AI tutor in every lesson
Save ₹0
The SQL Track: Foundations to Database Design
- SQL Foundations: Your First Queries, Explained Properly
- SQL & Database Design: Beyond the Basics (this course)