Structured Query Language (SQL) is the standard language used for interacting with relational databases. SQL is divided into several types based on the purpose and functionality of its statements. Understanding these types helps users write efficient queries and manage databases effectively.

Types of SQL Statements

SQL statements can be categorized into five main types:

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

Each SQL type serves a specific role in database management.

Data Definition Language (DDL)

Data Definition Language (DDL) is used to define and manage the structure of database objects like tables, indexes, and views. For performing DDL statements, user required SYSADM or SYSCTRL authority and DBADM authority on DATABASE. Primary Statements: CREATE, ALTER, DROP.

CREATE Statement -

CREATE statement is used to create the DB2 objects. The database objects include schemas, tables, views, sequences, catalogs, indexes, and aliases.

Example: Create an Employees table with columns for employee ID, name, department, and salary.

CREATE TABLE Employees (
	Emp_ID INT PRIMARY KEY,
	Emp_Name VARCHAR(50),
	Department VARCHAR(30),
	Salary DECIMAL(10, 2)
);

ALTER Statement -

ALTER can be used to add/delete/modify the DB2 Objects which already defined during the CREATE. Primary and foreign key also can be defined by using ALTER once the table got created.

Example: Add a Hire_Date column to the Employees table.

ALTER TABLE Employees ADD Hire_Date DATE;

DROP Statement -

DROP statement used to delete the DB2 objects. DROP will also deletes the data in it while deleting the Object.

Example: Delete the Employees table and all its data permanently.

DROP TABLE Employees;

Data Query Language (DQL)

Data Query Language (DQL) is used to retrieve data from the database. Primary Statement: SELECT.

SELECT statement -

It is the only DQL statement. It allows users to query data from tables based on specified conditions. Select statement retrieves the data from the tables. The cursor declaration also uses the select statement to retrieve the data for the cursor.

Example: retrieves the name and salary of employees from the Employees table where the department is 'Sales'.

SELECT Emp_Name, Salary FROM Employees WHERE Department = 'Sales';

Data Manipulation Language (DML)

DML is used to manipulate and manage data within database objects. DML is for adding, updating, and deleting records in a table. Primary Statements: INSERT, UPDATE, DELETE

INSERT Statement -

INSERT statement is used to insert the data into the table or view. INSERT can insert one row or more than one row at a time. Inserting into VIEW can insert the data into TABLE as well.

Example: Inserts a new record for employee Alice in the Employees table.

INSERT INTO Employees (Emp_ID, Emp_Name, Department, Salary)
VALUES (101, 'Alice', 'Sales', 60000);

UPDATE Statement -

UPDATE statement is used to update the rows which are already existed in the table. UPDATE can be single row update or mass update (more than one row) based on the condition provided in WHERE clause.

Example: Updates the salary of the employee with ID 101 to 65,000.

UPDATE Employees SET Salary = 65000 WHERE Emp_ID = 101;

DELETE Statement -

DELETE statement is used to delete the row/rows from the table or view. DELETE statement can delete more than one row/rows from the table/view based on the condition coded in WHERE clause.

Example: Deletes the record for the employee with ID 101 from the Employees table.

DELETE FROM Employees WHERE Emp_ID = 101;

Data Control Language (DCL)

Data Control Language is used to manage access and permissions for database objects. It is used to control data by giving or revoking access to retrieve the data based on the user group level or individual user level. One of the main advantages of DCL is that user groups can be restricted by revoking access to sensitive data. Primary Statements: GRANT, REVOKE

GRANT Statement -

GRANT statement is used to give permissions and add additional permissions to users. It provides specific permissions to a user or role.

Example: Allows User1 to select data from and insert data into the Employees table.

GRANT SELECT, INSERT ON Employees TO User1;

REVOKE Statement -

A REVOKE statement is used to revert the access granted earlier. It removes specific permissions from a user or role.

Example: Removes the INSERT permission for User1 on the Employees table.

REVOKE INSERT ON Employees FROM User1;

Transaction Control Language (TCL)

Transaction control language is used to control the transactions performed on the database. TCL can save or revoke the transactions applied on the database from the last sync point. The sync point is nothing but where the COMMIT or ROLLBACK is executed. Primary Statements: COMMIT or SYNCPOINT, ROLLBACK

COMMIT or SYNCPOINT Statement -

COMMIT is used to save all transactions performed on the database from the sync point. It can also close all the open cursors when COMMIT executes except the cursors WITH HOLD.

Example: Saves all the updates from the last sync point.

COMMIT; or SYNCPOINT;

ROLLBACK Statement -

ROLLBACK is used to revert all transactions performed on the database from the SYNC point. If the ROLLBACK is executed, the changes performed on the database from the sync point will be reverted.

Example: Revert all the updates from the last sync point.

ROLLBACK;

---