SQL Course

A free course where you learn SQL hands-on by running queries in your browser. From the SELECT / WHERE / function basics, through analytical queries with JOINs, subqueries, and window functions, to index design, execution plans, and tuning — 53 articles and 234 exercises take you from beginner to a practical, job-ready level. No setup required.

0/53テキスト終了（0%）

SQL Basics

Creating and Modifying Tables
Learn SQL table definitions from the ground up. Cover CREATE TABLE, PRIMARY KEY, inspecting table definitions, and adding, dropping, and renaming columns with ALTER TABLE — all running live in your browser.
SELECT — Pulling Data Out of a Table
Learn the SQL SELECT statement from the basics. Cover full and partial column selection, the AS alias, row filtering with WHERE, and combining comparison operators with AND / OR — all running live in your browser.
INSERT — Adding Data to a Table
Learn the SQL INSERT statement from the basics. Cover the named-columns form, the omitted-columns shortcut, and bulk multi-row inserts — three patterns illustrated with separate tables and run live in your browser.
UPDATE and DELETE — Updating and Deleting Rows
Learn SQL UPDATE and DELETE from the basics. Cover multi-column updates with SET, narrowed deletes with WHERE, and what happens when you forget WHERE — all running live in your browser.
TRUNCATE — The Fast Bulk Delete and the High Water Mark
An illustrated guide to the difference between DELETE and TRUNCATE: how the High Water Mark (HWM) works, whether you can roll back after running it, the real reason for the speed gap, and how to choose between them on Oracle / PostgreSQL / MySQL.
DISTINCT, ORDER BY, LIMIT — Shaping the Result Set
Learn SQL DISTINCT, ORDER BY, LIMIT, and OFFSET. Cover deduplication, sorting, row limiting, and pagination with OFFSET — all running live in your browser against a CSV-loaded score dataset.
WHERE Deep Dive ① — AND, OR, NOT for Building Compound Conditions
The first WHERE deep-dive article. Cover the truth table for AND / OR / NOT, operator precedence, and how to use parentheses to make evaluation order explicit — all running on a CSV-loaded staff dataset.
WHERE Deep Dive ② — BETWEEN and LIKE for Range and Pattern Filtering
The second WHERE deep-dive article. Cover ranges with BETWEEN, NOT BETWEEN, and prefix / suffix / contains matching with LIKE — combined with ORDER BY / LIMIT, all running on a CSV-loaded staff dataset.
WHERE Deep Dive ③ — IS NULL and IN for NULL and Set Filtering
The third WHERE deep-dive article — filtering on NULL and on sets. Cover three-valued logic, IS NULL / IS NOT NULL, IN / NOT IN, and the NOT IN + NULL gotcha, all on a CSV-loaded customer dataset.
Functions ① — Arithmetic, String Concatenation, and Date Functions
The first of three SQL function articles. Cover the + - * / % arithmetic operators, string concatenation with || and CONCAT, and date functions like current timestamp and year / month extraction — all on a CSV-loaded staff dataset.
Functions ② — String Functions (LENGTH / TRIM / REPLACE / UPPER / SUBSTR)
The second of three SQL function articles. Cover LENGTH for counting characters, TRIM for stripping whitespace, REPLACE for substitutions, UPPER / LOWER for case conversion, and SUBSTR — all on a CSV-loaded staff dataset.
Functions ③ — Math Functions (ROUND / FLOOR / CEILING) and COALESCE
The third of three SQL function articles. Cover the math functions ROUND / FLOOR / CEILING / POWER and COALESCE — which returns the first non-NULL value — using CSV-loaded staff and test-score datasets.
Functions ④ — IF / IIF for Single-Condition Branching
The 4th SQL functions article. Branching a value on one true/false condition with IF / IIF, how it differs from WHERE, combining with LIKE and AND, and stacking ORDER BY / LIMIT — using CSV staff data.
Functions ⑤ — CASE for Multi-Condition Branching
The 5th SQL functions article. The two CASE forms (simple form and searched form), three-plus branches, combining with functions and compound conditions, and pairing with ORDER BY — using CSV staff data.
Functions ⑥ — CASE in Practice — ORDER BY / UPDATE / NULL
The 6th SQL functions article. Assigning an arbitrary order with ORDER BY CASE, conditional bulk updates with UPDATE ~ SET column = CASE, and handling CASE with NULL — using CSV staff and customer data.
SQL Basics Summary — Table Operations, WHERE, and Functions
A wrap-up of the SQL Basics series. Table definition, INSERT, SELECT, UPDATE, DELETE, filtering with WHERE, and transforming values with functions and CASE, organized with diagrams and tables.

