CASE expression in PostgreSQL - Interactive Code Practice

Practice - 5 Tasks

1fill in blank

easy

Complete the code to return 'Adult' if age is 18 or more, otherwise 'Minor'.

PostgreSQL

SELECT name, CASE WHEN age >= [1] THEN 'Adult' ELSE 'Minor' END AS status FROM people;

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

A18

B21

C16

D20

Attempts:

3 left

💡 Hint

Common Mistakes

Using a number less than 18 will misclassify adults as minors.

Using '>' instead of '>=' excludes exactly 18 years old.

The CASE expression checks if age is 18 or more to classify as 'Adult'.

2fill in blank

medium

Complete the code to categorize scores: 90 or above as 'Excellent', else 'Good'.

PostgreSQL

SELECT student, CASE WHEN score >= [1] THEN 'Excellent' ELSE 'Good' END AS grade FROM results;

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

A80

B85

C90

D95

Attempts:

3 left

💡 Hint

Common Mistakes

Choosing a lower number will mark too many as 'Excellent'.

Choosing a higher number excludes some who scored exactly 90.

The CASE expression uses 90 as the threshold for 'Excellent'.

3fill in blank

hard

Fix the error in the CASE expression to correctly assign 'Pass' if marks are 50 or more, else 'Fail'.

PostgreSQL

SELECT student, CASE WHEN marks [1] 50 THEN 'Pass' ELSE 'Fail' END AS result FROM exams;

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

A>

B>=

C<=

D<

Attempts:

3 left

💡 Hint

Common Mistakes

Using '>' excludes students with exactly 50 marks.

Using '<' or '<=' reverses the logic.

The '>=' operator includes 50 marks as passing.

4fill in blank

hard

Fill both blanks to classify temperature: 'Hot' if temp > 30, 'Warm' if temp > 20, else 'Cold'.

PostgreSQL

SELECT city, CASE WHEN temperature [1] 30 THEN 'Hot' WHEN temperature [2] 20 THEN 'Warm' ELSE 'Cold' END AS weather FROM forecast;

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

A>

B>=

C<=

D<

Attempts:

3 left

💡 Hint

Common Mistakes

Using '>=' includes boundary values incorrectly here.

Using '<=' or '<' reverses the logic.

Use '>' to check if temperature is greater than 30 for 'Hot' and greater than 20 for 'Warm'.

5fill in blank

hard

Fill all three blanks to create a CASE expression that returns 'A' for score >= 90, 'B' for score >= 80, else 'C'.

PostgreSQL

SELECT student, CASE WHEN score [1] 90 THEN 'A' WHEN score [2] 80 THEN 'B' ELSE [3] END AS grade FROM tests;

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

A>=

B>

C'C'

D'B'

Attempts:

3 left

💡 Hint

Common Mistakes

Using '>' excludes boundary scores.

Not quoting the ELSE result causes syntax errors.

The CASE uses '>=' to include boundary scores and returns 'C' as the else result.

Practice

1. What does the CASE expression do in PostgreSQL?

easy

A. Returns different results based on conditions

B. Creates a new table

C. Deletes rows from a table

D. Updates data in a table

Solution

1. Step 1: Understand the purpose of CASE

   The CASE expression evaluates conditions and returns a result based on which condition is true.

2. Step 2: Compare with other SQL commands

   Unlike CREATE, DELETE, or UPDATE, CASE does not change data but returns values conditionally.

3. Final Answer:

   Returns different results based on conditions -> Option A

4. Quick Check:

   CASE = conditional results [OK]

Hint: CASE returns values based on conditions, like if-then-else [OK]

Common Mistakes:

• Thinking CASE modifies data
• Confusing CASE with table operations
• Ignoring ELSE for default results

2. Which of the following is the correct syntax to start a CASE expression in PostgreSQL?

easy

A. CASE IF condition THEN result ELSE default END

B. CASE WHEN condition THEN result ELSE default END

C. CASE condition THEN result ELSE default

D. CASE WHEN condition THEN result ELSE default

Solution

1. Step 1: Recall correct CASE syntax

   The correct syntax starts with CASE, followed by WHEN conditions, THEN results, optional ELSE, and ends with END.

2. Step 2: Identify syntax errors in options

   The syntax with 'CASE IF ... END' uses 'IF' instead of 'WHEN'. Syntaxes omitting 'END' (such as 'CASE WHEN ... default' or 'CASE condition ...') are invalid. Only the full syntax is correct.

3. Final Answer:

   CASE WHEN condition THEN result ELSE default END -> Option B

4. Quick Check:

   CASE syntax requires WHEN and END [OK]

