Let's move on to more interesting exercise!
Your task is to show the following columns:
id – ID of each department,name – name of the department,part_of – ID of the department one level up,path – defined as all names from the root department until the given department, separated with slashes ('/').Remember the root department has a NULL part_of id.
The query we created for employees might help you here:
WITH RECURSIVE hierarchy AS (
SELECT
id,
first_name,
last_name,
superior_id,
CAST(last_name AS text) AS path
FROM employee
WHERE superior_id IS NULL
UNION ALL
SELECT
employee.id,
employee.first_name,
employee.last_name,
employee.superior_id,
hierarchy.path || '->' || employee.last_name
FROM employee, hierarchy
WHERE employee.superior_id = hierarchy.id
)
SELECT
*
FROM hierarchy;
