SQL Basics: DDL, DML, DCL, TCL

Structured Query Language (SQL) is the standard language for managing and manipulating relational databases. SQL commands are categorized into various types based on their purpose. The four main categories are:

1. DDL – Data Definition Language

Purpose:
DDL commands are used to define and modify the structure of database objects like tables, schemas, indexes, and views.

Common DDL Commands:

• CREATE – Creates new database objects (e.g., tables).
• ALTER – Modifies existing database structures.
• DROP – Deletes objects from the database.
• TRUNCATE – Removes all rows from a table without logging individual deletions.

Example:

Note:
DDL commands automatically commit the changes—meaning they cannot be rolled back once executed.

2. DML – Data Manipulation Language

Purpose:
DML commands are used to manipulate the data stored within database objects (not the structure itself).

Common DML Commands:

• SELECT – Retrieves data from the database.
• INSERT – Adds new data.
• UPDATE – Modifies existing data.
• DELETE – Removes specific data.

Example:

Note:
DML operations are transactional—they can be committed or rolled back, and require explicit handling via TCL commands.

3. DCL – Data Control Language

Purpose:
DCL commands are used to control access and permissions in the database.

Common DCL Commands:

• GRANT – Gives a user access privileges.
• REVOKE – Removes previously granted access.

Example:

Note:
DCL statements affect security and are often used by database administrators.

4. TCL – Transaction Control Language

Purpose:
TCL commands manage transactions in a database, especially after DML operations. They ensure data integrity and consistency.

Common TCL Commands:

• COMMIT – Saves all changes made by the transaction.
• ROLLBACK – Reverts changes if something goes wrong.
• SAVEPOINT – Sets a save marker within a transaction for partial rollback.
• SET TRANSACTION – Defines transaction properties.

Example:

Note:
TCL ensures that a set of DML statements either complete successfully or do not affect the database at all.

Summary Table