Chapter 01

Database Fundamentals

What a database is, the relational model, the parts of SQL, how MySQL is put together, which version you are on, how to connect, and the sample data every other chapter uses.

8 topics

Parts marked Advanced are extra depth. Skip them on a first read or a quick revision.

#What a database is and why we use one

Added

A database is an organised collection of data, and a database management system (DBMS) is the program that stores it, guards it and answers questions about it. In everyday speech "the database" means both. MySQL, PostgreSQL, MongoDB and Redis are all DBMSs; the ch01 schema with its customers and orders is a database inside one of them.

You could keep the same data in JSON files and write code to read them. That works until two requests write at once and one overwrites the other, the process crashes halfway through a write and leaves a half-written file, someone stores an order for a customer who doesn't exist, or you need "every customer in Toronto who bought a laptop" and have to write and maintain a loop for it. A DBMS exists to solve those problems once, for every application: safe concurrent access, durability after a crash, rules that the data must obey, and a query language that lets you say what you want instead of how to fetch it.

A good mental model is a librarian with a strict rulebook. You never walk into the stacks yourself. You hand over a request ("all the books by this author"), the librarian works out the fastest way to find them, refuses to shelve a book that breaks the rules, keeps a log so nothing is lost if the lights go out, and makes sure two people borrowing at the same moment don't end up with the same copy.

SQL

SELECT DISTINCT p.name AS product
FROM customers c
JOIN orders o       ON o.customer_id = c.id
JOIN order_items oi ON oi.order_id   = o.id
JOIN products p     ON p.id          = oi.product_id
WHERE c.name = 'Rohit'
ORDER BY p.name;

Result

+---------------------------------------+
| product                               |
+---------------------------------------+
| Designing Data-Intensive Applications |
| SQL Performance Explained             |
| ThinkBook 14                          |
| USB-C Charger                         |
+---------------------------------------+
4 rows in set (0.000 sec)

What’s happening

The question is "which products has Rohit bought?". The answer lives in four tables: the customer is in customers, his orders in orders, the lines of each order in order_items and the product names in products.
The query only describes the result: start from customers, follow each foreign key to the next table, keep the rows where the customer is Rohit. It says nothing about loops, files or which table to read first.
MySQL's optimizer decides the plan: which table to start from, which indexes to use, in which order to join. The answer is Rohit's two orders (1 and 2), their four order lines and the four product names behind them. With a million customers the plan might be completely different, and the query text would not change.
DISTINCT removes repeats (if he had bought the same product twice it would appear once) and ORDER BY sorts the names alphabetically. Without ORDER BY the row order is not guaranteed.
This is the main thing a database gives you over files: one declarative question replaces a hand-written loop, and the engine stays responsible for doing it efficiently.

AdvancedIntegrity: a duplicate email is refused

The other half of the job is protection. The database refuses data that breaks its rules, whoever sends it:

SQL

INSERT INTO customers (name, email, city, created_at)
VALUES ('Rohit V', 'rohit@example.com', 'Ottawa', NOW());

Result

ERROR 1062 (23000): Duplicate entry 'rohit@example.com' for key 'customers.email'

What’s happening

The customers table was created with email VARCHAR(100) NOT NULL UNIQUE, so MySQL keeps a unique index on email.
Before writing the new row it looks up rohit@example.com in that index and finds customer 1 already there.
The whole statement is rejected with error 1062 and SQLSTATE 23000 (integrity constraint violation). Nothing is written.
The rule is enforced inside the database, so it holds for every application, script and person that connects, not just the one that remembered to check first.

AdvancedAtomicity: rolling back a stock change

And it can make several changes all or nothing with a transaction:

SQL

START TRANSACTION;
UPDATE products SET stock = stock - 1 WHERE id = 1;
SELECT id, name, stock FROM products WHERE id = 1;
ROLLBACK;
SELECT id, name, stock FROM products WHERE id = 1;

Result

Query OK, 0 rows affected (0.000 sec)

Query OK, 1 row affected (0.000 sec)
Rows matched: 1  Changed: 1  Warnings: 0

+----+--------------+-------+
| id | name         | stock |
+----+--------------+-------+
|  1 | ThinkBook 14 |    11 |
+----+--------------+-------+

Query OK, 0 rows affected (0.001 sec)

+----+--------------+-------+
| id | name         | stock |
+----+--------------+-------+
|  1 | ThinkBook 14 |    12 |
+----+--------------+-------+

What’s happening

START TRANSACTION opens a unit of work. Changes made from now on are provisional.
The UPDATE takes the ThinkBook's stock 12 → 11. Inside this session the change is visible straight away, so the first SELECT shows 11.
ROLLBACK throws the whole unit away. InnoDB uses its undo log to put the row back as it was.
The second SELECT shows 12 again. If the session had crashed instead of rolling back, the result would be the same: an uncommitted change never survives.
This is atomicity, the "A" in ACID. Transactions get their own chapter (Transactions and ACID).

AdvancedFiles vs a DBMS, problem by problem

What a DBMS gives you, compared with files you manage yourself:

Problem	Files and your own code	A DBMS
Two writers at once	Last write wins, data lost	Locks and MVCC keep both safe
Crash mid-write	Half-written file	Write-ahead (redo) log replays or discards it
Bad data	Every app must check	Constraints (`UNIQUE`, `FOREIGN KEY`, `CHECK`) checked centrally
New question	Write a new loop	Write a new query; the optimizer plans it
Large data	Read everything	Indexes find rows without scanning
Who may see what	Filesystem permissions	Users, roles and `GRANT` per table

AdvancedWorkloads: OLTP and OLAP

#The relational model: tables, rows and keys

Added

The relational model (Edgar Codd, 1970) says: store data as relations, which SQL calls tables. A table has a fixed set of named, typed columns and holds any number of rows; each row is one fact ("customer 4 is Sofia, email sofia@example.com, city unknown"). Tables are linked by values, not by pointers: an order doesn't contain its customer, it contains the customer's id.

Think of a set of spreadsheets that refer to each other by ID, with a strict rule-checker on top. Each sheet has one kind of thing. Every row has a unique ID (the primary key). When one sheet needs to point at a row in another, it stores that row's ID in a column (a foreign key), and the rule-checker refuses IDs that don't exist.

Three properties come from the theory and still matter in practice. A table is a set of rows, so there is no built-in row order (you ask for one with ORDER BY). Each row is identified by its key, not by its position. And each cell holds one value of the column's type (no lists inside a cell; that's first normal form, covered in First, second and third normal form).

The vocabulary, because interviewers mix the formal and the everyday terms:

Relational theory	SQL / everyday	In the sample schema
Relation	Table	`customers`
Tuple	Row, record	Sofia's row
Attribute	Column, field	`email`
Domain	Data type plus constraints	`VARCHAR(100) NOT NULL`
Cardinality	Number of rows	6 customers
Degree	Number of columns	5 columns
Candidate key	Any column set that could identify a row	`id`, and also `email`
Primary key	The candidate key you chose	`id`
Foreign key	Column that references another table's key	`orders.customer_id`

SQL

SHOW CREATE TABLE order_items\G

Result

*************************** 1. row ***************************
       Table: order_items
Create Table: CREATE TABLE `order_items` (
  `order_id` int NOT NULL,
  `product_id` int NOT NULL,
  `quantity` int NOT NULL,
  `unit_price` decimal(10,2) NOT NULL,
  PRIMARY KEY (`order_id`,`product_id`),
  KEY `product_id` (`product_id`),
  CONSTRAINT `order_items_ibfk_1` FOREIGN KEY (`order_id`) REFERENCES `orders` (`id`),
  CONSTRAINT `order_items_ibfk_2` FOREIGN KEY (`product_id`) REFERENCES `products` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.000 sec)

What’s happening

