What is a SQL Statement and types of SQL statements

A SQL (Structured Query Language) statement is a query or command used to perform various operations on data stored in a relational database. SQL statements are used to interact with databases to perform tasks such as retrieving data, updating records, inserting new data, and deleting existing records.

There are several types of SQL statements, broadly categorized into the following groups:

CREATE TABLE Employees (
    ID int,
    Name varchar(255),
    Age int,
    Department varchar(255)
);

ALTER TABLE Employees ADD COLUMN Salary int;

DROP TABLE Employees;

SELECT * FROM Employees;
INSERT INTO Employees (ID, Name, Age, Department) VALUES (1, 'John Doe', 30, 'HR');
UPDATE Employees SET Age = 31 WHERE ID = 1;
DELETE FROM Employees WHERE ID = 1;

GRANT SELECT ON Employees TO User1;
REVOKE SELECT ON Employees FROM User1;

COMMIT;
ROLLBACK;
SAVEPOINT sp1;

These SQL statements allow users to create and modify database structures, insert, update, delete, and query data, control access to data, and manage transactions.

GRANT privilege_name ON object_name TO user_name;
GRANT SELECT, INSERT, UPDATE ON Employees TO User1;
GRANT ALL PRIVILEGES ON Employees TO User1;
GRANT EXECUTE ON PROCEDURE Proc1 TO User1; --Allows a user to execute a stored procedure.

REVOKE privilege_name ON object_name FROM user_name;
REVOKE SELECT ON Employees FROM User1;

The SAVEPOINT statement in SQL is used to create a named point within a transaction to which you can later roll back if needed. This allows for more granular control over transactions, enabling partial rollbacks without affecting the entire transaction.

--Syntax
SAVEPOINT savepoint_name;

1. Creating a Savepoint: Suppose you are performing a series of operations within a transaction and want to create a savepoint.

BEGIN TRANSACTION;

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

INSERT INTO Employees (ID, Name, Age, Department) VALUES (2, 'Jane Smith', 25, 'Finance');
SAVEPOINT sp2;

UPDATE Employees SET Age = 26 WHERE ID = 2;
SAVEPOINT sp3;

2. Rolling Back to a Savepoint: If you decide that the last operation was incorrect and want to undo it, you can roll back to a specific savepoint.

ROLLBACK TO sp2;
--This command will undo the changes made after sp2 was created, but keep the changes made before sp2.

3. Committing a Transaction: After creating savepoints and possibly rolling back to one of them, you can commit the transaction to make all the changes permanent.

COMMIT;

4. Rolling Back the Entire Transaction: If needed, you can also roll back the entire transaction, disregarding all savepoints.

ROLLBACK;

Example Scenario

Imagine you are updating an inventory system where you need to update multiple tables in a single transaction. If an error occurs during one of the updates, you might want to roll back to a specific point without discarding all previous successful updates.

BEGIN TRANSACTION;

-- Step 1: Update product quantities
UPDATE Products SET Quantity = Quantity - 10 WHERE ProductID = 1;
SAVEPOINT sp1;

-- Step 2: Insert a record into the sales table
INSERT INTO Sales (ProductID, Quantity, SaleDate) VALUES (1, 10, '2024-07-10');
SAVEPOINT sp2;

-- Step 3: Update customer orders
UPDATE Orders SET Status = 'Shipped' WHERE OrderID = 1001;
-- Assume an error occurs here

-- Rollback to the savepoint before the error
ROLLBACK TO sp2;

-- Commit the transaction if everything else is fine
COMMIT;

In this scenario, the rollback to sp2 ensures that only the last update is undone, preserving the changes made in the previous steps.