CASE WHEN expression in MySQL - Interactive Code Practice

Practice - 5 Tasks

1fill in blank

easy

Complete the code to select the grade based on score using CASE WHEN.

MySQL

SELECT score, CASE WHEN score >= 90 THEN 'A' ELSE 'F' END AS grade FROM exams WHERE score [1] 80;

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

A!=

B<

C=

D>=

Attempts:

3 left

💡 Hint

Common Mistakes

Using '=' instead of '>=' causes only scores exactly 80 to be selected.

Using '<' selects scores less than 80, which is opposite of the goal.

The condition >= checks if the score is at least 80 to filter the rows.

2fill in blank

medium

Complete the code to assign 'Pass' or 'Fail' based on marks using CASE WHEN.

MySQL

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

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

A>=

B<

C=

D!=

Attempts:

3 left

💡 Hint

Common Mistakes

Using '<' assigns 'Pass' to marks below 50, which is incorrect.

Using '=' only passes marks exactly 50, missing others.

The operator >= checks if marks are 50 or more to assign 'Pass'.

3fill in blank

hard

Fix the error in the CASE WHEN expression to categorize age groups.

MySQL

SELECT name, age, CASE WHEN age [1] 18 THEN 'Minor' ELSE 'Adult' END AS age_group FROM people;

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

A<

B=>

C<=

D==

Attempts:

3 left

💡 Hint

Common Mistakes

Using '=>' causes syntax error.

Using '==' is not valid in SQL for comparisons.

The correct operator to check if age is 18 or less is <=. The option => is invalid syntax in SQL.

4fill in blank

hard

Fill both blanks to classify salary levels using CASE WHEN.

MySQL

SELECT employee_id, salary, CASE WHEN salary [1] 50000 THEN 'Low' WHEN salary [2] 100000 THEN 'High' ELSE 'Medium' END AS salary_level FROM employees;

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

A<

B>=

C<=

D>

Attempts:

3 left

💡 Hint

Common Mistakes

Using '>=' in the first blank would misclassify salaries equal to 50000.

Using '<' in the second blank would misclassify salaries exactly 100000.

The first condition checks if salary is less than 50000 for 'Low'. The second checks if salary is 100000 or more for 'High'. Salaries in between are 'Medium'.

5fill in blank

hard

Fill all three blanks to create a CASE WHEN expression that labels product stock status.

MySQL

SELECT product_name, stock, CASE WHEN stock [1] 0 THEN 'Out of stock' WHEN stock [2] 50 THEN 'Low stock' ELSE 'In stock' END AS stock_status FROM inventory WHERE stock [3] 0;

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

A=

B<=

C>

D>=

Attempts:

3 left

💡 Hint

Common Mistakes

Using '<' instead of '<=' in second blank excludes stock exactly 50.

Using '!=' instead of '=' in first blank misclassifies zero stock.

Using '<' instead of '>=' in third blank excludes zero stock.

The first blank checks if stock equals 0 for 'Out of stock'. The second checks if stock is 50 or less for 'Low stock'. The WHERE clause filters products with stock greater than or equal to 0.

Practice

1. What does the CASE WHEN expression do in a MySQL query?

easy

A. It creates a new table in the database.

B. It sorts the results in ascending order.

C. It deletes rows that meet a condition.

D. It returns different values based on specified conditions.

Solution

1. Step 1: Understand the purpose of CASE WHEN

   The CASE WHEN expression checks conditions and returns values accordingly.

2. Step 2: Compare with other options

   Creating tables, deleting rows, or sorting are different SQL operations, not done by CASE WHEN.

3. Final Answer:

   It returns different values based on specified conditions. -> Option D

4. Quick Check:

   CASE WHEN = conditional value return [OK]

Hint: CASE WHEN returns values based on conditions, like IF-THEN-ELSE [OK]

Common Mistakes:

• Thinking CASE WHEN creates or deletes tables
• Confusing CASE WHEN with ORDER BY sorting
• Assuming CASE WHEN changes data permanently

