Free through lesson 3

Database Fundamentals with MySQL

A course for getting out of the state where you patch copy-pasted SQL until it works. SELECT and WHERE, NULL and three-valued logic, the difference between JOIN and LEFT JOIN, window functions, transactions and locks, normalisation, indexes and EXPLAIN. Thirty lessons that take you to explaining why a query returns what it returns.

Start from lesson 1 Unlock every course

Curriculum

The 30 lessons are grouped into six chapters. Working through them in order is the best way, but you are welcome to read only the part you are stuck on right now. Note: SQL cannot run inside the browser. Install MySQL 8.4 (or 8.0), load the sample data from lesson 1, and try it there.

Chapter 1 — Getting MySQL running (lessons 1–3)

Connecting, creating a database and tables, and putting rows in and getting them back. The sample data you build here stays with you until lesson 30.

Connecting to MySQL

The server and the client are different things — and SELECT works with no tables at all.

Free

Creating a database and tables

For each column, CREATE TABLE decides just three things: type, nullability, and default.

Free

Putting data in and getting it out

Four statements — and forgetting the WHERE means every row.

Free

Chapter 2 — Getting SELECT under control (lessons 4–8)

Choosing columns and aliasing them, WHERE conditions, LIKE patterns, and NULL. You go down to three-valued logic to see why "= NULL" never matches, then finish with ordering and pagination.

The basics of SELECT

SELECT * breaks application code, and DISTINCT applies to the combination of columns.

Filtering with WHERE

Parenthesise whenever you write OR — and yes, 'abc' = 0 is true.

LIKE and pattern matching

Searching for '10%' also finds "10-day trial" — escape the wildcard with a backslash.

Working with NULL

One NULL inside NOT IN turns 38 rows into none — silently.

Ordering and pagination

Without a unique tie-breaker, page two shows you rows from page one.

Chapter 3 — Aggregating and joining (lessons 9–15)

From the fact that COUNT skips NULL through GROUP BY and HAVING, and then into JOIN. INNER against LEFT, what happens when ON and WHERE get mixed up, self joins, subqueries and CTEs.

Aggregate functions

On zero rows COUNT gives 0 while SUM and AVG give NULL — and AVG(condition) yields a ratio in one go.

GROUP BY and HAVING

Do not silence ERROR 1055 by changing sql_mode — the problem is the query.

Joining tables

Forty courses joined to 2,121 enrolments gives 2,121 rows — that is correct.

Outer joins with LEFT JOIN

Your LEFT JOIN returns the same count as an INNER JOIN — because the condition is in the WHERE.

Multiple joins and self joins

Join two one-to-many relations and revenue comes out six times too high.

Subqueries

Negate with NOT EXISTS — NOT IN collapses the moment a NULL appears.

UNION, CTEs and views

Three levels of nesting is the signal to rewrite with WITH — then it reads top to bottom.

Chapter 4 — Functions and changing data (lessons 16–19)

Writing "rank within each group" in a single statement with window functions, walking into the traps around dates and times, INSERT in practice, and running UPDATE and DELETE safely.

Window functions

"Top two per category" cannot be written with GROUP BY — that is what PARTITION BY is for.

Dates and times

BETWEEN '2026-08-01' AND '2026-08-31' loses everything after midnight on the 31st.

INSERT in practice

REPLACE INTO resets the columns you did not write — and changes the id.

UPDATE and DELETE

A statement with no WHERE rewrites every row — and nothing asks you to confirm.

Chapter 5 — Designing so it does not break (lessons 20–24)

Transactions and locks, and how to read a deadlock. Then choosing data types, primary and foreign keys, and normalisation — the decisions that are hardest to change later.

Transactions

ROLLBACK did nothing? autocommit already committed your statement.

Locks and deadlocks

A deadlock is not a bug — it is something that happens. Implement a retry.

Choosing data types

Store money as FLOAT and <code>WHERE price = 0.1</code> stops finding the row.

Primary keys and foreign keys

A UNIQUE column accepts any number of NULLs — no error at all.

Normalization

Columns named item1_name, item2_name, item3_name — what happens on the fourth item?

Chapter 6 — Speed and operations (lessons 25–30)

When an index helps and when it cannot, how to read EXPLAIN, character sets and collations, privileges and backups, and connecting from an application. It ends by running design through to implementation.

Indexes

555,600 rows: 55.9ms → 0.16ms. About 340×. Let's actually measure it.

Reading query plans with EXPLAIN

EXPLAIN prints a lot of columns. You only need to read four of them.

Character sets and collations

With MySQL's default settings, <code>'ハハ' = 'ババ'</code> evaluates to true.

User privileges and backups

Is your app connecting as root? One bug and a table is gone.

Using MySQL from an application

Concatenate <code>' OR '1'='1</code> into your SQL and every row comes back.

Putting it together: design to implementation

Design a room-booking system. MySQL has no constraint for overlapping time ranges.

Once you have finished all 30 lessons, see the same material through PostgreSQL in Database Fundamentals with PostgreSQL. To drive it from an application, continue with Django or FastAPI. Membership unlocks every course.