SQL Database(MSSQL)

SELECT - extracts data from a database

UPDATE - updates data in a database

DELETE - deletes data from a database

INSERT INTO - inserts new data into a database

CREATE DATABASE - creates a new database

ALTER DATABASE - modifies a database

CREATE TABLE - creates a new table

ALTER TABLE - modifies a table

DROP TABLE - deletes a table

CREATE INDEX - creates an index (search key)

DROP INDEX - deletes an index

----------------------------------------------------------

SQL Commands are mainly categorized into five categories:

DDL – Data Definition Language

DQL – Data Query Language

DML – Data Manipulation Language

DCL – Data Control Language

TCL – Transaction Control Language

-------------------------------

Common DDL (Data Definition Language) Commands

1. CREATE

Description: The CREATE command is used to create a database or its objects such as tables, indexes, functions, views, stored procedures, and triggers.

Syntax:

CREATE TABLE table_name (column1 data_type, column2 data_type, ...);

2. DROP

Description: The DROP command deletes objects from the database, such as tables, views, or indexes.

Syntax:

DROP TABLE table_name;

3. ALTER

Description: The ALTER command is used to alter the structure of an existing database object, such as adding new columns or modifying column data types.

Syntax:

ALTER TABLE table_name ADD COLUMN column_name data_type;

4. TRUNCATE

Description: The TRUNCATE command removes all records from a table, including removing all spaces allocated for the records. Unlike DELETE, it does not log individual row deletions.

Syntax:

TRUNCATE TABLE table_name;

5. COMMENT

Description: The COMMENT command is used to add comments to the data dictionary, often providing descriptive text for tables or columns.

Syntax:

COMMENT 'comment_text' ON TABLE table_name;

6. RENAME

Description: The RENAME command is used to rename an existing database object, such as a table.

Syntax:

RENAME TABLE old_table_name TO new_table_name;

------------------------

1.

CREATE TABLE Customer(

CustomerID INT PRIMARY KEY,

CustomerName VARCHAR(50),

LastName VARCHAR(50),

Country VARCHAR(50),

Age INT CHECK (Age >= 0 AND Age <= 99),

Phone VARCHAR(10)

);

SELECT*FROM Customer

---------------------------

Key Characteristics of a Primary Key:

Uniqueness: The primary key must have a unique value for each record in the table. No two records can have the same primary key value.

Non-null: The primary key cannot have a NULL value. Every record must have a valid primary key value.

Single or Composite: A primary key can be a single column or a combination of multiple columns (known as a composite primary key).

-------------------------------------------

INSERT INTO Customer (CustomerID, CustomerName,LastName, Country,Age,Phone)

VALUES (1,'Ravi', 'Rajput','India','25','9129868608'),

(2,'Anku', 'Yadav','India','25','1234567890'),

(3,'Ravi', 'Rana','India','25','9129868608')

SELECT*FROM Customer

-------------------------

SELECT CustomerID, CustomerName

INTO SubTable

FROM Customer;

select * from SubTable

TRUNCATE TABLE Customer

-- delete only data, table remain same

Drop table Customer

-- delete all the things with data and table

-- ALTER- modification

ALTER TABLE Customer

ADD Email VARCHAR(100);

ALTER TABLE Customer

ALTER COLUMN CustomerName VARCHAR(100);

select*from Customer

ALTER TABLE Customer

DROP COLUMN Email;

ALTER TABLE Customer

ADD CONSTRAINT PK_CustomerID PRIMARY KEY (CustomerID);

ALTER TABLE Customer

DROP CONSTRAINT PK_CustomerID;

--Rename

EXEC sp_rename 'Customer.User_Name', 'CustomerName', 'COLUMN';

------------

select*from Customer

SELECT CustomerName, LastName FROM Customer;

SELECT CustomerName FROM Customer where Age >= '21';

--------------------------

DQL – Data Query Language, with query examples,

Step 1: Understanding DQL

DQL (Data Query Language) is a subset of SQL used to retrieve data from databases. The main command used in DQL is SELECT.

Step 2: Create an Example Table

We will use the Employee table as our example.

CREATE TABLE Employee (

EmployeeID INT PRIMARY KEY,

FirstName NVARCHAR(50),

LastName NVARCHAR(50),

Department NVARCHAR(50),

Salary DECIMAL(10, 2),

HireDate DATE

);

