5 Categories of SQL Commands: DDL, DQL, DML, DCL, TCL

SQL is divided into five main categories, each serving a distinct purpose. Learning difference between these categories will boost your knowledge and understanding of SQL.

5 Categories of SQL Commands

SQL - Structured Query language - is the standard language for interacting with relational databases. Its commands are divided into five categories, each serving a distinct purpose. In this article, I’ll try to provide you with a clear distinction between them and practical examples to help you understand the role of each category:

1. DDL - Data Definition Language

2. DQL - Data Query Language

3. DML - Data Manipulation Language

4. DCL - Data Control Language

5. TCL - Transaction Control Language

Data Definition Language (DDL)

Data Definition Language is used to define and manage database structures, including creating, altering, and deleting database objects like tables, indexes, and schemas.

Key Commands: CREATE, ALTER, DROP

CREATE

CREATE TABLE departments (
    department_id INT PRIMARY KEY,
    department_name VARCHAR(50)
);

CREATE TABLE employees (
    employee_id INT PRIMARY KEY,
    name VARCHAR(50),
    last_name VARCHAR(50),
    position VARCHAR(50),
    salary DECIMAL(10, 2),
    department_id INT,
    FOREIGN KEY (department_id) REFERENCES departments(department_id)
);

ALTER

-- Adding Column
ALTER TABLE employees
ADD date_of_birth DATE;

-- Dropping Column
ALTER TABLE employees
DROP COLUMN date_of_birth;

-- Modify Column Type
ALTER TABLE employees
ALTER COLUMN last_name TYPE VARCHAR(100);

-- Rename Column
ALTER TABLE employees
RENAME COLUMN last_name TO surname;

-- Rename Table
ALTER TABLE employees
RENAME TO new_employees;

-- Add Constraint
ALTER TABLE employees
ADD CONSTRAINT chk_salary CHECK (salary >= 0);

-- Drop Constraint
ALTER TABLE employees
DROP CONSTRAINT chk_salary;

DROP

DROP TABLE employees;

Data Query Language (DQL)

DQL, as the term suggests, is all about querying and retrieving data from a database. It consists of the most famous SQL clauses that are taught in every tutorial.

Key Commands: SELECT, FROM, WHERE

Although I listed only 3 clauses in Key Commands, DQL includes many more. Some of them are:

• SELECT, FROM, & WHERE

• GROUP BY & HAVING + GROUPING SETS, CUBE, ROLLUP

• INNER JOIN & LEFT JOIN

• UNION & UNION ALL (Do you know the difference?)

• WINDOW FUNCTIONS (RANK, ROW_NUMBER, & DENSE_RANK) + QUALIFY

Data Manipulation Language (DML)

Used for managing data within database objects. The word within is the key distinction between DDL and DML. DML includes inserting, updating, and deleting records.

Key Commands: INSERT, UPDATE, DELETE

INSERT INTO

INSERT INTO employees (employee_id, name, last_name, position, salary, department_id)
VALUES (1, 'John', 'Doe', 'Manager', 75000.00, 12);

UPDATE

UPDATE employees
SET salary = 80000.00
WHERE employee_id = 1;

DELETE

DELETE FROM employees WHERE employee_id = 1;

Data Control Language (DCL)

Data Control Language is used to control access to database objects. It includes granting and revoking access.

Key Commands: GRANT & REVOKE

GRANT

GRANT gives access to users or roles on different database objects like tables, views, schemas, and databases themselves.

-- Granting SELECT or INSERT privileges on a table
GRANT SELECT ON employees TO user1;

GRANT INSERT ON employees TO user1;

-- Granting multiple privileges
GRANT SELECT, INSERT ON employees TO user1;

-- Granting all the privileges on a table
GRANT ALL ON employees TO user1;

REVOKE

REVOKE removes privileges from users or roles on different database objects like tables, views, schemas, and databases themselves.

-- Granting SELECT or INSERT privileges on a table
REVOKE SELECT ON employees FROM user1;

REVOKE INSERT ON employees FROM user1;

-- Granting multiple privileges
REVOKE SELECT, INSERT ON employees FROM user1;

Transaction Control Language (TCL)

In short, Transaction Control Language (TCL) commands help us manage transactions effectively and make sure our database objects stay consistent by conforming ACID properties.

Key Commands: COMMIT, ROLLBACK

COMMIT

The COMMIT command is used to save all changes made during the transaction to the database. Once you issue a COMMIT, the changes become permanent and visible to other users.

-- Usually the syntax is the following.
-- 1. We begin transaction.
-- 2. Perform actions.
-- 3. And COMMIT in the end (if everything goes well - atomicity)
BEGIN;

INSERT INTO employees (employee_id, name, last_name, position, salary, department_id)
VALUES (1, 'John', 'Doe', 'Manager', 75000.00, 12);

UPDATE employees
SET department_id = 13
WHERE employee_id = 1;

COMMIT;

ROLLBACK

ROLLBACK command reverts any unintended changes made during the transaction. For example, if we have a transaction consisting of 3 different actions/queries, we imply that when the transaction is complete all of them should be successful, if not, we don’t commit, instead, we roll back it. ROLLBACK usually depends on conditions. For example, if an error occurs, we roll back the transaction to avoid inconsistent writes in the database.

Conclusion

The commands discussed in this post are not a complete list but represent a major part of SQL that everyone should know. Understanding these categories and clauses in each of them is an important step to mastering SQL.

Have questions or need further clarification? Leave a comment below or reach out directly.

---

✅ Thank you for reading my article on SA Space! I welcome any questions, comments, or suggestions you may have.

Keep Knowledge Flowing by following me for more content on Solutions Architecture, System Design, Data Engineering, Business Analysis, and more. Your engagement is appreciated. 🚀

🔔 You can also follow my work on LinkedIn | Substack | Threads | X