Drill 11 of 12 in SQL Basics
0

SQL Basics · NULL · COALESCE

11. Filling in missing managers

Concept: NULL · COALESCEshow

NULL means 'no value'. COALESCE(a, b) returns the first argument that isn't NULL, so COALESCE(col, fallback) swaps NULLs for a default. All its arguments must share one type: to fall back to text from an integer column, cast first with col::text.

Example (different data):

SELECT name,
  COALESCE(phone, 'no phone') AS contact,
  COALESCE(discount_pct::text, 'none') AS discount
FROM suppliers;

Your task

The new org-chart tool rejects blank cells. Department heads have no manager_id, so they need the word 'head' instead.

Return name, department and reports_to for every employee, where reports_to is the manager_id as text, or 'head' when manager_id is NULL. Sort by department, then name.

Solution unlocks after 3 more tries

Starting Postgres in your browser…

query.sql
Output

Write a query and press Run Query (Ctrl/⌘ + Enter).