Why CASE expressions are needed in SQL - Challenge Your Understanding

Challenge - 5 Problems

🧠 Conceptual

intermediate

Why use CASE expressions in SQL?

Which of the following best explains why CASE expressions are needed in SQL queries?

A To speed up query execution by skipping joins.

B To perform conditional logic within queries, allowing different outputs based on row values.

C To create new tables automatically without specifying columns.

D To encrypt data stored in the database.

💡 Hint

Think about how you can show different results for different rows in one query.

CASE expressions let you add conditional logic inside SQL queries, so you can return different values depending on data in each row.

❓ query_result

intermediate

Output of a CASE expression in SELECT

Given a table Employees with a column Salary, what is the output of this query?

SELECT Name, CASE WHEN Salary >= 5000 THEN 'High' ELSE 'Low' END AS SalaryLevel FROM Employees;

Assuming the table has rows: (Name: 'Anna', Salary: 6000), (Name: 'Bob', Salary: 4000)

A Syntax error due to missing ELSE clause

B [{"Name": "Anna", "SalaryLevel": "Low"}, {"Name": "Bob", "SalaryLevel": "High"}]

C [{"Name": "Anna", "SalaryLevel": 6000}, {"Name": "Bob", "SalaryLevel": 4000}]

D [{"Name": "Anna", "SalaryLevel": "High"}, {"Name": "Bob", "SalaryLevel": "Low"}]

💡 Hint

Check the condition Salary >= 5000 for each row.

The CASE checks if Salary is 5000 or more; if yes, it returns 'High', else 'Low'. Anna has 6000 so 'High', Bob has 4000 so 'Low'.

📝 Syntax

advanced

Identify the syntax error in CASE expression

Which option contains a syntax error in the CASE expression?

SELECT Name, CASE Salary WHEN >= 5000 THEN 'High' ELSE 'Low' END AS SalaryLevel FROM Employees;

A CASE Salary WHEN 5000 THEN 'High' ELSE 'Low' END

B CASE WHEN Salary >= 5000 THEN 'High' ELSE 'Low' END

C CASE Salary WHEN >= 5000 THEN 'High' ELSE 'Low' END

D CASE WHEN Salary < 5000 THEN 'Low' ELSE 'High' END

💡 Hint

Remember the difference between simple CASE and searched CASE syntax.

Option C is invalid because simple CASE compares Salary to a value, but >= 5000 is a condition, not a value. It causes syntax error.

❓ optimization

advanced

Optimizing queries with CASE expressions

Which option shows the best use of CASE to avoid multiple queries when categorizing data?

A Use CASE in SELECT to assign categories in one query, reducing database calls.

B Run separate queries for each category and combine results in application code.

C Use multiple JOINs to filter categories separately.

D Use subqueries for each category and UNION them.

💡 Hint

Think about how to get all categories in one query efficiently.

Using CASE in SELECT lets you assign categories in one query, which is faster and simpler than multiple queries or joins.

🔧 Debug

expert

Debugging unexpected CASE output

Given this query:

SELECT Name, CASE WHEN Score > 90 THEN 'A' WHEN Score > 80 THEN 'B' ELSE 'C' END AS Grade FROM Students;

Why might some students with Score 100 get Grade 'C' instead of 'A'?

A The Score column is stored as text, so comparisons behave unexpectedly.

B The CASE expression is missing an ELSE clause.

C The query has a syntax error causing wrong output.

D The Score values are NULL, so CASE returns 'C' by default.

💡 Hint

Check the data type of Score and how comparisons work.

If Score is text, '100' > '90' is false because string comparison is lexicographic, causing unexpected CASE results.

Practice

1. Why do we need CASE expressions in SQL queries?

easy

A. To change the database schema

B. To create new tables in the database

C. To delete rows from a table

D. To return different values based on conditions within a query

Solution

1. Step 1: Understand the purpose of CASE expressions

   CASE expressions allow SQL queries to return different results depending on conditions you specify.

2. Step 2: Compare with other SQL operations

   Creating tables, deleting rows, or changing schema are done by other SQL commands, not CASE.

3. Final Answer:

   To return different values based on conditions within a query -> Option D

4. Quick Check:

   CASE = conditional output [OK]

Hint: CASE is for conditional results inside queries [OK]

Common Mistakes:

• Thinking CASE creates or deletes tables
• Confusing CASE with schema changes
• Assuming CASE modifies data directly

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

easy

A. IF condition THEN result ELSE other END

B. CASE WHEN condition THEN result ELSE other END

C. SWITCH condition THEN result ELSE other END

D. SELECT CASE condition THEN result ELSE other

Solution

1. Step 1: Recall the standard CASE syntax

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

