SELECT with CASE Statement
Classify each employee as High, Medium, or Low salary based on salary level.
- CASE expressions
Exercise brief
Understand the request
Compensation analyst The team wants a quick pay-band label directly in the result set.
Add a salary_category column that classifies each employee by salary using these exact thresholds: salary > 90000 → "High", salary > 65000 (and ≤ 90000) → "Medium", everyone else → "Low". Show first_name, last_name, salary, salary_category.
Return
- Return first_name, last_name, salary, and salary_category.
Constraints
- Use a CASE expression in the SELECT list.
Data you will use
Review the relevant tables before deciding how to join, filter, or aggregate them.
employees
first_nameVARCHAR(50)last_nameVARCHAR(50)salaryINTEGER
Hints, when you need them
Open one clue at a time so you still do the reasoning.
Hint 1
CASE evaluates its WHEN clauses top-down and stops at the first match. ELSE is the catch-all for rows that match nothing.
Hint 2
Use the exact thresholds in the prompt: > 90000 → 'High', > 65000 → 'Medium', else 'Low'. Don't forget the END keyword.
Hint 3
CASE WHEN salary > 90000 THEN 'High' WHEN salary > 65000 THEN 'Medium' ELSE 'Low' END AS salary_category
Verified SQL answer
Attempt the problem first, then compare structure and reasoning—not just syntax.
Reveal solution and explanation
SELECT first_name, last_name, salary, CASE WHEN salary > 90000 THEN 'High' WHEN salary > 65000 THEN 'Medium' ELSE 'Low' END AS salary_category FROM employees;Why this works
CASE is the SQL way to write if/else inside a SELECT list. Branches are checked in order, so put the strictest condition first. Because the second WHEN runs only when the first one failed, it implicitly handles the "between 65k and 90k" range without a second comparison.
Expected result
Use this output to verify values, aliases, ordering, and row count.
| first_name | last_name | salary | salary_category |
|---|---|---|---|
| John | Smith | 120000 | High |
| Alice | Johnson | 85000 | Medium |
| Bob | Wilson | 80000 | Medium |
| Carol | Davis | 60000 | Low |
| David | Brown | 70000 | Medium |
| Emma | Taylor | 95000 | High |
| Frank | Green | 65000 | Low |
| Grace | White | 90000 | Medium |
| Henry | Clark | 55000 | Low |
| Ivy | Martinez | 68000 | Medium |
Learn the concepts behind this answer
Strengthen your understanding with these targeted learning topics:
Continue practicing
Build the next SQL skill
WHERE Clause & Filtering
Practice SQL WHERE clauses with realistic boundary, NULL, text, date, exclusion, and production-filtering problems.
Basic SQL Functions
Practice production-oriented string cleanup, numeric transformations, NULL handling, tolerant conversion, and delimiter parsing.
ORDER BY & Sorting
Practice deterministic SQL ordering with tie-breakers, custom priorities, NULL placement, expressions, joined data, aggregates, and portable top-N patterns.
Open the interactive workspace and practice across SQL topics.