sometimes, we need to list out the employee details along with their hierarchical level.
The below query lists out the employee details with his/her manager name/ID and level
WITH emp_report AS
(SELECT a.employee_id, A.job_id, a.first_name, a.manager_id, 0 AS report_level
FROM employees a WHERE a.manager_id is null
UNION ALL
SELECT a.employee_id, A.job_id, a.first_name ,a.manager_id, report_level + 1 AS report_level
FROM employees a JOIN emp_report B ON (a.manager_id = b.employee_id)
)
SELECT * FROM emp_report;