DDL and DML: how a database designs a room and remembers who escaped it

A two-person escape room studio runs on paper for its first year. Room names on a whiteboard, puzzle notes in a shared folder, playtester feedback in a stack of sticky notes. It works until the studio opens its third room and someone asks a simple question: which puzzle has the worst average solve time across all three rooms? Nobody can answer without an afternoon of digging.

This is the moment a studio like this needs a database. And it's the moment every SQL learner runs into two commands that sound similar and get confused constantly: DDL and DML. One builds the containers. The other fills and changes what's inside them. Mixing them up is like confusing the shelf you built with the books you put on it.

We'll build a small database for a fictional studio called Vault of Whispers, using its rooms, puzzles, playtesters, and playtest sessions as the running example. Every query below includes the kind of output you'd see in a database GUI such as MySQL Workbench, DBeaver, or pgAdmin: a status line for the command, plus a result grid where a query returns rows.

What DDL actually does

DDL stands for Data Definition Language. It defines and changes the structure of a database: the tables, their columns, the data types those columns hold, and the constraints that keep bad data out. DDL doesn't touch the data sitting inside tables. It touches the tables themselves.

The core DDL commands:

• CREATE: builds a new table, database, index, or view
• ALTER: changes an existing table's structure, such as adding or dropping a column
• DROP: removes a table or database entirely, structure and data both
• TRUNCATE: empties every row from a table but keeps the table structure in place

A useful way to think about it: DDL runs before the studio has any data to work with, and again whenever the shape of that data needs to change. A GUI usually confirms a DDL command with a short status message rather than a grid of rows, because there's no data to display yet.

What DML actually does

DML stands for Data Manipulation Language. It works inside the structure DDL already built. Once a table exists, DML adds rows to it, changes the values in those rows, removes rows, and retrieves rows for reading.

The core DML commands:

• INSERT: adds new rows to a table
• UPDATE: changes existing values in one or more rows
• DELETE: removes rows from a table
• SELECT: retrieves rows, whether one column or the result of a join across several tables

Some SQL references classify SELECT under a separate category called DQL, for Data Query Language, since it only reads and never writes. In practice, most teams and most textbooks group it with DML, and we'll do the same here since it's the command you'll use most to check the results of every other one.

The order you write a query in isn't the order it runs in

SELECT causes more confusion than any other DML command, and most of it traces back to one fact: the order you type its clauses is not the order the database engine processes them. Written order puts SELECT first because that's the habit every tutorial teaches. Execution order puts it near the end.

Here's how the two line up:

Writing order	Execution order
SELECT	FROM / JOIN
FROM	WHERE
JOIN	GROUP BY
WHERE	HAVING
GROUP BY	SELECT
HAVING	ORDER BY
ORDER BY	LIMIT
LIMIT

The engine builds its working set from FROM and JOIN first, narrows it with WHERE, collapses it into groups with GROUP BY, filters those groups with HAVING, only then computes the actual output columns in SELECT, and finally sorts and trims with ORDER BY and LIMIT.

This explains a rule every SQL learner hits and few get explained to them: why WHERE can't reference a column alias defined in SELECT, but ORDER BY can. Take the studio's escape-rate query and add a filter for rooms with more than fifteen sessions:

SELECT
    r.room_name,
    COUNT(s.session_id) AS total_sessions,
    ROUND(100.0 * SUM(CASE WHEN s.escaped THEN 1 ELSE 0 END) / COUNT(s.session_id), 1) AS escape_rate_percent
FROM rooms r
JOIN playtest_sessions s ON r.room_id = s.room_id
GROUP BY r.room_name
HAVING COUNT(s.session_id) > 15
ORDER BY escape_rate_percent DESC;

FROM and JOIN run first, combining every room with its matching sessions. HAVING runs after GROUP BY, which is why it can filter on COUNT(s.session_id), a value that only exists once the grouping has happened; WHERE runs before grouping, so it can't see that count at all. SELECT runs next and defines the alias escape_rate_percent. Only then does ORDER BY run, which is why it can sort by that alias directly instead of repeating the full calculation. Write a WHERE escape_rate_percent > 50 clause into this same query and the engine rejects it: at the point WHERE executes, that column doesn't exist yet.

SQL Hack: An acrostic I use to remember the SQL execution order is "Fried Wings Give Heartburn So Order Lightly." (From, Where, Group by, Having, Select, Order by, Limit)

Building the studio's database

Here's the studio's first table, holding the escape rooms themselves.

CREATE TABLE rooms (
    room_id INT PRIMARY KEY AUTO_INCREMENT,
    room_name VARCHAR(100) NOT NULL,
    theme VARCHAR(50),
    difficulty VARCHAR(20),
    run_time_minutes INT
);

Output:

Query OK, 0 rows affected (0.04 sec)

A brand-new table has no rows to show, so the GUI confirms the command ran and moves on. Switching to the table's structure tab would now show four named columns with no data in any of them.

Three months later, the studio starts tracking how many hints each puzzle needs on average, and the puzzles table needs a new column. That's ALTER, not INSERT, because it changes the shape of the table, not its contents.

ALTER TABLE puzzles
ADD COLUMN average_hint_count DECIMAL(3,1) DEFAULT 0.0;

Output (Table Structure panel, after refresh):

Column	Type	Null	Key	Default
puzzle_id	INT	NO	PRI	NULL
room_id	INT	NO	MUL	NULL
puzzle_name	VARCHAR(100)	NO		NULL
puzzle_type	VARCHAR(50)	YES		NULL
average_solve_seconds	INT	YES		NULL
average_hint_count	DECIMAL(3,1)	YES		0.0

The new column sits at the bottom, and every existing puzzle row gets the default value of 0.0 automatically. No puzzle data was touched, added, or removed. Only the table's definition changed.

