SQL Commands: Types (DDL, DML, DCL, TCL, DQL), 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.

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:

Each type of SQL command serves a specific purpose. Some build the structure of the database. Others work with the actual data inside. Now, let us understand them in detail.

1. DDL Commands – To Build 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:

CREATE: Used to create a new database object, like a new table.

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

ALTER: Used to modify an existing database object, such as adding or deleting columns.

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

DROP: Used to delete an entire database object, like a table or a database.

-- Drop the Employees table entirely
DROP TABLE Employees;

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

TRUNCATE: Used to delete all records from a table, without deleting the table itself.

TRUNCATE TABLE Employees;

RENAME: Used to change the name of a database object.

-- Rename the table
RENAME TABLE Employees TO Staff;

COMMENT: Used to add descriptive text to database objects.

-- Comment on a table
COMMENT ON TABLE Employees IS 'Stores employee details.';

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

2. DML Commands – To Add and Change 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:

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

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

INSERT INTO SELECT: Copies data from one table and inserts it into another.

-- Assumes a HighEarners table exists with a similar structure
INSERT INTO HighEarners (employee_id, first_name, salary)
SELECT employee_id, first_name, salary
FROM employees
WHERE salary > 80000;

UPDATE: Used to change existing records in a table. Always use a WHERE clause unless you intend to change every row.

-- Give a 10% raise to everyone in the 'Engineering' department
UPDATE Employees
SET Salary = Salary * 1.1
WHERE Department = 'Engineering';

DELETE: Used to remove records from a table.

DELETE FROM Employees WHERE Department = 'Finance';

DELETE with a Join: Deletes records from one table based on a condition in another.

-- Example: Delete employees from a temp_employees table if they are in the 'Marketing' department.
DELETE t
FROM temp_employees t
JOIN departments d ON t.department_id = d.department_id
WHERE d.department_name = 'Marketing';

3. DQL Commands – To Get Information (These are the “Queries”)

DQL stands for Data Query Language. This category contains the single command you will use most often: SELECT. Its sole purpose is to retrieve data from the database. When people talk about writing "SQL Queries," they are almost always referring to the SELECT statement.

For example:

SELECT FirstName, LastName FROM Customers WHERE City = 'New York';

The SELECT statement has a vast number of clauses and functions like JOIN, GROUP BY, and window functions that allow for powerful data analysis.

→ For a deep dive with over 27 examples, see our complete guide: Practical SQL Query Examples to Master Data Analysis

4. 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.

Key DCL Commands:

GRANT: Gives specific users permissions to perform actions.

-- Grants the HR_Manager role permission to view and add data to the Employees table.
GRANT SELECT, INSERT ON Employees TO HR_Manager;

REVOKE: Removes previously granted permissions from users.

-- Removes the Sales_Team role's permission to delete data from the Customers table.
REVOKE DELETE ON Customers FROM Sales_Team;

5. TCL Commands – To Make Changes Safely

TCL stands for Transaction Control Language. These commands help manage transactions, which are sets of SQL actions that should all happen together as a single unit.

Key TCL Commands:

COMMIT: Saves all the changes you made during a transaction permanently.

BEGIN;
-- SQL statements
COMMIT;

ROLLBACK: Undoes changes you made during a transaction if something went wrong.

BEGIN;
-- SQL statements
ROLLBACK;

SAVEPOINT: Sets 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;

Important SQL Commands List

This table summarizes the most used and important SQL Commands:

Conclusion

SQL commands are how you communicate with a database. 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

Learning these commands is the first step to working effectively with data. The more you use them, the easier it will become to get the database to do exactly what you need.

Other SQL Resources:

• SQL Quiz
• SQL Exercise
• SQL Operators with Examples
• SQL Constraints with Examples
• SQL Projects Ideas for Beginners & Beyond
• SQL Interview Questions