SQL Command Types

[sahithya]

SQL commands are broadly categorized into Data Definition Language (DDL), Data Manipulation Language (DML), Data Control Language (DCL), Transaction Control Language (TCL), and Data Query Language (DQL), each serving distinct purposes.

[horizontour]

SQL (Structured Query Language) commands are categorized into different types based on their functionality in database operations. These command types include Data Definition Language (DDL), Data Manipulation Language (DML), Data Control Language (DCL), Transaction Control Language (TCL), and Data Query Language (DQL). Here's an overview of each type:

1. Data Definition Language (DDL)
DDL commands are used to define and modify the structure of database objects like tables, indexes, and schemas.

CREATE: Creates new objects such as databases, tables, indexes, or views.

Example:
sql
Copy code
CREATE TABLE Employees (
EmployeeID INT PRIMARY KEY,
FirstName VARCHAR(50),
LastName VARCHAR(50),
Age INT
);
ALTER: Modifies the structure of an existing object like a table.

Example:
sql
Copy code
ALTER TABLE Employees ADD COLUMN Salary DECIMAL(10, 2);
DROP: Deletes an existing object like a table, database, or index.

Example:
sql
Copy code
DROP TABLE Employees;
TRUNCATE: Removes all rows from a table without deleting the table itself.

Example:
sql
Copy code
TRUNCATE TABLE Employees;
RENAME: Changes the name of an existing object.

Example:
sql
Copy code
RENAME TABLE Employees TO Staff;
2. Data Manipulation Language (DML)
DML commands are used to modify data within database objects like tables.

INSERT: Adds new records to a table.

Example:
sql
Copy code
INSERT INTO Employees (EmployeeID, FirstName, LastName, Age)
VALUES (1, 'John', 'Doe', 30);
UPDATE: Modifies existing records in a table.

Example:
sql
Copy code
UPDATE Employees
SET Age = 31
WHERE EmployeeID = 1;
DELETE: Removes existing records from a table.

Example:
sql
Copy code
DELETE FROM Employees
WHERE EmployeeID = 1;
MERGE: Combines the functionalities of INSERT and UPDATE. It allows for conditional inserts and updates.

Example:
sql
Copy code
MERGE INTO Employees e
USING (SELECT EmployeeID FROM NewEmployees) ne
ON (e.EmployeeID = ne.EmployeeID)
WHEN MATCHED THEN
UPDATE SET e.Salary = 50000
WHEN NOT MATCHED THEN
INSERT (EmployeeID, FirstName, LastName, Age, Salary)
VALUES (ne.EmployeeID, 'John', 'Doe', 30, 50000);
3. Data Query Language (DQL)
DQL consists of the SELECT statement, which is used to query the database and retrieve data.

SELECT: Retrieves data from the database based on certain conditions.
Example:
sql
Copy code
SELECT FirstName, LastName
FROM Employees
WHERE Age > 25;
4. Data Control Language (DCL)
DCL commands are used to control access to data in the database, especially regarding permissions.

GRANT: Provides privileges to users.

Example:
sql
Copy code
GRANT SELECT, INSERT ON Employees TO UserName;
REVOKE: Removes privileges from users.

Example:
sql
Copy code
REVOKE SELECT, INSERT ON Employees FROM UserName;
5. Transaction Control Language (TCL)
TCL commands are used to manage transactions in a database, ensuring data integrity.

COMMIT: Saves all changes made during the current transaction.

Example:
sql
Copy code
COMMIT;
ROLLBACK: Undoes changes made during the current transaction if they haven't been committed yet.

Example:
sql
Copy code
ROLLBACK;
SAVEPOINT: Sets a point within a transaction to which you can later roll back.

Example:
sql
Copy code
SAVEPOINT Save1;
SET TRANSACTION: Sets the properties for a transaction, like isolation levels.

Example:
sql
Copy code
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
Summary of SQL Command Types:
DDL – Create, alter, and define database structures (e.g., CREATE, ALTER, DROP).
DML – Insert, update, and delete records (e.g., INSERT, UPDATE, DELETE).
DQL – Retrieve data (e.g., SELECT).
DCL – Control access to the data (e.g., GRANT, REVOKE).
TCL – Manage transactions (e.g., COMMIT, ROLLBACK, SAVEPOINT).
These command types together form the foundation for managing and interacting with databases in SQL.