SQL tutorial

Learn how to find, combine, and update data in relational databases.

Course contents

Introduction to SQL — Learn what SQL is, what it can do, how databases answer questions, the basic syntax rules, and the database engines you will use.

Databases — What a database is, what makes one relational, the five operations you will use most, and a tour of the eight practice tables.
Data Types — Numeric, string, date, boolean, and binary data types, when to use them, and how to store money values correctly.
Schema Design — Creating, altering and dropping tables, the six constraints that keep data sensible, and the three kinds of relationship between tables.
Database Objects — Generated id values, views, the catalog that describes the database itself, and when to normalize or denormalize.
Type Conversion — Converting values between types with CAST, what implicit conversion does behind your back, and how to convert safely when the data is messy.

SELECT — Choosing columns, naming them with aliases, calculating new ones, and why SQL runs its clauses in a different order from the one you write.
Data Filtering — WHERE, the comparison operators, combining conditions with AND, OR and NOT, and the three shortcuts IN, BETWEEN and LIKE.
NULL Handling — What NULL means, why = NULL never works, three-valued logic, and what NULL does to aggregates and joins.
Sorting — ORDER BY, DISTINCT, LIMIT and OFFSET, building page-by-page results, and deciding where NULL values land.

Functions — String and numeric functions, CASE expressions, the four NULL-handling functions, and a reference table for all of them.
Date and Time Functions — Formatting and parsing dates, extracting parts, truncating for reports, date arithmetic, and why timestamps belong in UTC.

Aggregation Basics — Aggregate functions, GROUP BY, HAVING versus WHERE, conditional aggregation, and joining rows into a single string.
Advanced Aggregation — Pivoting rows into columns, subtotals and grand totals with ROLLUP and CUBE, GROUPING SETS, and the portable way to write all of them.

Joins — Inner, left, right, full, cross and self joins, the anti-join pattern for finding missing rows, and the four ways a join goes wrong.
Set Operations — Stacking query results with UNION and UNION ALL, finding common rows with INTERSECT, and finding differences with EXCEPT.
Subqueries — Scalar, multi-row, correlated and nested subqueries, IN versus EXISTS, and why NOT IN breaks when the list contains a NULL.
CTEs — Naming query steps with WITH, chaining CTEs into a pipeline, and using recursive CTEs to walk org charts and other hierarchies.

Window Functions — OVER and PARTITION BY, ranking and numbering rows, reading the previous or next row, window frames, running totals and moving averages.
Analytical Patterns — A cookbook of reporting queries: duplicates, latest row per group, share of total, growth, calendars, cohorts, funnels, percentiles and outliers.

Data Manipulation — INSERT, INSERT SELECT, UPDATE and DELETE, plus updating and deleting based on data held in another table.
Transactions — TRUNCATE versus DELETE, upserts with ON CONFLICT, getting changed rows back with RETURNING, and ACID transactions with COMMIT and ROLLBACK.
Handling JSON — Storing JSON in a column, reading values out with paths, changing part of a document, and deciding when a value belongs in a real column instead.

Database Operations — Isolation levels, locking and deadlocks, how indexes work and what they cost, reading a query plan, SQL injection, and backup, restore and migration.

SQL Cheat Sheet — Every difference between SQLite, MySQL, PostgreSQL, SQL Server and DuckDB that you will actually hit, compared side by side.
SQLite — Dynamic typing and STRICT tables, rowid, the functions and date modifiers no other engine has, PRAGMA, and what SQLite genuinely cannot do.
PostgreSQL — DISTINCT ON, arrays, JSONB, generate_series, the index types beyond B-tree, and the strictness that makes PostgreSQL code portable.
SQL Server — Paging with TOP and FETCH, APPLY, MERGE, OUTPUT, the T-SQL date functions, and when to use a temp table over a table variable.
MySQL — What arrived in 8.0, the permissive defaults worth turning off, upserts and bulk writes, multi-table UPDATE and DELETE, and storage engines.
DuckDB — Querying CSV and Parquet files where they sit, EXCLUDE and GROUP BY ALL, QUALIFY, lists and structs, ASOF joins, and where DuckDB is the wrong answer.