SQL Commands: DDL, DML, DCL and TCL Explained

SQL statements are grouped into four families, and the grouping is not academic trivia: it tells you whether a statement can be undone, whether it fires triggers, and whether running it quietly commits everything you had done before it.

This page defines each family, lists what belongs in it, and then covers the one consequence that catches people out in production. Where the in-browser engine can run an example it is runnable; where it cannot — permissions and transactions are not implemented in a single-tab database — the block is labelled rather than given a Run button that would only produce an error.

The four families at a glance

Two notes before the detail. SELECT does not fit any of them cleanly — it reads without changing anything, and is sometimes given its own family, DQL (Data Query Language). And TRUNCATE is DDL despite feeling like a delete, which is exactly the kind of surprise the families are useful for predicting.

DDL: defining the structure

DDL creates, changes and removes the objects data lives in. CREATE makes one:

CREATE TABLE Suppliers (
  supplier_id INT PRIMARY KEY,
  name        STRING NOT NULL,
  country     STRING
);

SELECT * FROM Suppliers;

An empty table, with its shape defined. ALTER changes that shape afterwards:

ALTER TABLE Departments ADD COLUMN head STRING;
SELECT dept_id, dept_name FROM Departments;

DROP removes the object entirely, and TRUNCATE empties a table while keeping it — both covered in detail on the DELETE vs TRUNCATE vs DROP page, because choosing between them is a common interview question in its own right.

TRUNCATE TABLE Orders;
SELECT COUNT(*) AS orders_left FROM Orders;

DML: changing the rows

DML is the family you use every day. INSERT adds rows:

INSERT INTO Departments (dept_id, dept_name, location, budget)
VALUES (60, 'Legal', 'Pune', 300000);

SELECT * FROM Departments;

UPDATE changes existing ones, and DELETE removes them — both taking a WHERE clause that decides which rows are affected:

UPDATE Products
SET unit_price = unit_price * 1.1
WHERE category = 'Accessories';

SELECT name, unit_price FROM Products WHERE category = 'Accessories';

Because DML changes data rather than structure, it lives inside the transaction machinery: each change is logged, row triggers fire, and everything can be rolled back until it is committed. The changing data guide covers the habits that keep DML safe.

DCL: who is allowed to do what

Two statements, and they are the whole family. GRANT gives a permission and REVOKE takes it back:

GRANT SELECT ON Employees TO analyst;

REVOKE SELECT ON Employees FROM analyst;

Permissions in SQL are per-object and per-action, which is what makes least privilege practical: a reporting account can be granted SELECT on the tables it needs and nothing else, so a mistake in a report cannot delete anything. Roles group permissions so they can be granted once and assigned to many users.

This is the family that does not exist in the playground, and for a structural reason rather than a missing feature: the database here runs inside your own browser tab with no users, no accounts and nobody else to grant anything to.

TCL: committing and undoing work

A transaction is a group of statements that either all take effect or none do. TCL is how you mark the boundaries:

BEGIN TRANSACTION;

UPDATE Products SET unit_price = unit_price * 1.1
WHERE category = 'Accessories';

-- look at the result, then either keep it ...
COMMIT;
-- ... or undo everything since BEGIN with:
-- ROLLBACK;

SAVEPOINT adds partial undo inside a transaction: set one part-way through, and ROLLBACK TO SAVEPOINT name rewinds to that point without abandoning the whole transaction.

The consequence that actually bites: implicit commit

In several databases, including Oracle and MySQL, running a DDL statement commits the current transaction automatically, whether you wanted it to or not. So this sequence does not do what it looks like:

BEGIN TRANSACTION;
DELETE FROM Orders WHERE order_total < 2000;
CREATE TABLE audit_note (note STRING);   -- DDL: implicit COMMIT here
ROLLBACK;                                -- too late, the DELETE is permanent

The CREATE TABLE committed the delete before the ROLLBACK was reached. PostgreSQL is the notable exception — it keeps DDL inside transactions, which is why migrations on PostgreSQL can be written as all-or-nothing scripts and migrations on MySQL generally cannot.

That single behaviour is why knowing which family a statement belongs to is worth the five minutes it takes to learn. Never mix DDL into a transaction you intend to be able to undo.

Sorting any statement into its family

1. Does it change what the data means or where it lives? Structure → DDL.
2. Does it change which rows exist or what they contain? Rows → DML.
3. Does it change who can do something? Permissions → DCL.
4. Does it mark the start or end of a unit of work? Transactions → TCL.
5. Does it just read? SELECT — DQL, if your course insists on a name.

Applied to the two statements people most often place wrongly: TRUNCATE is DDL, because it deallocates storage rather than deleting rows one by one, and DELETE is DML, because it removes rows through the normal logged path.

Practice this topic

Reading explains the idea; producing it yourself is what makes it stick. These exercises use exactly what this page covers, and your answer is checked by running it against the same sample database:

• Look at the whole product catalogueEasy
• How many customers do we have?Easy
• Revenue actually shippedMedium

See all 42 exercises →

Frequently asked questions

Related reading

• DELETE vs TRUNCATE vs DROP: the real difference
• INSERT, UPDATE, DELETE: changing data safely
• SQL constraints: what the database refuses
• SQL cheat sheet: every clause on one page
• 25 SQL interview questions and answers