DDL, DML, DCL, TCL

Concept

SQL is a massive language. To make it easier to understand, its commands are grouped into four distinct categories based on their purpose: defining structure, manipulating data, controlling access, and managing transactions.

1. DDL (Data Definition Language)

Used to define or modify the structure of the database (Tables, Indexes, Schemas). DDL commands generally cannot be rolled back in older databases (though PostgreSQL supports transactional DDL).

• CREATE: Creates a new table or database.
• ALTER: Modifies an existing table (e.g., adding a new column).
• DROP: Permanently deletes a table and all its data.
• TRUNCATE: Empties all rows from a table instantly, but keeps the empty table structure intact.

-- DDL Example
CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100)
);

ALTER TABLE users ADD COLUMN age INT;

2. DML (Data Manipulation Language)

Used to interact with the data inside the tables. This is what backend engineers write 99% of the time.

• SELECT: Retrieves data. (Some textbooks classify this separately as DQL - Data Query Language).
• INSERT: Adds new rows.
• UPDATE: Modifies existing rows.
• DELETE: Removes specific rows.

-- DML Example
INSERT INTO users (name, age) VALUES ('Alice', 25);
UPDATE users SET age = 26 WHERE name = 'Alice';
DELETE FROM users WHERE age < 18;

3. DCL (Data Control Language)

Used by Database Administrators (DBAs) to manage security and permissions.

• GRANT: Gives a user permission to perform certain tasks.
• REVOKE: Removes permissions from a user.

-- DCL Example
-- Grant a read-only role permission to read the users table
GRANT SELECT ON users TO readonly_role;

4. TCL (Transaction Control Language)

Used to manage transactions (a sequence of DML operations that must succeed or fail as a single unit).

• BEGIN / START TRANSACTION: Starts a new transaction.
• COMMIT: Permanently saves all changes made during the transaction.
• ROLLBACK: Undoes all changes made since the transaction started if an error occurs.
• SAVEPOINT: Sets a marker within a transaction so you can roll back to that specific point without canceling the entire transaction.

-- TCL Example
BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE name = 'Alice';
UPDATE accounts SET balance = balance + 100 WHERE name = 'Bob';
COMMIT; -- Both updates are saved simultaneously

Mental Model

Category	Stands For	Purpose	Core Commands	Who Uses It?
DDL	Data Definition	Building the table structure.	CREATE, ALTER, DROP, TRUNCATE	Database Architects, Migrations
DML	Data Manipulation	Editing the rows inside the table.	SELECT, INSERT, UPDATE, DELETE	Backend Software Engineers
DCL	Data Control	Managing who can log in and read data.	GRANT, REVOKE	DBAs, Security Teams
TCL	Transaction Control	Grouping multiple DML commands safely.	COMMIT, ROLLBACK	Backend Engineers (Payments)

Interview Questions

Q: What is the fundamental difference between DELETE (DML) and TRUNCATE (DDL)? If you want to empty a table with 10 million rows, which should you use?
A: You should absolutely use TRUNCATE.

• DELETE is a DML command. It deletes rows one by one, logs every single deletion in the transaction log, and fires any ON DELETE triggers. Deleting 10 million rows will take minutes and consume massive disk space for the transaction log.
• TRUNCATE is a DDL command. It bypasses the transaction log and simply drops the physical data file on the hard drive and creates a new, empty one. It empties a 10-million row table in 1 millisecond. However, because it bypasses normal operations, it cannot be easily rolled back in all databases and does not fire triggers.