A salary report must classify employees into Low, Medium, and High salary bands inside the SELECT output. Which conditional expression would you use, and why is it preferable to IF here?
Interview preparation resource from Gate Smashers.
Use a searched CASE expression. It is standard SQL, returns a value for each row inside SELECT, and evaluates conditions top-to-bottom (so check the highest threshold first). IF is database-specific and can refer to procedural control flow rather than a portable query expression. Define boundary values explicitly with the business team.
SELECT employee_name, salary, CASE WHEN salary >= 80000 THEN 'High' WHEN salary >= 50000 THEN 'Medium' ELSE 'Low' END AS salary_band FROM Employees;Recommended expression
Use a searched CASE expression to classify employees into Low, Medium, and High salary bands inside the SELECT output. CASE is a query expression that returns a value for each row and is part of the SQL standard.
- Example query: See the SQL query in the code block below.
Why CASE is preferable
CASE is standard SQL and portable across database systems that support SQL. Because it is an expression, it can be used directly in the SELECT list to return a value per row.
- Portability: Standard SQL behavior makes queries more portable between systems.
- SELECT usage: CASE returns a value per row inside the SELECT output, which is what this report requires.
Evaluation order and thresholds
A searched CASE evaluates WHEN conditions from top to bottom. For salary bands you must check the highest threshold first so rows with salaries matching higher bands are classified correctly.
- Order: Check highest threshold before lower ones (top-to-bottom evaluation).
IF vs CASE and boundary values
IF syntax and behavior are database-specific; in some systems IF is a procedural construct rather than a portable query expression. Because of this, CASE is preferable for a portable SELECT-based classification. Also, boundary values (exact cutoffs) should be agreed explicitly with the business team.
- Boundary values: Define exact cutoffs (e.g., whether 80000 belongs to High or Medium) with the business team.