SQL Commands (DDL, DML, DCL, TCL, DQL): Types, Syntax, and Examples

Overview

SQL, which means “Structured Query Language,” is the primary language used for managing and manipulating relational databases. We use SQL commands to tell the database what actions we want it to perform.

What are SQL commands?

SQL commands are the keywords that tell the database what kind of action to perform, like CREATE, SELECT, or INSERT. These commands perform various database operations, such as creating tables, inserting data, querying information, and controlling access and security.

SQL Statement: It is a complete instruction you give to the database. It is made up of one or more commands and other elements like table names, columns, values, or conditions. For example, “CREATE TABLE Customers” is a statement.

A SQL Query is a type of SQL statement that asks the database to return data. It always starts with the SELECT command.

Five Types of SQL Commands

SQL commands are divided into five main groups based on their use. Knowing these groups helps you understand what different commands are meant to do.

The five types of SQL commands are:

Learn all the basics of SQL with these Free SQL Courses

Each type of SQL command has its own job. Some build the structure of the database. Others work with the actual data inside. Now let us understand them in detail.

DDL Commands – Building the Structure

DDL stands for Data Definition Language. These commands are used to build and manage the structure of the database. They set up where your data will be stored.

Key DDL Commands and what they mean:

1. CREATE: This command is used to create a new database object. For example, creating a new table, a view, or a database.

CREATE TABLE Employees (
    EmployeeID INT PRIMARY KEY,
    FirstName VARCHAR(50),
    LastName VARCHAR(50),
    ...
);

2. ALTER: This command is used to modify an existing database object, such as adding, deleting, or modifying columns in an existing table.

ALTER TABLE Employees
ADD Email VARCHAR(100),
DROP COLUMN PhoneNumber,
MODIFY COLUMN LastName VARCHAR(150);

3. DROP: This command is used to delete an entire database object, like a table, column, view, index, or even a database itself.

-- 1. Drop a column from the Employees table
ALTER TABLE Employees
DROP COLUMN Email;

-- 2. Drop the Employees table entirely
DROP TABLE Employees;

-- 3. Drop the database (for example, named CompanyDB)
DROP DATABASE CompanyDB;

4. TRUNCATE: This command is used to delete all records from a table, without deleting the table. It resets the table to empty, but keeps its structure (columns, data types, constraints). It’s a fast way to clear large data from a table.

TRUNCATE TABLE Employees;

5. RENAME: Used to change the name of a database object (like a table or column).

-- Rename the table
RENAME TABLE Employees TO Staff;

-- Rename a column (MySQL)
ALTER TABLE Staff
CHANGE COLUMN FullName Name VARCHAR(100);

6. COMMENT: Used to add descriptive text or annotations to database objects.

-- Comment on a table
COMMENT ON TABLE Employees IS 'Stores employee details including name, department, and salary.';

-- Comment on a column
COMMENT ON COLUMN Employees.Salary IS 'Monthly gross salary in USD.';

Use this Online SQL Compiler Tool to test different SQL Commands.

DML Commands – Adding and Changing Data

DML stands for Data Manipulation Language. These commands work with the actual data stored inside the database structure you created with DDL. We use them to add, change, or remove data rows.

Key DML Commands:

1. INSERT: Used to add new rows or records into a table.

INSERT INTO Employees (FirstName, LastName, Department) VALUES ('John', 'Doe', 'HR');

2. UPDATE: Used to change existing records in a table.

UPDATE Employees SET Salary = Salary * 1.1 WHERE Department = ‘Engineering’;

3. DELETE: Used to remove records from a table.

DELETE FROM Employees WHERE Department = 'Finance';

4. MERGE (Less common for beginners): Can combine adding, changing, or deleting in one command.

DML commands are always used to keep the information in your database correct and up-to-date.

DQL Commands – Getting Information (These are the “Queries”)

DQL stands for Data Query Language. This is the command type you will use most often. Its only job is to retrieve data out of the database based on rules you give it.

