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.

TCL (Transaction Control Language)

• Purpose: Group DML statements into a single transaction and decide its fate
• Commands:
  • COMMIT – Make every change in the transaction permanent
  • ROLLBACK – Undo every change back to the start of the transaction
  • SAVEPOINT – Mark a point you can roll back to partially

This is where atomicity actually happens. DML changes are provisional until a COMMIT makes them durable; a ROLLBACK discards them as if they never ran.

⚠️ Exam Trap: a very common myth says TRUNCATE cannot be rolled back. In SQL Server and Azure SQL it can — issue it inside an explicit transaction and ROLLBACK undoes it. What makes TRUNCATE different from DELETE is that it is minimally logged (it deallocates whole pages rather than logging every row), so it is far faster, but it cannot be filtered with a WHERE clause and it resets the identity seed.

Joining Tables

💡 First Principle: Normalisation deliberately splits data across tables, so almost every useful query has to put it back together. That is what a JOIN does — it matches rows from two tables on a shared key.

SELECT c.Name, o.OrderDate
FROM Customers AS c
INNER JOIN Orders AS o ON c.CustomerID = o.CustomerID;

Trap: an INNER JOIN silently drops rows that have no match. A customer who has never ordered simply disappears from that result — if you need to see them with no orders, you want a LEFT JOIN.

Filtering: WHERE vs HAVING

These two are constantly confused because both filter, but they run at different moments:

• WHERE filters individual rows, before any grouping happens.
• HAVING filters groups, after GROUP BY has aggregated them — so it is the only one that can test an aggregate.

SELECT Country, COUNT(*) AS Customers
FROM Customers
WHERE IsActive = 1          -- drops inactive rows first
GROUP BY Country
HAVING COUNT(*) > 10;       -- then drops small countries

Reasoning Tool: if the condition mentions COUNT, SUM or AVG, it has to be HAVING. If it tests a plain column value, use WHERE — it is cheaper, because it removes rows before the aggregation work is done.

NULL: The Absence of a Value

NULL does not mean zero and it does not mean an empty string. It means unknown — no value was recorded.

• Comparisons against NULL are never true: WHERE Email = NULL returns nothing. You must write WHERE Email IS NULL (or IS NOT NULL).
• Aggregates skip NULLs: AVG(Score) averages only the rows that have a score.
• COUNT(*) counts every row, but COUNT(ColumnName) counts only the rows where that column is not NULL — a difference that quietly changes report totals.
• A NOT NULL constraint is how you force a column to always carry a value.