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.

Practice

1. What is the main purpose of a WHILE loop inside a SQL procedure?

easy

A. To repeat a set of SQL statements while a condition is true

B. To create a new table in the database

C. To delete rows from a table

D. To join two tables together

Solution

1. Step 1: Understand the role of WHILE loops

   A WHILE loop runs repeatedly as long as its condition remains true.

2. Step 2: Identify the purpose in procedures

   Inside procedures, WHILE loops automate repeated tasks by running code multiple times.

3. Final Answer:

   To repeat a set of SQL statements while a condition is true -> Option A

4. Quick Check:

   WHILE loops repeat code while condition true = D [OK]

Hint: WHILE loops run code repeatedly while condition is true [OK]

Common Mistakes:

• Confusing WHILE with creating or deleting tables
• Thinking WHILE joins tables
• Assuming WHILE runs only once

2. Which of the following is the correct syntax to start a WHILE loop in a SQL procedure?

easy

A. WHILE condition DO

B. WHILE condition BEGIN

C. WHILE (condition BEGIN

D. WHILE condition LOOP

Solution

1. Step 1: Recall standard SQL procedure syntax

   In many SQL dialects, a WHILE loop starts with WHILE condition BEGIN to mark the loop start.

2. Step 2: Compare options

   WHILE condition BEGIN matches the correct syntax without extra parentheses or keywords like DO or LOOP.

3. Final Answer:

   WHILE condition BEGIN -> Option B

4. Quick Check:

   WHILE condition BEGIN is correct syntax [OK]

Hint: WHILE loops use BEGIN to start block, no DO or LOOP [OK]

Common Mistakes:

• Adding DO after WHILE (not standard in SQL)
• Using LOOP keyword (not in standard SQL)
• Putting condition in parentheses unnecessarily

3. Consider this SQL procedure snippet:

DECLARE @counter INT = 1;
WHILE @counter <= 3
BEGIN
  PRINT @counter;
  SET @counter = @counter + 1;
END

What will be printed when this runs?

medium

A. 1 2 3

B. 1 2 3 4

C. 0 1 2

D. No output, syntax error

Solution

1. Step 1: Analyze loop initialization and condition

   The counter starts at 1 and loops while it is less than or equal to 3.

2. Step 2: Trace loop iterations

   It prints 1, then increments to 2, prints 2, increments to 3, prints 3, then increments to 4 and stops.

3. Final Answer:

   1 2 3 -> Option A

4. Quick Check:

   Loop prints 1 to 3 inclusive = A [OK]

Hint: Count from start to condition limit inclusive [OK]

Common Mistakes:

• Thinking loop prints 4 because of last increment
• Confusing starting value as 0
• Assuming syntax error due to PRINT

4. Identify the error in this SQL procedure snippet:

DECLARE @i INT = 1;
WHILE @i < 5
BEGIN
  PRINT @i;
END

medium

A. Incorrect declaration of @i variable

B. PRINT statement syntax is wrong

C. Missing increment of @i inside the loop causing infinite loop

D. WHILE condition should use <= instead of <

Solution

1. Step 1: Check loop body for variable update

   The variable @i is never changed inside the loop, so condition stays true forever.

2. Step 2: Understand effect of missing increment

   Without increment, loop runs infinitely printing @i repeatedly.

3. Final Answer:

   Missing increment of @i inside the loop causing infinite loop -> Option C

4. Quick Check:

   Loop variable must update to avoid infinite loop = B [OK]

Hint: Always update loop variable inside WHILE to avoid infinite loop [OK]

Common Mistakes:

• Thinking PRINT syntax is wrong
• Believing declaration is incorrect
• Assuming condition operator causes error

5. You want to create a procedure that sums numbers from 1 to N using a WHILE loop. Which of these code snippets correctly implements this?

hard

A. DECLARE @sum INT = 0;WHILE @sum <= @NBEGIN SET @sum = @sum + 1;END

B. DECLARE @sum INT = 0, @i INT = 1;WHILE @i < @NBEGIN SET @sum = @sum + @i;END

C. DECLARE @sum INT = 0, @i INT = 1;WHILE @i <= @NBEGIN SET @sum = @sum + @i; SET @i = @i - 1;END

D. DECLARE @sum INT = 0, @i INT = 1;WHILE @i <= @NBEGIN SET @sum = @sum + @i; SET @i = @i + 1;END

Solution

1. Step 1: Check loop condition and variable updates

   DECLARE @sum INT = 0, @i INT = 1;
   WHILE @i <= @N
   BEGIN
   SET @sum = @sum + @i;
   SET @i = @i + 1;
   END correctly loops from 1 to N, adding each number to sum and incrementing i.

2. Step 2: Identify errors in other options

   DECLARE @sum INT = 0, @i INT = 1;
   WHILE @i < @N
   BEGIN
   SET @sum = @sum + @i;
   END misses increment of i, causing infinite loop. DECLARE @sum INT = 0, @i INT = 1;
   WHILE @i <= @N
   BEGIN
   SET @sum = @sum + @i;
   SET @i = @i - 1;
   END decrements i, causing infinite loop. DECLARE @sum INT = 0;
   WHILE @sum <= @N
   BEGIN
   SET @sum = @sum + 1;
   END incorrectly uses sum as loop variable.

3. Final Answer:

   DECLARE @sum INT = 0, @i INT = 1; WHILE @i <= @N BEGIN SET @sum = @sum + @i; SET @i = @i + 1; END -> Option D

4. Quick Check:

   Increment loop variable and add to sum = A [OK]

Hint: Increment loop counter and add to sum inside WHILE [OK]

Common Mistakes:

• Forgetting to increment loop variable
• Decrementing loop variable causing infinite loop
• Using sum as loop condition variable