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.