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
Category | Commands | Clauses (DQL only) | Operators (DQL only) |
---|---|---|---|
DDL | CREATE, ALTER, DROP, TRUNCATE, RENAME | - | - |
DML | INSERT, UPDATE, DELETE, MERGE | - | - |
DQL | SELECT | WHERE, GROUP BY, HAVING, ORDER BY, LIMIT, TOP | LIKE, IN, BETWEEN, =, >, <, AND, OR, NOT |
DCL | GRANT, REVOKE | - | - |
TCL | COMMIT, ROLLBACK, SAVEPOINT, SET TRANSACTION | - | - |
π 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!
Top comments (0)