WHILE loops in procedures in SQL - Interactive Code Practice

Practice - 5 Tasks

1fill in blank

easy

Complete the code to start a WHILE loop that runs as long as the counter is less than 5.

SQL

DECLARE @counter INT = 0;
WHILE [1] BEGIN
  SET @counter = @counter + 1;
END;

Drag options to blanks, or click blank then click option'

A@counter < 5

B@counter = 5

C@counter > 5

D@counter >= 5

Attempts:

3 left

💡 Hint

Common Mistakes

Using '=' instead of '<' in the condition.

Using '>=' which would stop the loop immediately.

The WHILE loop continues as long as the condition is true. Here, it runs while @counter < 5.

2fill in blank

medium

Complete the code to increment the variable inside the WHILE loop.

SQL

DECLARE @i INT = 1;
WHILE @i <= 3 BEGIN
  SET @i = [1];
END;

Drag options to blanks, or click blank then click option'

A@i * 2

B@i - 1

C@i + 1

D1

Attempts:

3 left

💡 Hint

Common Mistakes

Decreasing the counter which causes an infinite loop.

Setting the counter to a fixed number inside the loop.

Inside the loop, we increase @i by 1 each time to eventually end the loop.

3fill in blank

hard

Fix the error in the WHILE loop condition to avoid an infinite loop.

SQL

DECLARE @num INT = 10;
WHILE [1] BEGIN
  SET @num = @num - 1;
END;

Drag options to blanks, or click blank then click option'

A@num < 10

B@num = 10

C@num >= 10

D@num > 0

Attempts:

3 left

💡 Hint

Common Mistakes

Using a condition that never becomes false, causing an infinite loop.

Using '=' which checks for equality, not a range.

The loop should run while @num is greater than 0 to avoid running forever.

4fill in blank

hard

Fill both blanks to create a WHILE loop that counts down from 5 to 1.

SQL

DECLARE @count INT = 5;
WHILE [1] BEGIN
  PRINT @count;
  SET @count = [2];
END;

Drag options to blanks, or click blank then click option'

A@count > 0

B@count + 1

C@count - 1

D@count < 5

Attempts:

3 left

💡 Hint

Common Mistakes

Increasing the count inside the loop causing an infinite loop.

Using a wrong condition that never becomes false.

The loop runs while @count is greater than 0, and decreases @count by 1 each time.

5fill in blank

hard

Fill all three blanks to create a WHILE loop that sums numbers from 1 to 5.

SQL

DECLARE @sum INT = 0;
DECLARE @num INT = 1;
WHILE [1] BEGIN
  SET @sum = @sum + [2];
  SET @num = [3];
END;

Drag options to blanks, or click blank then click option'

A@num <= 5

B@num

C@num + 1

D@sum < 5

Attempts:

3 left

💡 Hint

Common Mistakes

Using the wrong loop condition that stops too early or never stops.

Not incrementing the number inside the loop causing an infinite loop.

The loop runs while @num is less than or equal to 5, adds @num to @sum, and increments @num by 1 each time.

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