SQL 4 🛢️ SQL Categories: DDL, DML, DCL, TCL, and DQL
Every SQL statement belongs to a category. The category determines what the statement does, when it takes effect, and whether it can be rolled back. A CREATE TABLE is not the same kind of operation as an INSERT. A GRANT is not the same as a SELECT. The categories are not a matter of style — they describe fundamentally different interactions with the database.
SQL, the Structured Query Language, was developed at IBM in the 1970s as the interface for the relational model . Over the decades, the language grew from a simple query tool into a comprehensive data management language. The categories — DDL, DML, DCL, TCL, and DQL — are the classification system that organizes this growth. Each category has a defined purpose and a defined set of statements .
Key point: The categories are not just labels. They determine transactional behavior. DML statements (INSERT, UPDATE, DELETE) can be rolled back within a transaction. DDL statements (CREATE, DROP, ALTER) typically cannot — they are auto-committed in most databases. This distinction is the reason the categories exist.
Why SQL categories matter
You can write SQL without knowing the categories. But you cannot debug a failed transaction, optimize a schema change, or control access without understanding them.
The rollback problem. A transaction updates three tables and then fails on the fourth operation. The DML statements can be rolled back. The transaction is atomic. But if the transaction also contains a CREATE TABLE, that statement may have already committed. The categories explain why: DDL is auto-committed in most RDBMSs, and the rollback does not affect it.
The permission problem. A user needs to query a table but not modify it. The GRANT SELECT statement gives read access. The GRANT INSERT statement gives write access. These are DCL statements. They are managed separately from DML and DDL because they control access, not structure or data.
The transaction problem. A transaction starts with BEGIN, performs several DML statements, and ends with COMMIT or ROLLBACK. The transaction control statements are TCL. They define the boundaries of the transaction. Without them, every statement is its own transaction, and there is no way to group operations.
The query problem. A SELECT statement retrieves data. It does not modify it. It is a DQL statement. Some classifications place it under DML because it operates on data, but most separate it because it is read-only and cannot be rolled back.
The trade-off. The categories add terminology. They require you to know that CREATE is DDL and INSERT is DML and GRANT is DCL. The terminology is the price of understanding what each statement does and how it interacts with transactions.
a. DDL — Data Definition Language
DDL is the category of SQL statements that define and modify the structure of the database. These statements operate on the schema, not the data. They create tables, alter columns, add constraints, and drop objects .
The core DDL statements are:
CREATE creates a new database object. The object can be a table, an index, a view, a schema, a sequence, or a stored procedure.
CREATE TABLE customers (
customer_id INTEGER PRIMARY KEY,
first_name VARCHAR(50) NOT NULL,
email VARCHAR(100) UNIQUE NOT NULL
);ALTER modifies an existing object. It can add a column, drop a column, change a data type, or add a constraint.
ALTER TABLE customers ADD COLUMN phone VARCHAR(20);DROP removes an object. The removal is permanent. The data in the object is lost.
DROP TABLE customers;TRUNCATE removes all rows from a table but keeps the table structure. It is faster than DELETE because it does not log individual row deletions, but it cannot be rolled back in most databases.
TRUNCATE TABLE customers;RENAME changes the name of an object.
ALTER TABLE customers RENAME TO clients;The defining characteristic of DDL is that it changes the schema, not the data. In most RDBMSs, DDL statements are auto-committed. They take effect immediately and cannot be rolled back within a transaction .
b. DML — Data Manipulation Language
DML is the category of SQL statements that manipulate the data stored in the tables. These statements insert, update, delete, and merge rows. They operate on the data, not the schema .
The core DML statements are:
INSERT adds new rows to a table.
INSERT INTO customers (customer_id, first_name, email)
VALUES (1, 'Alice', 'alice@example.com');UPDATE modifies existing rows.
UPDATE customers SET email = 'alice.johnson@example.com' WHERE customer_id = 1;DELETE removes existing rows.
DELETE FROM customers WHERE customer_id = 1;MERGE (also called UPSERT) inserts a row if it does not exist, updates it if it does. The syntax varies by RDBMS. PostgreSQL uses INSERT ... ON CONFLICT, MySQL uses INSERT ... ON DUPLICATE KEY UPDATE, and SQL Server uses MERGE.
INSERT INTO customers (customer_id, first_name, email)
VALUES (1, 'Alice', 'alice@example.com')
ON CONFLICT (customer_id) DO UPDATE SET email = EXCLUDED.email;DML statements can be grouped into transactions. They take effect when the transaction is committed and are rolled back if the transaction is rolled back. This is the opposite of DDL, and the distinction is important for understanding transactional behavior .
c. DCL, TCL, and DQL
The remaining three categories cover access control, transaction control, and data retrieval.
DCL — Data Control Language manages access to the database. It grants and revokes permissions.
GRANT gives a user or role a privilege on an object.
GRANT SELECT, INSERT ON customers TO alice;REVOKE removes a privilege.
REVOKE INSERT ON customers FROM alice;DCL statements are managed by database administrators. They determine who can read, write, or modify the database. The privileges can be granted at the table, schema, or database level .
TCL — Transaction Control Language manages transactions. It defines the boundaries of a transaction and controls whether changes are committed or rolled back.
BEGIN (or START TRANSACTION) starts a transaction.
BEGIN;COMMIT makes the changes permanent.
COMMIT;ROLLBACK undoes the changes since the last BEGIN or COMMIT.
ROLLBACK;SAVEPOINT creates a marker within a transaction. The transaction can be rolled back to the savepoint without undoing the entire transaction.
SAVEPOINT before_update;
UPDATE accounts SET balance = balance - 100 WHERE account_id = 1;
ROLLBACK TO before_update;TCL is what makes transactions possible. Without it, every statement would be its own transaction, and there would be no way to group operations into an atomic unit .
DQL — Data Query Language retrieves data. It consists of the SELECT statement and its clauses.
SELECT customer_id, first_name, email
FROM customers
WHERE created_at > '2026-01-01'
ORDER BY first_name;Some classifications place SELECT under DML because it operates on data. Most separate it because it is read-only, does not modify data, and cannot be rolled back. The distinction matters for permissions: a user can have SELECT permission without INSERT, UPDATE, or DELETE permission .
Complete Example Session
This session demonstrates all five categories in a single workflow.
-- ============================================
-- PART 1: DDL — CREATE THE SCHEMA
-- ============================================
CREATE TABLE accounts (
account_id INTEGER PRIMARY KEY,
owner_name VARCHAR(100) NOT NULL,
balance DECIMAL(10, 2) NOT NULL CHECK (balance >= 0),
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
-- DDL: defines the structure.
-- Auto-committed in most RDBMSs.
-- ============================================
-- PART 2: DCL — GRANT PERMISSIONS
-- ============================================
GRANT SELECT ON accounts TO reporting_user;
GRANT SELECT, INSERT, UPDATE ON accounts TO app_user;
-- DCL: controls access.
-- reporting_user can only read.
-- app_user can read, insert, and update.
-- ============================================
-- PART 3: TCL — BEGIN A TRANSACTION
-- ============================================
BEGIN;
-- TCL: starts a transaction.
-- All DML statements until COMMIT or ROLLBACK are part of it.
-- ============================================
-- PART 4: DML — INSERT DATA
-- ============================================
INSERT INTO accounts (account_id, owner_name, balance)
VALUES (1, 'Alice', 1000.00);
INSERT INTO accounts (account_id, owner_name, balance)
VALUES (2, 'Bob', 500.00);
-- DML: manipulates data.
-- The changes are not yet permanent.
-- ============================================
-- PART 5: DML — UPDATE DATA
-- ============================================
UPDATE accounts SET balance = balance - 100 WHERE account_id = 1;
UPDATE accounts SET balance = balance + 100 WHERE account_id = 2;
-- DML: modifies data.
-- Still within the transaction.
-- ============================================
-- PART 6: TCL — COMMIT
-- ============================================
COMMIT;
-- TCL: makes the changes permanent.
-- The transfer is complete.
-- ============================================
-- PART 7: DQL — QUERY THE DATA
-- ============================================
SELECT account_id, owner_name, balance
FROM accounts
ORDER BY account_id;
-- DQL: retrieves data.
-- Output:
-- account_id | owner_name | balance
-- 1 | Alice | 900.00
-- 2 | Bob | 600.00
-- ============================================
-- PART 8: TCL — ROLLBACK A TRANSACTION
-- ============================================
BEGIN;
UPDATE accounts SET balance = 0 WHERE account_id = 1;
ROLLBACK;
-- TCL: undoes the update.
-- Alice's balance is still 900.00.
SELECT balance FROM accounts WHERE account_id = 1;
-- 900.00
-- ============================================
-- PART 9: DDL — ALTER THE SCHEMA
-- ============================================
ALTER TABLE accounts ADD COLUMN email VARCHAR(100);
-- DDL: modifies the schema.
-- Auto-committed. Cannot be rolled back.
-- ============================================
-- PART 10: DDL — DROP THE TABLE
-- ============================================
DROP TABLE accounts;
-- DDL: removes the table and all its data.
-- Permanent. Cannot be rolled back.The ten parts cover DDL creating the schema, DCL granting permissions, TCL beginning a transaction, DML inserting data, DML updating data, TCL committing, DQL querying, TCL rolling back, DDL altering the schema, and DDL dropping the table.
Quick Reference
The Five Categories
| Category | Purpose | Statements |
|---|---|---|
| DDL | Define and modify schema | CREATE, ALTER, DROP, TRUNCATE, RENAME |
| DML | Manipulate data | INSERT, UPDATE, DELETE, MERGE |
| DCL | Control access | GRANT, REVOKE |
| TCL | Manage transactions | BEGIN, COMMIT, ROLLBACK, SAVEPOINT |
| DQL | Retrieve data | SELECT |
The Transactional Behavior
| Category | Rollback | Auto-Commit |
|---|---|---|
| DDL | No (most RDBMSs) | Yes |
| DML | Yes | No (within transaction) |
| DCL | No (most RDBMSs) | Yes |
| TCL | N/A (defines boundaries) | N/A |
| DQL | N/A (read-only) | N/A |
The Permissions
| Statement | Purpose |
|---|---|
GRANT | Give a privilege |
REVOKE | Remove a privilege |
The Transaction Statements
| Statement | Purpose |
|---|---|
BEGIN | Start a transaction |
COMMIT | Make changes permanent |
ROLLBACK | Undo changes |
SAVEPOINT | Create a marker for partial rollback |
Best Practices
✅ Do This:
-- Use transactions for multi-step DML
BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE account_id = 1;
UPDATE accounts SET balance = balance + 100 WHERE account_id = 2;
COMMIT; -- ✅-- Grant only the permissions needed
GRANT SELECT ON accounts TO reporting_user; -- ✅-- Use SAVEPOINT for partial rollback
SAVEPOINT before_update;
-- operation
ROLLBACK TO before_update; -- ✅-- Test DDL in a development environment first
-- DDL cannot be rolled back in most RDBMSs. -- ✅❌ Don’t Do This:
-- Don't run DDL inside a transaction expecting a rollback
BEGIN;
CREATE TABLE temp (id INTEGER);
ROLLBACK; -- the table may still exist -- ❌-- Don't grant excessive permissions
GRANT ALL PRIVILEGES ON ALL TABLES TO app_user; -- ❌-- Don't use TRUNCATE when you might need to roll back
TRUNCATE TABLE customers; -- cannot be rolled back -- ❌-- Don't forget the WHERE clause in UPDATE or DELETE
DELETE FROM customers; -- deletes all rows -- ❌Common Pitfalls
| Pitfall | Why It Happens | Fix |
|---|---|---|
| DDL not rolled back | DDL auto-commits | Use a separate migration strategy |
| Permission denied | Missing GRANT | Grant the required privilege |
| Transaction not committed | Missing COMMIT | Add COMMIT at the end |
| Lock timeout | Long-running transaction | Commit or roll back promptly |
| TRUNCATE cannot be rolled back | DDL auto-commits | Use DELETE if rollback is needed |
Real-World Examples
1. DDL — Create Table
CREATE TABLE users (id INTEGER PRIMARY KEY, name VARCHAR(50));2. DDL — Alter Table
ALTER TABLE users ADD COLUMN email VARCHAR(100);3. DML — Insert
INSERT INTO users (id, name) VALUES (1, 'Alice');4. DML — Update
UPDATE users SET name = 'Alicia' WHERE id = 1;5. DML — Delete
DELETE FROM users WHERE id = 1;6. DCL — Grant
GRANT SELECT ON users TO reporting;7. DCL — Revoke
REVOKE SELECT ON users FROM reporting;8. TCL — Transaction
BEGIN; UPDATE users SET name = 'Bob' WHERE id = 1; COMMIT;9. TCL — Savepoint
SAVEPOINT sp1; UPDATE users SET name = 'Carol' WHERE id = 1; ROLLBACK TO sp1;10. DQL — Query
SELECT name FROM users WHERE id = 1;Visual
The Five Categories
┌──────────────────────────────────────────────┐
│ DDL — Data Definition Language │
│ CREATE, ALTER, DROP, TRUNCATE, RENAME │
│ Defines the structure. │
│ │
│ DML — Data Manipulation Language │
│ INSERT, UPDATE, DELETE, MERGE │
│ Manipulates the data. │
│ │
│ DCL — Data Control Language │
│ GRANT, REVOKE │
│ Controls access. │
│ │
│ TCL — Transaction Control Language │
│ BEGIN, COMMIT, ROLLBACK, SAVEPOINT │
│ Manages transactions. │
│ │
│ DQL — Data Query Language │
│ SELECT │
│ Retrieves data. │
│ │
└──────────────────────────────────────────────┘The Transactional Behavior
┌──────────────────────────────────────────────┐
│ ROLLBACK BEHAVIOR │
│ │
│ DML (INSERT, UPDATE, DELETE): │
│ ┌────────────────────────────────────┐ │
│ │ BEGIN │ │
│ │ INSERT ... │ │
│ │ UPDATE ... │ │
│ │ ROLLBACK ──> changes undone │ │
│ └────────────────────────────────────┘ │
│ │
│ DDL (CREATE, DROP, ALTER): │
│ ┌────────────────────────────────────┐ │
│ │ BEGIN │ │
│ │ CREATE TABLE ... ──> auto-commits │ │
│ │ ROLLBACK ──> table still exists │ │
│ └────────────────────────────────────┘ │
│ │
│ The distinction is why the categories exist.│
│ │
└──────────────────────────────────────────────┘The Transaction Lifecycle
┌──────────────────────────────────────────────┐
│ TRANSACTION LIFECYCLE │
│ │
│ BEGIN │
│ │ │
│ ├─ INSERT │
│ ├─ UPDATE │
│ ├─ SAVEPOINT sp1 │
│ ├─ DELETE │
│ │ └─ ROLLBACK TO sp1 (partial undo) │
│ │ │
│ ▼ │
│ COMMIT or ROLLBACK │
│ │
│ COMMIT: changes permanent │
│ ROLLBACK: changes undone │
│ │
└──────────────────────────────────────────────┘The Permission Model
┌──────────────────────────────────────────────┐
│ DCL — PERMISSIONS │
│ │
│ GRANT SELECT ON users TO reporting; │
│ └─ reporting can read │
│ │
│ GRANT INSERT, UPDATE ON users TO app; │
│ └─ app can insert and update │
│ │
│ GRANT ALL PRIVILEGES ON users TO admin; │
│ └─ admin can do everything │
│ │
│ REVOKE INSERT ON users FROM app; │
│ └─ app can no longer insert │
│ │
└──────────────────────────────────────────────┘Summary
| Item | Value |
|---|---|
| DDL | Data Definition Language — CREATE, ALTER, DROP, TRUNCATE, RENAME |
| DML | Data Manipulation Language — INSERT, UPDATE, DELETE, MERGE |
| DCL | Data Control Language — GRANT, REVOKE |
| TCL | Transaction Control Language — BEGIN, COMMIT, ROLLBACK, SAVEPOINT |
| DQL | Data Query Language — SELECT |
| DDL rollback | No (auto-committed in most RDBMSs) |
| DML rollback | Yes (within a transaction) |
| Transaction boundaries | BEGIN, COMMIT, ROLLBACK |
| Permission control | GRANT, REVOKE |
Key takeaways:
- SQL is categorized into five groups based on what the statement does. DDL defines the schema. DML manipulates the data. DCL controls access. TCL manages transactions. DQL retrieves data .
- DDL statements are auto-committed in most RDBMSs. They take effect immediately and cannot be rolled back within a transaction. This is why schema changes require a migration strategy rather than a transaction .
- DML statements can be grouped into transactions. They take effect when the transaction is committed and are undone if the transaction is rolled back. This is what makes multi-step operations atomic .
- DCL controls who can do what.
GRANTgives a privilege.REVOKEremoves it. Permissions can be granted at the table, schema, or database level . - TCL defines the boundaries of a transaction.
BEGINstarts it.COMMITmakes the changes permanent.ROLLBACKundoes them.SAVEPOINTcreates a marker for partial rollback . - DQL is the read-only category.
SELECTretrieves data without modifying it. Some classifications place it under DML, but it is usually separated because it is read-only and cannot be rolled back. - The categories explain the behavior you observe. A
CREATE TABLEthat survives aROLLBACKis DDL behaving correctly. ADELETEthat is undone is DML behaving correctly. Understanding the categories makes the behavior predictable.
Remember: Every SQL statement belongs to a category. DDL defines. DML manipulates. DCL controls. TCL manages. DQL retrieves. The categories are not just labels — they determine transactional behavior, permission requirements, and how the statement interacts with the database. DDL auto-commits. DML can be rolled back. DCL is managed by administrators. TCL defines the boundaries. DQL is read-only. Understanding the categories is understanding what each statement does and why it behaves the way it does.
Stop using slow, ad-bloated tool sites! 🤮
🔎 Search “KandZ Tools” on Google to use many professional utilities for free.
KandZ.me is the ultimate minimalist hub for:
✅ Finance (Mortgage, Interest, Inflation)
✅ Tech (Base64, JSON, Dev Suite, IP)
✅ Health (BMI, BMR, TDEE)
✅ Productivity (Timer, Workspace, QR)
⚡️ Fast & Private
🔒 No data leaves your device
💎 100% Free
🔗 Use it now: https://tools.kandz.me
🔖 Bookmark it—you’ll need it later!