3.1.2. SQL Command Categories (DDL, DML, DCL)

💡 First Principle: SQL commands are categorized by their purpose: defining structure (DDL), manipulating data (DML), or controlling access (DCL). Understanding which category a command belongs to helps you predict its behavior—DDL changes are often irreversible and affect structure, while DML changes affect data and can be rolled back within transactions.

Scenario: A developer needs to create a new table, insert records, and grant a coworker read-only access. Each task uses a different category of SQL commands.

DDL (Data Definition Language)

• Purpose: Define and modify database structure (schema)
• Commands:
  • CREATE – Create tables, views, indexes
  • ALTER – Modify existing structures
  • DROP – Delete structures
  • TRUNCATE – Remove all rows (faster than DELETE)

CREATE TABLE Customers (
    CustomerID INT PRIMARY KEY,
    Name NVARCHAR(100),
    Email NVARCHAR(255)
);

DML (Data Manipulation Language)

• Purpose: Query and modify data within tables
• Commands:
  • SELECT – Retrieve data
  • INSERT – Add new rows
  • UPDATE – Modify existing rows
  • DELETE – Remove rows

SELECT Name, Email FROM Customers WHERE CustomerID = 42;
INSERT INTO Customers (CustomerID, Name, Email) VALUES (43, 'Alice', 'alice@example.com');
UPDATE Customers SET Email = 'new@example.com' WHERE CustomerID = 42;
DELETE FROM Customers WHERE CustomerID = 43;

DCL (Data Control Language)

• Purpose: Control access permissions
• Commands:
  • GRANT – Give permissions
  • REVOKE – Remove permissions

GRANT SELECT ON Customers TO ReportingUser;
REVOKE INSERT ON Customers FROM TempUser;

Visual: SQL Command Categories

⚠️ Exam Trap: Confusing DDL and DML is heavily tested. DROP TABLE (DDL) deletes the structure. DELETE FROM Table (DML) removes rows but keeps the table. TRUNCATE TABLE (DDL) removes all rows instantly but keeps the structure.