IF-ELSIF-ELSE control flow in PostgreSQL - Practice Problems & Coding Challenges

Challenge - 5 Problems

❓ query_result

intermediate

Output of IF-ELSIF-ELSE in a PL/pgSQL function

Consider the following PostgreSQL function. What will be the output when calling check_number(15)?

PostgreSQL

CREATE OR REPLACE FUNCTION check_number(num INTEGER) RETURNS TEXT AS $$
BEGIN
  IF num < 10 THEN
    RETURN 'Less than 10';
  ELSIF num < 20 THEN
    RETURN 'Between 10 and 19';
  ELSE
    RETURN '20 or more';
  END IF;
END;
$$ LANGUAGE plpgsql;

A 'Between 10 and 19'

B 'Less than 10'

C '20 or more'

D NULL

💡 Hint

Think about which condition matches the input number 15.

The function checks if the number is less than 10, then between 10 and 19, else 20 or more. Since 15 is between 10 and 19, it returns 'Between 10 and 19'.

📝 Syntax

intermediate

Identify the syntax error in IF-ELSIF-ELSE block

Which option contains a syntax error in the IF-ELSIF-ELSE block in PostgreSQL PL/pgSQL?

PostgreSQL

BEGIN
  IF x = 1 THEN
    RAISE NOTICE 'One';
  ELSEIF x = 2 THEN
    RAISE NOTICE 'Two';
  ELSE
    RAISE NOTICE 'Other';
  END IF;
END;

A Remove ELSE block

B Replace END IF with END

C Add semicolon after THEN

D Replace ELSEIF with ELSIF

💡 Hint

Check the correct keyword spelling for else-if in PL/pgSQL.

In PL/pgSQL, the correct keyword is ELSIF, not ELSEIF. Using ELSEIF causes a syntax error.

❓ query_result

advanced

Result of nested IF-ELSIF-ELSE in a function

What will be the output of evaluate_score(75) given this function?

PostgreSQL

CREATE OR REPLACE FUNCTION evaluate_score(score INTEGER) RETURNS TEXT AS $$
BEGIN
  IF score >= 90 THEN
    RETURN 'Excellent';
  ELSIF score >= 70 THEN
    IF score >= 80 THEN
      RETURN 'Very Good';
    ELSE
      RETURN 'Good';
    END IF;
  ELSE
    RETURN 'Needs Improvement';
  END IF;
END;
$$ LANGUAGE plpgsql;

A 'Good'

B 'Very Good'

C 'Excellent'

D 'Needs Improvement'

💡 Hint

Check the nested IF conditions carefully for score 75.

75 is >= 70 but less than 80, so the inner IF returns 'Good'.

🔧 Debug

advanced

Find the logical error in IF-ELSIF-ELSE

This function is intended to classify ages but returns incorrect results for age 18. What is the logical error?

PostgreSQL

CREATE OR REPLACE FUNCTION classify_age(age INTEGER) RETURNS TEXT AS $$
BEGIN
  IF age < 13 THEN
    RETURN 'Child';
  ELSIF age > 13 AND age < 18 THEN
    RETURN 'Teenager';
  ELSE
    RETURN 'Adult';
  END IF;
END;
$$ LANGUAGE plpgsql;

A Should use OR instead of AND in ELSIF

B Missing ELSEIF for age = 18

C The condition excludes age 13 and 18 from 'Teenager' category

D RETURN statements are misplaced

💡 Hint

Check the conditions carefully for boundary ages 13 and 18.

The ELSIF condition uses > 13 and < 18, so age 13 and 18 do not match 'Teenager'. Age 13 is classified as 'Adult' incorrectly.

❓ optimization

expert

Optimize IF-ELSIF-ELSE for performance

Which option optimizes this IF-ELSIF-ELSE block for better performance when checking multiple conditions on a variable val?

PostgreSQL

IF val = 1 THEN
  RETURN 'One';
ELSIF val = 2 THEN
  RETURN 'Two';
ELSIF val = 3 THEN
  RETURN 'Three';
ELSE
  RETURN 'Other';
END IF;

A Use nested IF statements for each condition

B Use CASE statement instead of IF-ELSIF-ELSE

C Replace ELSE with another ELSIF for val = 4

D Use multiple IF statements without ELSIF

💡 Hint

Consider which control structure is more efficient and readable for multiple discrete values.

CASE statements are optimized for multiple discrete value checks and improve readability and performance over multiple IF-ELSIF-ELSE blocks.

Practice

1.

What is the purpose of the ELSIF keyword in PostgreSQL's IF control flow?

easy

A. To test an additional condition if the previous IF condition is false

B. To end the IF statement

C. To execute code unconditionally

D. To start a loop inside the IF block

Solution

1. Step 1: Understand the role of IF and ELSIF

   The IF keyword tests the first condition. If it is false, ELSIF allows testing another condition.

2. Step 2: Differentiate ELSIF from other keywords

   ELSIF is not for ending or unconditional execution; it is for additional conditional checks.

3. Final Answer:

   To test an additional condition if the previous IF condition is false -> Option A