2. Step 2: Identify incorrect options

   IF and SWITCH are not valid SQL CASE keywords; SELECT CASE is incomplete syntax.

3. Final Answer:

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

4. Quick Check:

   CASE syntax = WHEN...THEN...END [OK]

Hint: CASE always uses WHEN...THEN...END structure [OK]

Common Mistakes:

• Using IF instead of CASE in SQL
• Omitting END keyword
• Starting CASE with SELECT

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 SalaryLevel FROM Employees;

medium

A. Only salaries below 5000 with label 'Low'

B. Only salaries above 5000 with label 'High'

C. A list of salaries with labels 'High' for salaries above 5000 and 'Low' otherwise

D. An error because CASE cannot be used in SELECT

Solution

1. Step 1: Understand the CASE logic in SELECT

   The CASE checks each Salary; if greater than 5000, label 'High', else 'Low'.

2. Step 2: Check what rows are returned

   The query returns all rows with Salary and corresponding label, no rows are filtered out.

3. Final Answer:

   A list of salaries with labels 'High' for salaries above 5000 and 'Low' otherwise -> Option C

4. Quick Check:

   CASE labels salaries conditionally [OK]

Hint: CASE in SELECT labels rows, does not filter [OK]

Common Mistakes:

• Thinking CASE filters rows like WHERE
• Assuming only 'High' salaries show
• Believing CASE causes syntax error in SELECT

4. Identify the error in this SQL snippet using CASE:

SELECT Name, CASE Salary > 5000 THEN 'High' ELSE 'Low' END AS SalaryLevel FROM Employees;

medium

A. Missing WHEN keyword after CASE

B. Missing END keyword at the end

C. Incorrect alias usage for SalaryLevel

D. CASE cannot be used in SELECT clause

Solution

1. Step 1: Review CASE syntax

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

2. Step 2: Check other parts

   END keyword is present; alias usage is correct; CASE is valid in SELECT.

3. Final Answer:

   Missing WHEN keyword after CASE -> Option A

4. Quick Check:

   CASE requires WHEN after CASE [OK]

Hint: CASE always needs WHEN after it [OK]

Common Mistakes:

• Omitting WHEN keyword
• Confusing END with alias
• Thinking CASE can't be in SELECT

5. You want to assign a grade based on Score in a table:
- Score ≥ 90: 'A'
- Score ≥ 80 and < 90: 'B'
- Score ≥ 70 and < 80: 'C'
- Otherwise: 'F'
Which CASE expression correctly implements this grading logic?

hard

A. CASE WHEN Score ≥ 90 THEN 'A' WHEN Score ≥ 80 THEN 'B' WHEN Score ≥ 70 THEN 'C' ELSE 'F' END

B. CASE WHEN Score < 70 THEN 'F' WHEN Score < 90 THEN 'C' WHEN Score < 80 THEN 'B' ELSE 'A' END

C. CASE WHEN Score ≥ 70 THEN 'C' WHEN Score ≥ 80 THEN 'B' WHEN Score ≥ 90 THEN 'A' ELSE 'F' END

D. CASE Score WHEN ≥ 90 THEN 'A' WHEN ≥ 80 THEN 'B' WHEN ≥ 70 THEN 'C' ELSE 'F' END

Solution

1. Step 1: Understand the grading order

   Grades must be checked from highest to lowest score to assign correct grade.

2. Step 2: Analyze each option

   CASE WHEN Score ≥ 90 THEN 'A' WHEN Score ≥ 80 THEN 'B' WHEN Score ≥ 70 THEN 'C' ELSE 'F' END checks from highest to lowest correctly; CASE WHEN Score < 70 THEN 'F' WHEN Score < 90 THEN 'C' WHEN Score < 80 THEN 'B' ELSE 'A' END incorrectly assigns scores 70-89 to 'C'; CASE WHEN Score ≥ 70 THEN 'C' WHEN Score ≥ 80 THEN 'B' WHEN Score ≥ 90 THEN 'A' ELSE 'F' END checks in wrong order; CASE Score WHEN ≥ 90 THEN 'A' WHEN ≥ 80 THEN 'B' WHEN ≥ 70 THEN 'C' ELSE 'F' END uses invalid CASE syntax.

3. Final Answer:

   CASE WHEN Score ≥ 90 THEN 'A' WHEN Score ≥ 80 THEN 'B' WHEN Score ≥ 70 THEN 'C' ELSE 'F' END -> Option A

4. Quick Check:

   Order conditions high to low in CASE [OK]

Hint: Check conditions from highest to lowest in CASE [OK]

Common Mistakes:

• Checking conditions in wrong order
• Using CASE with invalid syntax
• Assuming CASE compares values directly without WHEN