Learn MySQL for free: a 50-lesson course built on a real shop database

Most MySQL tutorials show you a table with three rows and a query that returns two of them. That is fine for the first ten minutes, and then you are stuck, because real databases have customers who have never ordered anything, products that nobody buys, and phone numbers that are missing. I wrote the free MySQL course around that problem. Every lesson uses the same small online-shop database, deliberately a little untidy, so you learn to deal with data as it really looks.

This article explains what the course covers, how it is built, and how I would study it if I were starting today.

Who the course is for

It is for anyone who wants to work with databases: students, people changing careers, and web developers who have been copying SQL from Stack Overflow without being sure why it works. You need a computer you can install software on. You do not need any programming experience. If you are not sure where MySQL fits next to HTML and CSS, the learning roadmap explains the order.

One database, from the first lesson to the last

You install MySQL in the second lesson and load the sample shop database in the fourth. From then on, every example uses it: customers, products, categories, orders, order lines, and a small employees table for hierarchies. Because you always have the same data, you can compare your results with the output printed in the lesson, which is the quickest way to know that you understood the query.

The data has gaps on purpose. Two customers have never ordered, one product has never been sold, some phone numbers are NULL, and one employee has no department. Each gap exists to teach something: LEFT JOIN, NOT EXISTS, IS NULL, and the difference between "no rows" and "an unknown value".

Every result is real

When a lesson shows the output of a query, that output was produced by running the query on a real MySQL 8.4 server. I did not type the tables by hand. A script runs every example in every lesson and compares the output with what is printed on the page, so a typo or a changed result is caught. That checking found real surprises while the course was written, for example that lines is a reserved word you cannot use as an alias without quoting, and that changing a column to NOT NULL fails if it already contains NULL values. Both are now part of the lessons.

What you will learn

The 50 lessons are grouped into nine sections.

1. Getting started. What MySQL is, how to install it, how to connect, and how to load the sample database (What MySQL is and how it works, Install MySQL).

2. Reading data. SELECT, WHERE, AND, OR, sorting with ORDER BY, LIMIT, LIKE, DISTINCT, and a whole lesson on NULL values, the source of more beginner bugs than anything else.

3. Changing data. INSERT, UPDATE, DELETE and upserts, with a safe routine: write the WHERE as a SELECT first and look at the rows before you change anything.

4. Functions. Text, numbers, dates and CASE expressions.

5. Summarising data. GROUP BY, HAVING, aggregates and WITH ROLLUP.

6. Combining tables. INNER JOIN, LEFT JOIN, self joins, UNION, subqueries and EXISTS.

7. Building databases. Creating tables, data types, keys, constraints, foreign keys and normalisation.

8. Faster and safer. Indexes, EXPLAIN, views and transactions.

9. Advanced and practical. Window functions, common table expressions, JSON, stored procedures, triggers, users and privileges, backups, and using MySQL from code without SQL injection.

The last lesson is a project: ten business reports for the online store, such as the best customers, revenue by category and which products never sold.

A taste of the course

Here is the kind of question you will be answering by the end of the joins section: how much revenue does each product category bring in, ignoring cancelled orders?

SELECT cat.name AS category, SUM(oi.quantity * oi.unit_price) AS revenue
FROM order_items oi
JOIN orders o ON o.id = oi.order_id
JOIN products p ON p.id = oi.product_id
JOIN categories cat ON cat.id = p.category_id
WHERE o.status <> 'cancelled'
GROUP BY cat.id, cat.name
ORDER BY revenue DESC;

And the result on the sample database:

+-------------+---------+
| category    | revenue |
+-------------+---------+
| Electronics | 1149.42 |
| Books       |  338.70 |
| Home        |  282.25 |
| Sports      |  201.95 |
+-------------+---------+

The query connects four tables, filters, groups, sums and sorts. It looks long, but it is built from pieces you meet one at a time. By the time you reach it, each clause is familiar.

How to study it

A few habits make a big difference:

1. Type every query yourself. Copying makes the lesson feel easy and teaches you very little.
2. Predict the output before you run it. If you were right, you understood. If you were wrong, you have found exactly what to re-read.
3. Break things on purpose. Remove a WHERE from a SELECT (never from an UPDATE), join on the wrong column, and see what the error or the odd result tells you.
4. Do the exercise at the end of each lesson before opening the answer. Every lesson ends with an exercise and a worked solution.
5. Reset when you want to. Running the setup script again recreates the sample database from scratch, so you can never damage it permanently.

If you only have a few hours, read these first: SELECT, WHERE, NULL values, GROUP BY and INNER JOIN. Those five give you most of what everyday reporting needs.

The exam

When you have finished the lessons, the course has a final exam. The question bank has 40 questions, and each attempt draws 15 of them at random. Some questions ask what a query returns, and those answers were checked against a real server too. Pass it and you can get a certificate with a public link you can share. You do not need an account to read the lessons, but you need a free learner account to track progress and take the exam.

A short history, and what comes next

If you are curious why MySQL looks the way it does, read the history of HTML, CSS and MySQL, which has dated timelines for all three. And if you are not sure what to learn alongside it, the roadmap puts HTML, CSS and MySQL in a sensible order with a pace for each.

Start here: the MySQL course. If you find a mistake, or a lesson that confused you, please tell me. I would like the course to be as clear as it can be.