Learn SQL: Free SQL Tutorial in 99 Lessons
Learn SQL free: 99 lessons in 16 modules, from first programs to interview questions, with exercises judged on MySQL 8 syntax on SQLite 3.49 and a certificate.
- Language: SQL
- Runtime: MySQL 8 syntax on SQLite 3.49
- Modules: 16
- Lessons: 99
- Reading time: about 27 hours
- Cost: Free on every plan, with a certificate
The language every backend, data and placement round asks for — SELECT and filtering, NULLs, functions, GROUP BY, joins, subqueries, CTEs, window functions, data changes, schema design, indexes and transactions — in MySQL 8, with every query judged in your browser's next second.
Syllabus
Module 1: Foundations
what a relational database is — tables, typed columns, rows without order, primary and foreign keys; SQL as a declarative language and its statement families; MySQL beside PostgreSQL, SQLite and SQL Server; the first SELECT; and the logical order a query's clauses run in.
- What a database is — tables, rows and keys 13 min
- What SQL is — a declarative language in five parts 13 min
- MySQL and its cousins — where the dialects differ 14 min
- Your first SELECT — columns, tables and order 13 min
- How a query runs — the logical order of the clauses 14 min
- Checkpoint — Foundations (checkpoint) 25 min
Module 2: Selecting and filtering
aliases for columns and tables and where they work; calculated columns with arithmetic, decimal division, DIV, MOD, ROUND and CONCAT; WHERE with comparisons on numbers, text and dates; AND, OR and NOT with precedence and three-valued logic; IN, BETWEEN and LIKE; and DISTINCT with COUNT(DISTINCT).
- Columns and aliases — naming what a query returns 13 min
- Expressions in SELECT — arithmetic, text and computed columns 14 min
- The WHERE clause — keeping the rows you want 14 min
- AND, OR and NOT — combining conditions 14 min
- IN, BETWEEN and LIKE — lists, ranges and patterns 15 min
- DISTINCT — removing duplicate rows 13 min
- Checkpoint — Selecting and filtering (checkpoint) 25 min
Module 3: Sorting and limiting
ORDER BY with ASC and DESC, aliases, expressions and where NULLs sort; several keys, tie-breakers and custom orders; LIMIT and OFFSET with MySQL's comma form, TOP and FETCH FIRST; offset and keyset pagination; top-N queries and the Nth highest value.
Module 4: NULLs and expressions
NULL as a missing value, UNKNOWN and three-valued logic, IS NULL and MySQL's NULL-safe equality, COALESCE, IFNULL and NULLIF, searched and simple CASE, data types, DIV versus decimal division, implicit conversion and CAST.
Module 5: Functions
String functions to join, clean, cut and pad text; ROUND, CEIL, FLOOR, DIV and MOD; date types, date parts and DATE_FORMAT; date arithmetic with INTERVAL, DATEDIFF and TIMESTAMPDIFF; and IF, IFNULL, NULLIF, GREATEST and FIELD.
- String functions — CONCAT, SUBSTRING, TRIM and friends 14 min
- Numeric functions — ROUND, CEIL, FLOOR, MOD and division 13 min
- Dates and times — types, parts and DATE_FORMAT 14 min
- Date arithmetic — INTERVAL, DATEDIFF and TIMESTAMPDIFF 14 min
- Conditional functions — IF, IFNULL, NULLIF, GREATEST and FIELD 13 min
- Checkpoint — Functions (checkpoint) 25 min
Module 6: Aggregation
COUNT, SUM, AVG, MIN and MAX and how they treat NULL; GROUP BY on columns and expressions; HAVING versus WHERE; conditional aggregation for counts, pivots and rates; GROUP_CONCAT; and the grouping mistakes — ONLY_FULL_GROUP_BY, the three COUNTs and averages of averages.
- Aggregate functions — COUNT, SUM, AVG, MIN and MAX 13 min
- GROUP BY — one summary row per group 14 min
- HAVING — filtering groups 12 min
- Conditional aggregation — SUM(CASE …) and pivots 14 min
- GROUP_CONCAT — a group's values as one string 12 min
- Grouping pitfalls — ONLY_FULL_GROUP_BY, COUNT and averages 14 min
- Checkpoint — Aggregation (checkpoint) 25 min
Module 7: Joins
INNER JOIN and row multiplication, LEFT and RIGHT JOIN with the ON-versus-WHERE trap, FULL OUTER JOIN emulated in MySQL, CROSS JOIN grids, self joins for hierarchies and pairs, chains through junction tables with the fan-out problem, and semi and anti joins.
- INNER JOIN — matching rows across tables 15 min
- LEFT and RIGHT JOIN — keeping the rows without a match 16 min
- FULL OUTER and CROSS JOIN — both sides, and every pair 15 min
- Self joins — a table joined to itself 15 min
- Joining three or more tables 16 min
- Semi joins and anti joins — "has a match" and "has none" 15 min
- Checkpoint — Joins (checkpoint) 25 min
Module 8: Subqueries
Scalar subqueries and the second-highest salary, IN and EXISTS with multi-column IN and ANY/ALL, correlated subqueries for per-group comparisons and the latest row per group, derived tables that aggregate before joining, and why NOT IN returns nothing when a NULL appears.
Module 9: Set operations and CTEs
UNION and UNION ALL for stacking results, INTERSECT and EXCEPT with their NOT EXISTS and anti-join equivalents, the NOT IN null trap, common table expressions for naming the steps of a query, and recursive CTEs for org charts, category paths and date series.
Module 10: Window functions
OVER and PARTITION BY for aggregates that keep every row, ROW_NUMBER, RANK, DENSE_RANK and NTILE with their tie rules, LAG, LEAD and FIRST_VALUE for comparing neighbours, frames with ROWS and RANGE for running totals and moving averages, and the top-N-per-group pattern.
- OVER and PARTITION BY — aggregates that keep every row 15 min
- Ranking functions — ROW_NUMBER, RANK, DENSE_RANK and NTILE 15 min
- LAG and LEAD — comparing a row with its neighbours 14 min
- Running totals and frames — ROWS, RANGE and moving averages 16 min
- Top N per group — the most asked window-function question 14 min
- Checkpoint — Window functions (checkpoint) 25 min
Module 11: Modifying data
INSERT with column lists, many rows and INSERT ... SELECT; UPDATE with expressions, CASE and values from another table; DELETE, TRUNCATE and DROP; MySQL upserts with ON DUPLICATE KEY UPDATE, REPLACE and INSERT IGNORE; and transactions with COMMIT, ROLLBACK and savepoints.
- INSERT — adding rows 14 min
- UPDATE — changing rows 14 min
- DELETE, TRUNCATE and DROP 14 min
- Upserts — insert or update 15 min
- Transactions in practice 14 min
- Checkpoint — Modifying data (checkpoint) 25 min
Module 12: Schema design
CREATE TABLE and choosing MySQL types; PRIMARY KEY, UNIQUE, NOT NULL, DEFAULT, CHECK and FOREIGN KEY; ALTER TABLE and DROP with safe migrations; normalization from 1NF to BCNF; one-to-many, many-to-many and self-referencing relationships; and views.
Module 13: Indexes and performance
B+tree indexes and InnoDB's clustered primary key, selectivity and the cost of every index, composite indexes and the leftmost prefix rule, reading MySQL's EXPLAIN and EXPLAIN ANALYZE, sargable predicates and their rewrites, and covering indexes.
Module 14: Transactions and concurrency
ACID and the InnoDB machinery behind it, the isolation levels and the dirty, non-repeatable and phantom reads they allow, row, gap and next-key locks, SELECT … FOR UPDATE, deadlocks and optimistic locking, MVCC with undo logs and read views, and savepoints.
Module 15: Query patterns
The shapes real reports and interview questions take: gaps and islands, deduplication, pivots, rolling windows, the nth highest value, and date series that fill the missing days.
- Gaps and islands — finding runs and holes in ordered data 16 min
- Deduplication — finding, keeping and deleting duplicate rows 15 min
- Pivoting — turning rows into columns and back 15 min
- Rolling windows — moving averages, sliding sums and period-over-period 16 min
- The nth highest value — LIMIT OFFSET, DENSE_RANK and the NULL answer 15 min
- Date series — calendars from recursive CTEs and filling missing days 15 min
- Checkpoint — Query patterns (checkpoint) 30 min
Module 16: Interview idioms
The six classic SQL interview questions solved two ways, the theory drill from WHERE vs HAVING to ACID, the idiom sheet of question shapes and their queries, and the twelve pitfalls that quietly fail a query.
- The classic SQL questions — six problems every interview reuses 18 min
- The SQL theory drill — the questions asked without a keyboard 18 min
- The idiom sheet — the shapes SQL questions take and the query for each 17 min
- SQL pitfalls — the twelve mistakes that fail interview queries 17 min
- Final checkpoint — Interview idioms (checkpoint) 30 min