Salary Bands with CASE — SQL

Solving tips

Use a CASE expression to map a numeric range onto a label inside the SELECT list.
CASE evaluates WHEN branches top to bottom and stops at the first match, so order the thresholds accordingly.
Add an ELSE branch to catch every value not covered by the WHEN conditions.

You are given an employees table with raw salaries. Produce a readable seniority band for each person for an HR report.

Schema

CREATE TABLE employees (
  id     INTEGER PRIMARY KEY,
  name   TEXT,
  salary INTEGER
);

Sample data:

id	name	salary
1	Alice	45000
2	Bob	72000
3	Carol	120000
4	Dave	60000
5	Eve	95000
6	Frank	30000

Task

Return the name, the salary, and a computed column salary_band for each employee, ordered by id ascending. Assign the band as follows:

Junior when salary is less than 50000
Mid when salary is at least 50000 but less than 90000
Senior when salary is 90000 or more

Expected output

name	salary	salary_band
Alice	45000	Junior
Bob	72000	Mid
Carol	120000	Senior
Dave	60000	Mid
Eve	95000	Senior
Frank	30000	Junior

Approach

A searched CASE expression maps each salary range to a label. Because CASE returns the result of the first WHEN that is true, ordering the branches from lowest threshold upward lets each later branch assume the earlier ones already failed, which keeps the conditions simple.

Query

SELECT
  name,
  salary,
  CASE
    WHEN salary < 50000 THEN 'Junior'
    WHEN salary < 90000 THEN 'Mid'
    ELSE 'Senior'
  END AS salary_band
FROM employees
ORDER BY id ASC;

Walkthrough

For each row the branches are tested top to bottom. Alice (45000) and Frank (30000) hit the first branch and become Junior. Bob (72000) and Dave (60000) skip the first branch and match salary < 90000, becoming Mid. Carol (120000) and Eve (95000) fail both WHEN tests and fall through to the ELSE, becoming Senior. Ordering by id preserves the original row order in the output.

Complexity & notes

The CASE is evaluated per row during the scan and adds negligible cost. The key pitfall is branch order: because the second branch is written as salary < 90000 rather than salary >= 50000 AND salary < 90000, it relies on the first branch having already removed anything under 50000. The ELSE guarantees no row returns NULL for salary_band; without it, a value matching no WHEN (for example a NULL salary) would yield NULL.