Beginner to Intermediate

SQL

A hands-on SQL course built around PostgreSQL. Start with how a database actually works, learn querying, joins, window functions and transactions, then move on to design, indexing, security, migrations, testing, two realistic projects and interview preparation.

10 modules
39 lessons
About 361 min reading

Start learning

Course syllabus

Select any lesson to open it.

Module 1: Getting Started

3 lessons

How databases work, a disposable PostgreSQL setup, and the shop and company dataset used in every lesson.

Module 2: Database Fundamentals and Basic Commands

5 lessons

Relational concepts, the five SQL command groups, data types and constraints, querying, filtering and operators.

Module 3: Joins, Aggregations and Functions

5 lessons

Aggregates and grouping, every join type, set operators, built-in functions, CASE and subqueries.

Module 4: CTEs, Views and Window Functions

4 lessons

WITH clauses and recursive CTEs, views and materialized views, ranking, LAG and LEAD, and window frames.

Module 5: Database Design and Transactions

4 lessons

ER modeling, normalization, ACID transactions, isolation levels, locking and deadlocks.

Module 6: Programmability and Performance

5 lessons

Functions, procedures, triggers, cursors, indexes, execution plans, partitioning, roles and SQL injection.

Module 7: Real-World Usage

4 lessons

SQL from application code, schema migrations, upserts, pagination, bulk loading, JSONB and messy data.

Module 8: Tooling and Testing

3 lessons

psql and GUI clients, backups, debugging queries and testing SQL with pgTAP and pytest.

Module 9: Projects

2 lessons

Two realistic projects: a clinic appointment system and a sales analytics reporting layer.

Module 10: Interview Questions

4 lessons

Conceptual questions with explanations and coding exercises with worked, verified solutions.

What you will learn

Explain how a relational database stores data, plans queries and keeps committed data safe
Write SELECT queries with filtering, sorting, aggregation, joins, set operators, subqueries and CTEs
Use window functions for rankings, running totals, month-over-month change and top-N-per-group reports
Design normalized schemas with keys, constraints and junction tables from an ER model
Reason about transactions, isolation levels, MVCC, locking and deadlocks, and prevent lost updates
Build indexes, read EXPLAIN ANALYZE plans and tune slow queries with measurement
Write PL/pgSQL functions, procedures and triggers, and know when not to use them
Secure a database with roles and least privilege, and prevent SQL injection with parameterized queries
Run safe migrations, bulk loads, upserts and keyset pagination on live systems
Test SQL with assertion queries, pgTAP and pytest, and debug wrong results and slow queries
Complete two realistic projects and answer common SQL interview questions with confidence

Prerequisites

Comfort using a terminal and installing software
Basic computer literacy (files, folders, text editors)
No prior database or programming experience required
Docker (recommended) or a native PostgreSQL install for the hands-on exercises

Loading course...