SQL CASE Statement MCQs

What does the following CASE statement do? SQL: SELECT OrderID, CASE WHEN Sales > 500 THEN 'High' ELSE 'Low' END AS SalesCategory FROM Orders;

• Classifies sales into multiple bands

• Returns NULL for Sales < 500

• Categorizes each order as 'High' or 'Low' based on sales

• Updates Sales values

Answer explanation

The CASE statement categorizes each order based on the Sales value. If Sales is greater than 500, it labels the order as 'High'; otherwise, it labels it as 'Low'. Thus, it effectively categorizes orders into 'High' or 'Low'.

Which SQL clause can include a CASE statement?

• SELECT

• WHERE

• ORDER BY

• All of the above

Answer explanation

The CASE statement can be used in various SQL clauses, including SELECT for conditional column values, WHERE for filtering, and ORDER BY for sorting based on conditions. Therefore, the correct answer is 'All of the above'.

What does the following query return? SQL: SELECT Region, CASE WHEN AVG(Sales) > 1000 THEN 'High Sales Region' ELSE 'Low Sales Region' END AS RegionCategory FROM Orders GROUP BY Region;

• Error

• Categorizes regions based on total orders

• Labels regions by average sales

• Filters regions

Answer explanation

The query results in an error because the AVG(Sales) function is used without a proper aggregation context in the GROUP BY clause. It does not categorize regions correctly.

What is the output of this query? SQL: SELECT OrderID, CASE Profit WHEN 0 THEN 'No Profit' ELSE 'Some Profit' END AS ProfitStatus FROM Orders;

• Returns error

• Uses simple CASE to check Profit = 0

• Categorizes into profit ranges

• Complex condition evaluation

Answer explanation

The query uses a simple CASE statement to evaluate the Profit column. It checks if Profit equals 0 and returns 'No Profit'; otherwise, it returns 'Some Profit'. Thus, the correct choice is that it uses simple CASE to check Profit = 0.

Identify the type of CASE used below: SQL: SELECT OrderID, CASE WHEN Profit > 1000 THEN 'Excellent' WHEN Profit > 500 THEN 'Good' ELSE 'Average' END AS ProfitBand FROM Orders;

• Simple CASE

• Searched CASE

• Nested CASE

• Invalid syntax

Answer explanation

The SQL uses a Searched CASE because it evaluates multiple conditions (Profit > 1000, Profit > 500) rather than a single expression. This allows for more complex logic compared to a Simple CASE.

How many WHEN clauses can a CASE statement have?

• Only one

• Up to 2

• Unlimited

• Exactly 3

Answer explanation

A CASE statement can have an unlimited number of WHEN clauses, allowing for multiple conditions to be evaluated. This flexibility makes it a powerful tool for conditional logic in SQL.

What does this query do? SQL: SELECT CustomerID, CASE WHEN EXISTS ( SELECT 1 FROM Returns WHERE Returns.CustomerID = Orders.CustomerID ) THEN 'Returned' ELSE 'Not Returned' END AS ReturnStatus FROM Orders;

• Error due to EXISTS

• Uses correlated subquery inside CASE

• Uses HAVING clause

• Filters only returned orders

Answer explanation

The query uses a correlated subquery within the CASE statement to check if a customer has any returns. It assigns 'Returned' or 'Not Returned' based on the existence of records in the Returns table for each CustomerID.

What is the default output if no CASE conditions match and no ELSE is given? SQL: SELECT CASE WHEN 1 = 0 THEN 'Zero' END AS Result;

• 'Zero'

• NULL

• Error

• 'Default'

Answer explanation

In SQL, if no CASE conditions match and no ELSE clause is provided, the default output is NULL. In this case, since 1 = 0 is false and no ELSE is specified, the result will be NULL.

What is true about nested CASE statements?

• Not allowed in SQL

• Only allowed in SELECT

• Allowed and useful for multi-level logic

• Causes syntax error

Answer explanation

Nested CASE statements are allowed in SQL and are useful for implementing multi-level logic, enabling complex conditional expressions within queries.

What does the following query do? SQL: SELECT OrderID, CASE WHEN Profit > 500 THEN 'High' WHEN Profit BETWEEN 100 AND 500 THEN 'Medium' WHEN Profit < 100 THEN 'Low' END AS ProfitLabel FROM Orders;

• Classifies orders into 3 profit bands

• Classifies orders into 2 bands only

• Returns error

• Requires ELSE clause

Answer explanation

The query classifies orders into three profit bands: 'High' for profits over 500, 'Medium' for profits between 100 and 500, and 'Low' for profits under 100. Thus, the correct choice is that it classifies orders into 3 profit bands.