SQL Commands Explained: DDL vs DML vs DQL vs DCL vs TCL

SQL (Structured Query Language) is the backbone of working with databases. But SQL is not just about writing SELECT * FROM table;.

It has multiple categories of commands, each with a specific purpose. If you’ve ever been confused between DDL, DML, DQL, DCL, and TCL, this blog will clear it up for you with simple explanations and examples.

🏗️ 1. DDL (Data Definition Language)

Definition:

DDL commands are used to define and manage the structure of database objects like tables, schemas, and indexes.

Common Commands:

• CREATE
• ALTER
• DROP
• TRUNCATE
• RENAME

Examples:

-- Create a table
CREATE TABLE Students (
    StudentID INT PRIMARY KEY,
    Name VARCHAR(50),
    Age INT
);

-- Alter table to add a new column
ALTER TABLE Students ADD Email VARCHAR(100);

-- Drop a table
DROP TABLE Students;

-- Truncate a table (removes all data but keeps structure)
TRUNCATE TABLE Students;

-- Rename a table
ALTER TABLE Students RENAME TO Learners;

✏️ 2. DML (Data Manipulation Language)

Definition:

DML commands are used to manipulate the actual data stored inside tables.

Common Commands:

• INSERT
• UPDATE
• DELETE
• MERGE

Examples:

-- Insert a new record
INSERT INTO Students (StudentID, Name, Age) VALUES (1, 'Alice', 20);

-- Update existing data
UPDATE Students SET Age = 21 WHERE StudentID = 1;

-- Delete a record
DELETE FROM Students WHERE StudentID = 1;

-- Merge data (commonly used in SQL Server/Oracle)
MERGE INTO Students S
USING NewStudents N
ON (S.StudentID = N.StudentID)
WHEN MATCHED THEN UPDATE SET S.Age = N.Age
WHEN NOT MATCHED THEN INSERT (StudentID, Name, Age) VALUES (N.StudentID, N.Name, N.Age);

🔍 3. DQL (Data Query Language)

Definition:

DQL is focused on querying and retrieving data. The main command is SELECT.

But querying becomes powerful with the use of clauses and operators.

Main Command:

• SELECT

Clauses:

• WHERE – filter rows
• GROUP BY – group rows by column values
• HAVING – filter groups
• ORDER BY – sort results
• LIMIT / TOP – restrict the number of rows

Operators:

• LIKE – pattern matching
• IN – match any value in a list
• BETWEEN – range check
• Comparison (=, >, <, >=, <=)
• Logical (AND, OR, NOT)

Examples:

-- Simple SELECT
SELECT Name, Age FROM Students;

-- WHERE clause
SELECT * FROM Students WHERE Age > 18;

-- LIKE operator
SELECT * FROM Students WHERE Name LIKE 'A%';

-- IN operator
SELECT * FROM Students WHERE Age IN (18, 20, 22);

-- BETWEEN operator
SELECT * FROM Students WHERE Age BETWEEN 18 AND 25;

-- GROUP BY + HAVING
SELECT Age, COUNT(*) AS Count
FROM Students
GROUP BY Age
HAVING COUNT(*) > 2;

-- ORDER BY
SELECT * FROM Students ORDER BY Age DESC;

-- LIMIT (MySQL/PostgreSQL)
SELECT * FROM Students LIMIT 5;

-- TOP (SQL Server)
SELECT TOP 5 * FROM Students;

🔑 4. DCL (Data Control Language)

Definition:

DCL commands are used to control access to data inside the database.

Common Commands:

• GRANT
• REVOKE

Examples:

-- Grant SELECT permission
GRANT SELECT ON Students TO user1;

-- Revoke permission
REVOKE SELECT ON Students FROM user1;

🔄 5. TCL (Transaction Control Language)

Definition:

TCL commands handle transactions in databases. They help maintain data consistency by grouping multiple DML operations.

Common Commands:

• COMMIT
• ROLLBACK
• SAVEPOINT
• SET TRANSACTION

Examples:

-- Start a transaction
BEGIN;

-- Insert data
INSERT INTO Students (StudentID, Name, Age) VALUES (2, 'Bob', 22);

-- Commit changes
COMMIT;

-- Rollback example
BEGIN;
UPDATE Students SET Age = 25 WHERE StudentID = 2;
ROLLBACK; -- Undo the update

-- Savepoint example
BEGIN;
INSERT INTO Students VALUES (3, 'Charlie', 23);
SAVEPOINT sp1;
UPDATE Students SET Age = 30 WHERE StudentID = 3;
ROLLBACK TO sp1; -- Undo update but keep insert
COMMIT;

📌 Summary: DQL at the Core

• DQL = SELECT + Clauses + Operators
• It’s what we use the most when working with databases daily.
• DDL defines structure, DML manipulates data, DCL controls access, and TCL ensures safe transactions.

📊 Quick Reference Comparison Table

🚀 Takeaway

Think of SQL like this:

• DDL builds the house 🏠
• DML arranges the furniture 🪑
• DQL lets you explore inside 🔍
• DCL decides who gets the keys 🔑
• TCL ensures everything stays safe and consistent 🔄

Master these categories, and you’ll have full control over any database you work with!