Types of SQL

SQL is a versatile language that can be categorized into four main types based on the kinds of operations it performs. Understanding these categories will help you write clear, purpose-driven queries and scripts.

---

Data Definition Language (DDL)

DDL deals with defining and modifying the structure of database objects such as tables, schemas, indexes, and views.

Common DDL Commands:

• CREATE: Creates a new database object (e.g., CREATE TABLE).
• ALTER: Modifies an existing database object (e.g., add a column).
• DROP: Deletes an existing object (e.g., drop a table).
• TRUNCATE: Removes all rows from a table but keeps its structure.

Example:

CREATE TABLE employees (
  id INT PRIMARY KEY,
  name VARCHAR(50),
  hire_date DATE
);

---

Data Manipulation Language (DML)

DML is used to manipulate the data stored in database tables. These commands handle inserting, updating, deleting, and retrieving data.

Common DML Commands:

• SELECT: Retrieves data from the database.
• INSERT: Adds new rows to a table.
• UPDATE: Modifies existing rows.
• DELETE: Removes rows.

Example:

INSERT INTO employees (id, name, hire_date)
VALUES (1, 'Alice', '2022-01-15');

---

Data Control Language (DCL)

DCL is used to control access to data stored in the database. It helps manage permissions and user privileges.

Common DCL Commands:

• GRANT: Gives privileges to users.
• REVOKE: Withdraws privileges from users.

Example:

GRANT SELECT, INSERT ON employees TO hr_user;

---

Transaction Control Language (TCL)

TCL is used to manage transactions within the database, ensuring data integrity by grouping SQL commands into logical units of work.

Common TCL Commands:

• COMMIT: Saves all changes made in the current transaction.
• ROLLBACK: Undoes changes since the last COMMIT.
• SAVEPOINT: Sets a point within a transaction to which you can roll back.
• SET TRANSACTION: Configures transaction properties.

Example:

BEGIN;
UPDATE employees SET name = 'Bob' WHERE id = 1;
COMMIT;

---

Summary

Category	Description	Examples
DDL	Defines database structure	CREATE, ALTER, DROP
DML	Manipulates data	SELECT, INSERT, UPDATE, DELETE
DCL	Controls permissions	GRANT, REVOKE
TCL	Manages transactions	COMMIT, ROLLBACK, SAVEPOINT

Understanding these types clarifies how SQL statements interact with both the structure and the contents of a database, helping you write more effective and secure SQL.

---

Tip: Not all RDBMSs implement DCL and TCL commands in exactly the same way — always check the documentation for your specific database.