4. Quick Check:

   ELSIF = additional condition test [OK]

Hint: Remember: ELSIF adds more conditions after IF [OK]

Common Mistakes:

• Thinking ELSIF ends the IF block
• Confusing ELSIF with ELSE
• Using ELSIF without a preceding IF

2.

Which of the following is the correct syntax to close an IF statement in PostgreSQL?

easy

A. END;

B. ENDIF;

C. END IF;

D. FINISH IF;

Solution

1. Step 1: Recall PostgreSQL block ending syntax

   PostgreSQL requires END IF; to close an IF block explicitly.

2. Step 2: Compare options

   ENDIF; and FINISH IF; are invalid. END; alone closes other blocks but not IF.

3. Final Answer:

   END IF; -> Option C

4. Quick Check:

   Close IF with END IF; [OK]

Hint: Always end IF blocks with END IF; in PostgreSQL [OK]

Common Mistakes:

• Using END; alone to close IF
• Writing ENDIF; without space
• Forgetting to close IF blocks

3.

Consider this PostgreSQL code snippet inside a function:

IF score >= 90 THEN
  result := 'A';
ELSIF score >= 80 THEN
  result := 'B';
ELSIF score >= 70 THEN
  result := 'C';
ELSE
  result := 'F';
END IF;

If score is 85, what will be the value of result after execution?

medium

A. 'A'

B. 'F'

C. 'C'

D. 'B'

Solution

1. Step 1: Evaluate conditions in order for score = 85

   Check if 85 >= 90? No. Then check 85 >= 80? Yes.

2. Step 2: Assign result based on first true condition

   Since 85 >= 80 is true, result is set to 'B'. Remaining conditions are skipped.

3. Final Answer:

   'B' -> Option D

4. Quick Check:

   85 >= 80 = true, so result = 'B' [OK]

Hint: Check conditions top to bottom; first true sets the result [OK]

Common Mistakes:

• Choosing 'A' because 85 is close to 90
• Ignoring order of conditions
• Assigning 'C' or 'F' incorrectly

4.

Identify the error in this PostgreSQL IF block:

IF value > 10 THEN
  RAISE NOTICE 'Value is large';
ELSIF value < 5
  RAISE NOTICE 'Value is small';
ELSE
  RAISE NOTICE 'Value is medium';
END IF;

medium

A. Using ELSE without condition

B. Missing THEN after ELSIF value < 5

C. Incorrect use of RAISE NOTICE

D. Missing END IF;

Solution

1. Step 1: Check syntax of each condition

   The ELSIF line lacks the required THEN keyword after the condition.

2. Step 2: Confirm other parts are correct

   END IF; is present, RAISE NOTICE is valid, and ELSE does not take a condition.

3. Final Answer:

   Missing THEN after ELSIF value < 5 -> Option B

4. Quick Check:

   ELSIF must have THEN [OK]

Hint: Always write THEN after IF and ELSIF conditions [OK]

Common Mistakes:

• Omitting THEN after ELSIF
• Adding condition after ELSE
• Forgetting END IF;

5.

You want to write a PostgreSQL function that returns 'Positive', 'Negative', or 'Zero' based on an integer input num. Which IF-ELSIF-ELSE block correctly implements this logic?

-- Options:
A) IF num > 0 THEN RETURN 'Positive';
   ELSIF num < 0 THEN RETURN 'Negative';
   ELSE RETURN 'Zero';
   END IF;

B) IF num > 0 THEN RETURN 'Positive';
   ELSEIF num < 0 THEN RETURN 'Negative';
   ELSE RETURN 'Zero';
   END IF;

C) IF num > 0 THEN RETURN 'Positive';
   ELSIF num < 0 THEN RETURN 'Negative';
   ELSEIF num = 0 THEN RETURN 'Zero';
   END IF;

D) IF num > 0 THEN RETURN 'Positive';
   IF num < 0 THEN RETURN 'Negative';
   ELSE RETURN 'Zero';
   END IF;

hard

A. Correct use of IF, ELSIF, ELSE with proper syntax

B. Uses invalid keyword ELSEIF instead of ELSIF

C. Uses ELSEIF after ELSE which is invalid

D. Nested IF without closing first IF properly

Solution

1. Step 1: Check keywords and structure

   Correct use of IF, ELSIF, ELSE with proper syntax uses IF, ELSIF, and ELSE correctly with proper endings.

2. Step 2: Identify errors in other options

   Uses invalid keyword ELSEIF instead of ELSIF uses invalid ELSEIF. Uses ELSEIF after ELSE which is invalid uses ELSEIF after ELSE. Nested IF without closing first IF properly nests IF without closing properly.

3. Final Answer:

   Correct use of IF, ELSIF, ELSE with proper syntax -> Option A

4. Quick Check:

   Use IF, ELSIF, ELSE; no ELSEIF [OK]

Hint: Use ELSIF, not ELSEIF; close IF with END IF; [OK]

Common Mistakes:

• Using ELSEIF instead of ELSIF
• Placing ELSEIF after ELSE
• Improper nesting without END IF;