The Complete SQL Course
Learn SQL from your first query to professional database engineering. Build strong relational models, write analytical queries, protect data with transactions and permissions, tune performance, and complete portfolio-ready projects using PostgreSQL.
Phase 1 · SQL Foundations
Understand relational databases, set up PostgreSQL, retrieve rows, filter safely, reason about NULL, sort results, and complete your first exploration project.
Databases, SQL & Your First Query
Understand relational databases and SQL, connect to PostgreSQL, and retrieve your first result set with SELECT.
PostgreSQL Setup, psql & the Course Dataset
Install or connect to PostgreSQL, navigate psql, load the course dataset, and build a repeatable query workspace.
Tables, Rows, Columns & Data Types
Develop a precise mental model for relations, rows, attributes, domains, and the data types that protect meaning.
SELECT Lists, Aliases & Expressions
Choose output columns deliberately, calculate derived values, assign readable aliases, and understand expression evaluation.
Filtering with WHERE & Boolean Logic
Filter rows with comparisons, ranges, pattern matching, membership tests, and correctly grouped Boolean conditions.
NULL & Three-Valued Logic
Reason correctly about missing and unknown values using IS NULL, COALESCE, and SQL's three-valued logic.
ORDER BY, LIMIT & Reliable Pagination
Produce deterministic result order, control NULL placement, return top rows, and compare offset with keyset pagination.
Phase Project: Explore a Bookstore Database
Combine foundational SELECT skills to answer a structured set of bookstore questions and present reproducible findings.
Phase 2 · Defining & Changing Data
Create schemas and constrained tables, insert and modify rows safely, use core functions, and build a clean data-management workflow.
Schemas, CREATE TABLE & Type Design
Create namespaces and tables with meaningful names, precise PostgreSQL types, defaults, identity columns, and timestamps.
Keys, Constraints & Referential Integrity
Protect identity and invariants with primary, unique, foreign, check, and not-null constraints.
INSERT, Generated Values & Bulk Loading
Insert single and multiple rows, retrieve generated values, handle conflicts deliberately, and load data in bulk.
UPDATE & Safe State Changes
Modify rows with precise predicates, computed assignments, joins, returning clauses, and verification-first habits.
DELETE, TRUNCATE & Data-Loss Safety
Remove data with explicit scope, understand cascades and TRUNCATE, and use transactions and backups to reduce irreversible mistakes.
String, Numeric & Date-Time Functions
Clean, calculate, and summarize values with portable core functions and PostgreSQL date-time capabilities.
CASE, COALESCE, NULLIF & Type Conversion
Express conditional business logic, supply fallbacks, prevent invalid arithmetic, and convert values explicitly.
Phase Project: Build a Clean Inventory Schema
Design, create, populate, validate, and safely modify a constrained inventory database from a written specification.
Phase 3 · Relationships & Reporting
Model normalized relationships, master every major join, aggregate business measures, compose subqueries and set operations, and deliver a reporting project.
Relational Modeling & Normalization
Translate business concepts into entities and relationships, identify dependencies, and normalize designs without losing useful context.
INNER JOIN & Matching Relationships
Join related tables with explicit predicates, qualified columns, and cardinality awareness.
LEFT, RIGHT, FULL, CROSS & SELF JOIN
Choose outer, cross, and self joins to preserve unmatched rows, generate combinations, and traverse same-table relationships.
Multi-Table Joins & Duplicate Control
Compose larger join graphs, predict result grain, diagnose accidental multiplication, and avoid DISTINCT as a blind repair.
Aggregate Functions, GROUP BY & HAVING
Calculate counts, totals, averages, and conditional measures at a clearly defined grouping grain.
Subqueries, EXISTS & Correlated Logic
Use scalar, table, and correlated subqueries while choosing EXISTS, IN, joins, or aggregation by semantics.
UNION, INTERSECT & EXCEPT
Combine compatible result sets and reason about duplicate elimination, ALL variants, column alignment, and final ordering.
Phase Project: Sales Reporting Database
Model customers, products, orders, and payments, then deliver a tested reporting pack for business stakeholders.
Phase 4 · Advanced Querying & Analytics
Use CTEs, recursive queries, window functions, advanced grouping, views, JSON, arrays, and lateral joins to solve complex analytical problems.
Common Table Expressions & Query Decomposition
Use WITH queries to name intermediate results, stage transformations, and improve reasoning while understanding planner behavior.
Recursive CTEs & Hierarchical Data
Traverse trees and graphs with anchor and recursive terms, track depth and paths, and prevent cycles.
Window Functions: Ranking & Partitions
Calculate ranks, row numbers, percentiles, and partition-level measures without collapsing detail rows.
Window Frames, Running Totals & Time Analysis
Control ROWS and RANGE frames to build running totals, moving averages, deltas, and period comparisons correctly.
GROUPING SETS, ROLLUP & CUBE
Generate multiple aggregation levels in one query and distinguish subtotal NULLs from stored NULL values.
Views, Materialized Views & Stable Interfaces
Encapsulate query contracts with views, precompute expensive reads with materialized views, and manage refresh and security tradeoffs.
JSON, Arrays & LATERAL Queries
Work with semi-structured PostgreSQL values, expand nested data, aggregate JSON, and use LATERAL for per-row dependent queries.
Phase Project: Analytical Dashboard Queries
Build a reusable analytical query layer with trends, rankings, cohorts, subtotals, JSON responses, and documented metric definitions.
Phase 5 · Performance, Transactions & Security
Design useful indexes, read execution plans, tune queries, control concurrency, apply least privilege, evolve schemas, and harden a production workload.
Index Fundamentals & B-Tree Design
Understand index structure and cost, then design single, composite, covering, expression, and partial indexes from workload.
EXPLAIN, ANALYZE & Planner Statistics
Read PostgreSQL execution plans, compare estimates with actuals, identify scans and join algorithms, and improve statistics.
Query Optimization & Sargability
Make predicates index-friendly, reduce work early, optimize joins and sorts, and verify improvements with repeatable measurements.
Transactions, ACID & Savepoints
Group changes atomically, understand ACID guarantees, recover with savepoints, and keep transaction boundaries intentional.
Isolation, Locks & Concurrency Control
Reason about concurrent anomalies, isolation levels, MVCC, row locks, deadlocks, and safe retry boundaries.
Roles, Privileges & Row-Level Security
Apply least privilege with roles, ownership, grants, default privileges, schemas, and row-level security policies.
Migrations & Zero-Downtime Schema Evolution
Treat schema changes as versioned releases and use expand-contract techniques to stay compatible during rolling deployments.
Phase Project: Tune & Secure a Transactional System
Diagnose a realistic workload, add evidence-based indexes, repair transaction races, and implement least-privilege access.
Phase 6 · Professional Database Engineering
Add database programmability, search, partitioning, data pipelines, backup and monitoring, application integration, warehousing, and a complete capstone.
Stored Functions, Procedures & Triggers
Use server-side programmability selectively for data-local operations, while controlling volatility, side effects, recursion, and deployment complexity.
Full-Text Search
Build language-aware search with tsvector, tsquery, ranking, highlighting, and GIN indexes.
Table Partitioning & Data Lifecycle
Partition large tables by range, list, or hash, enable pruning, manage local indexes, and automate retention safely.
Import, Export, ETL & Data Quality
Move data through staging tables, validate and transform it set-wise, reconcile results, and make pipelines idempotent.
Backup, Restore, Monitoring & Maintenance
Plan logical and physical recovery, test restores, monitor workload health, and understand vacuum, analyze, bloat, and routine operations.
SQL from Applications & Prepared Statements
Integrate SQL safely from application code with parameters, connection pools, transaction ownership, result mapping, and observability.
Data Warehousing & Dimensional Modeling
Design facts, dimensions, grain, surrogate keys, slowly changing dimensions, and incremental analytical loads.
Capstone: Commerce Database & Analytics Portfolio
Design, build, secure, tune, operate, and present a complete commerce database with transactional and analytical workloads.