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.