CURSOR concept and usage in SQL - Practice Problems & Coding Challenges

Challenge - 5 Problems

❓ query_result

intermediate

Output of cursor fetching rows one by one

Consider a cursor declared on a table Employees with columns id and name. The cursor fetches rows ordered by id. What will be the output of the following pseudo-code?

DECLARE emp_cursor CURSOR FOR SELECT id, name FROM Employees ORDER BY id;
OPEN emp_cursor;
FETCH NEXT FROM emp_cursor INTO @id, @name;
PRINT @id, @name;
FETCH NEXT FROM emp_cursor INTO @id, @name;
PRINT @id, @name;
CLOSE emp_cursor;
DEALLOCATE emp_cursor;

Assume the table has rows: (2, 'Alice'), (1, 'Bob'), (3, 'Charlie').

SQL

DECLARE emp_cursor CURSOR FOR SELECT id, name FROM Employees ORDER BY id;
OPEN emp_cursor;
FETCH NEXT FROM emp_cursor INTO @id, @name;
PRINT @id, @name;
FETCH NEXT FROM emp_cursor INTO @id, @name;
PRINT @id, @name;
CLOSE emp_cursor;
DEALLOCATE emp_cursor;

A

1 Bob
2 Alice

B

2 Alice
1 Bob

C

1 Bob
3 Charlie

D

2 Alice
3 Charlie

💡 Hint

Remember the cursor fetches rows in the order specified by the ORDER BY clause.

The cursor selects rows ordered by id ascending: (1, Bob), (2, Alice), (3, Charlie). The first FETCH gets (1, Bob), the second FETCH gets (2, Alice).

📝 Syntax

intermediate

Identify the syntax error in cursor declaration

Which of the following cursor declarations is syntactically correct in standard SQL?

A DECLARE my_cursor FOR CURSOR SELECT * FROM table_name;

B DECLARE my_cursor CURSOR FOR SELECT * FROM table_name;

C DECLARE CURSOR my_cursor FOR SELECT * FROM table_name;

D CURSOR DECLARE my_cursor FOR SELECT * FROM table_name;

💡 Hint

The correct order is DECLARE cursor_name CURSOR FOR query;

The correct syntax is: DECLARE cursor_name CURSOR FOR select_statement; Options A, B, and D have incorrect keyword order.

🧠 Conceptual

advanced

Why use cursors instead of set-based queries?

Which of the following is the best reason to use a cursor in SQL?

A To process rows one at a time when row-by-row operations are needed

B To improve performance by avoiding loops

C To replace all SELECT queries with faster alternatives

D To automatically index tables for faster access

💡 Hint

Think about when you need to handle each row individually.

Cursors allow row-by-row processing which is useful when operations depend on individual rows. They are not used to improve performance or replace set-based queries.

🔧 Debug

advanced

Find the error in cursor usage

Given the following code snippet, what error will occur?

DECLARE emp_cursor CURSOR FOR SELECT id FROM Employees;
OPEN emp_cursor;
FETCH NEXT FROM emp_cursor INTO @emp_id;
CLOSE emp_cursor;
FETCH NEXT FROM emp_cursor INTO @emp_id;

SQL

DECLARE emp_cursor CURSOR FOR SELECT id FROM Employees;
OPEN emp_cursor;
FETCH NEXT FROM emp_cursor INTO @emp_id;
CLOSE emp_cursor;
FETCH NEXT FROM emp_cursor INTO @emp_id;

A No error, fetch returns no rows

B Error: Cursor not declared

C Error: Cannot fetch from a closed cursor

D Error: Variable @emp_id not declared

💡 Hint

Think about what happens if you fetch after closing the cursor.

After closing a cursor, fetching from it causes an error because the cursor is no longer open.

❓ optimization

expert

Optimizing cursor usage for large datasets

You have a cursor processing 1 million rows. Which option best improves performance and resource usage?

A Use a LOCAL cursor to share across sessions

B Use a STATIC cursor to lock all rows

C Use a DYNAMIC cursor to see real-time changes

D Use a FAST_FORWARD cursor to reduce overhead

💡 Hint

Consider cursor types that minimize locking and resource use.

FAST_FORWARD cursors are read-only and forward-only, which reduces overhead and improves performance for large datasets. STATIC and DYNAMIC cursors have more overhead. LOCAL affects scope, not performance.

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