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

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

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

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

Hint

Use CLOSE and DEALLOCATE to finish working with the cursor.

Practice

1. What is the main purpose of using a CURSOR in SQL?

easy

A. To process query results row by row

B. To speed up bulk data insertion

C. To create new tables dynamically

D. To automatically backup the database

Solution

1. Step 1: Understand what a cursor does

   A cursor allows you to handle each row returned by a query individually, instead of all at once.

2. Step 2: Compare options to cursor purpose

   Options B, C, and D describe tasks unrelated to row-by-row processing, which is the cursor's main use.

3. Final Answer:

   To process query results row by row -> Option A

4. Quick Check:

   Cursor = row-by-row processing [OK]

Hint: Remember: Cursor = handle rows one at a time [OK]

Common Mistakes:

• Thinking cursor speeds up bulk inserts
• Confusing cursor with table creation
• Assuming cursor automates backups

2. Which of the following is the correct order of cursor operations in SQL?

easy

A. DECLARE, FETCH, OPEN, CLOSE, DEALLOCATE

B. OPEN, DECLARE, FETCH, CLOSE, DEALLOCATE

C. DECLARE, OPEN, FETCH, CLOSE, DEALLOCATE

D. FETCH, DECLARE, OPEN, CLOSE, DEALLOCATE

Solution

1. Step 1: Recall the cursor lifecycle

   You first DECLARE the cursor, then OPEN it to start fetching rows, FETCH rows one by one, CLOSE it when done, and finally DEALLOCATE to free resources.

2. Step 2: Match the correct sequence

   Only DECLARE, OPEN, FETCH, CLOSE, DEALLOCATE follows this exact order. Other options mix the steps incorrectly.

3. Final Answer:

   DECLARE, OPEN, FETCH, CLOSE, DEALLOCATE -> Option C

4. Quick Check:

   Cursor lifecycle order = C [OK]

Hint: Remember: Declare first, then open, fetch, close, deallocate [OK]

Common Mistakes:

• Opening cursor before declaring
• Fetching before opening
• Not deallocating after closing

3. Given the following SQL cursor code snippet, what will be the output?

DECLARE @Name VARCHAR(50);
DECLARE name_cursor CURSOR FOR SELECT Name FROM Employees WHERE Department = 'Sales';
OPEN name_cursor;
FETCH NEXT FROM name_cursor INTO @Name;
WHILE @@FETCH_STATUS = 0
BEGIN
  PRINT @Name;
  FETCH NEXT FROM name_cursor INTO @Name;
END
CLOSE name_cursor;
DEALLOCATE name_cursor;

Assuming the Employees table has Sales department names: 'Alice', 'Bob', 'Carol'.

medium

A. Only 'Alice' is printed

B. Syntax error due to missing FETCH

C. No output because cursor is not opened

D. Alice Bob Carol (each printed on a new line)

Solution

1. Step 1: Understand cursor fetch loop

   The cursor fetches each row where Department = 'Sales'. It prints the @Name variable for each fetched row.

2. Step 2: Identify the printed names

   Since the table has 'Alice', 'Bob', and 'Carol' in Sales, all three names will be printed one by one.

3. Final Answer:

   Alice Bob Carol (each printed on a new line) -> Option D

4. Quick Check:

   Cursor prints all fetched rows [OK]

Hint: Cursor loops print all fetched rows until none left [OK]

Common Mistakes:

• Assuming only first row prints
• Forgetting to open cursor
• Confusing fetch status check

4. Identify the error in the following cursor code:

DECLARE cur CURSOR FOR SELECT Id FROM Products;
OPEN cur;
FETCH NEXT FROM cur INTO @ProductId;
WHILE @@FETCH_STATUS = 0
BEGIN
  PRINT @ProductId;
END
CLOSE cur;
DEALLOCATE cur;

medium

A. Missing FETCH NEXT inside the WHILE loop

B. Cursor not declared properly

C. Cursor not opened before fetching

D. No error, code runs fine

Solution

1. Step 1: Check fetch inside loop

   The code fetches once before the loop but does not fetch again inside the loop, causing an infinite loop.

2. Step 2: Identify missing statement

   To move to next row, FETCH NEXT must be inside the WHILE loop after PRINT.

3. Final Answer:

   Missing FETCH NEXT inside the WHILE loop -> Option A

4. Quick Check:

   Fetch inside loop needed to advance cursor [OK]

Hint: Always fetch next row inside the loop to avoid infinite loop [OK]

Common Mistakes:

• Forgetting FETCH NEXT inside loop
• Assuming one fetch is enough
• Not opening cursor before fetch

5. You want to update the salary of employees one by one using a cursor. Which approach correctly uses a cursor for this task?

hard

A. Open cursor, but never fetch rows, then update salary

B. Declare cursor for SELECT, open it, fetch each employee, update salary inside loop, close and deallocate

C. Declare cursor, but update salary before opening cursor

D. Use a single UPDATE statement without cursor for all employees

Solution

1. Step 1: Understand row-by-row update need

   When updating salaries individually, you must fetch each employee row and update inside the loop.

2. Step 2: Identify correct cursor usage

   Declare cursor for SELECT, open it, fetch each employee, update salary inside loop, close and deallocate correctly declares, opens, fetches, updates inside loop, then closes and deallocates cursor.

3. Final Answer:

   Declare cursor for SELECT, open it, fetch each employee, update salary inside loop, close and deallocate -> Option B

4. Quick Check:

   Cursor updates row-by-row inside loop [OK]

Hint: Update inside fetch loop after opening cursor [OK]

Common Mistakes:

• Trying to update before opening cursor
• Not fetching rows before update
• Using cursor but updating all at once