INSERT INTO Employee (EmployeeID, FirstName, LastName, Department, Salary, HireDate) VALUES

(1, 'John', 'Doe', 'IT', 70000, '2020-01-15'),

(2, 'Jane', 'Smith', 'HR', 60000, '2019-06-01'),

(3, 'Alice', 'Johnson', 'Finance', 80000, '2021-03-10'),

(4, 'Bob', 'Brown', 'IT', 75000, '2020-12-25'),

(5, 'Eve', 'Davis', 'Marketing', 50000, '2018-09-12');

Now that we have data, we can use DQL commands to query and retrieve data from the table.

SELECT ALL COLUMNS

Retrieve all columns and rows from the Employee table.

SELECT * FROM Employee;

SELECT SPECIFIC COLUMNS

Retrieve specific columns (e.g., FirstName and Salary).

SELECT FirstName, Salary FROM Employee;

SELECT WITH WHERE CLAUSE

Filter rows using the WHERE clause (e.g., employees in the IT department).

SELECT * FROM Employee

WHERE Department = 'IT';

SELECT WITH ORDER BY

Sort the data by Salary in descending order.

SELECT * FROM Employee

ORDER BY Salary DESC;

-----------------------------

SELECT WITH DISTINCT

Retrieve unique values in the Department column.

SELECT DISTINCT Department FROM Employee;

SELECT WITH AGGREGATE FUNCTIONS

Use aggregate functions like COUNT, SUM, AVG, MIN, and MAX.

SELECT COUNT(*) AS TotalEmployees FROM Employee;

SELECT AVG(Salary) AS AverageSalary FROM Employee;

Find the highest salary

SELECT MAX(Salary) AS HighestSalary FROM Employee;

------------------

ELECT WITH GROUP BY

Group data by department and calculate the average salary for each department.

SELECT Department, AVG(Salary) AS AverageSalary

FROM Employee

GROUP BY Department;

-----------

SELECT WITH HAVING CLAUSE

Filter grouped data using the HAVING clause (e.g., departments with an average salary greater than 60000).

SELECT Department, AVG(Salary) AS AverageSalary

FROM Employee

GROUP BY Department

HAVING AVG(Salary) > 60000;

SELECT WITH LIMIT (TOP)

Retrieve only the top 2 highest-paid employees.

SELECT TOP 2 * FROM Employee

ORDER BY Salary DESC;

------------------

SELECT WITH JOINS

Assume there is another table DepartmentDetails:

CREATE TABLE DepartmentDetails (

Department NVARCHAR(50),

Location NVARCHAR(50)

);

INSERT INTO DepartmentDetails VALUES

('IT', 'New York'),

('HR', 'Chicago'),

('Finance', 'San Francisco'),

('Marketing', 'Boston');

-------------

Query: Join Employee with DepartmentDetails

SELECT e.FirstName, e.Department, d.Location

FROM Employee e

INNER JOIN DepartmentDetails d

ON e.Department = d.Department;

---------------------------------------

DML – Data Manipulation Language

Understanding DML

DML (Data Manipulation Language) is a subset of SQL used to manipulate data in database tables. The key operations in DML are:

INSERT: Add new records to a table.

UPDATE: Modify existing records in a table.

DELETE: Remove records from a table.

MERGE: Combine INSERT, UPDATE, and DELETE in one statement (useful for upserts).

CREATE TABLE Employee (

EmployeeID INT PRIMARY KEY,

FirstName NVARCHAR(50),

LastName NVARCHAR(50),

Department NVARCHAR(50),

Salary DECIMAL(10, 2),

HireDate DATE

);

--already created table

INSERT Command

The INSERT command adds rows to a table.

Basic Insert

Add a new employee to the Employee table.

INSERT INTO Employee (EmployeeID, FirstName, LastName, Department, Salary, HireDate)

VALUES (6, 'Michael', 'Lee', 'Sales', 55000, '2022-07-01');

Insert Multiple Rows

Insert multiple records in one statement.

INSERT INTO Employee (EmployeeID, FirstName, LastName, Department, Salary, HireDate)

VALUES

(7, 'Emily', 'Clark', 'IT', 72000, '2023-01-01'),

(8, 'Robert', 'Williams', 'HR', 58000, '2023-05-15');

Insert Using a Subquery

Insert data into a new table (EmployeeBackup) by copying it from Employee.

SELECT * INTO EmployeeBackup

FROM Employee

