DDL, DML, DCL, TCL

SQL statements grouped by purpose. Interview-classic — "is TRUNCATE DDL or DML?"

The Four Categories

Memory trick: DDL = Definition (schema). DML = Manipulation (rows). DCL = aCcess. TCL = Transactions.

---

DDL — Schema Operations

CREATE TABLE customers (id INT PRIMARY KEY, name VARCHAR(255));
ALTER TABLE customers ADD email VARCHAR(255);
ALTER TABLE customers DROP COLUMN email;
ALTER TABLE customers CHANGE name full_name VARCHAR(255);  -- MySQL rename
DROP TABLE customers;       -- removes table + data + structure
TRUNCATE TABLE customers;   -- removes all rows, keeps structure

Auto-commit: DDL statements implicitly commit in most DBMS — can't ROLLBACK a DROP TABLE.

---

DELETE vs TRUNCATE vs DROP

The interview classic. Know cold.

DELETE FROM orders WHERE status = 'cancelled';   -- selective, rollbackable
TRUNCATE TABLE staging_data;                     -- fast wipe, keep schema
DROP TABLE old_archive;                          -- nuke entire table

Best practice: Use TRUNCATE for staging table refresh. Never TRUNCATE if FKs depend on it (some DBMS block this).

---

DML — Row Operations

INSERT INTO customers (name, email) VALUES ('John', 'j@x.com');
INSERT INTO archive SELECT * FROM customers WHERE created < '2020-01-01';

UPDATE customers SET email = 'new@x.com' WHERE id = 1;

DELETE FROM customers WHERE id = 1;

-- MERGE (upsert) — single statement insert-or-update
MERGE INTO target t USING source s ON t.id = s.id
WHEN MATCHED THEN UPDATE SET t.val = s.val
WHEN NOT MATCHED THEN INSERT (id, val) VALUES (s.id, s.val);

Best practice: Always wrap multi-row UPDATE/DELETE in a transaction. Always include WHERE — forgetting WHERE on UPDATE = update all rows.

---

DCL — Access Control

GRANT SELECT, INSERT ON sales.* TO 'analyst'@'localhost';
GRANT ALL PRIVILEGES ON sales.* TO 'admin'@'%';
REVOKE INSERT ON sales.* FROM 'analyst'@'localhost';

-- Role-based (Postgres / modern MySQL)
CREATE ROLE read_only;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO read_only;
GRANT read_only TO alice;

Privilege types:

• System — CREATE TABLE, CREATE USER, SHUTDOWN
• Object — SELECT, INSERT, UPDATE, DELETE, EXECUTE on specific table/proc

Best practice: Least privilege. Read-only user for analytics, separate user per app, never use root for app connections.

---

TCL — Transaction Control

START TRANSACTION;     -- or BEGIN
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
SAVEPOINT after_debit;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;

-- Oops, second update was wrong target
ROLLBACK TO after_debit;   -- partial rollback to savepoint

UPDATE accounts SET balance = balance + 100 WHERE id = 3;
COMMIT;

See [[Transactions]] for ACID, isolation levels, deadlocks.

---

Auto-commit Behavior

• MySQL: auto-commit ON by default — each statement is its own tx unless START TRANSACTION used.
• PostgreSQL: psql client auto-commits; libraries usually default to explicit tx.
• Oracle: explicit COMMIT required.

SET autocommit = 0;       -- MySQL: turn off
-- now changes don't persist until COMMIT

---

Cross-refs

• [[Transactions]] — ACID, isolation levels, MVCC
• [[Constraints]] — what DDL can enforce
• [[SQL Security]] — DCL deep dive, injection
• [[Basics]] — SELECT (DQL)