SHOW CREATE TABLE asks MySQL to print the table's definition as it stores it, including things it added itself. \G prints the result vertically, one column per line, which suits long values.
The four columns are the table's attributes, each with a type: int for IDs and quantities, decimal(10,2) for money. NOT NULL means every row must have a value.
PRIMARY KEY (order_id, product_id) is a composite key: one row per product per order. The pair must be unique, so the same product can't appear twice in one order (you raise quantity instead).
The two CONSTRAINT … FOREIGN KEY lines are the links: order_id must match an orders.id, product_id a products.id. MySQL named them order_items_ibfk_1 and _2 because the schema didn't.
KEY product_id (product_id) wasn't in the original CREATE TABLE. InnoDB needs an index on every foreign key column so it can check and enforce the link quickly, and created one. order_id didn't need its own because it is the first column of the primary key.
The last line shows defaults filled in: storage engine InnoDB and the utf8mb4 character set with the utf8mb4_0900_ai_ci collation (accent- and case-insensitive comparisons).

AdvancedBreaking the keys: errors 1452, 1451 and 1062

The keys are rules, and MySQL enforces them. Each of these statements breaks one:

SQL

INSERT INTO orders (customer_id, status, ordered_at) VALUES (99, 'pending', NOW());
DELETE FROM customers WHERE id = 1;
INSERT INTO order_items VALUES (1, 1, 5, 899.00);

Result

ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`ch01`.`orders`, CONSTRAINT `orders_ibfk_1` FOREIGN KEY (`customer_id`) REFERENCES `customers` (`id`))
ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (`ch01`.`orders`, CONSTRAINT `orders_ibfk_1` FOREIGN KEY (`customer_id`) REFERENCES `customers` (`id`))
ERROR 1062 (23000): Duplicate entry '1-1' for key 'order_items.PRIMARY'

What’s happening

The first INSERT points an order at customer 99. There is no such customer, so the child row (the order) is rejected with 1452.
The DELETE tries to remove customer 1, but orders 1 and 2 still point at him. Deleting the parent would leave orphans, so it is rejected with 1451. (You can ask for ON DELETE CASCADE or SET NULL instead; see Foreign keys and referential actions.)
The last INSERT adds product 1 to order 1 again. The composite primary key (1, 1) already exists, so it fails with 1062. MySQL shows the key value as '1-1', the two parts joined with a dash.
Together these are entity integrity (every row has a unique, non-null primary key) and referential integrity (every foreign key points at a real row), the two rules the relational model is built on.

AdvancedPitfall: tables have no row order

Which delete succeeds?

Zara (6) has never ordered; Noah (5) has one order. Inside a transaction (rolled back afterwards), what happens to each statement?

SQL

DELETE FROM customers WHERE id = 6;
DELETE FROM customers WHERE id = 5;

Show answer

The first succeeds, the second fails:

Result

Query OK, 1 row affected (0.000 sec)

ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (`ch01`.`orders`, CONSTRAINT `orders_ibfk_1` FOREIGN KEY (`customer_id`) REFERENCES `customers` (`id`))

What’s happening

The foreign key orders.customer_id → customers.id only protects customers that are referenced. No order points at Zara, so deleting her leaves nothing orphaned and it's allowed.
Order 7 points at Noah. Deleting him would leave order 7 referring to a customer that doesn't exist, so InnoDB rejects it with 1451. The constraint is checked per row, at the moment of the change.
The default referential action is RESTRICT (MySQL also calls it NO ACTION; InnoDB treats them the same). ON DELETE CASCADE would delete order 7 too, and SET NULL would need a nullable customer_id.
I ran both inside START TRANSACTION … ROLLBACK, so Zara is back afterwards and every chapter still sees six customers.

#SQL: the language and its parts

Added

SQL (Structured Query Language, said "S-Q-L" or "sequel") is the language every relational database speaks. It is declarative: you describe the result you want and the database decides how to compute it. It's standardised (ISO/IEC 9075, revised most recently in 2023), but every product speaks a dialect with its own extras, and MySQL's is one of the more relaxed ones.

SQL statements fall into families, and interviews often ask you to name them. The useful mental model is the life of a table: you define it (DDL), fill and change it (DML), ask it questions (DQL), group changes so they succeed or fail together (TCL), and decide who may do what (DCL).

SQL

-- DDL: define structure
CREATE TABLE notes (
  id   INT AUTO_INCREMENT PRIMARY KEY,
  body VARCHAR(200) NOT NULL
);

-- DML: change data
INSERT INTO notes (body) VALUES ('buy milk'), ('call Maya');
UPDATE notes SET body = 'buy oat milk' WHERE id = 1;

-- DQL: read data
SELECT id, body FROM notes;

-- TCL: group changes into a transaction
START TRANSACTION;
DELETE FROM notes;
ROLLBACK;
SELECT COUNT(*) AS notes_left FROM notes;

-- DCL: control access
CREATE ROLE reporting;
GRANT SELECT ON ch01.* TO reporting;
SHOW GRANTS FOR reporting;

Result

Query OK, 0 rows affected (0.019 sec)

Query OK, 2 rows affected (0.002 sec)
Records: 2  Duplicates: 0  Warnings: 0

Query OK, 1 row affected (0.002 sec)
Rows matched: 1  Changed: 1  Warnings: 0

+----+--------------+
| id | body         |
+----+--------------+
|  1 | buy oat milk |
|  2 | call Maya    |
+----+--------------+

Query OK, 0 rows affected (0.000 sec)

Query OK, 2 rows affected (0.000 sec)

Query OK, 0 rows affected (0.001 sec)

+------------+
| notes_left |
+------------+
|          2 |
+------------+

Query OK, 0 rows affected (0.003 sec)

Query OK, 0 rows affected (0.002 sec)

+---------------------------------------------+
| Grants for reporting@%                      |
+---------------------------------------------+
| GRANT USAGE ON *.* TO `reporting`@`%`       |
| GRANT SELECT ON `ch01`.* TO `reporting`@`%` |
+---------------------------------------------+

What’s happening

DDL (data definition language: CREATE, ALTER, DROP, TRUNCATE, RENAME) changes structure. CREATE TABLE notes reports 0 rows affected because it touches no data. In MySQL every DDL statement also commits any open transaction.
DML (data manipulation language: INSERT, UPDATE, DELETE, also REPLACE and LOAD DATA) changes rows. The multi-row INSERT adds two rows with ids 1 and 2 from AUTO_INCREMENT; the UPDATE matches and changes one.
DQL (data query language) is just SELECT. Many textbooks count it as part of DML; say both when asked.
TCL (transaction control: START TRANSACTION/BEGIN, COMMIT, ROLLBACK, SAVEPOINT) groups statements. The DELETE removes both notes (2 rows affected), ROLLBACK undoes it, and the count is back to 2.
DCL (data control language: GRANT, REVOKE, and in MySQL CREATE USER and CREATE ROLE) manages permissions. The role gets SELECT on every table in ch01; USAGE just means "may exist, no global privileges".

AdvancedCase sensitivity of table names

Keywords are case-insensitive (select = SELECT), and so are column names. Table and database names depend on the operating system, because MySQL stores each table as files and asks the filesystem. On Linux (this sandbox, and almost every production server) they are case-sensitive:

SQL

SELECT * FROM Customers;
select NAME from customers where ID = 1;
SELECT @@lower_case_table_names;

Result

ERROR 1146 (42S02): Table 'ch01.Customers' doesn't exist
+-------+
| NAME  |
+-------+
| Rohit |
+-------+
+--------------------------+
| @@lower_case_table_names |
+--------------------------+
|                        0 |
+--------------------------+

What’s happening

Customers with a capital C is looked up as a file name on a case-sensitive Linux filesystem and isn't found → error 1146.
The second query uses odd capitals for the keywords and the columns NAME and ID but the correct table name, and it works. The result header echoes the column as you typed it (NAME).
lower_case_table_names = 0 is the Linux default: names are stored and compared exactly as written. macOS defaults to 2 and Windows to 1, which is how code developed on a laptop breaks on a Linux server. Write table names in lowercase everywhere.

AdvancedReserved words and identifier quoting

Reserved words (order, group, key, select) can't be used as bare names. MySQL quotes identifiers with backticks; standard SQL uses double quotes:

SQL

CREATE TABLE t (order INT);
CREATE TABLE t (`order` INT);
SELECT "hello" AS greeting;

Result

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'order INT)' at line 1
Query OK, 0 rows affected (0.010 sec)
+----------+
| greeting |
+----------+
| hello    |
+----------+