WHERE 1 = 0; -- This will create the table with the same structure but no data.

INSERT INTO EmployeeBackup

SELECT * FROM Employee;

---------------

UPDATE Command

The UPDATE command modifies existing rows in a table.

Basic Update

Update the salary of an employee.

UPDATE Employee

SET Salary = 75000

WHERE EmployeeID = 6;

------------------------------

Update Multiple Rows

Update the department of multiple employees.

Result: All employees previously in the HR department are now in Operations.

UPDATE Employee

SET Department = 'Operations'

WHERE Department = 'HR';

Update Using a Subquery

Increase the salary of employees in the IT department by 10%.

UPDATE Employee

SET Salary = Salary * 1.10

WHERE Department = 'IT';

-----------------

DELETE Command

The DELETE command removes rows from a table.

Basic Delete

Delete an employee from the table.

Result: The row for EmployeeID = 5 (Eve Davis) is removed.

DELETE FROM Employee

WHERE EmployeeID = 5;

Delete Using a Subquery

Delete all employees hired before 2020.

DELETE FROM Employee

WHERE HireDate < '2020-01-01';

-------------------------------------

MERGE Command

The MERGE command combines INSERT, UPDATE, and DELETE in one operation. This is useful for synchronizing two tables.

Merge Example

Synchronize the Employee table with EmployeeBackup.

MERGE INTO Employee AS Target

USING EmployeeBackup AS Source

ON Target.EmployeeID = Source.EmployeeID

WHEN MATCHED THEN

UPDATE SET Target.Salary = Source.Salary + 5000

WHEN NOT MATCHED BY TARGET THEN

INSERT (EmployeeID, FirstName, LastName, Department, Salary, HireDate)

VALUES (Source.EmployeeID, Source.FirstName, Source.LastName, Source.Department, Source.Salary, Source.HireDate)

WHEN NOT MATCHED BY SOURCE THEN

DELETE;

-----------------------------

Transactions

Transactions allow you to group multiple DML operations into a single unit of work, ensuring data integrity.

Transaction Example

Update multiple rows and roll back if any error occurs.

BEGIN TRANSACTION;

BEGIN TRY

UPDATE Employee

SET Salary = Salary + 5000

WHERE Department = 'IT';

UPDATE Employee

SET Salary = Salary + 3000

WHERE Department = 'Finance';

COMMIT TRANSACTION; -- Commit changes if successful

END TRY

BEGIN CATCH

ROLLBACK TRANSACTION; -- Roll back changes if an error occurs

PRINT 'Transaction failed. Changes rolled back.';

END CATCH;

-------------------

Savepoints

Savepoints allow partial rollbacks within a transaction.

Savepoint Example

Rollback to a savepoint instead of the entire transaction.

BEGIN TRANSACTION;

SAVE TRANSACTION BeforeITUpdate; -- Create a savepoint

UPDATE Employee

SET Salary = Salary + 5000

WHERE Department = 'IT';

ROLLBACK TRANSACTION BeforeITUpdate; -- Roll back to savepoint

COMMIT TRANSACTION;

--------------------------

OUTPUT Clause

The OUTPUT clause returns information about rows affected by a DML command.

OUTPUT Example

Track updated salaries.

UPDATE Employee

SET Salary = Salary + 5000

OUTPUT INSERTED.EmployeeID, INSERTED.Salary

WHERE Department = 'IT';

----------------

Deleted Records Tracking

Use the OUTPUT clause with DELETE to log deleted rows.

DELETE FROM Employee

OUTPUT DELETED.EmployeeID, DELETED.FirstName, DELETED.LastName

WHERE Department = 'Marketing';

select*from Employee

select*from EmployeeBackup

Summary

INSERT: Add new data to tables.

UPDATE: Modify existing data.

DELETE: Remove data.

MERGE: Combine operations.

Transactions: Ensure data integrity by grouping DML operations.

Savepoints: Allow partial rollbacks.

OUTPUT: Track changes made by DML operations.

-------------------

DCL – Data Control Language

Data Control Language (DCL) in SQL Server is used to control access to data within the database. It primarily involves managing permissions and security using the following commands:

GRANT - Give specific privileges to users or roles.

REVOKE - Remove previously granted privileges.

DENY - Explicitly deny privileges to users or roles (overrides GRANT).

We will use the Employee table from the earlier example and step through the commands and their advanced implementations.

Setting Up Users and Roles

