Cursors for row iteration in MySQL - Practice Problems & Coding Challenges

Challenge - 5 Problems

❓ query_result

intermediate

Output of cursor iteration with FETCH

Consider the following MySQL stored procedure snippet that uses a cursor to iterate over a table employees with columns id and salary. What will be the value of total_salary after the cursor loop completes?

DECLARE done INT DEFAULT FALSE;
DECLARE emp_salary INT;
DECLARE total_salary INT DEFAULT 0;
DECLARE emp_cursor CURSOR FOR SELECT salary FROM employees WHERE id <= 3;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

OPEN emp_cursor;
read_loop: LOOP
  FETCH emp_cursor INTO emp_salary;
  IF done THEN
    LEAVE read_loop;
  END IF;
  SET total_salary = total_salary + emp_salary;
END LOOP;
CLOSE emp_cursor;

Assuming the employees table has rows with id 1, 2, 3 and salaries 1000, 1500, 2000 respectively.

A Syntax error due to missing cursor declaration

B 4500

C 1000

D 0

💡 Hint

Think about how the cursor fetches each salary and adds it to the total.

The cursor fetches salaries 1000, 1500, and 2000 one by one and adds them to total_salary. So total_salary becomes 1000 + 1500 + 2000 = 4500.

📝 Syntax

intermediate

Identify the syntax error in cursor declaration

Which of the following cursor declarations in MySQL is syntactically correct?

A DECLARE CURSOR emp_cursor FOR SELECT name FROM employees;

B CURSOR DECLARE emp_cursor FOR SELECT name FROM employees;

C DECLARE emp_cursor FOR CURSOR SELECT name FROM employees;

D DECLARE emp_cursor CURSOR FOR SELECT name FROM employees;

💡 Hint

Look at the order of keywords in the declaration.

The correct syntax is 'DECLARE cursor_name CURSOR FOR select_statement;'. Option D follows this syntax exactly.

🔧 Debug

advanced

Why does this cursor loop never end?

Given the following MySQL cursor loop, why does it never exit?

DECLARE done INT DEFAULT FALSE;
DECLARE emp_name VARCHAR(50);
DECLARE emp_cursor CURSOR FOR SELECT name FROM employees;
-- Missing CONTINUE HANDLER

OPEN emp_cursor;
read_loop: LOOP
  FETCH emp_cursor INTO emp_name;
  IF done THEN
    LEAVE read_loop;
  END IF;
  -- process emp_name
END LOOP;
CLOSE emp_cursor;

A Because the FETCH statement syntax is incorrect.

B Because the cursor is not opened before the loop.

C Because the CONTINUE HANDLER for NOT FOUND is missing, so 'done' is never set to TRUE.

D Because the loop label is missing.

💡 Hint

Think about how the loop knows when to stop fetching rows.

Without the CONTINUE HANDLER for NOT FOUND, the 'done' variable never becomes TRUE, so the loop never leaves.

❓ optimization

advanced

Optimizing cursor usage for large datasets

You have a cursor iterating over a large table to calculate the sum of a numeric column. Which approach is more efficient in MySQL?

A Use a single SQL query with SUM() aggregate function instead of a cursor loop.

B Use a cursor with a small FETCH size and commit after each fetch.

C Use a cursor and store each row in a temporary table before summing.

D Use nested cursors to process rows in batches.

💡 Hint

Think about what SQL is best at doing.

SQL aggregate functions like SUM() are optimized and faster than row-by-row cursor processing.

🧠 Conceptual

expert

Understanding cursor scope and variable lifetime

In MySQL stored procedures, what happens to a cursor and its associated variables after the procedure ends?

A The cursor and variables are automatically closed and deallocated when the procedure ends.

B The cursor remains open and variables retain their values until the session ends.

C The cursor is closed but variables keep their values after procedure ends.

D The cursor and variables persist globally and can be accessed by other procedures.

💡 Hint

Consider the scope of variables and cursors inside stored procedures.

Cursors and variables declared inside a stored procedure exist only during its execution and are cleaned up afterward.