Skip to content
aviral gupta
Get in touch

// LEARN · LANGUAGES & FRAMEWORKS

SQL

A practical SQL course on PostgreSQL, from creating a first table to tuning queries, securing data and keeping a database healthy. Every query is run and checked on PostgreSQL 18.

Level: Beginner to advanced · For: Developers, analysts and anyone who wants correct answers from a relational database. No experience needed.

Start the course

Syllabus

Beginner

Complete

After this level you can

  • Create tables with suitable types and constraints, and insert, update and delete rows
  • Write SELECT queries with WHERE, ORDER BY, LIMIT, expressions and functions, and handle NULL correctly
  • Join tables with inner and outer joins, summarise with aggregates, GROUP BY and HAVING, and predict the rows returned
  • Use psql to connect, run scripts and inspect a schema
  1. B1Getting started4 of 4 written · 4 lessonsBrowser and your computer

    Create a database, tables and rows, and query them

    You build: The library database

    1. B1.1Installing PostgreSQL and psql (or the in-browser editor)
    2. B1.2Creating tables: data types, names and DROP TABLE
    3. B1.3Inserting rows: many at once, NULL, DEFAULT and quotes
    4. B1.4Build: the library database
  2. B2Querying one table5 of 5 written · 5 lessonsBrowser and your computer

    Answer questions with filters, sorting, functions and correct NULL handling

    You build: Answering ten questions about the library

    1. B2.1SELECT and WHERE
    2. B2.2ORDER BY, LIMIT and OFFSET
    3. B2.3Expressions and string functions
    4. B2.4NULL, IS NULL and COALESCE
    5. B2.5Build: answer ten questions about the library
  3. B3Joins and aggregates6 of 6 written · 6 lessonsBrowser and your computer

    Combine tables with joins and summarise data with aggregates, GROUP BY and HAVING

    You build: A loans report

    1. B3.1Joins, GROUP BY and NULL: predicting what SQL returns
    2. B3.2Inner joins
    3. B3.3Outer joins
    4. B3.4Aggregates and GROUP BY
    5. B3.5HAVING and FILTER
    6. B3.6Build: a loans report
  4. B4Changing data and schema5 of 5 written · 5 lessonsBrowser and your computer

    Modify rows and evolve the schema safely with constraints and ALTER TABLE

    You build: Evolving the library schema

    1. B4.1UPDATE and DELETE
    2. B4.2RETURNING: the rows you just changed
    3. B4.3Constraints: primary, foreign, unique, check
    4. B4.4ALTER TABLE and defaults
    5. B4.5Build: evolving the library schema

Beginner project

Library database

A script that creates books, members and loans with keys and constraints, loads sample data and answers a set of questions with queries.

Runs in your browserSelf-checked against a rubric

Open the project guide

Intermediate

Coming soon

After this level you can

  • Choose and use PostgreSQL data types correctly: numeric, text, date and time with time zones, JSONB, arrays and ranges
  • Write subqueries, CTEs including recursive ones, set operations and window functions
  • Build views, materialized views, SQL and PL/pgSQL functions, and triggers
  • Use transactions, explain isolation levels and MVCC, and write safe upserts
  1. I1Data types in depthComing soon · 5 lessonsBrowser and your computer

    Pick the right type and avoid type pitfalls with numbers, text, time zones and JSONB

    You build: An event-log schema with arrays and ranges

    1. I1.1Coming soon
    2. I1.2Coming soon
    3. I1.3Coming soon
    4. I1.4Coming soon
    5. I1.5Coming soon
  2. I2Advanced queriesComing soon · 6 lessonsBrowser and your computer

    Write multi-step queries and rankings with subqueries, CTEs, set operations and window functions

    You build: A leaderboard with running totals

    1. I2.1Coming soon
    2. I2.2Coming soon
    3. I2.3Coming soon
    4. I2.4Coming soon
    5. I2.5Coming soon
    6. I2.6Coming soon
  3. I3Views, functions and triggersComing soon · 5 lessonsBrowser and your computer

    Put reusable logic into the database with views, functions and triggers

    You build: An audit-log trigger

    1. I3.1Coming soon
    2. I3.2Coming soon
    3. I3.3Coming soon
    4. I3.4Coming soon
    5. I3.5Coming soon
  4. I4Transactions and concurrencyComing soon · 5 lessonsRuns on your computer

    Keep data correct when many sessions write at once

    You build: A safe money transfer

    1. I4.1Coming soon
    2. I4.2Coming soon
    3. I4.3Coming soon
    4. I4.4Coming soon
    5. I4.5Coming soon

Intermediate project

Shop analytics

Monthly revenue and top customers per month with window functions, a materialized view for a dashboard and an audit trigger on orders.

Runs on your computerSelf-checked against a rubric

Advanced

Coming soon

After this level you can

  • Read EXPLAIN ANALYZE output, choose multicolumn, partial and expression indexes, and load data efficiently with COPY
  • Design a normalised schema with keys, constraints, generated columns, schemas and partitioning
  • Secure a database with roles, privileges and row-level security, prevent SQL injection, and back up and restore
  • Implement full-text search and JSON path queries, and maintain a database with VACUUM and statistics views
  1. A1Indexes and query performanceComing soon · 6 lessonsRuns on your computer

    Find slow queries with EXPLAIN and make them fast with the right indexes

    You build: Speeding up five slow queries

    1. A1.1Coming soon
    2. A1.2Coming soon
    3. A1.3Coming soon
    4. A1.4Coming soon
    5. A1.5Coming soon
    6. A1.6Coming soon
  2. A2Schema designComing soon · 4 lessonsBrowser and your computer

    Design a schema that keeps data consistent

    You build: Designing the shop schema

    1. A2.1Coming soon
    2. A2.2Coming soon
    3. A2.3Coming soon
    4. A2.4Coming soon
  3. A3Security and administrationComing soon · 5 lessonsRuns on your computer

    Control access with roles and row-level security, and recover from mistakes with backups

    You build: Multi-tenant data with row-level security

    1. A3.1Coming soon
    2. A3.2Coming soon
    3. A3.3Coming soon
    4. A3.4Coming soon
    5. A3.5Coming soon
  4. A4Search, JSON and maintenanceComing soon · 4 lessonsRuns on your computer

    Add full-text search and keep a database healthy

    You build: Book search with ranking

    1. A4.1Coming soon
    2. A4.2Coming soon
    3. A4.3Coming soon
    4. A4.4Coming soon

Advanced project

Tuned, secured shop

The shop database with a larger dataset, indexes chosen from EXPLAIN ANALYZE evidence, tenant isolation with row-level security and a tested backup and restore.

Runs on your computerSelf-checked against a rubric

Capstone

Capstone

Booking system database

A schema whose constraints prevent double booking, functions for the booking workflow, reports, roles, search and a portfolio README.

Runs on your computerSelf-checked against a rubric

Sources