Before we explore DCL commands, we must create users and roles in SQL Server.

1.1 Create a Database User

Create a new SQL Server login and user for managing permissions.

-- Create a new SQL login

CREATE LOGIN TestUser WITH PASSWORD = 'StrongPassword123';

-- Map the login to the current database

CREATE USER TestUser FOR LOGIN TestUser;

1.2 Create a Role

Create a database role for managing permissions at the group level.

-- Create a role

CREATE ROLE EmployeeManager;

-- Add the user to the role

ALTER ROLE EmployeeManager ADD MEMBER TestUser;

---------------------------------------------------

DCL Commands

2.1 GRANT Command

The GRANT command allows a user or role to perform specific actions (e.g., SELECT, INSERT, UPDATE, DELETE) on database objects.

Grant Basic Permissions

Allow TestUser to select data from the Employee table.

GRANT SELECT ON Employee TO TestUser;

----------------------

Explanation:

The user TestUser can now query data from the Employee table using:

SELECT * FROM Employee;

---------------------------------

Grant Permissions to a Role

Allow the EmployeeManager role to perform SELECT and INSERT on the Employee table.

GRANT SELECT, INSERT ON Employee TO EmployeeManager;

Explanation:

Any user added to the EmployeeManager role, including TestUser, inherits these permissions.

---------------------

Grant Schema-Wide Permissions

Allow TestUser to perform any action (SELECT, INSERT, UPDATE, DELETE) on all objects within the dbo schema.

GRANT CONTROL ON SCHEMA::dbo TO TestUser;

Explanation:

This grants TestUser full control over all objects in the dbo schema.

-----------------------------------

REVOKE Command

The REVOKE command removes previously granted permissions.

Revoke Specific Permissions

Revoke the SELECT permission from TestUser on the Employee table.

REVOKE SELECT ON Employee FROM TestUser;

Explanation:

TestUser can no longer query data from the Employee table.

----------------------------

Revoke Role Membership

Remove TestUser from the EmployeeManager role.

ALTER ROLE EmployeeManager DROP MEMBER TestUser;

Explanation:

TestUser no longer inherits permissions granted to the EmployeeManager role.

---------------------------

DENY Command

The DENY command explicitly prevents a user or role from performing specific actions, even if those actions are granted through other means (e.g., roles).

Deny Specific Permissions

Explicitly deny INSERT permissions for TestUser on the Employee table.

DENY INSERT ON Employee TO TestUser;

Explanation:

Even if TestUser is later granted INSERT permission through a role, the DENY will take precedence.

--------------------

Deny Schema-Wide Permissions

Prevent TestUser from altering any object in the dbo schema.

DENY ALTER ON SCHEMA::dbo TO TestUser;

Explanation:

TestUser cannot modify any objects in the dbo schema, even if they have ALTER privileges through other means.

-------------------------

Viewing Permissions

View the permissions for a specific user or role.

-- View all permissions for TestUser

SELECT * FROM sys.database_permissions

WHERE grantee_principal_id = USER_ID('TestUser');

--------------------------------------------------------

Permission Hierarchy

SQL Server follows a strict hierarchy for permissions:

DENY takes precedence over GRANT.

Role-based permissions apply to all members of the role.

Permissions can be granted at multiple levels (object, schema, or database).

Example: Role Precedence

If TestUser is granted SELECT via the EmployeeManager role but explicitly denied SELECT on the Employee table, the DENY takes precedence.

Auditing Permissions

Use SQL Server auditing to track permission changes.

-- Enable auditing for GRANT, REVOKE, and DENY statements

CREATE SERVER AUDIT MyServerAudit

