Software Development DDL, DML, DCL and TCL Explained

DDL, DML, DCL and TCL Commands

SQL commands are divided into categories based on their purpose.

Category	Full form	Purpose	Common commands
DDL	Data Definition Language	Defines database structure	`CREATE`, `ALTER`, `DROP`, `TRUNCATE`
DML	Data Manipulation Language	Adds or changes data	`INSERT`, `UPDATE`, `DELETE`
DCL	Data Control Language	Controls permissions	`GRANT`, `DENY`, `REVOKE`
TCL	Transaction Control Language	Manages transactions	`BEGIN TRANSACTION`, `COMMIT`, `ROLLBACK`, `SAVE TRANSACTION`

1. DDL — Data Definition Language

DDL commands create or modify database objects such as tables.

CREATE TABLE Employee
(
    EmployeeId INT PRIMARY KEY,
    EmployeeName VARCHAR(100)
);

ALTER TABLE Employee
ADD Salary DECIMAL(10,2);

TRUNCATE TABLE Employee;

DROP TABLE Employee;

CREATE — creates an object
ALTER — changes its structure
TRUNCATE — removes all rows while keeping the table
DROP — removes the complete object

2. DML — Data Manipulation Language

DML commands add, modify or remove table data.

INSERT INTO Employee (EmployeeId, EmployeeName)
VALUES (1, 'Syed');

UPDATE Employee
SET EmployeeName = 'Mohamed'
WHERE EmployeeId = 1;

DELETE FROM Employee
WHERE EmployeeId = 1;

INSERT — adds records
UPDATE — modifies records
DELETE — removes records

SELECT is commonly classified as DQL—Data Query Language, because it retrieves data.

SELECT * FROM Employee;

3. DCL — Data Control Language

DCL commands control access and permissions.

GRANT SELECT ON dbo.Employee TO User1;

DENY DELETE ON dbo.Employee TO User1;

REVOKE SELECT ON dbo.Employee FROM User1;

GRANT — gives permission
DENY — explicitly blocks permission
REVOKE — removes a previously granted or denied permission

4. TCL — Transaction Control Language

TCL commands manage a group of database operations as one unit.

BEGIN TRANSACTION;

UPDATE Accounts
SET Balance = Balance - 1000
WHERE AccountId = 1;

UPDATE Accounts
SET Balance = Balance + 1000
WHERE AccountId = 2;

COMMIT TRANSACTION;

If an error occurs:

ROLLBACK TRANSACTION;

BEGIN TRANSACTION — starts a transaction
COMMIT — permanently saves the changes
ROLLBACK — cancels the changes
SAVE TRANSACTION — creates a savepoint inside a transaction

Simple memory tip

DDL → Define structure
DML → Modify data
DCL → Control access
TCL → Control transactions

DDL, DML, DCL and TCL