DDL, DML, DCL & TCL: SQL Command Types Explained

Final lesson — and it's the one that organises everything you already know. Every SQL command you've met belongs to one of four families, sorted by what it acts on: the building (DDL), the contents (DML), the keys to the building (DCL), or the safety rules for moving contents (TCL). Interviewers open with this classification constantly — and hiding inside it is the single most-asked SQL comparison of all time: DELETE vs TRUNCATE vs DROP.

The four families at a glance

(Purists sometimes park SELECT in its own family, DQL — Data Query Language. Mention that footnote and collect the bonus mark.)

DDL — building and reshaping the building

CREATE TABLE students (
  student_id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(100) NOT NULL,
  email VARCHAR(100) UNIQUE,
  enrolled_date DATE DEFAULT (CURRENT_DATE)
);

ALTER TABLE students ADD COLUMN phone_number VARCHAR(15);
ALTER TABLE students CHANGE COLUMN phone_number contact_number VARCHAR(15);

DROP TABLE students;   -- structure + data + indexes, all gone

DDL statements change the schema — they define what exists. ALTER TABLE is the everyday workhorse: add a column, change a type, rename, add a constraint — all without recreating the table. Note DDL's personality trait for later: in MySQL, DDL statements implicitly commit. Pin that thought.

DML — working with the rows

-- the archival classic: copy between tables in one statement
INSERT INTO orders_archive (order_id, order_date, customer_name, amount)
SELECT order_id, order_date, customer_name, amount
FROM orders
WHERE order_date >= '2025-01-01' AND order_date < '2026-01-01';

INSERT, UPDATE, DELETE — you've used them all course long. The combo above, INSERT … SELECT, is worth a special place in your toolkit: it moves data between tables in one statement and powers every archival and migration job you'll ever write. DML is row-level, logged, and rollback-able inside a transaction — which is exactly what distinguishes it from its DDL lookalike below.

DELETE vs TRUNCATE vs DROP — the eternal question

The line that wins the follow-up: TRUNCATE's rollback story depends on the database — MySQL and Oracle can't undo it (implicit commit), but SQL Server and PostgreSQL can roll it back inside an explicit transaction. "It depends — and here's exactly how" beats a memorised yes/no every time.

DCL — handing out (and taking back) the keys

GRANT SELECT ON orders TO analyst_ro;    -- read-only access
REVOKE SELECT ON orders FROM analyst_ro; -- and it's gone

GRANT and REVOKE manage privileges — SELECT, INSERT, UPDATE, DELETE, EXECUTE — per user or role, per object. Realistic patterns: a reporting user gets SELECT only; an application account gets SELECT + INSERT + UPDATE but never DROP. Add WITH GRANT OPTION and the grantee can pass the privilege onward — power that should be handed out rarely. DCL pairs beautifully with views: grant access to a view, and the base table's hidden columns don't exist for that user.

TCL — you already know it

COMMIT, ROLLBACK and SAVEPOINT got their own full lesson — Transactions & ACID. Here, just file them correctly: they control units of work, not data or structure — that's what makes them their own family and a favourite classification-question distractor.

Wait — the implicit-commit trap

START TRANSACTION;
UPDATE accounts SET balance = balance - 5000 WHERE account_id = 'A101';
-- teammate's migration script runs here:
ALTER TABLE accounts ADD COLUMN branch VARCHAR(30);
-- ⚠ MySQL just IMPLICITLY COMMITTED your uncommitted UPDATE
ROLLBACK;   -- rolls back… nothing. The debit is already permanent.

In MySQL, every DDL statement (CREATE, ALTER, DROP, TRUNCATE…) first commits whatever transaction is open — silently. Your planned "I'll roll back if step 3 fails" safety net evaporates without a single warning. The rule that follows: never mix DDL into the middle of a transaction. Schema changes go in their own scripts, run at their own time.

Common mistakes

• Classifying TRUNCATE as DML because it "deletes data" — it's DDL, and that's precisely why it behaves differently.
• Using DELETE without WHERE when you meant TRUNCATE (slow) — or TRUNCATE when you needed rollback (gone).
• Expecting the auto-increment counter to reset after DELETE — only TRUNCATE resets it.
• Running migrations (DDL) inside application transactions — implicit commit eats your rollback.
• Granting broad privileges "temporarily" and never revoking — audits exist, and they will find it.

Quick recap

Practice Zone — PYQs from real selection rounds

Q1Command familieseasy

INSERT belongs to which family of SQL commands?

Asked in

ADML — it manipulates the data inside tablesBDDL — it defines structureCDCL — it controls permissionsDTCL — it controls transactions

Q2DELETE vs TRUNCATE vs DROPmedium

You want to remove ALL rows fast, reset the auto-increment counter, but keep the table structure. Which command?

Asked in

ATRUNCATE TABLEBDELETE FROM … (no WHERE)CDROP TABLEDALTER TABLE … RESET

Q3DCLeasy

GRANT and REVOKE belong to which command family, and what do they manage?

Asked in

ADCL — user privileges on database objectsBTCL — transaction boundariesCDDL — table structuresDDML — row contents

Q4TRUNCATE and rollbackhard

In MySQL (InnoDB), can TRUNCATE TABLE be rolled back?

Asked in

ANo — it implicitly commits and can't be undone by ROLLBACKBYes, like any DML statementCYes, but only within 30 secondsDOnly if the table has no indexes

Q5Implicit commithard

Mid-transaction (with uncommitted UPDATEs), you run ALTER TABLE … in MySQL. What happens to those pending UPDATEs?

Asked in

AThey are silently committed — DDL causes an implicit commit firstBThey are rolled back automaticallyCThe ALTER waits until you COMMITDMySQL throws an error and refuses the ALTER

FAQ

That's the full course. Now go practise company-wise — start with TCS SQL Interview Questions →