Label Employees as Executive or Staff

The problem

Helix Systems' HR team is building an org chart and needs to distinguish leadership from the general workforce.

Write a query to return each employee's name and a level label:

'executive' for employees who have no manager on record (i.e., manager_id is NULL).
'staff' for everyone else.

Assumptions:

The employees table contains every active and former employee at Helix Systems.
The manager_id column links each employee to their direct manager; top-of-hierarchy executives have manager_id set to NULL.

Output:

One row per employee, with columns name and level.

Schema · hr 4 tables

departments

id integer

name text

location text

budget numeric

salaries

id integer

employee_id integer

amount numeric

effective_date date

end_date? date

employees

id integer

name text

email text

department_id integer

manager_id? integer

hire_date date

title text

is_active boolean

job_history

id integer

employee_id integer

title text

department_id integer

start_date date

end_date? date

Check answerShift Ctrl ↵

Run previews · Check grades

Write a query, then run it to see results here.

Worked solution Try it yourself first

Solution query

SELECT
  name,
  CASE
    WHEN manager_id IS NULL THEN 'executive'
    ELSE 'staff'
  END AS level
FROM
  employees

The shape

CASE WHEN manager_id IS NULL THEN 'executive' ELSE 'staff' END splits the workforce on a single test: does this employee have a manager on record? Top-of-hierarchy employees have manager_id IS NULL, and the CASE returns 'executive' for them. Everyone else is 'staff'.

Clause by clause

SELECT name returns the employee's name column unchanged. The org chart needs the name alongside the level label.
CASE WHEN manager_id IS NULL THEN 'executive' ELSE 'staff' END AS level is the derived column. The WHEN uses IS NULL — the only operator that asks "is this value missing." When that's true, the THEN branch returns 'executive'. Otherwise ELSE 'staff' fires.
FROM employees is the source set: every employee Helix Systems has on file.

The trap

Writing the branch as WHEN manager_id = NULL looks reasonable but is silently wrong. NULL is the absence of a value, and any comparison against it with = or <> returns NULL (PostgreSQL's three-valued logic), which a WHEN branch treats as not-matching. Every executive would fall through to ELSE, get labelled 'staff', and the bug is invisible — no error, no warning, just every executive misclassified. IS NULL is the only operator that returns a real boolean against a missing value.

You practiced testing for NULL inside a CASE branch using IS NULL. The same rule from WHERE carries over: manager_id = NULL would always evaluate to unknown and the branch would never match — IS NULL is the only correct test.

- `'executive'` for employees who have no manager on record (i.e., `manager_id` is `NULL`). - `'staff'` for everyone else

The shape

Clause by clause

The trap

Reading explains SQL. Writing it, over and over with instant feedback, is what makes you fluent.