SQL Commands Overview – Understanding the SQL Command Categories

Structured Query Language (SQL) is composed of various types of commands that serve different purposes. These commands allow users to create database structures, manipulate data, control access, and ensure consistent transactions. To master SQL, one must first understand the classification of these commands and their scope within a relational database management system (RDBMS).

In this chapter, we will dive deep into the four primary categories of SQL commands — DDL, DML, DCL, and TCL — along with detailed subcommands, syntax, examples, and best practices. This classification helps you structure your SQL operations logically and understand their real-world applications.

Categories of SQL Commands

Command Type	Description	Examples
DDL	Data Definition Language – defines database schema	CREATE, ALTER, DROP, TRUNCATE
DML	Data Manipulation Language – modifies data	SELECT, INSERT, UPDATE, DELETE
DCL	Data Control Language – manages user access	GRANT, REVOKE
TCL	Transaction Control Language – handles transactions	COMMIT, ROLLBACK, SAVEPOINT, SET TRANSACTION

Let’s explore each of these command types in depth.

Data Definition Language (DDL)

DDL commands are used to define or alter the structure of database objects like tables, schemas, indexes, and views. These operations are typically performed by database administrators or developers during database setup or schema changes.

1. CREATE

Creates a new database object.

CREATE TABLE Employees (
  EmployeeID INT PRIMARY KEY,
  Name VARCHAR(100),
  HireDate DATE,
  Salary DECIMAL(10, 2)
);

2. ALTER

Modifies an existing object structure.

ALTER TABLE Employees ADD COLUMN Department VARCHAR(50);

You can also drop or rename columns, change data types, or add constraints.

3. DROP

Completely removes a table, view, or other object.

DROP TABLE Employees;

Be cautious: DROP deletes data and structure — irreversible unless a backup exists.

4. TRUNCATE

Removes all records from a table, but retains the table structure.

TRUNCATE TABLE Employees;

TRUNCATE is faster than DELETE and cannot be rolled back in many RDBMSs.

✍️ Data Manipulation Language (DML)

DML commands are used for handling the data itself — retrieving, inserting, updating, and deleting records.

1. SELECT

Retrieves data from one or more tables.

SELECT Name, Salary FROM Employees WHERE Department = 'HR';

Supports filtering (WHERE), sorting (ORDER BY), grouping (GROUP BY), joins, subqueries, and more.

2. INSERT

Adds new records to a table.

INSERT INTO Employees (EmployeeID, Name, HireDate, Salary)
VALUES (101, 'Alice Smith', '2024-03-01', 65000.00);

Supports bulk inserts and INSERT INTO ... SELECT pattern.

3. UPDATE

Modifies existing records.

UPDATE Employees SET Salary = Salary * 1.10 WHERE Department = 'Sales';

Ensure WHERE clause is specific; otherwise, it may update all records.

4. DELETE

Removes records from a table.

DELETE FROM Employees WHERE EmployeeID = 101;

Use DELETE for selective removal. For all rows, consider TRUNCATE (DDL).

Best Practices:

• Always test with a SELECT query before DELETE or UPDATE
• Use transactions when performing critical DML

Data Control Language (DCL)

DCL commands are used to manage user privileges and permissions in a database. These commands are essential in multi-user environments for maintaining security and data access controls.

1. GRANT

Provides privileges to users.

GRANT SELECT, INSERT ON Employees TO john_doe;

Grants john_doe permission to read and insert into the Employees table.

2. REVOKE

Removes previously granted privileges.

REVOKE INSERT ON Employees FROM john_doe;

Typical Privileges:

• SELECT, INSERT, UPDATE, DELETE
• ALL PRIVILEGES
• EXECUTE (for stored procedures)

Best Practices:

• Follow principle of least privilege — grant only necessary rights
• Use roles for managing permissions in larger teams

Transaction Control Language (TCL)

TCL commands manage transactions, which are sequences of SQL operations executed as a unit. Transactions ensure data integrity and enable rollback in case of failure.

1. BEGIN or START TRANSACTION

Marks the beginning of a transaction (implicit in some databases).

START TRANSACTION;

2. COMMIT

Saves all changes made in the transaction.

COMMIT;

Once committed, changes are permanent.

3. ROLLBACK

Undoes changes made in the current transaction.

ROLLBACK;

Useful when an error occurs, or data integrity is violated.

4. SAVEPOINT

Defines a point to which a transaction can be rolled back.

SAVEPOINT sp1;
UPDATE Employees SET Salary = 0;
ROLLBACK TO sp1;

5. SET TRANSACTION

Configures transaction properties like isolation level.

SET TRANSACTION ISOLATION LEVEL READ COMMITTED;

Best Practices for TCL:

• Wrap multiple DML operations in a transaction
• Always handle exceptions and roll back on errors
• Use SAVEPOINT in complex business logic for partial rollbacks

Summary Table of SQL Commands

Command	Type	Description
CREATE	DDL	Create new tables, views, indexes
ALTER	DDL	Modify structure of existing object
DROP	DDL	Delete database objects permanently
TRUNCATE	DDL	Remove all rows (keep structure)
SELECT	DML	Retrieve data
INSERT	DML	Add new records
UPDATE	DML	Modify existing records
DELETE	DML	Remove specific records
GRANT	DCL	Give privileges
REVOKE	DCL	Remove privileges
COMMIT	TCL	Save transaction changes
ROLLBACK	TCL	Undo transaction changes
SAVEPOINT	TCL	Create rollback point in transaction

Real-World Scenario

Let’s imagine a banking system where the administrator creates tables using DDL, performs deposits and withdrawals using DML, secures access using DCL, and ensures integrity using TCL.

-- Step 1: Define structure (DDL)
CREATE TABLE Accounts (
  AccountID INT PRIMARY KEY,
  HolderName VARCHAR(100),
  Balance DECIMAL(10,2)
);

-- Step 2: Modify data (DML + TCL)
START TRANSACTION;
UPDATE Accounts SET Balance = Balance - 1000 WHERE AccountID = 1;
UPDATE Accounts SET Balance = Balance + 1000 WHERE AccountID = 2;
COMMIT;

-- Step 3: Assign permissions (DCL)
GRANT SELECT, UPDATE ON Accounts TO finance_team;

This structured approach using different SQL command types ensures reliability, security, and clarity in database operations.

Common Pitfalls to Avoid

1. Dropping tables without backups – always back up before DROP
2. Forgetting WHERE clause in UPDATE/DELETE – can affect all rows
3. Not using transactions – especially in critical updates
4. Excessive permissions – avoid using GRANT ALL unless necessary
5. Assuming COMMIT happens automatically – some systems require explicit COMMIT

Summary

Each SQL command serves a distinct purpose in managing data and its environment:

• DDL helps you define and evolve your database structure.
• DML lets you interact with data.
• DCL enforces access control and security.
• TCL ensures consistency through transaction management.

A clear understanding of these command categories forms the bedrock of SQL mastery and helps developers build reliable, maintainable, and secure data-driven applications.

In the next chapter, we’ll start exploring Data Definition Language (DDL) commands in detail, beginning with how to create tables, schemas, indexes, and define constraints.