SQL Intermediate

Aggregate Functions — COUNT / SUM / AVG / MIN / MAX
Learn the SQL aggregate functions COUNT / SUM / AVG / MIN / MAX hands-on — from whole-table aggregates to combining them with WHERE — using CSV score data, all live in your browser.
GROUP BY and HAVING — Grouped Aggregates
Learn SQL GROUP BY and HAVING: per-group aggregates, filtering groups with HAVING, and how it differs from WHERE — practiced on CSV score data, live in your browser.
Table Joins (1) — INNER JOIN and Aliases
Learn SQL INNER JOIN from the ground up: table aliases, ON vs USING, and joining multiple tables — practiced on employee and department data, live in your browser.
Table Joins (2) — OUTER JOIN, CROSS JOIN, Self-Join
Learn SQL OUTER JOIN (LEFT / RIGHT / FULL), CROSS JOIN, and self-join hands-on — using employee and department data with NULLs, live in your browser.
Table Joins (3) — Stacking WHERE / ORDER BY / CASE on a JOIN
Learn how to build a real-world report by layering WHERE, ORDER BY, and CASE on top of a 3-table JOIN across employee, department, and sales data — live in your browser.
Subqueries ① — Scalar, IN, and Aggregates in WHERE
Learn SQL subqueries from scratch: scalar subqueries, IN, aggregates in WHERE, correlated subqueries, and the NOT IN + NULL pitfall — hands-on in your browser.
Subqueries ② — Derived Tables, SELECT Subqueries, CREATE / INSERT SELECT
Learn SQL derived tables, SELECT-clause subqueries, pairing with CASE, and CREATE / INSERT SELECT — hands-on with employee and sales data in your browser.
EXISTS and Correlated Subqueries
Learn SQL EXISTS and correlated subqueries: EXISTS / NOT EXISTS, how to safely avoid the NOT IN + NULL pitfall with EXISTS, and when to pick EXISTS over IN — practiced on employee and sales data, all running live in your browser.
Set Operations — UNION / INTERSECT / EXCEPT
Learn SQL set operations UNION / UNION ALL / INTERSECT / EXCEPT with employee and contractor data, all running live in your browser.
Composing Queries — Combining JOIN, Subqueries, and UNION
Learn how to grow a single query step by step by combining JOIN, subqueries, and UNION with WHERE / ORDER BY / LIMIT, all running live in your browser.
WITH Clause — Stage Your Query with Named Intermediate Results
Learn the SQL WITH clause (CTE). Carve out intermediate results with names, chain multiple CTEs into multi-stage aggregation, and compare with the subquery version on employee + department data.
WITH RECURSIVE — Generate Sequences and Walk Hierarchies with Recursive Queries
Learn SQL WITH RECURSIVE. Generate a 1..N sequence, run a compound salary projection, walk the employee.manager_id hierarchy in both directions, and pull a whole org tree with a multi-row anchor.
Window Functions ① — OVER and PARTITION BY
Learn the basics of SQL window functions. Walk through how OVER () adds a single column without collapsing rows, and how PARTITION BY emp_id pushes per-employee aggregates onto each detail row of sales data.
Window Functions ② — ORDER BY and Frames (ROWS / RANGE)
Learn ORDER BY and frames (ROWS / RANGE) for window functions. Visualize running totals and moving aggregates with sales data, and see how ROWS and RANGE diverge when ORDER BY values are tied.
Window Functions ③ — Ranking, Analytic Functions, and Execution Order
Learn ROW_NUMBER / RANK / DENSE_RANK / LAG / LEAD / NTILE and SQL's logical execution order. Understand why a rank computed in SELECT can't be referenced from WHERE, and how to work around it with a subquery / CTE.
Transactions and Locks
Learn SQL transactions. Walk through atomicity with BEGIN / COMMIT / ROLLBACK, partial rollback with SAVEPOINT, AUTOCOMMIT behavior, and the ideas behind locks, deadlocks, isolation levels, and the ACID properties.
SQL Intermediate Recap — JOIN, Subqueries, and Window Functions at a Glance
Single-page recap of the SQL Intermediate series. Aggregation, JOIN, subqueries, EXISTS, set operators, CTEs, window functions, and transactions — all tied together with diagrams and a classification table.

SQL Advanced

