SQL (Structured Query Language)
Introduction: SQL stands for Structured Query Language. It is a standard language used to
communicate with databases. SQL helps users store, retrieve, update, and delete data from a database.
It is widely used in Database Management Systems (DBMS) such as MySQL, Oracle Database, and
Microsoft SQL Server.
Why SQL is Used
To create databases and tables
To insert data into tables
To retrieve data from databases
To update existing data
To delete unwanted data
To manage database security
Advantages of SQL
Easy to learn and use.
Fast data retrieval.
Supports large databases.
Provides security features.
Reduces data redundancy.
Works with many DBMS software.
Disadvantages of SQL
Complex queries can be difficult.
Different DBMS may use different SQL versions.
Large databases require powerful hardware.
SQL Types
SQL commands are divided into five main types:
DDL (Data Definition Language)
DML (Data Manipulation Language)
DQL (Data Query Language)
DCL (Data Control Language)
TCL (Transaction Control Language)
1. DDL (Data Definition Language)
Definition
DDL is used to create, modify, and delete the structure of database objects like databases and tables.
Main DDL Commands
a) CREATE
Creates a new database or table.
Example:
CREATE TABLE Student(
ID INT,
Name VARCHAR(50),
Age INT
);
b) ALTER
Changes the structure of an existing table.
Example:
ALTER TABLE Student
ADD Address VARCHAR(100);
c) DROP
Deletes a table or database permanently.
Example:
DROP TABLE Student;
d) TRUNCATE
Deletes all records from a table but keeps the table structure.
Example:
TRUNCATE TABLE Student;
e) RENAME
Changes the name of a table.
Example:
RENAME TABLE Student TO Students;
Uses of DDL
Create tables
Modify tables
Delete tables
Change table names
2. DML (Data Manipulation Language)
Definition
DML is used to insert, update, and delete data inside tables.
Main DML Commands
a) INSERT
Adds new records into a table.
Example:
INSERT INTO Student
VALUES(1,'Ali',20);
b) UPDATE
Changes existing data.
Example:
UPDATE Student
SET Age=21
WHERE ID=1;
c) DELETE
Deletes selected records.
Example:
DELETE FROM Student
WHERE ID=1;
Uses of DML
Add new records
Modify records
Remove records
3. DQL (Data Query Language)
Definition
DQL is used to retrieve (display) data from the database.
Main Command
SELECT
Displays data from one or more tables.
Example
SELECT * FROM Student;
Display only names:
SELECT Name FROM Student;
Using WHERE condition:
SELECT * FROM Student
WHERE Age>18;
Uses of DQL
View records
Search data
Filter data
Generate reports
4. DCL (Data Control Language)
Definition
DCL is used to control access and permissions in a database.
Main Commands
a) GRANT
Gives permission to a user.
Example
GRANT SELECT
ON Student
TO Ahmed;
b) REVOKE
Removes permission from a user.
Example
REVOKE SELECT
ON Student
FROM Ahmed;
Uses of DCL
Give user permissions
Remove permissions
Improve database security
5. TCL (Transaction Control Language)
Definition
TCL manages transactions. A transaction is a group of SQL commands treated as one unit of work.
Main Commands
a) COMMIT
Saves all changes permanently.
Example
COMMIT;
b) ROLLBACK
Cancels changes made after the last commit.
Example
ROLLBACK;
c) SAVEPOINT
Creates a point to return to during a transaction.
Example
SAVEPOINT A;
Uses of TCL
Save changes
Undo changes
Manage transactions safely
Real-Life Example
Imagine a school database:
DDL: Create the Student table.
DML: Add, update, or delete student records.
DQL: Display student information.
DCL: Give teachers permission to view records.
TCL: Save or undo changes after updating student marks.
Conclusion
SQL is the most widely used language for managing databases. Its five types—DDL, DML, DQL, DCL, and
TCL—perform different tasks, from creating tables to controlling user access and managing transactions.
Understanding these SQL command types is essential for working with any database system and is a
very important topic in database exams.