02 – Understanding SQL Operations (DML, DDL, DCL, and TCL)

Structured Query Language (SQL) is the backbone of database management and serves as a critical tool for interacting with relational databases. To perform various operations on a database, SQL is categorized into four main types of commands:

• Data Definition Language (DDL)
• Data Manipulation Language (DML)
• Data Control Language (DCL)
• Transaction Control Language (TCL)

Understanding these categories is essential for efficient database management. Let’s explore each in detail.

Data Definition Language (DDL) #

DDL commands deal with the structure and schema of a database. These commands are used to define, modify, or delete database objects such as tables, indexes, and schemas.

• CREATE: Used to create database objects like tables, indexes, or databases.

CREATE TABLE employees ( 
    id INT PRIMARY KEY,
    name VARCHAR(100),
    department VARCHAR(50),
    salary DECIMAL(10, 2)
);

• ALTER: Used to modify existing database objects, such as adding or removing columns from a table.

ALTER TABLE employees ADD COLUMN hire_date DATE;

• DROP: Used to delete database objects completely.

DROP TABLE employees;

• TRUNCATE: Deletes all records from a table but retains the table structure for future use.

TRUNCATE TABLE employees;

Data Manipulation Language (DML) #

DML commands are used to retrieve, insert, update, and delete data in a database. These commands operate on the data within the database tables.

• SELECT: Retrieves data from the database.

SELECT name, department FROM employees WHERE salary > 50000;

• INSERT: Adds new records to a table.

INSERT INTO employees (id, name, department, salary, hire_date) VALUES (1, 'John Doe', 'Engineering', 75000, '2023-01-15');

• UPDATE: Modifies existing records in a table.

UPDATE employees SET salary = 80000 WHERE id = 1;

• DELETE: Removes specific records from a table.

 DELETE FROM employees WHERE id = 1;

Data Control Language (DCL) #

DCL commands manage permissions and access to the database. These commands are primarily used by database administrators to enforce security.

• GRANT: Provides specific privileges to users or roles.

GRANT SELECT, INSERT ON employees TO user1;

• REVOKE: Removes previously granted permissions.

REVOKE INSERT ON employees FROM user1;

Transaction Control Language (TCL) #

TCL commands manage transactions in a database. Transactions ensure that a series of operations are executed as a single unit of work, maintaining data consistency and integrity.

• COMMIT: Saves all changes made during the transaction.

COMMIT;

• ROLLBACK: Undoes all changes made during the current transaction.

ROLLBACK;

• SAVEPOINT: Creates a point within a transaction to which you can later roll back.

SAVEPOINT sp1;

• SET TRANSACTION: Defines properties for a transaction, such as isolation level.

 SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

Summary Table of SQL Commands #

Conclusion #

By mastering DDL, DML, DCL, and TCL commands, you gain full control over your database, from defining its structure to managing its data and access permissions. Whether you are a database administrator or a developer, understanding these categories will help you write efficient and secure SQL scripts that ensure optimal database performance.