What’s happening

order is a reserved word (ORDER BY), so the parser stops at it with the generic 1064 syntax error. The near '…' part shows where it gave up, which is the most useful bit of any 1064.
Backticks turn it into an identifier and the table is created. Better still, don't name columns after keywords.
In MySQL, double quotes make a string, so "hello" is just text. In standard SQL and PostgreSQL, double quotes make an identifier: the same query on PostgreSQL 18 fails with ERROR: column "hello" does not exist. Use single quotes for strings and your SQL works on both. (MySQL switches to the standard meaning if you enable the ANSI_QUOTES SQL mode.)

What does this return?

SQL

SELECT 5 --1;

Show answer

It returns 6.

What’s happening

In MySQL, -- starts a comment only when it is followed by a space (or a control character). --1 has no space.
So the parser reads 5 - -1: five minus negative one.
5 - (-1) = 6, and the column is named after the expression text, 5 --1.
Write SELECT 5 -- 1 (with a space) and the comment starts, the query is just SELECT 5, and you get 5. MySQL also accepts # to end of line and /* … */ comments. I ran both versions on 9.7 to confirm.

#SQL vs NoSQL at a glance

Added

SQL databases (MySQL, PostgreSQL, SQL Server, Oracle) store data in tables with a fixed schema, link them with keys and joins, and give you transactions across any rows. NoSQL is an umbrella for everything else, grouped by data model: document stores (MongoDB) keep JSON-like documents, key-value stores (Redis, DynamoDB) map a key to a value, wide-column stores (Cassandra) keep sparse rows partitioned across machines, and graph databases (Neo4j) store nodes and edges.

