SQL Command Categories
SQL commands are divided into different categories based on the type of operation they perform. Understanding these categories makes it easier to identify which SQL command should be used for a particular database operation.
In SQL Server, commands are commonly studied under five major categories: DDL, DML, DQL, DCL and TCL. Each category has a different responsibility when working with databases and data.
SQL Command Categories Overview
| Category | Full Form | Main Purpose | Common Commands |
|---|---|---|---|
| DDL | Data Definition Language | Defines and changes the structure of database objects. | CREATE, ALTER, DROP, TRUNCATE |
| DML | Data Manipulation Language | Adds, modifies and removes data. | INSERT, UPDATE, DELETE |
| DQL | Data Query Language | Retrieves data from tables. | SELECT |
| DCL | Data Control Language | Controls permissions and access. | GRANT, REVOKE |
| TCL | Transaction Control Language | Manages transactions. | COMMIT, ROLLBACK |
1. DDL – Data Definition Language
DDL stands for Data Definition Language. DDL commands are mainly used to define and modify the structure of database objects.
Database objects include databases, tables, views and other objects. DDL commands do not primarily deal with changing individual records; they work with the structure of objects.
Common DDL Commands
- CREATE – Creates a database object.
- ALTER – Changes the structure of an existing object.
- DROP – Removes a database object.
- TRUNCATE – Removes all rows from a table while keeping the table structure.
CREATE Example
CREATE TABLE Students
(
StudentId INT,
StudentName VARCHAR(100),
Age INT
);
ALTER Example
ALTER TABLE Students
ADD Email VARCHAR(100);
DROP Example
DROP TABLE Students;
2. DML – Data Manipulation Language
DML stands for Data Manipulation Language. DML commands are used to work with the actual records stored inside database tables.
When we want to add a new record, change an existing record or remove a record, we use DML commands.
Common DML Commands
- INSERT – Adds new records.
- UPDATE – Modifies existing records.
- DELETE – Removes records.
INSERT Example
INSERT INTO Students
(
StudentId,
StudentName,
Age
)
VALUES
(
101,
'Rahul',
22
);
UPDATE Example
UPDATE Students
SET Age = 23
WHERE StudentId = 101;
DELETE Example
DELETE FROM Students
WHERE StudentId = 101;
3. DQL – Data Query Language
DQL stands for Data Query Language. It is used to retrieve data from database tables.
The main command associated with data retrieval is SELECT.
SELECT Example
SELECT *
FROM Students;
Selecting Specific Columns
SELECT StudentName, Age
FROM Students;
Filtering Data
SELECT *
FROM Students
WHERE Age > 18;
4. DCL – Data Control Language
DCL stands for Data Control Language. DCL commands are used to control access and permissions on database objects.
Database administrators can use permissions to determine what operations a user or database principal is allowed to perform.
Common DCL Commands
- GRANT – Gives permissions.
- REVOKE – Removes previously granted permissions.
GRANT Example
GRANT SELECT
ON Students
TO UserName;
REVOKE Example
REVOKE SELECT
ON Students
FROM UserName;
5. TCL – Transaction Control Language
TCL stands for Transaction Control Language. It is used to manage transactions and control changes made during a transaction.
A transaction is a logical unit of work containing one or more database operations.
Common TCL Commands
- COMMIT – Permanently saves the changes made in a transaction.
- ROLLBACK – Undoes changes according to the transaction state.
- SAVE TRANSACTION – Creates a savepoint within a transaction.
Transaction Example
BEGIN TRANSACTION;
UPDATE Students
SET Age = 25
WHERE StudentId = 101;
COMMIT;
ROLLBACK Example
BEGIN TRANSACTION;
UPDATE Students
SET Age = 30
WHERE StudentId = 101;
ROLLBACK;
SQL Command Categories Flow
The complete classification can be understood using the following diagram:
SQL Commands
|
---------------------------------------------
| | | | |
DDL DML DQL DCL TCL
| | | | |
Structure Data Retrieve Access Transaction
| | | | |
CREATE INSERT SELECT GRANT COMMIT
ALTER UPDATE REVOKE ROLLBACK
DROP DELETE
TRUNCATE
Real-World Understanding
Suppose we are developing a Student Management System. Different SQL categories will be used for different tasks.
| Requirement | Category | Command |
|---|---|---|
| Create Students table | DDL | CREATE |
| Add a new student | DML | INSERT |
| Display students | DQL | SELECT |
| Change student's age | DML | UPDATE |
| Delete a student | DML | DELETE |
| Give SELECT permission | DCL | GRANT |
| Save transaction changes | TCL | COMMIT |
Quick Comparison
| Category | Works With | Main Purpose |
|---|---|---|
| DDL | Database Structure | Create / Change / Remove structure |
| DML | Table Data | Insert / Update / Delete records |
| DQL | Table Data | Retrieve records |
| DCL | Permissions | Control access |
| TCL | Transactions | Manage transaction changes |
Important Points
- DDL mainly deals with database object structure.
- DML works with records stored in tables.
- SELECT is used to retrieve data.
- DCL is related to database permissions.
- TCL is used to manage transactions.
Summary
In this section, we learned the major SQL command categories: DDL, DML, DQL, DCL and TCL. We understood the purpose of each category, studied their common commands and looked at practical examples of where each category is used in SQL Server.