What are DDL, DML, DCL and TCL?

Question

Accepted Answer

SQL commands are grouped into **categories** by their purpose: **DDL** (define schema), **DML** (manipulate data), **DCL** (control access), and **TCL** (control transactions). Understanding these categories organizes your knowledge of SQL and clarifies how the language is structured.

## DDL — Data Definition Language (defines structure)

```sql
CREATE TABLE users (id INT, name VARCHAR(100));   -- create a table/index/database
ALTER TABLE users ADD COLUMN email VARCHAR(255);   -- modify structure
DROP TABLE users;                                   -- delete a table
TRUNCATE TABLE logs;                                -- empty a table (fast)
```

DDL defines and modifies the database **schema** (tables, columns, indexes, constraints). Note: DDL statements typically **auto-commit** (can't usually be rolled back in a transaction in many databases).

## DML — Data Manipulation Language (changes data)

```sql
SELECT * FROM users;                  -- read (sometimes classed as DQL)
INSERT INTO users VALUES (...);        -- add rows
UPDATE users SET name = 'X' WHERE ..;  -- modify rows
DELETE FROM users WHERE ...;            -- remove rows
```

DML works with the **data inside** tables — the everyday querying and modifying. (`SELECT` is sometimes separated as DQL, Data Query Language.)

## DCL — Data Control Language (permissions)

```sql
GRANT SELECT, INSERT ON users TO analyst;   -- give permissions
REVOKE INSERT ON users FROM analyst;         -- take them away
```

DCL manages **access control** — granting and revoking permissions on database objects (security).

## TCL — Transaction Control Language

```sql
BEGIN;                       -- start a transaction
COMMIT;                       -- save all changes permanently
ROLLBACK;                     -- undo changes since the last commit
SAVEPOINT sp1;                -- a partial rollback point
ROLLBACK TO sp1;
```

TCL manages **transactions** — committing, rolling back, and savepoints (the all-or-nothing guarantees).

## Summary

```text
DDL → CREATE, ALTER, DROP, TRUNCATE   — define/modify SCHEMA (structure)
DML → SELECT, INSERT, UPDATE, DELETE  — work with DATA
DCL → GRANT, REVOKE                    — control ACCESS (permissions)
TCL → COMMIT, ROLLBACK, SAVEPOINT      — control TRANSACTIONS
```

## Why it matters

Understanding the DDL/DML/DCL/TCL categories is valuable knowledge that **organizes your understanding of SQL** — it provides a clear mental framework for the different kinds of operations SQL performs, which helps in learning, communicating about, and reasoning about database commands.

Knowing the categories clarifies the distinct purposes: **DDL** (defining and modifying schema — tables, indexes, constraints), **DML** (the everyday data operations — querying and modifying rows), **DCL** (access control — permissions for security), and **TCL** (transaction control — the commit/rollback that provides data-integrity guarantees).

This categorization is useful for several reasons: it helps you understand SQL's structure as a complete language (not just disconnected commands), it clarifies important behavioral differences (e.g. **DDL typically auto-commits** and can't be rolled back, unlike DML which works within transactions — an important practical distinction), and it provides shared vocabulary for discussing database operations.

Understanding that schema definition (DDL), data manipulation (DML), access control (DCL), and transaction management (TCL) are distinct categories with different behaviors and purposes gives you a coherent framework for the full scope of SQL.

While more conceptual than a specific technique, understanding the SQL command categories is valuable foundational knowledge that organizes your overall SQL understanding, clarifies behavioral distinctions (like DDL auto-commit), and is a common interview topic that demonstrates a structured understanding of the SQL language and its different operational domains — a useful organizing concept for comprehensive SQL knowledge.