SQL Commands: DDL, DQL, DML, DCL & TCL Explained

Introduction of SQL Commands

Structured Query Language (SQL) is the standard language used to interact with relational databases. SQL commands are categorized into five major types: DDL (Data Definition Language), DQL (Data Query Language), DML (Data Manipulation Language), DCL (Data Control Language), and TCL (Transaction Control Language). Each category serves a distinct purpose in database management and plays a vital role in handling databases efficiently.

1. Data Definition Language (DDL)

SQL commands under DDL are used to define and modify database structures such as tables, indexes, and schemas. These commands directly impact the database schema and require privileges to execute. DDL ensures that databases maintain a structured and organized format.

Common DDL Commands

CREATE: Creates a new database object (table, view, index, etc.).

CREATE TABLE Employees (
    EmployeeID INT PRIMARY KEY,
    Name VARCHAR(100),
    Age INT,
    Department VARCHAR(50)
);

ALTER: Modifies an existing database structure.

ALTER TABLE Employees ADD COLUMN Salary DECIMAL(10,2);

DROP: Deletes a database object permanently.

DROP TABLE Employees;

TRUNCATE: Removes all records from a table but retains the structure.

TRUNCATE TABLE Employees;

2. SQL Commands for Data Query (DQL)

DQL SQL commands are used to retrieve data from a database. The primary command under DQL is SELECT. It allows users to fetch and analyze data based on specified conditions, ensuring accurate data retrieval.

Common DQL Commands

SELECT: Retrieves data from one or more tables.

SELECT Name, Age FROM Employees WHERE Department = 'IT';

SELECT DISTINCT: Returns unique values in a specified column.

SELECT DISTINCT Department FROM Employees;

ORDER BY: Sorts the result set based on one or more columns.

SELECT Name, Age FROM Employees ORDER BY Age DESC;

GROUP BY: Groups rows with the same values in specified columns.

SELECT Department, COUNT(*) FROM Employees GROUP BY Department;

HAVING: Filters groups based on conditions.

SELECT Department, COUNT(*) FROM Employees GROUP BY Department HAVING COUNT(*) > 10;

3. Data Manipulation Lanuage (DML)

DML SQL commands manipulate data within database tables. These commands affect stored data but do not modify the structure. DML ensures that databases remain dynamic and adaptable.

Common DML Commands

INSERT: Adds new records to a table.

INSERT INTO Employees (EmployeeID, Name, Age, Department) VALUES (1, 'John Doe', 30, 'HR');

UPDATE: Modifies existing records.

UPDATE Employees SET Age = 35 WHERE EmployeeID = 1;

DELETE: Removes specific records from a table.

DELETE FROM Employees WHERE Age > 60;

MERGE: Merges data from different sources.

MERGE INTO Employees USING NewEmployees ON Employees.EmployeeID = NewEmployees.EmployeeID
WHEN MATCHED THEN UPDATE SET Employees.Age = NewEmployees.Age
WHEN NOT MATCHED THEN INSERT (EmployeeID, Name, Age, Department) VALUES (NewEmployees.EmployeeID, NewEmployees.Name, NewEmployees.Age, NewEmployees.Department);

4. Data Control Language (DCL)

DCL SQL commands manage access permissions and security of database objects. These commands are crucial for maintaining data integrity and security.

Common SQL commands of DCL

GRANT: Assigns privileges to users.

GRANT SELECT, INSERT ON Employees TO user1;

REVOKE: Removes assigned privileges.

REVOKE INSERT ON Employees FROM user1;

5. Transaction Control Language (TCL)

TCL SQL commands manage transactions in SQL databases, ensuring integrity and consistency. These commands are essential for handling large-scale data operations and maintaining consistency.

Common SQL commands of TCL

COMMIT: Saves all transactions permanently.

COMMIT;

ROLLBACK: Undoes uncommitted transactions.

ROLLBACK

SAVEPOINT: Creates a savepoint within a transaction.

SAVEPOINT sp1;

SET TRANSACTION: Defines transaction properties.

SET TRANSACTION READ ONLY;

Q1. What is the difference between DDL and DML?

DDL commands define and modify database structures, whereas DML commands manipulate the data within those structures.

Q2. Can we rollback a TRUNCATE operation?

No, TRUNCATE is a DDL command and cannot be rolled back as it does not generate undo logs.

Q3. What is the purpose of the HAVING clause in SQL?

The HAVING clause filters aggregated results after the GROUP BY operation.

Q4. What is the main difference between DELETE and DROP?

DELETE removes specific rows from a table, whereas DROP removes the entire table structure and data permanently.

Q5. Why do we use the COMMIT command in SQL?

The COMMIT command is used to save all changes made during a transaction permanently.

Understanding SQL commands is essential for effective database management. Whether defining structures using DDL, retrieving data with DQL, modifying records via DML, managing access through DCL, or handling transactions using TCL, mastering these commands ensures efficiency in database operations. SQL commands play a pivotal role in software development, data analysis, and business intelligence.

SQL commands are indispensable tools for database administrators, developers, and data analysts. By understanding and mastering these commands, businesses and professionals can manage databases efficiently, maintain data integrity, and ensure secure and optimized performance.

What is SQL? The Ultimate Guide to Understanding Databases