TO FILE (FILEPATH = 'C:\AuditLogs\', MAXSIZE = 5 MB);

CREATE SERVER AUDIT SPECIFICATION MyAuditSpec

FOR SERVER AUDIT MyServerAudit

ADD (SCHEMA_OBJECT_PERMISSION_CHANGE_GROUP);

ALTER SERVER AUDIT MyServerAudit WITH (STATE = ON);

---ravi--

--------------------------------

ransactional Control on DCL

Use transactions to control permission changes.

BEGIN TRANSACTION;

-- Grant SELECT permission

GRANT SELECT ON Employee TO TestUser;

-- Revoke SELECT permission

REVOKE SELECT ON Employee FROM TestUser;

-- Rollback changes if necessary

ROLLBACK TRANSACTION;

-------------------------------

Practical Example

Let’s implement a scenario to combine all the DCL concepts:

Grant SELECT and INSERT permissions to the EmployeeManager role.

Add TestUser to the EmployeeManager role.

Deny DELETE permission to TestUser.

Revoke INSERT permission from the role.

BEGIN TRANSACTION;

-- Grant permissions to the role

GRANT SELECT, INSERT ON Employee TO EmployeeManager;

-- Add user to the role

ALTER ROLE EmployeeManager ADD MEMBER TestUser;

-- Deny DELETE permission to the user

DENY DELETE ON Employee TO TestUser;

-- Revoke INSERT permission from the role

REVOKE INSERT ON Employee FROM EmployeeManager;

COMMIT TRANSACTION;

----------------------

Summary

GRANT: Assign permissions to users or roles for specific actions on database objects.

REVOKE: Remove previously granted permissions.

DENY: Explicitly prevent users or roles from performing actions, overriding GRANT.

Transactions with DCL: Ensure safe permission management.

Auditing: Monitor permission changes for security and compliance.

------------------------------------------------------------------------------

TCL – Transaction Control Language

Transaction Control Language (TCL) in SQL Server is used to manage transactions in a database.

A transaction is a sequence of operations performed as a single logical unit of work.

TCL ensures that these operations are executed consistently and reliably.

Key Commands in TCL

BEGIN TRANSACTION: Starts a new transaction.

COMMIT TRANSACTION: Saves all changes made during the transaction.

ROLLBACK TRANSACTION: Undoes all changes made during the transaction.

SAVEPOINT: Creates a marker within a transaction, allowing partial rollback to that point.

SET TRANSACTION ISOLATION LEVEL: Sets the isolation level for transactions,

controlling how data is accessed by concurrent transactions.

-------------------------

tep-by-Step Explanation

We will use the previously created Employee table for this demonstration.

Step 1: Set Up the Employee Table

--Employee Employees

CREATE TABLE Employees (

EmployeeID INT PRIMARY KEY IDENTITY(1,1),

Name NVARCHAR(50),

Position NVARCHAR(50),

Salary DECIMAL(10, 2),

Department NVARCHAR(50)

);

-- Insert initial data

INSERT INTO Employees (Name, Position, Salary, Department)

VALUES

('John Doe', 'Manager', 75000, 'HR'),

('Jane Smith', 'Developer', 60000, 'IT'),

('Emily Davis', 'Analyst', 55000, 'Finance');

------------------------

Begin a Transaction

A transaction groups a set of operations that must succeed or fail together.

Use BEGIN TRANSACTION to start a transaction.

Example: Insert and Update within a Transaction

BEGIN TRANSACTION;

-- Insert a new employee

INSERT INTO Employees (Name, Position, Salary, Department)

VALUES ('Michael Scott', 'Regional Manager', 80000, 'Sales');

-- Update the salary of an existing employee

UPDATE Employees

SET Salary = Salary + 5000

WHERE Name = 'Jane Smith';

-- Check if everything is correct before committing

SELECT * FROM Employees;

-- If everything looks good, commit the transaction

COMMIT TRANSACTION;

------------------------------------------

Rollback a Transaction

If something goes wrong during the transaction, use ROLLBACK TRANSACTION to undo all changes.

Example: Rollback on Error

BEGIN TRANSACTION;

-- Try to insert a duplicate EmployeeID (this will cause an error)

BEGIN TRY

INSERT INTO Employees (EmployeeID, Name, Position, Salary, Department)

VALUES (1, 'Jim Halpert', 'Salesman', 45000, 'Sales');

-- Update an employee

UPDATE Employee

SET Salary = Salary + 1000

WHERE Name = 'Emily Davis';

-- Commit the transaction if no errors occur

COMMIT TRANSACTION;

END TRY

BEGIN CATCH

-- Rollback the transaction if an error occurs

ROLLBACK TRANSACTION;

-- Print the error message

PRINT ERROR_MESSAGE();

END CATCH;

---------------------------

Savepoints

Savepoints allow partial rollbacks within a transaction.

Example: Using SAVEPOINT

BEGIN TRANSACTION;

-- Insert a new employee

INSERT INTO Employees (Name, Position, Salary, Department)

VALUES ('Dwight Schrute', 'Assistant Regional Manager', 65000, 'Sales');

-- Create a savepoint

SAVE TRANSACTION SavePoint1;

-- Insert another employee

INSERT INTO Employees (Name, Position, Salary, Department)

VALUES ('Pam Beesly', 'Receptionist', 40000, 'Admin');

-- Rollback to the savepoint if needed

ROLLBACK TRANSACTION SavePoint1;

-- Commit the remaining changes

COMMIT TRANSACTION;

Explanation:

The insertion of Pam Beesly is rolled back, but Dwight Schrute remains in the table.

----------------------------------------

Transaction Isolation Levels

Isolation levels control how transactions interact with each other,

particularly in concurrent environments.

Use SET TRANSACTION ISOLATION LEVEL to define the isolation level.

Types of Isolation Levels

READ UNCOMMITTED: Allows dirty reads (reading uncommitted data).

READ COMMITTED: Prevents dirty reads but allows non-repeatable reads.

REPEATABLE READ: Prevents dirty and non-repeatable reads but allows phantom reads.

SERIALIZABLE: Prevents dirty, non-repeatable, and phantom reads (highest level of isolation).

Example- Setting Isolation Level

SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;

BEGIN TRANSACTION;

-- Query the Employee table

SELECT * FROM Employees WHERE Department = 'Sales';

-- Insert a new employee

INSERT INTO Employees (Name, Position, Salary, Department)

VALUES ('Kevin Malone', 'Accountant', 50000, 'Accounting');

-- Commit the transaction

COMMIT TRANSACTION;

------------------------------------------------------------

Nested Transactions

SQL Server supports nested transactions,

which allow you to start a new transaction within an existing one.

However, ROLLBACK rolls back all nested transactions.

Example: Nested Transactions

BEGIN TRANSACTION;

-- Outer transaction

INSERT INTO Employees (Name, Position, Salary, Department)

VALUES ('Stanley Hudson', 'Salesman', 45000, 'Sales');

BEGIN TRANSACTION;

-- Inner transaction

UPDATE Employees

SET Salary = Salary + 3000

WHERE Name = 'Dwight Schrute';

-- Commit inner transaction

COMMIT TRANSACTION;

-- Rollback outer transaction (this undoes both changes)

ROLLBACK TRANSACTION;

------------------

Distributed Transactions

Distributed transactions involve multiple databases or servers.

Use the BEGIN DISTRIBUTED TRANSACTION command.

BEGIN DISTRIBUTED TRANSACTION;

-- Operations on Database1

INSERT INTO Database1.dbo.Employees (Name, Position, Salary, Department)

VALUES ('Andy Bernard', 'Salesman', 48000, 'Sales');

-- Operations on Database2

INSERT INTO Database2.dbo.SalaryHistory (EmployeeID, SalaryChange, ChangeDate)

VALUES (1, 5000, GETDATE());

COMMIT TRANSACTION;

--ravi

--------------------------

Checking Transaction State

Use the @@TRANCOUNT function to check the current transaction count.

BEGIN TRANSACTION;

SELECT @@TRANCOUNT AS TransactionCount; -- Output: 1

BEGIN TRANSACTION;

SELECT @@TRANCOUNT AS TransactionCount; -- Output: 2

ROLLBACK TRANSACTION;

SELECT @@TRANCOUNT AS TransactionCount; -- Output: 0

--------------------------------

Practical Example

Let’s combine everything into a comprehensive example:

BEGIN TRANSACTION;

-- Insert a new employee

INSERT INTO Employees (Name, Position, Salary, Department)

VALUES ('Oscar Martinez', 'Accountant', 55000, 'Accounting');

-- Create a savepoint

SAVE TRANSACTION SavePoint1;

-- Update an existing employee

UPDATE Employees

SET Salary = Salary + 2000

WHERE Name = 'Stanley Hudson';

-- Rollback to savepoint if needed

ROLLBACK TRANSACTION SavePoint1;

-- Commit the transaction

COMMIT TRANSACTION;

------------------------------

BEGIN TRANSACTION: Start a transaction.

COMMIT TRANSACTION: Save changes made during the transaction.

ROLLBACK TRANSACTION: Undo changes made during the transaction.

SAVEPOINT: Allow partial rollback within a transaction.

SET TRANSACTION ISOLATION LEVEL: Define how transactions interact in a multi-user environment.

Nested Transactions: Transactions within transactions for granular control.

--------------------------------

Might be few doubts or issue there so please comment for correction,

Best

Ravi Rajput