The central DQL command is SELECT. When people talk about “SQL Queries”, they usually mean using SELECT.

SELECT column1, column2, ...FROM table_nameWHERE condition;

How SELECT works:

You tell the database exactly what data you want to see and which table it should look in.

Simple SELECT examples:

• SELECT * FROM Customers; – Get all the data from the Customers table.
• SELECT FirstName, LastName FROM Customers; – Get only the first and last names from the Customers table.
• SELECT * FROM Customers WHERE City = 'New York'; – Get all data for customers who live in New York.

When you run a SELECT command, the database gives you back the information you asked for.

Also: Practice SQL Queries with Examples (Basic to Advanced)

DCL Commands – Who Can Do What

DCL stands for Data Control Language. These commands are used to manage access and permissions in a database. They control who can see, modify, or manage the data and database objects.

Key DCL Commands:

1. GRANT: Use this command to give specific users certain permissions, like giving a user permission to read or modify data in a table.

GRANT SELECT, INSERT ON Employees TO HR_Manager;

This grants the “HR_Manager” role the privileges to select and insert data into the “Employees” table.

2. REVOKE: This command is used to remove previously granted permissions from users. You can REVOKE their authorization if you don’t want the user to access specific data.

REVOKE DELETE ON Customers FROM Sales_Team;

This revokes the privilege to delete data from the “Customers” table from the “Sales_Team” role.

DCL commands are essential for keeping your database secure and ensuring only the right people can access or change the data.

TCL Commands – Making Changes Safely

TCL stands for Transaction Control Language. These commands help manage transactions. A transaction is a set of SQL actions that should all happen together as a single unit. TCL ensures that if something goes wrong during a set of changes, the database can go back to how it was before.

Key TCL Commands:

1. BEGIN: Starts a transaction. It marks the beginning of a series of operations that should be treated as one unit.

BEGIN;
-- SQL statements
COMMIT;

2. COMMIT: Used to save all the changes you made during a transaction permanently.

BEGIN;
-- SQL statements
COMMIT;

This example begins a transaction, performs SQL statements, and then commits the changes to the database.

3. ROLLBACK: Used to undo changes you made during a transaction if something went wrong.

BEGIN;
-- SQL statements
ROLLBACK;

This example begins a transaction, performs SQL statements, and then rolls back the changes, restoring the database to its previous state.

4. SAVEPOINT: It lets you set a point inside a transaction to which you can roll back if needed.

BEGIN;
-- SQL statements
SAVEPOINT my_savepoint;
-- More SQL statements
ROLLBACK TO my_savepoint;

This example creates a savepoint and later rolls back to that point, undoing some of the transaction’s changes.

These commands are very important for keeping your data consistent and safe, especially when several actions must happen correctly together.

Important SQL Commands

These are the most important and most used SQL Commands:

The SQL Command Line

Many database systems provide a command-line interface (CLI). It is a simple text window where you can type and run SQL commands directly.

Why use it?

• It is perfect for performing tasks quickly or running SQL scripts.
• You can interact directly with the database. No need for a separate application or graphical tool.
• It is handy for testing commands or managing the database efficiently.

Conclusion

SQL commands are the way you communicate with a database and tell it what to do. They fall into five main categories:

• DDL (Data Definition Language) — managing database structure
• DML (Data Manipulation Language) — working with the data itself
• DQL (Data Query Language) — retrieving data
• DCL (Data Control Language) — controlling permissions
• TCL (Transaction Control Language) — managing changes safely

Among these, queries, especially the SELECT command, are how you ask the database for information.

Learning these basic commands is a crucial first step to working effectively with data in almost any software system. As you practice more, you’ll become confident in directly “talking” to databases, not just using data, but controlling it.

Keep practicing! The more you use these commands, the easier it will become to get the database to do exactly what you need.

Other SQL Resources:

• SQL Operators with Examples
• SQL Constraints with Examples
• SQL Projects Ideas for Beginners & Beyond