SQL (Structured Query Language) is used to manage and manipulate relational databases. Its statements are grouped into different categories based on their functions. The five main subdivisions of SQL are:
- Data Definition Language (DDL)
- Data Manipulation Language (DML)
- Data Control Language (DCL)
- Transaction Control Language (TCL)
- Data Query Language (DQL)
Each subdivision has a specific role in database operations and supports different commands. This note explains each in detail.
1. Data Definition Language (DDL)
DDL is used to define and modify the structure of database objects such as tables, indexes, and schemas. It deals with how data is stored rather than the data itself.
Key commands:
CREATE: Creates new database objects such as tables, views, indexes, or databases.ALTER: Modifies an existing object’s structure, such as adding or removing columns from a table.DROP: Deletes an object from the database permanently.TRUNCATE: Deletes all rows from a table, but preserves the table structure. It is faster thanDELETEand cannot be rolled back.
Example:
sql
CopyEdit
CREATE TABLE employees (
id INT PRIMARY KEY,
name VARCHAR(100),
department VARCHAR(50)
);
ALTER TABLE employees ADD salary DECIMAL(10,2);
DROP TABLE employees;
TRUNCATE TABLE employees;
Important Notes:
- DDL commands execute immediately and implicitly commit the current transaction.
- These changes cannot be undone with a rollback.
- Mainly used by database administrators and during initial schema design.
2. Data Manipulation Language (DML)
DML is used to manage the data stored in database objects. It allows users to retrieve, insert, update, and delete data records.
Key commands:
SELECT: Retrieves data from one or more tables.INSERT: Adds new records into a table.UPDATE: Modifies existing data within a table.DELETE: Removes data from a table based on a condition.
Example:
INSERT INTO employees (id, name, department) VALUES (1, 'John Doe', 'HR');
SELECT * FROM employees;
UPDATE employees SET department = 'Finance' WHERE name = 'John Doe';
DELETE FROM employees WHERE id = 1;
Important Notes:
- DML commands can be rolled back if used within a transaction.
- They are used frequently in daily operations such as data entry, processing, and maintenance.
SELECTis technically read-only and does not modify data, though it is often included in DML in basic classifications.
3. Data Control Language (DCL)
DCL is responsible for controlling access to the data and managing user permissions and security levels.
Key commands:
GRANT: Provides specific privileges to users, such as the ability to select, insert, or update data.REVOKE: Removes previously granted privileges from users.
Example:
sql
CopyEdit
GRANT SELECT, INSERT ON employees TO user1;
REVOKE INSERT ON employees FROM user1;
Important Notes:
- Used by database administrators to manage access control.
- Helps ensure data security and prevents unauthorized access.
- Works in combination with roles and user authentication systems.
4. Transaction Control Language (TCL)
TCL is used to manage transactions in a database. A transaction is a group of SQL operations performed as a single logical unit of work. TCL ensures data consistency and handles changes in a controlled manner.
Key commands:
COMMIT: Saves all changes made by the current transaction permanently in the database.ROLLBACK: Undoes changes made in the current transaction.SAVEPOINT: Sets a point within a transaction to which you can later roll back.SET TRANSACTION: Sets properties such as isolation level for a transaction.
Example:
sql
CopyEdit
UPDATE employees SET salary = salary + 1000 WHERE department = 'Finance';
COMMIT;
ROLLBACK;
SAVEPOINT before_bonus;
ROLLBACK TO before_bonus;
Important Notes:
- Transactions begin implicitly when a DML command is issued.
- A transaction ends with either a
COMMITorROLLBACK. - TCL commands are critical in multi-step database operations, such as financial transfers or batch updates, to ensure data integrity.
5. Data Query Language (DQL)
DQL is a subset of SQL used exclusively to query the database and retrieve data. While it consists of only one command, SELECT, it is the most frequently used and most complex SQL operation.
Key command:
SELECT: Extracts data from one or more tables using various clauses such asWHERE,GROUP BY,ORDER BY,JOIN, etc.
Example:
sql
CopyEdit
SELECT name, salary FROM employees WHERE department = 'Finance';
Important Notes:
SELECTis often combined with functions, joins, and subqueries to produce meaningful insights from the data.- Though some consider it part of DML, it is generally treated as a separate category due to its unique function and frequency of use.
- Essential for reporting, data analysis, and application interfaces.
Conclusion
The five subdivisions of SQL help categorize commands based on their roles in managing data and databases:
- DDL is used for structure.
- DML is used for content.
- DCL is used for security.
- TCL is used for transaction control.
- DQL is used for querying and data retrieval.
Understanding these categories helps a beginner learn SQL in an organized way and apply the right type of command for each task.