The mental model is where the joining happens. A relational database stores each fact once and joins at read time: an order references a customer and products, and a query stitches them together. A document database usually joins at write time: you store the order with its items (and maybe a copy of the customer's name) inside one document, so one read returns everything, at the cost of duplicated data you must keep in step.

	Relational (MySQL, PostgreSQL)	Document (MongoDB)	Key-value (Redis)
Data model	Tables, rows, foreign keys	JSON-like documents, nested	Key → value (strings, hashes, lists, sets)
Schema	Declared up front, enforced	Flexible, optional validation	None
Relationships	Joins at read time	Embed, or `$lookup`	Application handles them
Transactions	Multi-row ACID, always	Single-document atomic; multi-document since 4.0	`MULTI`/`EXEC`, Lua scripts
Scaling writes	Mostly vertical; sharding is extra work	Built-in sharding	Cluster mode, in-memory
Best at	Consistent, related data; ad-hoc questions	Aggregates read as a whole; evolving shapes	Caching, counters, sessions, queues

Here's order 1 both ways. In MySQL it is spread over four tables and assembled by a query:

SQL

SELECT o.id AS order_id, c.name AS customer, o.status,
       p.name AS product, oi.quantity, oi.unit_price
FROM orders o
JOIN customers c    ON c.id = o.customer_id
JOIN order_items oi ON oi.order_id = o.id
JOIN products p     ON p.id = oi.product_id
WHERE o.id = 1;

Result

+----------+----------+---------+---------------+----------+------------+
| order_id | customer | status  | product       | quantity | unit_price |
+----------+----------+---------+---------------+----------+------------+
|        1 | Rohit    | shipped | ThinkBook 14  |        1 |     899.00 |
|        1 | Rohit    | shipped | USB-C Charger |        2 |      29.00 |
+----------+----------+---------+---------------+----------+------------+
2 rows in set (0.000 sec)

What’s happening

The order header (status) is in orders, the customer's name in customers, the lines in order_items and product names in products. Each fact is stored once: rename a product and every order shows the new name.
The joins follow the keys: order → its customer, order → its lines, line → its product.
The result is flat: one row per order line, with the order and customer columns repeated on each. The application regroups them into "one order with two items" if it needs that shape.
That's the relational trade: no duplication and any question can be asked later, paid for with joins at read time.

AdvancedThe same order as a MongoDB document

In MongoDB 9.0 the natural shape is one document with the lines embedded:

mongosh

db.orders.insertOne({
  _id: 1,
  customer: { id: 1, name: "Rohit", city: "Toronto" },
  status: "shipped",
  orderedAt: ISODate("2025-02-01T10:00:00Z"),
  items: [
    { product: "ThinkBook 14", quantity: 1, unitPrice: Decimal128("899.00") },
    { product: "USB-C Charger", quantity: 2, unitPrice: Decimal128("29.00") }
  ]
});
db.orders.findOne({ _id: 1 });
db.orders.find({ "items.product": "USB-C Charger" }, { _id: 1, "customer.name": 1 });

Result

{ acknowledged: true, insertedId: 1 }
{
  _id: 1,
  customer: { id: 1, name: 'Rohit', city: 'Toronto' },
  status: 'shipped',
  orderedAt: ISODate('2025-02-01T10:00:00.000Z'),
  items: [
    {
      product: 'ThinkBook 14',
      quantity: 1,
      unitPrice: Decimal128('899.00')
    },
    {
      product: 'USB-C Charger',
      quantity: 2,
      unitPrice: Decimal128('29.00')
    }
  ]
}
[ { _id: 1, customer: { name: 'Rohit' } } ]

What’s happening

There is no CREATE TABLE: the collection orders springs into existence on the first insert, and the document's shape is whatever you send. (You can add a JSON Schema validator later.)
The customer's name and city are copied into the order, and the items are an array inside it. findOne returns the whole order, already nested, in one read with no join.
The second query reaches into the array: "items.product": "USB-C Charger" matches if any element has that product. The projection returns only _id and customer.name.
The cost is the copy: if Rohit moves to Ottawa, this order still says Toronto unless you update it too. Sometimes that's what you want (an order should record the address it was shipped to); sometimes it's a bug. MongoDB gets its own part of the site starting at What MongoDB is and when to use it.

AdvancedWhy SQL vs NoSQL is a blurry line

The labels are older than the products. MySQL and PostgreSQL have JSON columns and can index inside them (see JSON columns); MongoDB has had multi-document ACID transactions since 4.0 and $lookup joins since 3.2. The useful question isn't "SQL or NoSQL?" but "what shape is my data, how will I query it, and what consistency do I need?" The closing chapter of this site starts with SQL vs NoSQL: the real trade-offs, which is about exactly that.

#How MySQL is built: server, storage engines and InnoDB

Added

MySQL is two layers. The server layer handles everything about SQL: accepting connections, checking passwords and privileges, parsing your statement, the optimizer that picks a plan, and the executor that runs it. Underneath sits a storage engine that actually stores and retrieves rows. The two talk through a fixed internal API ("give me the next row from this index", "write this row"), so engines are pluggable: different tables can even use different engines.

A good picture is a restaurant. The server layer is the front of house: it greets you (connection), checks your booking (authentication), takes your order and makes sure the kitchen understands it (parser), and the head chef decides in which order to prepare things (optimizer). The storage engine is the kitchen and pantry: how the food is stored, kept fresh and fetched. You can swap kitchens without changing the menu.

The server layer understands SQL; the storage engine stores rows. InnoDB has been the default since MySQL 5.5.

SQL

SHOW ENGINES;

Result

+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| Engine             | Support | Comment                                                        | Transactions | XA   | Savepoints |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| ndbcluster         | NO      | Clustered, fault-tolerant tables                               | NULL         | NULL | NULL       |
| MEMORY             | YES     | Hash based, stored in memory, useful for temporary tables      | NO           | NO   | NO         |
| InnoDB             | DEFAULT | Supports transactions, row-level locking, and foreign keys     | YES          | YES  | YES        |
| PERFORMANCE_SCHEMA | YES     | Performance Schema                                             | NO           | NO   | NO         |
| MyISAM             | YES     | MyISAM storage engine                                          | NO           | NO   | NO         |
| FEDERATED          | NO      | Federated MySQL storage engine                                 | NULL         | NULL | NULL       |
| ndbinfo            | NO      | MySQL Cluster system information storage engine                | NULL         | NULL | NULL       |
| MRG_MYISAM         | YES     | Collection of identical MyISAM tables                          | NO           | NO   | NO         |
| BLACKHOLE          | YES     | /dev/null storage engine (anything you write to it disappears) | NO           | NO   | NO         |
| CSV                | YES     | CSV storage engine                                             | NO           | NO   | NO         |
| ARCHIVE            | YES     | Archive storage engine                                         | NO           | NO   | NO         |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+

What’s happening

SHOW ENGINES lists every storage engine compiled into this MySQL 9.7.2 server and whether it can be used (YES), is the default (DEFAULT) or isn't available (NO).
InnoDB is the default and the only general-purpose engine here with Transactions, XA (distributed transactions) and Savepoints all YES. Its comment sums it up: transactions, row-level locking and foreign keys.
MyISAM, the default before MySQL 5.5, has NO in all three columns: no transactions, table-level locks only, no foreign keys, and no crash recovery. It survives for compatibility.
The rest are special-purpose: MEMORY keeps tables in RAM (gone on restart), CSV stores a table as a CSV file, BLACKHOLE accepts writes and keeps nothing (used in replication tricks), PERFORMANCE_SCHEMA is the engine behind the monitoring tables. ndbcluster is MySQL NDB Cluster, a separate product.
The takeaway: when people say "MySQL" behaviour (MVCC, row locks, crash safety) they nearly always mean InnoDB's behaviour.

AdvancedInside InnoDB: buffer pool, logs and pages

How a query reaches the disk with InnoDB, in the order things happen:

Buffer pool: an in-memory cache of 16 KB pages (data and index). Reads are served from it when the page is cached; changes are made to the page in memory first. It's the single most important memory setting.
Redo log (write-ahead log): before a commit returns, InnoDB writes a compact record of the change to the redo log and flushes it to disk. Dirty pages in the buffer pool are written to the data files later. After a crash, InnoDB replays the redo log, so committed changes are never lost. That's the "D" in ACID.
Undo log: the old version of every changed row, kept so a ROLLBACK can restore it and so other transactions can read the version that was current when they started (MVCC, see MVCC: how InnoDB gives each transaction a snapshot).
Doublewrite buffer: pages are written to a doublewrite area before their real location, so a page half-written during a power cut can be repaired.
Tablespaces: with innodb_file_per_table (the default), each table lives in its own .ibd file, organised as a B+tree ordered by the primary key (the clustered index, see Clustered and secondary indexes in InnoDB).

You can see these on the sandbox server:

SQL

SELECT @@innodb_buffer_pool_size / 1024 / 1024 AS buffer_pool_mb,
       @@innodb_page_size AS page_size,
       @@innodb_flush_log_at_trx_commit AS flush_at_commit,
       @@innodb_doublewrite AS doublewrite;

Result

+----------------+-----------+-----------------+-------------+
| buffer_pool_mb | page_size | flush_at_commit | doublewrite |
+----------------+-----------+-----------------+-------------+
|   128.00000000 |     16384 |               1 | ON          |
+----------------+-----------+-----------------+-------------+

What’s happening

The buffer pool is 128 MB, the compiled-in default. On a dedicated database server you'd set it to roughly 50–75% of RAM (or turn on innodb_dedicated_server, which sizes it for you).
Pages are 16384 bytes (16 KB). Every read and write to disk happens in whole pages, which is why the number of pages a query touches matters more than the number of rows.
innodb_flush_log_at_trx_commit = 1 means the redo log is flushed to disk at every commit, giving full durability. 2 or 0 are faster but can lose about a second of commits in a crash.
Doublewrite is ON, protecting against torn pages.

AdvancedInnoDB files on disk

And the files themselves, listed inside the container:

Shell

docker compose exec mysql ls -la /var/lib/mysql/ch01

Result

total 920
drwxr-x--- 1 mysql mysql    184 Oct 16 15:25 .
drwxrwxrwt 1 mysql mysql    718 Oct 16 15:25 ..
-rw-r----- 1 mysql mysql 131072 Oct 16 15:20 categories.ibd
-rw-r----- 1 mysql mysql 131072 Oct 16 15:25 customers.ibd
-rw-r----- 1 mysql mysql 131072 Oct 16 15:20 departments.ibd
-rw-r----- 1 mysql mysql 147456 Oct 16 15:20 employees.ibd
-rw-r----- 1 mysql mysql 131072 Oct 16 15:20 order_items.ibd
-rw-r----- 1 mysql mysql 131072 Oct 16 15:20 orders.ibd
-rw-r----- 1 mysql mysql 131072 Oct 16 15:25 products.ibd

What’s happening

Each table is one .ibd file in a directory named after the database. That's why table-name case sensitivity follows the filesystem.
Even a table with five rows takes 128 KB: InnoDB allocates space in pages and extents up front.
There are no .frm files. MySQL 5.7 and earlier kept each table's definition in a .frm file next to the data; MySQL 8.0 replaced them with a transactional data dictionary stored in InnoDB itself (mysql.ibd), so a crash during ALTER TABLE can't leave the definition and the data out of step.

AdvancedMyISAM: a rollback that can't roll back

Mixing engines shows what "no transactions" means in practice:

SQL

CREATE TABLE audit_innodb (msg VARCHAR(50)) ENGINE=InnoDB;
CREATE TABLE audit_myisam (msg VARCHAR(50)) ENGINE=MyISAM;

START TRANSACTION;
INSERT INTO audit_innodb VALUES ('hello');
ROLLBACK;

START TRANSACTION;
INSERT INTO audit_myisam VALUES ('hello');
ROLLBACK;
SHOW WARNINGS;

SELECT (SELECT COUNT(*) FROM audit_innodb) AS innodb_rows,
       (SELECT COUNT(*) FROM audit_myisam) AS myisam_rows;

Result

Query OK, 0 rows affected, 1 warning (0.000 sec)

+---------+------+---------------------------------------------------------------+
| Level   | Code | Message                                                       |
+---------+------+---------------------------------------------------------------+
| Warning | 1196 | Some non-transactional changed tables couldn't be rolled back |
+---------+------+---------------------------------------------------------------+

+-------------+-------------+
| innodb_rows | myisam_rows |
+-------------+-------------+
|           0 |           1 |
+-------------+-------------+

What’s happening

The InnoDB insert is rolled back normally and leaves 0 rows.
The MyISAM insert is written straight to the table: MyISAM has no undo log. ROLLBACK reports 1 warning instead of an error.
The warning is 1196, "Some non-transactional changed tables couldn't be rolled back", and the MyISAM row is still there: myisam_rows = 1.
On MySQL 9.7 you can't even put both inserts in one transaction: GTIDs are on by default (gtid_mode = ON, enforce_gtid_consistency = ON; both defaulted to OFF in 8.4), and the server rejects it with ERROR 1785 (HY000): Statement violates GTID consistency: Updates to non-transactional tables can only be done in either autocommitted statements or single-statement transactions, and never in the same statement as updates to transactional tables.
Use InnoDB for every table unless you have a very specific reason not to.

#MySQL versions: 5.7, 8.0, 8.4 LTS, 9.7 LTS and Innovation releases

Added

Until 2023 MySQL had one simple model: a major version (5.6, 5.7, 8.0) received years of patch releases, and 8.0 kept adding features in patch releases (8.0.13, 8.0.31…), which surprised people when an "upgrade" changed behaviour. In 2023 Oracle split releases into two tracks, and in 2026 changed the version numbers again. You will meet all of these in interviews and in production, so it's worth knowing which is which.

The mental model is a stable road and a fast lane. LTS (Long-Term Support) releases are the stable road: features are frozen when the series starts and later releases only fix bugs and security issues, for 5 years of premier support plus 3 of extended support. Innovation releases are the fast lane: a new one every few months with new features, removals and behaviour changes, each supported only until the next. Every so often the fast lane's work is frozen into the next LTS.

Version	Released	Status in Oct 2026	What people remember it for
5.7	2015	End of life (October 2023)	JSON type, generated columns; still found in old systems
8.0	2018	End of life (April 2026)	CTEs, window functions, `utf8mb4` default, data dictionary, roles, `caching_sha2_password`, query cache removed; features added in patch releases
8.3 and 9.0–9.6	2024–2026	Innovation releases, superseded	`VECTOR` type (9.0), `mysql_native_password` removed (9.0)
8.4 LTS	April 2024	Supported LTS	First LTS: `mysql_native_password` off by default, `MASTER`/`SLAVE` syntax removed
9.7 LTS	April 2026	Current LTS (this site)	Second LTS, rolls up 9.0–9.6
26.7	July 2026	Current Innovation release	First release with calendar version numbers

The MySQL 9.7 reference manual says 9.7 is the final release line using sequential version numbers. From July 2026 versions are YY.M: the year and the scheduled month. MySQL 26.7.0 (July 2026) was the first Innovation release under the new scheme, and the next one is scheduled as 26.10 (October 2026). Oracle's published plan names 28.4 (April 2028) as the next LTS. An LTS keeps its YY.M number for its whole life and only the last part grows (so a future 28.4.3 is still the 28.4 LTS). Targeted security patches between quarterly releases get a fourth number, such as 26.7.0.1.

SQL

SELECT VERSION(), @@version_comment;

Result

+-----------+------------------------------+
| VERSION() | @@version_comment            |
+-----------+------------------------------+
| 9.7.2     | MySQL Community Server - GPL |
+-----------+------------------------------+

What’s happening

VERSION() returns the server's version string: 9.7.2 is the third release (patch 2) of the 9.7 LTS series, which went GA in April 2026.
@@version_comment says which edition: Community Server (free, GPL). Enterprise Edition adds features such as the thread pool, audit log, data masking and JavaScript stored programs.
Run this first whenever behaviour surprises you. Many "MySQL does X" answers online are true for 5.7 or 8.0 and wrong for the server you're actually on.

AdvancedUpgrade paths between versions

AdvancedRemoved features: native password, SLAVE syntax

Some of the changes between 8.0 and 9.7 break old scripts, and you can trigger them:

SQL

CREATE USER 'legacy'@'%' IDENTIFIED WITH mysql_native_password BY 'pw';
SHOW SLAVE STATUS;
SHOW REPLICA STATUS;

Result

ERROR 1524 (HY000): Plugin 'mysql_native_password' is not loaded
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'SLAVE STATUS' at line 1
Empty set (0.000 sec)

What’s happening

mysql_native_password (SHA-1 based) was the default login method up to 5.7. 8.0 made caching_sha2_password the default, 8.4 disabled the old plugin by default, and 9.0 removed it. Old drivers and old CREATE USER scripts fail with 1524 here.
The MASTER/SLAVE replication syntax was deprecated in 8.0.22 in favour of SOURCE/REPLICA and removed in 8.4, so SHOW SLAVE STATUS is now just a syntax error.
SHOW REPLICA STATUS works and returns an empty set because this server isn't a replica.

AdvancedNew on 9.x: VECTOR and Enterprise-only JavaScript

SQL

CREATE TABLE vec_demo (id INT PRIMARY KEY, embedding VECTOR(3));
INSERT INTO vec_demo VALUES (1, STRING_TO_VECTOR('[0.1, 0.2, 0.3]'));
SELECT id, VECTOR_TO_STRING(embedding) FROM vec_demo;

CREATE FUNCTION js_add(a INT, b INT) RETURNS INT DETERMINISTIC
  LANGUAGE JAVASCRIPT AS $$ return a + b $$;
SHOW WARNINGS;
SELECT js_add(2, 3);

Result

+----+---------------------------------------+
| id | VECTOR_TO_STRING(embedding)           |
+----+---------------------------------------+
|  1 | [1.00000e-01,2.00000e-01,3.00000e-01] |
+----+---------------------------------------+

Query OK, 0 rows affected, 1 warning (0.004 sec)

+---------+------+------------------------------------+
| Level   | Code | Message                            |
+---------+------+------------------------------------+
| Warning | 6001 | Language component: Not available. |
+---------+------+------------------------------------+

ERROR 6001 (HY000): Language component: Not available.

What’s happening

The VECTOR(3) type, added in 9.0, stores fixed-length arrays of 32-bit floats for embeddings. STRING_TO_VECTOR parses the text form and VECTOR_TO_STRING prints it back in scientific notation.
CREATE FUNCTION … LANGUAGE JAVASCRIPT is accepted by the parser and stored, but with a warning: running JavaScript needs the MLE language component, which ships with Enterprise Edition only.
Calling it fails with the same 6001 error. Good to know before you design around a feature from a release blog post.
On the first try without DETERMINISTIC, MySQL refused with ERROR 1418 (HY000): This function has none of DETERMINISTIC, NO SQL, or READS SQL DATA in its declaration and binary logging is enabled, because 9.7 has the binary log on by default and needs to know a function is safe to replicate.

AdvancedMySQL 5.7 and 8.0 differences you'll still meet

These come up whenever you work on an older system. The sandbox runs 9.7 only, so they're described, not demonstrated:

No CTEs or window functions in 5.7. WITH, ROW_NUMBER() OVER (…) and recursive queries arrived in 8.0. In 5.7, "top N per group" needs user variables or self-joins.
utf8 is 3-byte utf8mb3 and can't store emoji. 5.7 defaulted to latin1; 8.0 changed the default to utf8mb4. On 9.7 utf8 still means utf8mb3, and creating a column with it warns: Warning 3719 'utf8' is currently an alias for the character set UTF8MB3, but will be an alias for UTF8MB4 in a future release. (I ran that one.)
The query cache is gone since 8.0 (it was a global mutex bottleneck). Answers that tell you to tune query_cache_size are about 5.7.
GROUP BY sorted the result in 5.7 (an implicit ORDER BY); 8.0 stopped doing that, so a query that "always came back sorted" may not on 8.0+.
ONLY_FULL_GROUP_BY became a default SQL mode in 5.7.5; older code that selects non-grouped columns breaks (see ONLY_FULL_GROUP_BY).
Descending indexes were parsed but ignored before 8.0; invisible indexes, instant ADD COLUMN, hash joins (8.0.18) and EXPLAIN ANALYZE (8.0.18) are all 8.0-era.

#Connecting: the mysql client, Workbench and drivers

Added

Everything talks to the server over the MySQL client/server protocol, on TCP port 3306 by default or a Unix socket on the same machine. A client sends a user name and password, the server checks them against mysql.user (an account is 'user'@'host', so the same name from different hosts can be different accounts), and then the client sends SQL text and receives result sets.

There are three kinds of client you'll use. The mysql command-line client is the universal tool: always installed with the server, scriptable, and what DBAs reach for. GUI tools (MySQL Workbench, DBeaver, DataGrip, TablePlus) show schemas and results visually and draw ER diagrams. Drivers are libraries your application uses: mysql2 for Node.js, Connector/J for Java, mysqlclient or Connector/Python for Python. Think of it as one phone line with different handsets.

Shell

docker compose exec mysql mysql -uroot -pstudy -h 127.0.0.1 -P 3306 ch01

Result

mysql: [Warning] Using a password on the command line interface can be insecure.
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 446
Server version: 9.7.2 MySQL Community Server - GPL

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> SELECT DATABASE();
+------------+
| DATABASE() |
+------------+
| ch01       |
+------------+
1 row in set (0.000 sec)

mysql> \s
--------------
mysql  Ver 9.7.2 for Linux on aarch64 (MySQL Community Server - GPL)

Connection id:          446
Current database:       ch01
Current user:           root@127.0.0.1
SSL:                    Cipher in use is TLS_AES_128_GCM_SHA256
Server version:         9.7.2 MySQL Community Server - GPL
Connection:             127.0.0.1 via TCP/IP
Server characterset:    utf8mb4
Db     characterset:    utf8mb4
Client characterset:    latin1
Conn.  characterset:    latin1
TCP port:               3306
Uptime:                 22 min 5 sec
--------------

mysql> \q
Bye

What’s happening

In the sandbox the server runs in a Docker container, so the client is run inside it with docker compose exec mysql. On a machine with the client installed you'd drop that prefix and use port 3307, where the sandbox maps the container's 3306.
-u root is the user, -h the host, -P (capital) the port, and the last argument the default database. -pstudy glues the password to the flag, which is why the client warns: anyone who can list processes can read it. Use -p on its own and the client prompts for it instead.
"Reading table information for completion" means the client is loading table and column names for tab completion; -A skips it on big schemas. The banner shows the connection id (the same number as CONNECTION_ID() and the Id in SHOW PROCESSLIST) and the server version.
Inside the monitor, statements end with ; (or \G for vertical output) and backslash commands run in the client itself: \s prints the connection status, \q quits, and source file.sql runs a script.
The status confirms what you're connected to: TCP/IP with TLS (Cipher in use is TLS_AES_128_GCM_SHA256), user root@127.0.0.1, database ch01. Copyright lines and a few status lines are trimmed here; nothing else is changed.
Client characterset: latin1 is a real trap: the container has no locale set, so the client fell back to latin1, and emoji or accented text typed here could be mangled. Start it with --default-character-set=utf8mb4 (or run SET NAMES utf8mb4). Every other result on this site comes from the non-interactive form (mysql -t … < file.sql).

Advancedlocalhost means socket, 127.0.0.1 means TCP

To the mysql client, the host name localhost is special: it means use the Unix socket file, not TCP. Only 127.0.0.1 (or a real host name) uses the network. You can ask the server which one you got:

Shell

Q="SELECT USER(), (SELECT connection_type FROM performance_schema.threads
   WHERE processlist_id = CONNECTION_ID()) AS transport, @@port"
