Types of SQL Commands

When working with databases, you use SQL (Structured Query Language) to perform different kinds of operations — such as creating tables, adding data, updating records, or managing user permissions. These operations are categorized into specific groups known as types of SQL commands. Each group has a clear purpose and helps keep database management organized, structured, and efficient.

 Understanding SQL Command Categories

SQL commands are divided into five major categories based on their functionality:

1. DDL (Data Definition Language)
2. DML (Data Manipulation Language)
3. DQL (Data Query Language)
4. DCL (Data Control Language)
5. TCL (Transaction Control Language)

This classification makes it easier to understand what kind of command you are using and what effect it has on the database. Let’s explore each of them in detail.

 DDL – Data Definition Language

DDL (Data Definition Language) commands are used to define and manage the structure of a database and its objects such as tables, schemas, and indexes. In simple terms, DDL commands shape your database — they help you create new tables, modify existing ones, or delete them entirely.

Some common DDL commands include:

• CREATE – used to create new tables or databases.
• ALTER – used to modify the structure of a table (like adding or deleting a column).
• DROP – used to permanently delete a table or database.
• TRUNCATE – used to remove all rows from a table but keep its structure intact.

Example:

CREATE TABLE Students (

    ID INT PRIMARY KEY,

    Name VARCHAR(50),

    Age INT

);

When you execute this command, a new table named Students is created in the database. One important thing to note is that DDL commands are auto-committed, which means once executed, the changes cannot be rolled back.

 DML – Data Manipulation Language

Once you’ve created your database structure, the next step is to add and manage data within it. This is where DML (Data Manipulation Language) commands come in. These commands deal directly with the records stored in your tables.

The most commonly used DML commands are:

• INSERT – adds new records to a table.
• UPDATE – modifies existing records.
• DELETE – removes records from a table.

Example:

INSERT INTO Students (ID, Name, Age)

VALUES (1, 'Rahul', 21);

Here, we are inserting a new student record into the table. Unlike DDL, DML commands are not auto-committed, which means you can ROLLBACK changes if needed before finalizing them.

 DQL – Data Query Language

DQL (Data Query Language) is all about retrieving data. Whenever you want to view specific information from one or more tables, you use DQL commands. The most commonly used and powerful DQL command is SELECT.

The SELECT command allows you to specify exactly which data you want to view, and from which table.

Example:

SELECT Name, Age FROM Students WHERE Age > 18;

This query will fetch the names and ages of all students who are older than 18. DQL commands are used frequently in reporting, analytics, and everyday database operations since they help you analyze and display data in meaningful ways.

 DCL – Data Control Language

In a multi-user environment, it’s important to control who can access what in a database. DCL (Data Control Language) commands are used to manage permissions and access control. Database administrators often use these commands to ensure security and privacy of data.

The two main DCL commands are:

• GRANT – gives specific privileges to users (e.g., permission to read or modify data).
• REVOKE – removes privileges that were previously granted.

Example:

GRANT SELECT ON Students TO user1;

This command allows a user named user1 to view data from the Students table. Using DCL helps protect sensitive information by ensuring that only authorized people can perform certain actions.

 TCL – Transaction Control Language

When working on a set of SQL operations that depend on each other, TCL (Transaction Control Language) ensures that all commands either complete successfully or fail together. This helps maintain data consistency and prevents errors or partial updates in your database.

The key TCL commands are:

• COMMIT – saves all changes made during a transaction.
• ROLLBACK – undoes changes if an error occurs.
• SAVEPOINT – sets a checkpoint to roll back to a specific point if needed.