DDL, DML, DCL and TCL

DDL, Data Definition Language, defines the structure of the database. It creates, changes and removes objects such as tables and indexes.

• CREATE, ALTER, DROP, TRUNCATE

DML, Data Manipulation Language, works on the data inside the tables.

• INSERT, UPDATE, DELETE
• SELECT is often listed here too. Some books place it in its own group, called DQL, for Data Query Language.

DCL, Data Control Language, controls who may do what.

• GRANT gives a permission, and REVOKE takes it back.

TCL, Transaction Control Language, manages transactions.

• COMMIT saves the changes, ROLLBACK undoes them, and SAVEPOINT marks a point you can roll back to.

CREATE TABLE notes (id INT PRIMARY KEY, body TEXT);   -- DDL
INSERT INTO notes VALUES (1, 'hello');                -- DML
GRANT SELECT ON notes TO analyst;                     -- DCL
COMMIT;                                               -- TCL

A point interviewers like: DDL statements in many databases are committed automatically and cannot be rolled back, while DML statements wait for a COMMIT. The exact behaviour depends on the database, so say so if you are asked.

A quick way to remember

> Define the shape (DDL), change the data (DML), control access (DCL), control the saving (TCL).

If you are asked which group TRUNCATE belongs to, the answer is DDL, even though it empties rows, because it works at the table level.