LOOP, WHILE, FOR iterations in PostgreSQL - Practice Problems & Coding Challenges

Challenge - 5 Problems

❓ query_result

intermediate

Output of a simple FOR loop in PL/pgSQL

What will be the output of the following PL/pgSQL block?

DO $$
DECLARE
  i INT;
BEGIN
  FOR i IN 1..3 LOOP
    RAISE NOTICE 'Number: %', i;
  END LOOP;
END $$;

PostgreSQL

DO $$
DECLARE
  i INT;
BEGIN
  FOR i IN 1..3 LOOP
    RAISE NOTICE 'Number: %', i;
  END LOOP;
END $$;

A Syntax error due to missing semicolon

B

Number: 0
Number: 1
Number: 2
Number: 3

C

Number: 1
Number: 2

D

Number: 1
Number: 2
Number: 3

💡 Hint

The FOR loop runs from 1 to 3 inclusive.

The FOR loop iterates over the range 1 to 3, printing each number with RAISE NOTICE.

❓ query_result

intermediate

WHILE loop behavior with decrement

What will be the output of this PL/pgSQL block?

DO $$
DECLARE
  counter INT := 3;
BEGIN
  WHILE counter > 0 LOOP
    RAISE NOTICE 'Count: %', counter;
    counter := counter - 1;
  END LOOP;
END $$;

PostgreSQL

DO $$
DECLARE
  counter INT := 3;
BEGIN
  WHILE counter > 0 LOOP
    RAISE NOTICE 'Count: %', counter;
    counter := counter - 1;
  END LOOP;
END $$;

A

Count: 3
Count: 2
Count: 1

B

Count: 3
Count: 2
Count: 1
Count: 0

C

Count: 2
Count: 1
Count: 0

D Infinite loop error

💡 Hint

The loop stops when counter is no longer greater than 0.

The WHILE loop runs while counter is 3, 2, and 1, then stops before 0.

📝 Syntax

advanced

Identify the syntax error in this FOR loop

Which option contains the correct syntax for a FOR loop iterating over integers 1 to 5 in PL/pgSQL?

A

FOR i IN 1 TO 5 LOOP
  RAISE NOTICE '%', i;
END LOOP;

B

FOR i IN 1..5 LOOP
  RAISE NOTICE '%', i;
END LOOP;

C

FOR i FROM 1 TO 5 LOOP
  RAISE NOTICE '%', i;
END LOOP;

D

FOR i IN RANGE(1,5) LOOP
  RAISE NOTICE '%', i;
END LOOP;

💡 Hint

PL/pgSQL uses two dots .. for ranges.

The correct syntax uses 'IN 1..5' to specify the range in a FOR loop.

❓ query_result

advanced

Output of nested loops with conditional exit

What will be the output of this PL/pgSQL block?

DO $$
DECLARE
  i INT;
  j INT;
BEGIN
  FOR i IN 1..2 LOOP
    FOR j IN 1..3 LOOP
      IF i * j > 3 THEN
        EXIT;
      END IF;
      RAISE NOTICE 'i=% j=%', i, j;
    END LOOP;
  END LOOP;
END $$;

PostgreSQL

DO $$
DECLARE
  i INT;
  j INT;
BEGIN
  FOR i IN 1..2 LOOP
    FOR j IN 1..3 LOOP
      IF i * j > 3 THEN
        EXIT;
      END IF;
      RAISE NOTICE 'i=% j=%', i, j;
    END LOOP;
  END LOOP;
END $$;

A

i=1 j=1
i=1 j=2
i=1 j=3
i=2 j=1

B

i=1 j=1
i=1 j=2
i=1 j=3
i=2 j=1
i=2 j=2

C

i=1 j=1
i=1 j=2
i=2 j=1
i=2 j=2

D

i=1 j=1
i=1 j=2
i=2 j=1
i=2 j=2
i=2 j=3

💡 Hint

The inner loop exits when i*j > 3, skipping further j values.

For i=1, j=1,2,3 all print since 1*3=3 not >3. For i=2, j=1 prints (2*1=2 not >3), j=2 checks 4>3 and EXITs before printing.

🧠 Conceptual

expert

Effect of EXIT vs CONTINUE in loops

In PL/pgSQL loops, what is the difference between EXIT and CONTINUE statements?

A EXIT skips the current iteration; CONTINUE stops the loop completely.

B EXIT and CONTINUE both stop the loop but differ in syntax.

C EXIT stops the current loop entirely; CONTINUE skips to the next iteration of the loop.

D EXIT restarts the loop from the beginning; CONTINUE ends the loop.

💡 Hint

Think about how to stop a loop vs skip one iteration.

EXIT ends the loop immediately; CONTINUE skips the rest of the current iteration and moves to the next.

Practice

1. What does a LOOP statement do in PostgreSQL procedural code?

easy

A. Executes code only if a condition is true at the start.

B. Runs the code only once and then stops automatically.

C. Goes through each row of a query automatically.

D. Repeats the code inside it until an EXIT command is reached.

Solution

1. Step 1: Understand the LOOP statement

   A LOOP in PostgreSQL repeats the code inside it indefinitely until an EXIT command is used to stop it.

