Write a note on TCL & DCL.

Q.7 Write a note on TCL & DCL.

Answer :- 

• Transaction Control Language (TCL) and Data Control Language (DCL) in SQL. 

• SQL is divided into various categories based on the type of operations they perform. Among these are Transaction Control Language (TCL) and Data Control Language (DCL), which serve specific purposes in managing transactions and controlling access to the database.

Transaction Control Language (TCL)

• TCL commands are used to manage changes made to the database during a transaction.

• A transaction is a sequence of operations that are treated as a single unit.

• TCL commands ensure data integrity and consistency by providing control over how and when changes are permanently saved or undone.

TCL Commands:

• COMMIT: Permanently saves the changes made by a transaction.

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

• ROLLBACK: Undoes changes made during the transaction, reverting the database to its previous state.

1. COMMIT Command

• Purpose : The COMMIT command is used to make all changes made by a transaction permanent in the database. Once committed, the changes cannot be undone.

• Syntax:  COMMIT;

 Features:

• Saves all modifications made during the transaction.

• Releases any locks held by the transaction.

• Ensures durability in the database.

Example : 

• BEGIN TRANSACTION;

  INSERT INTO Employees (EmpID, Name, Department) VALUES (101, ‘John’, ‘IT’);

  UPDATE Employees SET Department = ‘HR’ WHERE EmpID = 102;

  COMMIT;

In this example, the changes made by the INSERT and UPDATE statements are permanently saved to the database.

2. SAVEPOINT Command

• Purpose: The SAVEPOINT command allows you to create a point within a transaction to which you can roll back later if needed. This is useful for dividing a transaction into smaller parts.

• Syntax: SAVEPOINT savepoint_name;

Features:

• Temporarily saves the state of the database at a specific point.

• Multiple savepoints can be created within a single transaction.

• Does not commit the transaction.

Example:

• BEGIN TRANSACTION;

  INSERT INTO Employees (EmpID, Name, Department) VALUES (103, ‘Alice’, ‘Finance’);

  SAVEPOINT sp1;

  UPDATE Employees SET Department = ‘Marketing’ WHERE EmpID = 104;

  SAVEPOINT sp2;

  DELETE FROM Employees WHERE EmpID = 105;

  ROLLBACK TO sp1; — Reverts changes made after sp1.

  COMMIT;

In this example:

• sp1 and sp2 are savepoints.

• Rolling back to sp1 undoes changes made after the first savepoint but keeps earlier changes.

3. ROLLBACK Command :

• Purpose: The ROLLBACK command undoes all changes made by the current transaction or to a specific savepoint.

• Syntax: ROLLBACK; 

Features:

• Restores the database to its state before the transaction began or to a specific savepoint.

• Releases any locks held by the transaction.

• Ensures data consistency by discarding unwanted changes.

Example:

• BEGIN TRANSACTION; INSERT INTO Employees (EmpID, Name, Department) VALUES (106, ‘Bob’, ‘Legal’); DELETE FROM Employees WHERE EmpID = 107; ROLLBACK; 

In this example

• the database is reverted to its state before the transaction began, and neither the INSERT nor DELETE operation is applied.

Data Control Language (DCL)

• DCL commands are used to control access to the database by granting or revoking privileges.

• These commands help manage database security and ensure that only authorized users can perform specific operations.

• Data Control Language (DCL) is a subset of SQL used to manage permissions and access rights to the database objects, such as tables, views, and procedures.

• DCL ensures that the right users have the appropriate level of access to the database, maintaining data security and integrity.

Key DCL Commands: GRANT and REVOKE

• GRANT: Provides specific privileges to users or roles.

• REVOKE: Removes previously granted privileges from users or roles.

1. GRANT Command

• Purpose: The GRANT command is used to give permissions to users or roles for accessing or manipulating database objects. These privileges can be given for various operations, such as selecting, inserting, updating, or deleting data.
• Syntax: GRANT privilege_name ON object_name TO user_or_role [WITH GRANT OPTION];

Key Components: 

• privilege_name: The specific permission(s) to be granted (e.g., SELECT, INSERT, UPDATE, DELETE).
• object_name: The database object (e.g., table, view) to which the permission applies.
• user_or_role: The recipient of the permissions, which can be a user or a role.
• WITH GRANT OPTION: Allows the recipient to further grant the same permissions to others.

Example:

• GRANT SELECT, INSERT ON Employees TO Alice;
• GRANT SELECT ON Employees TO Bob WITH GRANT OPTION;

2. REVOKE Command 

• Purpose: The REVOKE command is used to withdraw permissions that were previously granted to a user or role.

• Syntax: REVOKE privilege_name ON object_name FROM user_or_role;

Key Components: 

• privilege_name: The specific permission(s) to be revoked.

• object_name: The database object from which the permission is being revoked.

• user_or_role: The user or role losing the permissions.

Example:

• REVOKE INSERT ON Employees FROM Alice;

• REVOKE SELECT ON Employees FROM Bob;