LOOP, WHILE, FOR iterations in PostgreSQL - Interactive Code Practice

Practice - 5 Tasks

1fill in blank

easy

Complete the code to create a simple FOR loop that iterates from 1 to 5.

PostgreSQL

DO $$ BEGIN
  FOR i IN 1..[1] LOOP
    RAISE NOTICE 'Number: %', i;
  END LOOP;
END $$;

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

A0

B10

C1

D5

Attempts:

3 left

💡 Hint

Common Mistakes

Using 10 instead of 5 causes the loop to run too many times.

Using 0 or 1 as the upper limit will not loop five times.

The FOR loop runs from 1 to 5, so the upper limit should be 5.

2fill in blank

medium

Complete the code to create a WHILE loop that counts down from 3 to 1.

PostgreSQL

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

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

A4

B3

C0

D1

Attempts:

3 left

💡 Hint

Common Mistakes

Using 1 as the condition causes the loop to stop too early.

Using 3 or 4 causes the loop to never run or run incorrectly.

The loop continues while counter is greater than 0, counting down from 3 to 1.

3fill in blank

hard

Fix the error in the FOR loop that should iterate over an array of integers.

PostgreSQL

DO $$ DECLARE
  numbers INTEGER[] := ARRAY[2, 4, 6];
  num INTEGER;
BEGIN
  FOR num IN [1] LOOP
    RAISE NOTICE 'Value: %', num;
  END LOOP;
END $$;

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

AUNNEST(numbers)

BSELECT * FROM numbers

C1..array_length(numbers, 1)

Dnumbers

Attempts:

3 left

💡 Hint

Common Mistakes

Using the array name directly does not work in FOR loops.

Using SELECT * FROM numbers is invalid because numbers is not a table.

Using 1..array_length(numbers, 1) loops over indexes, not values.

To loop over array elements in PostgreSQL, use UNNEST(array) in the FOR loop.

4fill in blank

hard

Fill both blanks to create a FOR loop that sums numbers from 1 to 4.

PostgreSQL

DO $$ DECLARE
  total INTEGER := 0;
BEGIN
  FOR i IN [1] LOOP
    total := total [2] i;
  END LOOP;
  RAISE NOTICE 'Sum: %', total;
END $$;

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

A1..4

B+

C-

D5..10

Attempts:

3 left

💡 Hint

Common Mistakes

Using 5..10 loops over wrong numbers.

Using - subtracts instead of adds.

The loop runs from 1 to 4, adding each number to total using the + operator.

5fill in blank

hard

Fill all three blanks to create a WHILE loop that multiplies a number by 2 until it is greater than 20.

PostgreSQL

DO $$ DECLARE
  val INTEGER := [1];
BEGIN
  WHILE val [2] [3] LOOP
    RAISE NOTICE 'Value: %', val;
    val := val * 2;
  END LOOP;
END $$;

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

A1

B<=

C20

D>

Attempts:

3 left

💡 Hint

Common Mistakes

Starting val at 20 or higher causes no loop iterations.

Using > 20 as condition causes loop to never run.

Start val at 1, loop while val is less than or equal to 20, doubling val each time.

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