-- Query the Oracle HR Employees table to show the company hierarchy
-- Works with Oracle 10g 

SELECT employee_id, manager_id,
LPAD(' ', (LEVEL - 1) * 3) || last_name || ' ' || first_name AS Employee, -- Padding to better show the hierarchy 
LEVEL, -- Pseudo Oracle column indicate the nested level
CONNECT_BY_ROOT last_name  His_Boss, -- Indicate who is the GranFather
DECODE(CONNECT_BY_ISLEAF, 1, 'No', 'Yes') has_kids, -- Indicate if there are children for the node
SYS_CONNECT_BY_PATH (last_name, ' ->') hierarchy_path -- Indicate the node path

FROM employees

CONNECT BY employee_id = PRIOR manager_id -- Get parents from the child
START WITH employee_id = 113 -- Indicate from wich employee to start
ORDER BY level DESC -- Reverse the order to start with the parent
 -- Mantain the natural order