SQL

SQL Statements

📝 SQL Statements are commands used to communicate with a relational database. They allow you to create database objects, insert data, retrieve information, update existing records, delete unwanted data, manage transactions, and control user permissions. Every interaction with a database is performed using one or more SQL statements.

📖 What are SQL Statements?

An SQL statement is a complete instruction given to a Database Management System (DBMS). The database interprets the statement, performs the requested operation, and returns a result or confirmation message.

Information

Every SQL statement follows a defined syntax and typically ends with a ; (semicolon), which marks the end of the statement.

🗂️ Categories of SQL Statements

SQL statements are commonly grouped into five major categories based on their purpose.

Category	Full Form	Purpose
DDL	Data Definition Language	Defines and modifies database structures.
DML	Data Manipulation Language	Inserts, updates, and deletes data.
DQL	Data Query Language	Retrieves data from tables.
DCL	Data Control Language	Controls user permissions and access.
TCL	Transaction Control Language	Manages database transactions.

🏗️ Data Definition Language (DDL)

DDL statements are used to create and modify the structure of database objects such as tables, views, indexes, and schemas.

1️⃣ CREATE

The CREATE statement creates new database objects.

Create a Table

CREATE TABLE Students (
    StudentID INT PRIMARY KEY,
    Name VARCHAR(100),
    Department VARCHAR(50)
);

2️⃣ ALTER

The ALTER statement modifies an existing database object.

Add a New Column

ALTER TABLE Students
ADD Email VARCHAR(100);

3️⃣ DROP

The DROP statement permanently removes a database object.

Drop a Table

DROP TABLE Students;

4️⃣ TRUNCATE

TRUNCATE removes all rows from a table while preserving its structure.

Truncate a Table

TRUNCATE TABLE Students;

Warning

DROP removes both the table structure and its data, whereas TRUNCATE removes only the data while keeping the table intact.

✏️ Data Manipulation Language (DML)

DML statements are used to add, modify, and remove records stored in tables.

1️⃣ INSERT

Insert a Record

INSERT INTO Students (StudentID, Name, Department)
VALUES (101, 'Alice', 'Computer Science');

2️⃣ UPDATE

Update a Record

UPDATE Students
SET Department = 'Information Technology'
WHERE StudentID = 101;

3️⃣ DELETE

Delete a Record

DELETE FROM Students
WHERE StudentID = 101;

Important

Always use a WHERE clause with UPDATE and DELETE unless you intentionally want to affect every row in the table.

🔍 Data Query Language (DQL)

DQL consists primarily of the SELECT statement, which retrieves data from one or more tables.

Retrieve Student Records

SELECT StudentID, Name, Department
FROM Students
WHERE Department = 'Computer Science'
ORDER BY Name ASC;

🔐 Data Control Language (DCL)

DCL statements manage user privileges and database security.

1️⃣ GRANT

Grant SELECT Permission

GRANT SELECT
ON Students
TO User1;

2️⃣ REVOKE

Revoke SELECT Permission

REVOKE SELECT
ON Students
FROM User1;

🔄 Transaction Control Language (TCL)

TCL statements manage transactions, ensuring that groups of related operations are completed successfully or rolled back if an error occurs.

1️⃣ COMMIT

Commit Changes

COMMIT;

2️⃣ ROLLBACK

Rollback Changes

ROLLBACK;

3️⃣ SAVEPOINT

Create a Savepoint

SAVEPOINT UpdatePoint;

📊 Summary of Common SQL Statements

Statement	Category	Purpose
CREATE	DDL	Create database objects.
ALTER	DDL	Modify database objects.
DROP	DDL	Remove database objects.
TRUNCATE	DDL	Remove all rows from a table.
INSERT	DML	Add new records.
UPDATE	DML	Modify existing records.
DELETE	DML	Delete records.
SELECT	DQL	Retrieve data.
GRANT	DCL	Grant user privileges.
REVOKE	DCL	Remove user privileges.
COMMIT	TCL	Save a transaction.
ROLLBACK	TCL	Undo a transaction.
SAVEPOINT	TCL	Create a transaction checkpoint.

💼 Real-World Example

Consider a student management system. An administrator first creates the Students table using CREATE, adds student records with INSERT, retrieves information using SELECT, updates student details with UPDATE, removes outdated records using DELETE, and commits the changes using COMMIT. User access is managed through GRANT and REVOKE.

✅ Best Practices

Best Practice

Use descriptive table and column names, format SQL statements for readability, always test UPDATE and DELETE queries with a SELECT statement first, use transactions for related operations, and grant only the minimum permissions required to users.

🚀 Key Points to Remember

📌 SQL statements are the primary way to communicate with a database.
📌 DDL defines database structures.
📌 DML modifies the data stored in tables.
📌 DQL retrieves data using the SELECT statement.
📌 DCL manages user access and permissions.
📌 TCL ensures reliable transaction management.

>>"Well-structured SQL statements make databases easier to manage, maintain, and scale."

Summary

✅ SQL statements are the building blocks of database operations. By understanding the five major categories—DDL, DML, DQL, DCL, and TCL—you can confidently create, manage, query, secure, and maintain relational databases.