docker compose exec mysql mysql -uroot -pstudy -h localhost ch01 -t -e "$Q"
docker compose exec mysql mysql -uroot -pstudy -h 127.0.0.1 -P 3306 ch01 -t -e "$Q"

Result

mysql: [Warning] Using a password on the command line interface can be insecure.
+----------------+-----------+--------+
| USER()         | transport | @@port |
+----------------+-----------+--------+
| root@localhost | Socket    |   3306 |
+----------------+-----------+--------+
mysql: [Warning] Using a password on the command line interface can be insecure.
+----------------+-----------+--------+
| USER()         | transport | @@port |
+----------------+-----------+--------+
| root@127.0.0.1 | SSL/TLS   |   3306 |
+----------------+-----------+--------+

What’s happening

performance_schema.threads has one row per connection; matching processlist_id to CONNECTION_ID() finds this session's row and its connection_type.
With -h localhost the transport is Socket, and the server sees the client as root@localhost.
With -h 127.0.0.1 it goes over TCP, and the transport is SSL/TLS: since 8.0 the server generates certificates at first start and clients use TLS by default when it's available. The account is matched as root@127.0.0.1.
That difference matters for accounts: 'app'@'localhost' and 'app'@'%' are separate accounts with separate passwords and grants. "Access denied" when the same credentials work elsewhere is very often this.
The warning on each run is real: -pstudy puts the password on the command line, where other users can see it in the process list. Fine for a throwaway sandbox, not elsewhere; use -p alone, an option file, or mysql_config_editor.

