SQL stands for Structured Query Language. It is used to create, manage, modify, and retrieve data from relational databases.
SQL commands are mainly divided into five categories:
- DDL – Data Definition Language
- DML – Data Manipulation Language
- DCL – Data Control Language
- TCL – Transaction Control Language
- DQL – Data Query Language
1. DDL – Data Definition Language
DDL is used to create and modify the structure of database objects, such as tables.
CREATE
The CREATE command is used to create a new table.
CREATE TABLE students (
id INT,
name VARCHAR(50),
age INT
);
ALTER
The ALTER command is used to modify an existing table.
ALTER TABLE students
ADD email VARCHAR(100);
This adds a new email column to the table.
DROP
The DROP command permanently removes a table.
DROP TABLE students;
It deletes both the table structure and its data.
TRUNCATE
The TRUNCATE command removes all records from a table while keeping the table structure.
TRUNCATE TABLE students;
RENAME
The RENAME command changes the name of a table.
ALTER TABLE students
RENAME TO learners;
In short: DDL is mainly used for database structure.
2. DML – Data Manipulation Language
DML is used to insert, update, and delete data in a table.
INSERT
The INSERT command adds new records to a table.
INSERT INTO students (id, name, age)
VALUES (1, 'Ram', 20);
Multiple records can also be inserted:
INSERT INTO students (id, name, age)
VALUES
(2, 'Sita', 21),
(3, 'Hari', 19);
UPDATE
The UPDATE command modifies existing records.
UPDATE students
SET age = 22
WHERE id = 2;
The WHERE clause specifies which record should be updated.
DELETE
The DELETE command removes records from a table.
DELETE FROM students
WHERE id = 3;
The WHERE clause is important because without it, all records may be deleted.
MERGE
The MERGE command is used by database systems that support it to insert or update data depending on whether a matching record exists.
MERGE INTO students AS target
USING new_students AS source
ON target.id = source.id
WHEN MATCHED THEN
UPDATE SET name = source.name
WHEN NOT MATCHED THEN
INSERT (id, name)
VALUES (source.id, source.name);
In short: DML is used for data manipulation.
3. DCL – Data Control Language
DCL is used to control access and permissions in a database.
GRANT
The GRANT command gives permissions to a user.
GRANT SELECT ON students TO user1;
This gives user1 permission to read the students table.
REVOKE
The REVOKE command removes permissions from a user.
REVOKE SELECT ON students FROM user1;
In short: DCL is used for permissions and security.
4. TCL – Transaction Control Language
TCL is used to manage transactions in a database.
A transaction is a group of database operations treated as one unit.
COMMIT
COMMIT permanently saves the changes.
COMMIT;
ROLLBACK
ROLLBACK cancels changes that have not been committed.
ROLLBACK;
SAVEPOINT
SAVEPOINT creates a temporary point inside a transaction.
SAVEPOINT point1;
You can return to that point using:
ROLLBACK TO point1;
Example
UPDATE students
SET age = 25
WHERE id = 1;
SAVEPOINT point1;
DELETE FROM students
WHERE id = 2;
ROLLBACK TO point1;
COMMIT;
Here, the transaction returns to point1, undoing changes made after that savepoint, and then commits the remaining changes.
In short: TCL is used for transaction management.
5. DQL – Data Query Language
DQL is used to retrieve data from a database.
SELECT
The SELECT command retrieves data.
SELECT * FROM students;
This displays all columns and records.
To select specific columns:
SELECT name, age
FROM students;
WHERE
The WHERE clause filters records.
SELECT *
FROM students
WHERE age > 20;
ORDER BY
The ORDER BY clause sorts the results.
SELECT *
FROM students
ORDER BY age DESC;
ASC sorts in ascending order, while DESC sorts in descending order.
GROUP BY
The GROUP BY clause groups records with the same values.
SELECT age, COUNT(*) AS total
FROM students
GROUP BY age;
JOIN
A JOIN combines related data from multiple tables.
SELECT students.name, courses.course_name
FROM students
JOIN courses
ON students.id = courses.student_id;
In short: DQL is used for retrieving and querying data.
Difference Between SQL Command Types
| Type | Full Form | Purpose | Examples |
|---|---|---|---|
| DDL | Data Definition Language | Defines database structure | CREATE, ALTER, DROP, TRUNCATE, RENAME |
| DML | Data Manipulation Language | Manipulates data | INSERT, UPDATE, DELETE, MERGE |
| DCL | Data Control Language | Controls permissions | GRANT, REVOKE |
| TCL | Transaction Control Language | Controls transactions | COMMIT, ROLLBACK, SAVEPOINT |
| DQL | Data Query Language | Retrieves data | SELECT |
Easy Way to Remember
DDL → Structure
DML → Data
DCL → Control/Permissions
TCL → Transactions
DQL → Query
Therefore:
SQL = DDL + DML + DCL + TCL + DQL
These command groups together provide the main tools needed to create database structures, manipulate data, retrieve information, control access, and manage transactions.
