WHILE loops in procedures in SQL - Practice Problems & Coding Challenges

Challenge - 5 Problems

❓ query_result

intermediate

Output of a simple WHILE loop procedure

Consider the following SQL procedure that uses a WHILE loop to insert numbers into a table. What will be the content of the table Numbers after executing the procedure?

SQL

CREATE TABLE Numbers (num INT);

CREATE PROCEDURE InsertNumbers()
BEGIN
  DECLARE i INT DEFAULT 1;
  WHILE i <= 3 DO
    INSERT INTO Numbers VALUES (i);
    SET i = i + 1;
  END WHILE;
END;

CALL InsertNumbers();
SELECT * FROM Numbers ORDER BY num;

A

1
2
3

B

1
2

C

0
1
2
3

D Empty table

💡 Hint

Think about how the WHILE loop increments and when it stops.

The loop starts at 1 and inserts values until i is greater than 3. It inserts 1, 2, and 3, then stops.

📝 Syntax

intermediate

Identify the syntax error in this WHILE loop procedure

Which option contains the correct syntax for a WHILE loop inside a SQL procedure?

SQL

CREATE PROCEDURE CountDown()
BEGIN
  DECLARE x INT DEFAULT 5;
  WHILE x > 0 DO
    SET x = x - 1;
  END WHILE;
END;

A Missing DO keyword after WHILE condition

B SET statement cannot be inside WHILE loop

C DECLARE statement must be after BEGIN but before WHILE

D Missing semicolon after END WHILE

💡 Hint

Check punctuation carefully after loop blocks.

In SQL procedures, each statement must end with a semicolon. The END WHILE must be followed by a semicolon.

❓ optimization

advanced

Optimizing a WHILE loop to avoid unnecessary iterations

Given a procedure that sums numbers from 1 to N using a WHILE loop, which option optimizes the loop to reduce the number of iterations?

SQL

CREATE PROCEDURE SumNumbers(IN N INT, OUT total INT)
BEGIN
  DECLARE i INT DEFAULT 1;
  SET total = 0;
  WHILE i <= N DO
    SET total = total + i;
    SET i = i + 1;
  END WHILE;
END;

A Add a BREAK statement inside WHILE when i > N

B Change WHILE to REPEAT loop with same logic

C Replace the WHILE loop with SET total = N*(N+1)/2;

D Use a cursor to iterate numbers instead of WHILE

💡 Hint

Think about mathematical formulas that can replace loops.

The sum of numbers from 1 to N can be calculated directly with the formula N*(N+1)/2, avoiding the loop.

🔧 Debug

advanced

Debugging an infinite loop in a WHILE procedure

This procedure causes an infinite loop. What is the cause?

SQL

CREATE PROCEDURE InfiniteLoop()
BEGIN
  DECLARE counter INT DEFAULT 1;
  WHILE counter <= 5 DO
    -- Missing increment
  END WHILE;
END;

A Missing BEGIN and END inside WHILE loop

B The counter variable is never incremented inside the loop

C The WHILE condition is incorrect syntax

D DECLARE statement is inside the loop causing reset

💡 Hint

Check if the loop variable changes inside the loop.

Without incrementing counter, the condition counter <= 5 is always true, causing an infinite loop.

🧠 Conceptual

expert

Understanding variable scope in WHILE loops inside procedures

In a SQL procedure, what happens to a variable declared inside a WHILE loop block compared to one declared before the loop?

A Variables declared inside the loop are re-declared each iteration and lose previous values

B Variables declared inside the loop keep their values between iterations

C Variables cannot be declared inside WHILE loops in SQL procedures

D Variables declared before the loop are local to the loop only

💡 Hint

Think about how SQL handles variable declarations inside blocks.

Variables declared inside the loop block are re-initialized each iteration, so they do not retain values between iterations.