AdvancedCommon connection errors: 1045 and 2003

Shell

docker compose exec mysql mysql -uroot -pwrong -h 127.0.0.1 ch01 -e "SELECT 1"
docker compose exec mysql mysql -uroot -pstudy -h 127.0.0.1 -P 3399 -e "SELECT 1"

Result

ERROR 1045 (28000): Access denied for user 'root'@'127.0.0.1' (using password: YES)
ERROR 2003 (HY000): Can't connect to MySQL server on '127.0.0.1:3399' (111)

What’s happening

1045 means the server was reached but rejected the login. It deliberately doesn't say whether the user or the password was wrong. Check the host part too: the message shows the account as the server matched it, 'root'@'127.0.0.1'.
2003 means nothing answered at that address. Error numbers 2000 and up come from the client library, not the server; (111) is the operating system's "connection refused". Wrong port, server not running, or a firewall.
When scripting, the client prints ERROR 1045 (28000) at line 1: with the line number of the failing statement in the script; the interactive client leaves the line out.

AdvancedConnecting from Node.js with mysql2

Applications use a driver and, almost always, a connection pool so requests reuse open connections instead of paying for a new TCP and TLS handshake and login each time. This runs from the sandbox's node/ folder against the container:

connect.mjs

import mysql from "mysql2/promise";

const pool = mysql.createPool({
  host: "127.0.0.1",
  port: 3307,          // the sandbox maps MySQL's 3306 to 3307 on the Mac
  user: "root",
  password: process.env.MYSQL_PASSWORD,
  database: "ch01",
  connectionLimit: 5,
});

const [rows] = await pool.execute(
  "SELECT id, name, city FROM customers WHERE city = ? ORDER BY id",
  ["Toronto"],
);
console.log(rows);

const [[info]] = await pool.query("SELECT VERSION() AS version, CURRENT_USER() AS user");
console.log(info);

await pool.end();

Result

[
  { id: 1, name: 'Rohit', city: 'Toronto' },
  { id: 5, name: 'Noah', city: 'Toronto' }
]
{ version: '9.7.2', user: 'root@%' }

What’s happening

createPool doesn't connect yet; it opens up to connectionLimit connections on demand and hands them out per query. The password comes from an environment variable, not the source code.
pool.execute(sql, params) sends a prepared statement: the SQL with ? placeholders goes to the server first, then the value "Toronto" separately. The value is never spliced into the SQL text, which is what prevents SQL injection.
The driver returns [rows, fields]; destructuring takes the rows, which are plain objects keyed by column name: Rohit and Noah.
CURRENT_USER() shows root@%: the account that matched is the 'root'@'%' one (any host), because the connection came over Docker's network. Compare USER() above, which shows where you connected from.
pool.end() closes the connections so the script can exit. The full story of using MySQL from Node is in mysql2: connections and pools.

AdvancedMySQL Workbench and MySQL Shell

MySQL Workbench is Oracle's free GUI: a connection manager, a SQL editor with result grids, a visual EXPLAIN, an ER-diagram designer that can forward-engineer a model into CREATE TABLE statements (and reverse-engineer an existing database into a diagram), plus server status and user admin screens. It's a desktop app, so it isn't run in this sandbox. MySQL Shell (mysqlsh, version 9.7.1 in the container) is the modern command-line client: it speaks SQL, JavaScript and Python modes, and it's the tool for InnoDB Cluster setup and for fast parallel dump and load (util.dumpInstance, util.loadDump).

#The sample schema used on this site

Added

Every SQL example on this site runs against the same small dataset, so you only have to learn one. It models a shop that also has staff: customers place orders, each order has order items that point at products, products belong to categories (which can nest), and employees belong to departments and report to a manager who is another employee.

It's deliberately tiny, so every result fits on screen and you can check answers by eye, and it's deliberately imperfect: it has a customer with no city, a customer with no orders, a product nobody has ordered, an employee with no department, a boss with no manager, a department with no staff and two people on the same salary. Those are exactly the rows that make joins, NULL handling and ranking interesting.

Arrows point from the "one" side to the "many" side. order_items is the junction table between orders and products.

SQL

SELECT 'departments' AS table_name, COUNT(*) AS row_count FROM departments
UNION ALL SELECT 'employees',   COUNT(*) FROM employees
UNION ALL SELECT 'customers',   COUNT(*) FROM customers
UNION ALL SELECT 'categories',  COUNT(*) FROM categories
UNION ALL SELECT 'products',    COUNT(*) FROM products
UNION ALL SELECT 'orders',      COUNT(*) FROM orders
UNION ALL SELECT 'order_items', COUNT(*) FROM order_items;

Result

+-------------+-----------+
| table_name  | row_count |
+-------------+-----------+
| departments |         4 |
| employees   |        10 |
| customers   |         6 |
| categories  |         5 |
| products    |         8 |
| orders      |         8 |
| order_items |        13 |
+-------------+-----------+
7 rows in set (0.000 sec)

What’s happening

