CURSOR concept and usage in SQL - Mini Project: Build & Apply

Using CURSOR to Process Employee Salaries

📖 Scenario: You work in the HR department of a company. You have a table of employees with their salaries. You want to give a 10% bonus to employees who earn less than 5000. To do this, you will use a CURSOR to go through each employee and update their salary if needed.

🎯 Goal: Create a SQL script that uses a CURSOR to iterate over employees, check their salary, and update it by adding a 10% bonus if the salary is less than 5000.

📋 What You'll Learn

Create a table called Employees with columns EmployeeID (integer), Name (varchar), and Salary (decimal).

Insert exactly three employees with these details: (1, 'Alice', 4500), (2, 'Bob', 5200), (3, 'Charlie', 4800).

Declare a CURSOR to select EmployeeID and Salary from Employees.

Use the CURSOR to loop through each employee, and if their salary is less than 5000, update their salary by adding 10%.

Close and deallocate the CURSOR after processing.

💡 Why This Matters

🌍 Real World

Cursors are useful when you need to process rows one at a time, for example, applying complex logic or calling external procedures for each row.

💼 Career

Understanding cursors helps in database administration and writing procedural SQL scripts for business logic that cannot be done easily with simple queries.

1

Create the Employees table and insert data

Write SQL statements to create a table called Employees with columns EmployeeID (integer), Name (varchar(50)), and Salary (decimal(10,2)). Then insert these three rows exactly: (1, 'Alice', 4500), (2, 'Bob', 5200), and (3, 'Charlie', 4800).

SQL

-- Create Employees table and insert data
-- Your code here

Need a hint?

Use CREATE TABLE to define the table and INSERT INTO to add the rows.

2

Declare variables and the CURSOR

Declare two variables: @EmpID as INT and @EmpSalary as DECIMAL(10,2). Then declare a CURSOR named EmpCursor that selects EmployeeID and Salary from the Employees table.

SQL

CREATE TABLE Employees (
    EmployeeID INT PRIMARY KEY,
    Name VARCHAR(50),
    Salary DECIMAL(10,2)
);

INSERT INTO Employees (EmployeeID, Name, Salary) VALUES
(1, 'Alice', 4500),
(2, 'Bob', 5200),
(3, 'Charlie', 4800);

-- Declare variables and cursor
-- Your code here

Need a hint?

Use DECLARE to create variables and the cursor.

3

Open the CURSOR and fetch data in a loop

Open the EmpCursor. Use FETCH NEXT FROM EmpCursor INTO @EmpID, @EmpSalary to get the first row. Then write a WHILE loop that continues while @@FETCH_STATUS = 0. Inside the loop, check if @EmpSalary is less than 5000, and if yes, update the Employees table to increase the salary by 10% for that @EmpID. Then fetch the next row inside the loop.

SQL

CREATE TABLE Employees (
    EmployeeID INT PRIMARY KEY,
    Name VARCHAR(50),
    Salary DECIMAL(10,2)
);

INSERT INTO Employees (EmployeeID, Name, Salary) VALUES
(1, 'Alice', 4500),
(2, 'Bob', 5200),
(3, 'Charlie', 4800);

DECLARE @EmpID INT;
DECLARE @EmpSalary DECIMAL(10,2);

DECLARE EmpCursor CURSOR FOR
SELECT EmployeeID, Salary FROM Employees;

-- Open cursor and loop through rows
-- Your code here

Need a hint?

Remember to open the cursor before fetching and use a WHILE loop with @@FETCH_STATUS.

4

Close and deallocate the CURSOR

After the loop, write SQL statements to close the EmpCursor and then deallocate it to free resources.

SQL

CREATE TABLE Employees (
    EmployeeID INT PRIMARY KEY,
    Name VARCHAR(50),
    Salary DECIMAL(10,2)
);

INSERT INTO Employees (EmployeeID, Name, Salary) VALUES
(1, 'Alice', 4500),
(2, 'Bob', 5200),
(3, 'Charlie', 4800);

DECLARE @EmpID INT;
DECLARE @EmpSalary DECIMAL(10,2);

DECLARE EmpCursor CURSOR FOR
SELECT EmployeeID, Salary FROM Employees;

OPEN EmpCursor;
FETCH NEXT FROM EmpCursor INTO @EmpID, @EmpSalary;

WHILE @@FETCH_STATUS = 0
BEGIN
    IF @EmpSalary < 5000
    BEGIN
        UPDATE Employees
        SET Salary = Salary * 1.10
        WHERE EmployeeID = @EmpID;
    END
    FETCH NEXT FROM EmpCursor INTO @EmpID, @EmpSalary;
END

-- Close and deallocate cursor
-- Your code here

Need a hint?

Use CLOSE and DEALLOCATE to finish working with the cursor.