2. Which of the following is the correct syntax for a simple CASE WHEN expression in MySQL?

easy

A. SELECT CASE WHEN score > 50 THEN 'Pass' ELSE 'Fail' END FROM students;

B. SELECT CASE score > 50 THEN 'Pass' ELSE 'Fail' END FROM students;

C. SELECT CASE WHEN score > 50 THEN 'Pass' ELSE 'Fail' FROM students;

D. SELECT CASE score > 50 THEN 'Pass' ELSE 'Fail' END students;

Solution

1. Step 1: Check the correct CASE WHEN syntax

   The correct syntax includes CASE WHEN condition THEN result ELSE result END.

2. Step 2: Identify syntax errors in other options

   The other options miss keywords or punctuation: some omit WHEN, others forget END, or misplace FROM.

3. Final Answer:

   SELECT CASE WHEN score > 50 THEN 'Pass' ELSE 'Fail' END FROM students; -> Option A

4. Quick Check:

   Correct CASE WHEN syntax = SELECT CASE WHEN score > 50 THEN 'Pass' ELSE 'Fail' END FROM students; [OK]

Hint: CASE WHEN needs WHEN, THEN, ELSE, and END keywords [OK]

Common Mistakes:

• Omitting WHEN keyword
• Forgetting END at the end
• Misplacing FROM clause

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

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

medium

A. An error because CASE WHEN cannot be used in SELECT.

B. Only employees with salary greater than 5000 will be shown.

C. A list of employee names with 'High' or 'Low' based on salary threshold 5000.

D. A list of salaries labeled as 'High' or 'Low' without employee names.

Solution

1. Step 1: Understand the CASE WHEN usage in SELECT

   The query labels each employee's salary as 'High' if >= 5000, else 'Low'.

2. Step 2: Check what columns are selected

   It selects employee names and the computed salary_level, so all employees appear.

3. Final Answer:

   A list of employee names with 'High' or 'Low' based on salary threshold 5000. -> Option C

4. Quick Check:

   CASE WHEN labels salaries, all employees listed [OK]

Hint: CASE WHEN in SELECT returns labels per row without filtering [OK]

Common Mistakes:

• Thinking CASE WHEN filters rows instead of labeling
• Assuming query returns error
• Ignoring selected columns

4. Identify the error in this query:

SELECT name, CASE salary > 10000 THEN 'Rich' ELSE 'Average' END AS status FROM customers;

medium

A. Missing WHEN keyword after CASE.

B. Missing END keyword at the end.

C. Incorrect alias usage for CASE expression.

D. Using ELSE without THEN is invalid.

Solution

1. Step 1: Review CASE WHEN syntax

   CASE must be followed by WHEN before condition; here WHEN is missing.

2. Step 2: Check other parts of the query

   END keyword is present, alias is correct, ELSE is properly used after THEN.

3. Final Answer:

   Missing WHEN keyword after CASE. -> Option A

4. Quick Check:

   CASE requires WHEN after CASE [OK]

Hint: Always write CASE WHEN, not CASE condition directly [OK]

Common Mistakes:

• Omitting WHEN keyword
• Confusing ELSE and THEN order
• Forgetting END keyword

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

hard

A. CASE WHEN price < 20 THEN 'Cheap' WHEN 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 the price ranges needed

   'Cheap' is price less than 20, 'Moderate' is price between 20 and 100 inclusive, 'Expensive' is above 100.

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 price < 20 for 'Cheap', price >= 20 and <= 100 for 'Moderate', else 'Expensive'. Others have incorrect boundaries.

3. Final Answer:

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

4. Quick Check:

   Correct range checks = CASE WHEN price < 20 THEN 'Cheap' WHEN price >= 20 AND price <= 100 THEN 'Moderate' ELSE 'Expensive' END [OK]

Hint: Use inclusive ranges carefully with AND and >=, <= operators [OK]

Common Mistakes:

• Overlapping or missing ranges
• Using incorrect comparison operators
• Not covering all possible values