Each SELECT 'name', COUNT(*) FROM t produces one row: a fixed label and that table's row count.
UNION ALL stacks the seven one-row results into one result. (UNION without ALL would also remove duplicate rows, which costs a sort and isn't wanted here.)
The whole dataset is 54 rows. Small enough that you can predict every result on this site by reading the tables below, which is the point.

AdvancedHow the tables relate: the foreign keys

How the tables relate, read from the foreign keys MySQL stores:

SQL

SELECT table_name, column_name, referenced_table_name, referenced_column_name
FROM information_schema.key_column_usage
WHERE table_schema = 'ch01' AND referenced_table_name IS NOT NULL
ORDER BY table_name, column_name;

Result

+-------------+---------------+-----------------------+------------------------+
| TABLE_NAME  | COLUMN_NAME   | REFERENCED_TABLE_NAME | REFERENCED_COLUMN_NAME |
+-------------+---------------+-----------------------+------------------------+
| categories  | parent_id     | categories            | id                     |
| employees   | department_id | departments           | id                     |
| employees   | manager_id    | employees             | id                     |
| order_items | order_id      | orders                | id                     |
| order_items | product_id    | products              | id                     |
| orders      | customer_id   | customers             | id                     |
| products    | category_id   | categories            | id                     |
+-------------+---------------+-----------------------+------------------------+
7 rows in set (0.000 sec)

What’s happening

information_schema is a set of read-only views describing the server's own objects. key_column_usage has one row per column in a key; rows with a referenced_table_name are foreign keys.
Five are ordinary one-to-many links: a customer has many orders, an order many items, a product many items, a category many products, a department many employees.
order_items sits between orders and products with a foreign key to each. That makes it a junction table for a many-to-many relationship: an order has many products, and a product is in many orders.
Two are self-references: employees.manager_id → employees.id (the org chart) and categories.parent_id → categories.id (Laptops, Phones and Accessories sit under Electronics). These are what Self joins and Recursive CTEs walk.
The column headers come back in capitals (TABLE_NAME) even though the query asked in lowercase: since 8.0 information_schema is built on the data dictionary and reports its own column names.

Read the data: who isn't in Engineering?

Using only the data below (or your memory of the quirks), how many rows does this return?

SQL

SELECT COUNT(*) AS not_engineering FROM employees WHERE department_id <> 1;

Show answer

5, not 6:

Result

+-----------------+
| not_engineering |
+-----------------+
|               5 |
+-----------------+

What’s happening

Ten employees, four of them in Engineering (Asha, Ben, Chloe, Dev). That leaves six people who are "not in Engineering".
But Jonas's department_id is NULL, and NULL <> 1 is unknown, not true, so WHERE drops him.
The query counts Sales (3) and Support (2): 5. To include Jonas, add OR department_id IS NULL. This is the sample data's most important quirk; NULL and three-valued logic explains the rule.

AdvancedData: departments and employees

departments and employees:

SQL

SELECT * FROM departments ORDER BY id;
SELECT * FROM employees;

Result

+----+-------------+
| id | name        |
+----+-------------+
|  1 | Engineering |
|  2 | Sales       |
|  3 | Support     |
|  4 | Research    |
+----+-------------+
+----+-------+---------------+------------+-----------+------------+
| id | name  | department_id | manager_id | salary    | hired_on   |
+----+-------+---------------+------------+-----------+------------+
|  1 | Asha  |             1 |       NULL | 185000.00 | 2018-03-01 |
|  2 | Ben   |             1 |          1 | 142000.00 | 2019-07-15 |
|  3 | Chloe |             1 |          1 | 142000.00 | 2020-01-10 |
|  4 | Dev   |             1 |          2 | 118000.00 | 2021-05-03 |
|  5 | Elena |             2 |          1 | 130000.00 | 2019-02-20 |
|  6 | Farid |             2 |          5 |  95000.00 | 2022-09-12 |
|  7 | Grace |             2 |          5 |  99000.00 | 2023-01-09 |
|  8 | Hiro  |             3 |          1 |  88000.00 | 2020-11-30 |
|  9 | Isla  |             3 |          8 |  72000.00 | 2024-04-01 |
| 10 | Jonas |          NULL |          1 |  91000.00 | 2025-06-16 |
+----+-------+---------------+------------+-----------+------------+

What’s happening

Asha (1) is the head of the company: manager_id is NULL. Ben, Chloe, Elena, Hiro and Jonas report to her; Dev reports to Ben; Farid and Grace to Elena; Isla to Hiro. That's a three-level tree.
Research (4) has no employees, and Jonas has no department (department_id is NULL). Those two rows are what make inner, left, right and full joins give different answers.
Ben and Chloe both earn 142000.00: a tie, which is where RANK, DENSE_RANK and ROW_NUMBER differ and where "second-highest salary" questions get tricky.
Salaries are DECIMAL(10,2), exact money, not floating point.

AdvancedData: customers

customers:

SQL

SELECT * FROM customers;

Result

+----+-------+-------------------+---------+---------------------+
| id | name  | email             | city    | created_at          |
+----+-------+-------------------+---------+---------------------+
|  1 | Rohit | rohit@example.com | Toronto | 2025-01-05 09:00:00 |
|  2 | Maya  | maya@example.com  | Mumbai  | 2025-01-20 14:30:00 |
|  3 | Liam  | liam@example.com  | London  | 2025-02-11 08:15:00 |
|  4 | Sofia | sofia@example.com | NULL    | 2025-03-02 19:45:00 |
|  5 | Noah  | noah@example.com  | Toronto | 2025-03-18 11:00:00 |
|  6 | Zara  | zara@example.com  | Dubai   | 2025-04-01 16:20:00 |
+----+-------+-------------------+---------+---------------------+

What’s happening

Sofia's city is NULL (unknown). She's the row that disappears from WHERE city <> 'Toronto' and shows up in NULL-handling puzzles.
Two customers share a city (Rohit and Noah in Toronto), so DISTINCT and GROUP BY city have something to collapse.
Zara (6) has never ordered. She's the row an inner join drops and an anti-join finds.
email is UNIQUE; id is AUTO_INCREMENT, so new customers get 7, 8, …

AdvancedData: categories and products

categories and products:

SQL

SELECT * FROM categories;
SELECT * FROM products;

Result

+----+-------------+-----------+
| id | name        | parent_id |
+----+-------------+-----------+
|  1 | Electronics |      NULL |
|  2 | Laptops     |         1 |
|  3 | Phones      |         1 |
|  4 | Books       |      NULL |
|  5 | Accessories |         1 |
+----+-------------+-----------+
+----+---------------------------------------+-------------+---------+-------+
| id | name                                  | category_id | price   | stock |
+----+---------------------------------------+-------------+---------+-------+
|  1 | ThinkBook 14                          |           2 |  899.00 |    12 |
|  2 | MacBook Air                           |           2 | 1299.00 |     5 |
|  3 | Pixel 10                              |           3 |  799.00 |    20 |
|  4 | iPhone 17                             |           3 | 1099.00 |     0 |
|  5 | SQL Performance Explained             |           4 |   39.00 |    50 |
|  6 | Designing Data-Intensive Applications |           4 |   49.00 |    35 |
|  7 | USB-C Charger                         |           5 |   29.00 |   100 |
|  8 | Noise-cancelling Headphones           |           5 |  249.00 |     8 |
+----+---------------------------------------+-------------+---------+-------+

What’s happening

Categories form a two-level tree: Electronics has three children (Laptops, Phones, Accessories); Books stands alone. No product points directly at Electronics, so "all products under Electronics" needs the hierarchy.
The iPhone 17 (4) has never been ordered and has stock = 0. It's the product-side twin of Zara.
Prices range from 29.00 to 1299.00, which gives CASE price bands and ORDER BY … LIMIT something to work with.

AdvancedData: orders and order_items

orders and order_items:

SQL

SELECT * FROM orders;
SELECT * FROM order_items;

Result

+----+-------------+-----------+---------------------+
| id | customer_id | status    | ordered_at          |
+----+-------------+-----------+---------------------+
|  1 |           1 | shipped   | 2025-02-01 10:00:00 |
|  2 |           1 | paid      | 2025-03-15 12:30:00 |
|  3 |           2 | shipped   | 2025-02-10 09:20:00 |
|  4 |           3 | cancelled | 2025-02-14 18:00:00 |
|  5 |           3 | shipped   | 2025-03-01 07:45:00 |
|  6 |           4 | pending   | 2025-03-20 21:10:00 |
|  7 |           5 | paid      | 2025-04-02 13:05:00 |
|  8 |           2 | shipped   | 2025-04-05 15:40:00 |
+----+-------------+-----------+---------------------+
+----------+------------+----------+------------+
| order_id | product_id | quantity | unit_price |
+----------+------------+----------+------------+
|        1 |          1 |        1 |     899.00 |
|        1 |          7 |        2 |      29.00 |
|        2 |          5 |        1 |      39.00 |
|        2 |          6 |        1 |      49.00 |
|        3 |          3 |        1 |     799.00 |
|        3 |          8 |        1 |     249.00 |
|        4 |          2 |        1 |    1299.00 |
|        5 |          6 |        2 |      49.00 |
|        5 |          7 |        1 |      29.00 |
|        6 |          8 |        1 |     249.00 |
|        7 |          1 |        1 |     899.00 |
|        7 |          5 |        3 |      39.00 |
|        8 |          7 |        4 |      29.00 |
+----------+------------+----------+------------+

What’s happening

status is an ENUM('pending','paid','shipped','cancelled'): one order of each kind at least, and order 4 (Liam's MacBook) is cancelled, so "revenue" questions must decide whether to count it.
Rohit, Maya and Liam have two orders each; Sofia and Noah one; Zara none. Orders span February to April 2025, which is enough for per-month grouping.
order_items.unit_price is copied from the product at the time of the order. That's deliberate: if the price changes later, old orders keep the price that was actually charged. A line's value is quantity * unit_price.
The primary key (order_id, product_id) means a product appears at most once per order.

AdvancedLoading the schema yourself

The full script is below. In the sandbox each chapter loads it into its own database (this chapter used ch01):

Shell

docker compose exec -T mysql mysql -uroot -p -e "CREATE DATABASE IF NOT EXISTS ch01"
docker compose exec -T mysql mysql -uroot -p ch01 < sample-schema.sql

AdvancedThe full sample-schema.sql script

sample-schema.sql

DROP TABLE IF EXISTS order_items, orders, products, categories, customers, employees, departments;

CREATE TABLE departments (
  id   INT PRIMARY KEY,
  name VARCHAR(50) NOT NULL UNIQUE
);

CREATE TABLE employees (
  id            INT PRIMARY KEY,
  name          VARCHAR(50)  NOT NULL,
  department_id INT          NULL,
  manager_id    INT          NULL,
  salary        DECIMAL(10,2) NOT NULL,
  hired_on      DATE         NOT NULL,
  FOREIGN KEY (department_id) REFERENCES departments(id),
  FOREIGN KEY (manager_id)    REFERENCES employees(id)
);

CREATE TABLE customers (
  id         INT AUTO_INCREMENT PRIMARY KEY,
  name       VARCHAR(50)  NOT NULL,
  email      VARCHAR(100) NOT NULL UNIQUE,
  city       VARCHAR(50)  NULL,
  created_at DATETIME     NOT NULL
);

CREATE TABLE categories (
  id        INT PRIMARY KEY,
  name      VARCHAR(50) NOT NULL,
  parent_id INT NULL,
  FOREIGN KEY (parent_id) REFERENCES categories(id)
);

CREATE TABLE products (
  id          INT AUTO_INCREMENT PRIMARY KEY,
  name        VARCHAR(80)   NOT NULL,
  category_id INT           NOT NULL,
  price       DECIMAL(10,2) NOT NULL,
  stock       INT           NOT NULL DEFAULT 0,
  FOREIGN KEY (category_id) REFERENCES categories(id)
);

CREATE TABLE orders (
  id          INT AUTO_INCREMENT PRIMARY KEY,
  customer_id INT         NOT NULL,
  status      ENUM('pending','paid','shipped','cancelled') NOT NULL,
  ordered_at  DATETIME    NOT NULL,
  FOREIGN KEY (customer_id) REFERENCES customers(id)
);

CREATE TABLE order_items (
  order_id   INT           NOT NULL,
  product_id INT           NOT NULL,
  quantity   INT           NOT NULL,
  unit_price DECIMAL(10,2) NOT NULL,
  PRIMARY KEY (order_id, product_id),
  FOREIGN KEY (order_id)   REFERENCES orders(id),
  FOREIGN KEY (product_id) REFERENCES products(id)
);

INSERT INTO departments VALUES (1,'Engineering'),(2,'Sales'),(3,'Support'),(4,'Research');

INSERT INTO employees VALUES
  (1,'Asha',   1, NULL, 185000.00,'2018-03-01'),
  (2,'Ben',    1, 1,    142000.00,'2019-07-15'),
  (3,'Chloe',  1, 1,    142000.00,'2020-01-10'),
  (4,'Dev',    1, 2,    118000.00,'2021-05-03'),
  (5,'Elena',  2, 1,    130000.00,'2019-02-20'),
  (6,'Farid',  2, 5,     95000.00,'2022-09-12'),
  (7,'Grace',  2, 5,     99000.00,'2023-01-09'),
  (8,'Hiro',   3, 1,     88000.00,'2020-11-30'),
  (9,'Isla',   3, 8,     72000.00,'2024-04-01'),
  (10,'Jonas', NULL, 1,  91000.00,'2025-06-16');

INSERT INTO customers (id,name,email,city,created_at) VALUES
  (1,'Rohit','rohit@example.com','Toronto','2025-01-05 09:00:00'),
  (2,'Maya','maya@example.com','Mumbai','2025-01-20 14:30:00'),
  (3,'Liam','liam@example.com','London','2025-02-11 08:15:00'),
  (4,'Sofia','sofia@example.com',NULL,'2025-03-02 19:45:00'),
  (5,'Noah','noah@example.com','Toronto','2025-03-18 11:00:00'),
  (6,'Zara','zara@example.com','Dubai','2025-04-01 16:20:00');

INSERT INTO categories VALUES (1,'Electronics',NULL),(2,'Laptops',1),(3,'Phones',1),(4,'Books',NULL),(5,'Accessories',1);

INSERT INTO products (id,name,category_id,price,stock) VALUES
  (1,'ThinkBook 14',      2, 899.00, 12),
  (2,'MacBook Air',       2,1299.00,  5),
  (3,'Pixel 10',          3, 799.00, 20),
  (4,'iPhone 17',         3,1099.00,  0),
  (5,'SQL Performance Explained', 4, 39.00, 50),
  (6,'Designing Data-Intensive Applications', 4, 49.00, 35),
  (7,'USB-C Charger',     5,  29.00,100),
  (8,'Noise-cancelling Headphones', 5, 249.00, 8);

INSERT INTO orders (id,customer_id,status,ordered_at) VALUES
  (1,1,'shipped',  '2025-02-01 10:00:00'),
  (2,1,'paid',     '2025-03-15 12:30:00'),
  (3,2,'shipped',  '2025-02-10 09:20:00'),
  (4,3,'cancelled','2025-02-14 18:00:00'),
  (5,3,'shipped',  '2025-03-01 07:45:00'),
  (6,4,'pending',  '2025-03-20 21:10:00'),
  (7,5,'paid',     '2025-04-02 13:05:00'),
  (8,2,'shipped',  '2025-04-05 15:40:00');

INSERT INTO order_items VALUES
  (1,1,1, 899.00),(1,7,2, 29.00),
  (2,5,1,  39.00),(2,6,1, 49.00),
  (3,3,1, 799.00),(3,8,1,249.00),
  (4,2,1,1299.00),
  (5,6,2,  49.00),(5,7,1, 29.00),
  (6,8,1, 249.00),
  (7,1,1, 899.00),(7,5,3, 39.00),
  (8,7,4,  29.00);

What’s happening

DROP TABLE IF EXISTS lists children before parents (order_items before orders), so the drop never trips a foreign key. Running the script again resets the data.
Tables are created parents first for the same reason: a FOREIGN KEY can only reference a table that already exists.
employees and categories reference themselves. That works because each row's manager or parent is inserted before the rows that point at it (Asha is row 1; Electronics is category 1).
Ids are written out explicitly even on AUTO_INCREMENT tables, so every chapter sees the same ids. MySQL moves the counter past the highest one, so the next customer gets id 7.

AdvancedCheat sheet: where the quirks are