CASE in PL/pgSQL in PostgreSQL - Interactive Code Practice

Practice - 5 Tasks

1fill in blank

easy

Complete the code to start a CASE expression in PL/pgSQL.

PostgreSQL

DECLARE
  grade CHAR := 'B';
  result TEXT;
BEGIN
  result := CASE [1]
    WHEN 'A' THEN 'Excellent'
    WHEN 'B' THEN 'Good'
    ELSE 'Average'
  END;
END;

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

ACHAR

Bresult

Cgrade

DTEXT

Attempts:

3 left

💡 Hint

Common Mistakes

Using the variable that stores the result instead of the grade.

Using the data type name instead of a variable.

The CASE expression evaluates the variable grade to decide the result.

2fill in blank

medium

Complete the code to use CASE without an expression (searched CASE) in PL/pgSQL.

PostgreSQL

DECLARE
  score INT := 75;
  result TEXT;
BEGIN
  result := CASE
    WHEN score >= [1] THEN 'Pass'
    ELSE 'Fail'
  END;
END;

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

A50

B90

C100

D60

Attempts:

3 left

💡 Hint

Common Mistakes

Using a passing score that is too high like 90 or 100.

Confusing the comparison operator.

The searched CASE checks if score is greater or equal to 50 to pass.

3fill in blank

hard

Fix the error in the CASE expression by completing the missing keyword.

PostgreSQL

DECLARE
  day INT := 3;
  day_name TEXT;
BEGIN
  day_name := CASE day
    [1] 1 THEN 'Monday'
    WHEN 2 THEN 'Tuesday'
    ELSE 'Other'
  END;
END;

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

ATHEN

BWHEN

CELSE

DIF

Attempts:

3 left

💡 Hint

Common Mistakes

Using IF instead of WHEN inside CASE.

Omitting the WHEN keyword causes syntax errors.

Each condition in a CASE expression must start with the WHEN keyword.

4fill in blank

hard

Fill both blanks to complete the searched CASE expression that categorizes age.

PostgreSQL

DECLARE
  age INT := 25;
  category TEXT;
BEGIN
  category := CASE
    WHEN age [1] 18 THEN 'Minor'
    WHEN age [2] 18 THEN 'Adult'
    ELSE 'Senior'
  END;
END;

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

A<

B>=

C<=

D>

Attempts:

3 left

💡 Hint

Common Mistakes

Using wrong comparison operators that invert the logic.

Mixing up greater than and less than signs.

The first condition checks if age is less than 18 for 'Minor'. The second checks if age is greater or equal to 18 for 'Adult'.

5fill in blank

hard

Fill all three blanks to complete the CASE expression that assigns a letter grade based on score.

PostgreSQL

DECLARE
  score INT := 85;
  grade CHAR;
BEGIN
  grade := CASE
    WHEN score [1] 90 THEN 'A'
    WHEN score [2] 80 THEN 'B'
    ELSE [3]
  END;
END;

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

A>=

B<

C'C'

D'B'

Attempts:

3 left

💡 Hint

Common Mistakes

Using wrong comparison operators that cause wrong grade assignment.

Putting the wrong letter grade in the ELSE clause.

The CASE assigns 'A' if score is 90 or more, 'B' if score is 80 or more, else 'C'.

Practice

1. What is the main purpose of using CASE in PL/pgSQL?

easy

A. To choose different actions based on conditions

B. To create loops that repeat actions

C. To define new tables in the database

D. To permanently store data in variables

Solution

1. Step 1: Understand the role of CASE

   CASE is used to select one action from many based on conditions, like a traffic light deciding when to stop or go.

2. Step 2: Compare with other options

   Loops repeat actions, table creation defines structure, and variables store data, none of which is the main role of CASE.

3. Final Answer:

   To choose different actions based on conditions -> Option A

4. Quick Check:

   CASE chooses actions based on conditions [OK]

Hint: CASE picks actions by conditions, not loops or storage [OK]

Common Mistakes:

• Confusing CASE with loops
• Thinking CASE creates tables
• Assuming CASE stores data permanently

2. Which of the following is the correct way to end a CASE block in PL/pgSQL?

easy

A. END;

B. STOP;

C. FINISH CASE;

D. END CASE;

Solution

1. Step 1: Recall PL/pgSQL syntax for CASE

   In PL/pgSQL, a CASE block must be closed with END CASE; to mark its end clearly.