Filling the rooms with data

With the structure in place, the studio's staff enter their first room and its puzzles. This is DML, specifically INSERT.

INSERT INTO rooms (room_name, theme, difficulty, run_time_minutes)
VALUES
    ('The Cartographer''s Study', 'Victorian explorer', 'Hard', 60),
    ('Signal Lost', 'Abandoned space station', 'Medium', 45);

Output:

Query OK, 2 rows affected (0.02 sec)

INSERT INTO puzzles (room_id, puzzle_name, puzzle_type, average_solve_seconds)
VALUES
    (1, 'The Silent Vault', 'Combination lock', 480),
    (1, 'Ink and Compass', 'Physical assembly', 210),
    (2, 'Static on the Line', 'Audio cipher', 300);

Output:

Query OK, 3 rows affected (0.03 sec)

Checking the work means switching to a SELECT, which returns an actual grid rather than a status line.

SELECT puzzle_id, puzzle_name, puzzle_type, average_solve_seconds
FROM puzzles
WHERE room_id = 1;

Output:

puzzle_id	puzzle_name	puzzle_type	average_solve_seconds
1	The Silent Vault	Combination lock	480
2	Ink and Compass	Physical assembly	210

Updating and removing data

The Silent Vault turns out to run faster than expected once players get used to the lock mechanism. The design team wants the average solve time reflected after twenty new playtests bring the number down.

UPDATE puzzles
SET average_solve_seconds = 365
WHERE puzzle_name = 'The Silent Vault';

Output:

1 row(s) affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

Running the earlier SELECT again confirms the change in place:

puzzle_id	puzzle_name	puzzle_type	average_solve_seconds
1	The Silent Vault	Combination lock	365
2	Ink and Compass	Physical assembly	210

Later, a playtester who signed up twice by mistake needs one of their duplicate records removed.

DELETE FROM playtesters
WHERE playtester_id = 14;

Output:

1 row(s) affected (0.01 sec)

Where the two meet: a report across four tables

The real payoff of separating structure from data shows up once the studio has enough playtest sessions to ask harder questions, like which room has the highest escape rate. Answering that means joining four tables that DDL built, using the rows that DML filled.

SELECT
    r.room_name,
    COUNT(s.session_id) AS total_sessions,
    SUM(CASE WHEN s.escaped THEN 1 ELSE 0 END) AS successful_escapes,
    ROUND(100.0 * SUM(CASE WHEN s.escaped THEN 1 ELSE 0 END) / COUNT(s.session_id), 1) AS escape_rate_percent
FROM rooms r
JOIN playtest_sessions s ON r.room_id = s.room_id
GROUP BY r.room_name
ORDER BY escape_rate_percent DESC;

Output:

room_name	total_sessions	successful_escapes	escape_rate_percent
Signal Lost	18	15	83.3
The Cartographer's Study	22	9	40.9

That result grid only exists because CREATE TABLE gave each piece of data somewhere to live, and INSERT put real sessions in those tables. Change the table structure later, with ALTER or DROP, and every query built on it changes with it. Change the data, with INSERT, UPDATE, or DELETE, and the structure never moves.

Writing order and execution order aren't the same

Write a SELECT statement and you type it top to bottom: SELECT the columns, FROM the table, WHERE some condition holds, GROUP BY a column, HAVING some aggregate condition, ORDER BY the result. That's the order your fingers move in. It's not the order the database engine runs the statement.

The engine works through roughly this sequence instead:

1. FROM: gather the source tables and resolve any joins
2. WHERE: filter individual rows before any grouping happens
3. GROUP BY: collapse the filtered rows into groups
4. HAVING: filter those groups
5. SELECT: pick and compute the columns to return
6. ORDER BY: sort the final result
7. LIMIT: cut the result down to a set number of rows

Take the escape rate query from earlier. SELECT sits first on the page, but the engine doesn't touch it first. It starts by joining rooms to playtest_sessions, because nothing else can happen until those source rows exist. Only after grouping the filtered rows by room_name does the engine evaluate the SELECT list, which is why a column alias defined there, like escape_rate_percent, can't be reused in a WHERE clause on the same query: WHERE runs before that alias exists. It can be reused in ORDER BY, since ORDER BY runs after SELECT.

This explains a rule that trips up a lot of people. Filtering on an aggregate inside WHERE fails:

SELECT room_name, COUNT(*) AS total_sessions
FROM playtest_sessions
WHERE COUNT(*) > 10
GROUP BY room_name;

Output:

Error: Invalid use of group function

COUNT(*) doesn't exist yet when WHERE runs, since grouping hasn't happened. Swap WHERE for HAVING, which runs after GROUP BY, and the same idea works:

SELECT room_name, COUNT(*) AS total_sessions
FROM playtest_sessions
GROUP BY room_name
HAVING COUNT(*) > 10;

Output:

room_name	total_sessions
Signal Lost	18

The execution order matters most for SELECT, since it's the one command that runs through all seven steps. DDL commands like CREATE and ALTER skip this pipeline entirely: they act on a table's structure directly, with no rows to filter, group, or sort.

The distinction that actually matters

DDL answers "what does this database look like." DML answers "what does this database currently hold." Confuse a TRUNCATE for a DELETE with a WHERE clause and you'll empty an entire table instead of removing a handful of rows. Confuse an ALTER TABLE for an UPDATE and you'll be trying to change a table's blueprint using a command built to fill in the blanks.

Next time you're staring at a schema, ask which category each command belongs to before you run it: are you reshaping the room, or filling it? Try rebuilding this Vault of Whispers schema yourself, add a hints_used table linked to playtest_sessions, and see what other questions your own DML can answer once the DDL is in place.