Hint: CASE always ends with END and uses WHEN for conditions [OK]

Common Mistakes:

• Using IF instead of WHEN
• Forgetting END at the end
• Omitting ELSE clause

3. Given the table employees with column salary, what will this query return?

SELECT salary, CASE WHEN salary > 5000 THEN 'High' ELSE 'Low' END AS salary_level FROM employees;

medium

A. A list of salaries with labels 'High' or 'Low' based on salary value

B. Only salaries greater than 5000

C. Only salaries less than or equal to 5000

D. An error because ELSE is missing

Solution

1. Step 1: Understand the CASE condition

   The CASE checks if salary is greater than 5000; if true, returns 'High', else 'Low'.

2. Step 2: Analyze the SELECT output

   The query returns all salaries with a new column showing 'High' or 'Low' labels accordingly.

3. Final Answer:

   A list of salaries with labels 'High' or 'Low' based on salary value -> Option A

4. Quick Check:

   CASE labels salaries as High or Low [OK]

Hint: CASE adds labels based on conditions without filtering rows [OK]

Common Mistakes:

• Thinking CASE filters rows
• Assuming ELSE is mandatory (it is here but present)
• Confusing output with WHERE clause filtering

4. Identify the error in this CASE expression:

SELECT name, CASE WHEN age > 18 THEN 'Adult' WHEN age < 18 THEN 'Minor' END AS age_group FROM users;

medium

A. Syntax error in SELECT statement

B. Incorrect use of THEN keyword

C. Missing END keyword to close CASE

D. Missing ELSE clause for age = 18

Solution

1. Step 1: Check CASE conditions coverage

   The CASE covers age > 18 and age < 18 but misses age = 18 case.

2. Step 2: Understand ELSE role

   Without ELSE, age = 18 returns NULL. Adding ELSE handles this missing case.

3. Final Answer:

   Missing ELSE clause for age = 18 -> Option D

4. Quick Check:

   CASE needs ELSE for unmatched conditions [OK]

Hint: Add ELSE to handle all unmatched cases in CASE [OK]

Common Mistakes:

• Assuming CASE covers all values without ELSE
• Confusing END keyword placement
• Misusing THEN keyword

5. You want to categorize products by price into 'Cheap' (price < 20), 'Moderate' (20 ≤ price < 100), and 'Expensive' (price ≥ 100). Which CASE expression correctly implements this?

hard

A. CASE WHEN price < 20 THEN 'Cheap' WHEN price >= 20 AND price <= 100 THEN 'Moderate' ELSE 'Expensive' END

B. CASE WHEN price < 20 THEN 'Cheap' WHEN price > 20 AND price < 100 THEN 'Moderate' ELSE 'Expensive' END

C. CASE WHEN price < 20 THEN 'Cheap' WHEN price >= 20 AND price < 100 THEN 'Moderate' ELSE 'Expensive' END

D. CASE WHEN price <= 20 THEN 'Cheap' WHEN price >= 20 AND price < 100 THEN 'Moderate' ELSE 'Expensive' END

Solution

1. Step 1: Analyze price ranges for categories

   'Cheap' is price less than 20, 'Moderate' is price from 20 up to but not including 100, 'Expensive' is 100 or more.

2. Step 2: Check each option's conditions

   CASE WHEN price < 20 THEN 'Cheap' WHEN price >= 20 AND price < 100 THEN 'Moderate' ELSE 'Expensive' END correctly uses < 20, then >= 20 and < 100, else 'Expensive'. CASE WHEN price < 20 THEN 'Cheap' WHEN price > 20 AND price < 100 THEN 'Moderate' ELSE 'Expensive' END misses price = 20 in 'Moderate'. CASE WHEN price <= 20 THEN 'Cheap' WHEN price >= 20 AND price < 100 THEN 'Moderate' ELSE 'Expensive' END overlaps at 20 in both 'Cheap' and 'Moderate'. CASE WHEN price < 20 THEN 'Cheap' WHEN price >= 20 AND price <= 100 THEN 'Moderate' ELSE 'Expensive' END includes 100 in 'Moderate' but should be 'Expensive'.

3. Final Answer:

   CASE WHEN price < 20 THEN 'Cheap' WHEN price >= 20 AND price < 100 THEN 'Moderate' ELSE 'Expensive' END -> Option C

4. Quick Check:

   Use inclusive and exclusive ranges carefully in CASE [OK]

Hint: Use >= and < carefully to avoid overlapping ranges [OK]

Common Mistakes:

• Overlapping or missing ranges
• Using > instead of >= causing gaps
• Misplacing ELSE category boundaries