2. Step 2: Check other options

   END; ends blocks like functions, but CASE specifically needs END CASE;. FINISH CASE; and STOP; are invalid keywords.

3. Final Answer:

   END CASE; -> Option D

4. Quick Check:

   CASE ends with END CASE; [OK]

Hint: Always close CASE with END CASE; in PL/pgSQL [OK]

Common Mistakes:

• Using END; alone to close CASE
• Writing FINISH CASE; which is invalid
• Using STOP; which is not a PL/pgSQL keyword

3. Consider this PL/pgSQL snippet:

DECLARE
  grade CHAR := 'B';
  result TEXT;
BEGIN
  CASE grade
    WHEN 'A' THEN result := 'Excellent';
    WHEN 'B' THEN result := 'Good';
    ELSE result := 'Average';
  END CASE;
  RETURN result;
END;

What will be the returned value?

medium

A. 'Good'

B. 'Average'

C. 'Excellent'

D. NULL

Solution

1. Step 1: Identify the value of grade

   The variable grade is set to 'B'.

2. Step 2: Match grade in CASE

   CASE checks 'B', matches the second WHEN clause, so result becomes 'Good'.

3. Final Answer:

   'Good' -> Option A

4. Quick Check:

   grade 'B' returns 'Good' [OK]

Hint: Match CASE value to WHEN clause for output [OK]

Common Mistakes:

• Choosing ELSE when a WHEN matches
• Confusing variable assignment inside CASE
• Assuming NULL if no ELSE present

4. Identify the error in this PL/pgSQL CASE block:

DECLARE
  score INT := 85;
  grade TEXT;
BEGIN
  CASE
    WHEN score >= 90 THEN grade := 'A';
    WHEN score >= 80 THEN grade := 'B';
    ELSE grade := 'C';
  END;
  RETURN grade;
END;

medium

A. Incorrect variable declaration

B. Invalid comparison operators

C. Missing END CASE; to close CASE block

D. No ELSE clause present

Solution

1. Step 1: Check CASE block ending

   The CASE block is closed with END; but PL/pgSQL requires END CASE; to close CASE.

2. Step 2: Verify other parts

   Variable declarations and comparisons are correct, and ELSE clause is present.

3. Final Answer:

   Missing END CASE; to close CASE block -> Option C

4. Quick Check:

   CASE must end with END CASE; [OK]

Hint: Close CASE with END CASE;, not just END; [OK]

Common Mistakes:

• Using END; instead of END CASE;
• Thinking ELSE is optional here
• Misreading comparison operators

5. You want to write a PL/pgSQL function that returns 'Pass' if a student's score is 50 or more, 'Fail' if below 50, and 'Invalid' if the score is NULL. Which CASE structure correctly implements this?

hard

A. CASE score
WHEN NULL THEN RETURN 'Invalid';
WHEN >= 50 THEN RETURN 'Pass';
ELSE RETURN 'Fail';
END CASE;

B. CASE
WHEN score IS NULL THEN RETURN 'Invalid';
WHEN score >= 50 THEN RETURN 'Pass';
ELSE RETURN 'Fail';
END CASE;

C. CASE
WHEN score >= 50 THEN RETURN 'Pass';
WHEN score IS NULL THEN RETURN 'Fail';
ELSE RETURN 'Invalid';
END CASE;

D. CASE score
WHEN score >= 50 THEN RETURN 'Pass';
WHEN score < 50 THEN RETURN 'Fail';
ELSE RETURN 'Invalid';
END CASE;

Solution

1. Step 1: Handle NULL explicitly

   Since NULL cannot be matched by simple WHEN, use WHEN score IS NULL to check NULL values.

2. Step 2: Order conditions correctly

   Check NULL first, then score >= 50 for 'Pass', else 'Fail'. This matches

   CASE
     WHEN score IS NULL THEN RETURN 'Invalid';
     WHEN score >= 50 THEN RETURN 'Pass';
     ELSE RETURN 'Fail';
   END CASE;

   .

3. Final Answer:

   CASE with WHEN score IS NULL, then score >= 50, else Fail -> Option B

4. Quick Check:

   Use IS NULL to check NULL in CASE [OK]

Hint: Use WHEN score IS NULL to test NULL in CASE [OK]

Common Mistakes:

• Trying to match NULL with WHEN NULL
• Using CASE score with conditions inside WHEN
• Not checking NULL before other conditions