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. The playground runs SQLite in your browser, transactions included, so most examples here are runnable. Where it cannot run one — SQLite has no TRUNCATE, and a database with no user accounts has nobody to GRANT anything to — 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 INTEGER PRIMARY KEY,
  name        VARCHAR(100) NOT NULL,
  country     VARCHAR(60)
);

SELECT * FROM Suppliers;

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

ALTER TABLE Departments ADD COLUMN head VARCHAR(100);
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.

-- MySQL, PostgreSQL, SQL Server and Oracle (SQLite has no TRUNCATE)
TRUNCATE TABLE Orders;

SQLite, the engine behind the playground, has no TRUNCATE. Its equivalent is a DELETE with no WHERE clause. When no triggers or foreign keys are involved, as here, SQLite spots it and clears the table in one step rather than row by row:

DELETE FROM 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:

-- PostgreSQL, MySQL, SQL Server and Oracle (SQLite has no users to grant to)
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 with COMMIT,
-- or undo everything since BEGIN:
ROLLBACK;

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

The prices are back to 189.5 and 119, the values they had before the transaction began: the ROLLBACK threw the 10% rise away. Change ROLLBACK to COMMIT and run the block again, and the new prices stay.

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:

BEGIN TRANSACTION;

UPDATE Products SET in_stock = in_stock - 5 WHERE product_id = 201;

SAVEPOINT before_price_change;
UPDATE Products SET unit_price = 0 WHERE product_id = 201;
ROLLBACK TO SAVEPOINT before_price_change;

COMMIT;

SELECT product_id, name, unit_price, in_stock FROM Products WHERE product_id = 201;

The mistaken price of 0 was rewound, but the stock change made before the savepoint survived and was committed: 42 laptops in stock became 37.

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:

-- MySQL (Oracle behaves the same way)
START TRANSACTION;
DELETE FROM Orders WHERE order_total < 2000;
CREATE TABLE audit_note (note VARCHAR(200));   -- DDL: implicit COMMIT here
ROLLBACK;                                      -- too late, the DELETE is permanent

The CREATE TABLE committed the delete before the ROLLBACK was reached. PostgreSQL, SQL Server and SQLite keep most DDL inside the transaction instead; Oracle and MySQL commit it implicitly. That is why migrations on PostgreSQL can be written as all-or-nothing scripts and migrations on MySQL generally cannot.

The playground runs SQLite, so there the same sequence has the opposite ending:

BEGIN TRANSACTION;
DELETE FROM Orders WHERE order_total < 2000;
CREATE TABLE audit_note (note VARCHAR(200));
ROLLBACK;

SELECT COUNT(*) AS orders_now FROM Orders;

All 15 orders are still there, and the audit_note table is not: the ROLLBACK undid the delete and the CREATE TABLE together. Add SELECT * FROM audit_note; to the end of the block and SQLite reports that no such table exists.

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