What are the four categories of SQL commands — DDL, DML, DCL and TCL — and what does each one actually touch?
SQL commands fall into four families, and the family tells you what the command is allowed to touch.
DDL is the data definition language, and it changes the shape of the database rather than the data in it. The commands are CREATE, ALTER, DROP, TRUNCATE, CREATE INDEX and CREATE VIEW. When you run one of these, you are changing the tables, the columns or the indexes.
DML is the data manipulation language, and it works with the rows. The commands are SELECT, INSERT, UPDATE, DELETE and the MERGE family. These are the commands that run in your application code all day long.
DCL is the data control language, and it deals with who is allowed to do what. The commands are GRANT, REVOKE, CREATE USER and CREATE ROLE.
TCL is the transaction control language, and it decides when the work becomes permanent. The commands are BEGIN or START TRANSACTION, COMMIT, ROLLBACK and SAVEPOINT.
So what does each one actually touch? That is the useful way to remember it. DDL touches the schema. DML touches the rows. DCL touches the permissions. And TCL touches the boundary between the work that is done and the work that has been kept.
-- DDL: the shape of the table.
CREATE TABLE coupons (
code TEXT PRIMARY KEY,
discount_pct INTEGER NOT NULL
);
-- DML: the rows inside it.
INSERT INTO coupons (code, discount_pct) VALUES ('WELCOME10', 10);
-- DCL: who may do it.
GRANT SELECT ON coupons TO reporting_user;
-- TCL: when it is kept.
BEGIN;
UPDATE coupons SET discount_pct = 15 WHERE code = 'WELCOME10';
COMMIT;
The next question in this topic asks you to sort four real statements into the families.