2. Step 2: Compare with other loops

   Unlike WHILE or FOR, LOOP does not check a condition automatically; it relies on EXIT to stop.

3. Final Answer:

   Repeats the code inside it until an EXIT command is reached. -> Option D

4. Quick Check:

   LOOP repeats until EXIT [OK]

Hint: LOOP runs forever until EXIT stops it [OK]

Common Mistakes:

• Thinking LOOP stops automatically without EXIT
• Confusing LOOP with WHILE or FOR
• Assuming LOOP checks conditions itself

2. Which of the following is the correct syntax to start a FOR loop iterating over rows from a query in PostgreSQL?

easy

A. FOR record FROM users LOOP

B. FOR (SELECT * FROM users) LOOP

C. FOR record IN SELECT * FROM users LOOP

D. FOR record WHILE SELECT * FROM users LOOP

Solution

1. Step 1: Recall FOR loop syntax for query iteration

   The correct syntax uses FOR record IN SELECT ... LOOP to iterate over query rows.

2. Step 2: Check each option

   FOR record IN SELECT * FROM users LOOP matches the correct syntax. Options B, C, and D are invalid or incomplete.

3. Final Answer:

   FOR record IN SELECT * FROM users LOOP -> Option C

4. Quick Check:

   FOR ... IN SELECT ... LOOP is correct syntax [OK]

Hint: FOR loops use 'IN SELECT' to iterate query rows [OK]

Common Mistakes:

• Omitting IN keyword
• Using parentheses incorrectly
• Mixing WHILE syntax with FOR

3. What will be the output of this PostgreSQL code snippet?

DECLARE
  counter INT := 1;
  total INT := 0;
BEGIN
  WHILE counter <= 3 LOOP
    total := total + counter;
    counter := counter + 1;
  END LOOP;
  RAISE NOTICE 'Total: %', total;
END;

medium

A. Syntax error

B. Total: 6

C. Total: 0

D. Total: 3

Solution

1. Step 1: Trace the WHILE loop iterations

   The loop runs while counter is 1, 2, and 3. Each time, total adds counter's value.

2. Step 2: Calculate total after loop

   Total = 0 + 1 + 2 + 3 = 6 after the loop ends.

3. Final Answer:

   Total: 6 -> Option B

4. Quick Check:

   Sum 1+2+3 = 6 [OK]

Hint: Add counter each loop until condition false [OK]

Common Mistakes:

• Stopping loop too early
• Adding counter after increment
• Confusing total initialization

4. Identify the error in this PostgreSQL loop code:

DECLARE
  i INT := 1;
BEGIN
  WHILE i < 5 LOOP
    RAISE NOTICE '%', i;
  END LOOP;
END;

medium

A. Missing increment of variable i inside the loop.

B. Incorrect declaration of variable i.

C. Syntax error in RAISE NOTICE statement.

D. WHILE condition should be i <= 5.

Solution

1. Step 1: Check loop control variable usage

   The variable i is never increased inside the loop, so the condition i < 5 never becomes false.

2. Step 2: Understand loop behavior

   Without increment, the loop runs forever causing an infinite loop error.

3. Final Answer:

   Missing increment of variable i inside the loop. -> Option A

4. Quick Check:

   Loop needs variable increment to stop [OK]

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

Common Mistakes:

• Forgetting to increment loop variable
• Assuming loop stops automatically
• Misreading WHILE condition

5. You want to sum the price column from a products table using a FOR loop in PL/pgSQL. Which code snippet correctly does this?

hard

A. DECLARE total NUMERIC := 0; BEGIN FOR rec IN SELECT price FROM products LOOP total := total + rec.price; END LOOP; RAISE NOTICE '%', total; END;

B. DECLARE total NUMERIC := 0; BEGIN FOR rec products LOOP total := total + rec.price; END LOOP; RAISE NOTICE '%', total; END;

C. DECLARE total NUMERIC := 0; BEGIN FOR rec FROM SELECT price FROM products LOOP total := total + rec.price; END LOOP; RAISE NOTICE '%', total; END;

D. DECLARE total NUMERIC := 0; BEGIN WHILE rec IN SELECT price FROM products LOOP total := total + rec.price; END LOOP; RAISE NOTICE '%', total; END;

Solution

1. Step 1: Identify correct FOR loop syntax for query rows

   DECLARE total NUMERIC := 0; BEGIN FOR rec IN SELECT price FROM products LOOP total := total + rec.price; END LOOP; RAISE NOTICE '%', total; END; uses FOR rec IN SELECT price FROM products LOOP, which is correct syntax to iterate query results.

2. Step 2: Verify accumulation and output

   Inside the loop, it adds rec.price to total and then outputs total with RAISE NOTICE.

3. Final Answer:

   DECLARE total NUMERIC := 0; BEGIN FOR rec IN SELECT price FROM products LOOP total := total + rec.price; END LOOP; RAISE NOTICE '%', total; END; -> Option A

4. Quick Check:

   FOR ... IN SELECT ... LOOP sums prices [OK]

Hint: Use FOR ... IN SELECT ... LOOP to sum column values [OK]

Common Mistakes:

• Omitting IN keyword in FOR loop
• Using WHILE instead of FOR for query rows
• Incorrect loop variable or query syntax