UPDATE / DELETE with Subqueries and JOINs
Run advanced UPDATE / DELETE with subqueries and JOINs, UPDATE … FROM, and correlated updates against an inventory dataset — all live in your browser.
UPSERT (ON CONFLICT) and Bulk INSERT Applications
Run UPSERT via INSERT … ON CONFLICT ("update if exists, insert if not") and bulk-INSERT applications on an inventory dataset — all live in your browser.
Column Constraints — NOT NULL / UNIQUE / CHECK / PRIMARY KEY
Declare NOT NULL / UNIQUE / CHECK / PRIMARY KEY column constraints, then trigger constraint-violation errors — all live in your browser.
Foreign Key Constraints and Referential Actions (ON DELETE / ON UPDATE)
Foreign key constraints (protecting referential integrity), PRAGMA foreign_keys, and ON DELETE / ON UPDATE CASCADE / SET NULL / RESTRICT — all run live on category/item data in your browser.
Adding/Changing Constraints and Table Redefinition
Add a column with a DEFAULT after the fact, then retrofit UNIQUE / NOT NULL onto an existing table via the rebuild procedure (CREATE new → INSERT SELECT → DROP → RENAME) — all run live in your browser on user data.
Strings, Numbers, Booleans and Implicit Type Conversion
See how declared types differ from storage classes, watch '10' and 123 convert back and forth with typeof(), and compare it all against MySQL's strict types — run live in the browser.
Date/Time Types and STRICT Tables
Learn the three date storage formats (ISO strings, julianday, unixepoch), date()/strftime() for aggregation, and how STRICT tables reject type mismatches — run live in the browser.
Indexes — Composite, Partial, Expression, and UNIQUE
Create indexes with CREATE INDEX, including composite, partial, expression, and UNIQUE variants, then watch EXPLAIN QUERY PLAN switch from FULL SCAN to INDEX SCAN — run live in the browser.
Table Design and Normalization (3-Tier Schema)
Reproduce the update, insert, and delete anomalies on a denormalized table, then split it step by step through 1NF / 2NF / 3NF into a 3-tier schema — design exercises in the browser.
Views (VIEW) — Definition and Use Cases
Define views with CREATE VIEW, learn the three use cases (reuse, authorization, abstraction), and watch a view re-evaluate as soon as the underlying table changes — run live in the browser.
Reading the Execution Plan — EXPLAIN QUERY PLAN
Walk through every term EXPLAIN QUERY PLAN can print: full-table scan vs index search, temp B-tree for sorting, index-only scan (covering index), correlated subqueries, and materialization — see how each one changes when you add the right index.
Statistics and the Optimizer — ANALYZE and Selectivity
See how the database uses the statistics ANALYZE collects to decide between an index search and a full scan. Watch how high-selectivity and low-selectivity columns produce different plans, all in the browser.
Join Algorithms and Join Order
Read nested loop join's outer / inner loops and join order in the execution plan. Add an index on the inner table to flip SCAN to SEARCH, and compare with hash join and sort-merge join used by other databases — illustrated with diagrams.
Index Design — Composite Column Order and Conditions That Use the Index
Learn composite index design: the leftmost prefix rule that only fires when the first column is in the condition, equality-then-range column ordering, and how wrapping a column in a function falls back to SCAN — all confirmed with EXPLAIN QUERY PLAN in the browser.
Index-Only Scan — An Index That Skips the Table
Include every column the query touches in a single index and the round-trip back to the table disappears — that's an index-only scan (also known as a covering index). Confirm it with USING COVERING INDEX in EXPLAIN.
Speeding Up ORDER BY / GROUP BY with Indexes
Avoid temp-sort work in ORDER BY, GROUP BY, and DISTINCT by indexing the right columns. Watch USE TEMP B-TREE disappear from EXPLAIN QUERY PLAN, run live in the browser.
When Indexes Don't Kick In — Patterns and Fixes
Patterns that fall back to SCAN even with an index — wrapping the column in a function or arithmetic, middle-match LIKE, negation — and the rescue plays (expression and partial indexes), run live in the browser.
Rewriting IN / EXISTS and Correlated Subqueries
Choose between IN and EXISTS, remove correlated subqueries, and rewrite queries into the 'keep rows with / without a match' form — run live in the browser with the query plan side by side.
Removing Repeated Work — Subquery Optimization Cases
Three rewrite patterns side by side: fold repeated aggregations into a single CTE, collapse correlated subqueries into a one-shot aggregation, and materialize a heavy derived table once with a CTE — practiced live in the browser.
SQL Advanced Recap — Constraints, Design, and Tuning at a Glance
A recap of the SQL Advanced series. Cross-reference advanced UPDATE/DELETE and UPSERT, constraints and foreign keys, type affinity and STRICT, normalization and views, and index design and tuning from EXPLAIN QUERY PLAN — with diagrams and an article index.