SQL statements are grouped into four sub-languages based on what they do: DDL defines database structure, DML manipulates data within that structure, DCL controls access permissions, and TCL manages transactions.
Key Points: • DML (Data Manipulation Language) reads and modifies data: SELECT, INSERT, UPDATE, DELETE, and MERGE (Oracle). • DDL (Data Definition Language) defines or changes structure and constraints: CREATE, ALTER, DROP, RENAME, TRUNCATE. • DCL (Data Control Language) manages security and permissions: GRANT and REVOKE. • TCL (Transaction Control Language) manages the boundaries of a transaction: COMMIT, ROLLBACK, SAVEPOINT. • DDL changes are typically auto-committed in most databases, while DML changes can be rolled back until committed.
Example: Creating a table with CREATE TABLE is DDL, inserting rows into it with INSERT is DML, granting a read-only user access with GRANT SELECT is DCL, and wrapping several inserts in a transaction that ends with COMMIT or ROLLBACK is TCL.
Code Example:
-- DDL: define structure
CREATE TABLE accounts (id INT PRIMARY KEY, balance DECIMAL(10,2));
-- DML: manipulate data
INSERT INTO accounts VALUES (1, 500.00);
UPDATE accounts SET balance = 600.00 WHERE id = 1;
-- DCL: control access
GRANT SELECT ON accounts TO reporting_user;
-- TCL: control transaction boundaries
START TRANSACTION;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
COMMIT;Interview Tip: A concise interview answer is:
"SQL statements fall into four categories: DDL for defining structure like CREATE and ALTER, DML for manipulating data like SELECT and INSERT, DCL for permissions like GRANT and REVOKE, and TCL for transaction control like COMMIT and ROLLBACK. Knowing which bucket a statement falls into helps explain things like why DDL usually auto-commits while DML can be rolled back."