-- 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 PRIOR employee_id = manager_id -- Indicate the field in relationship
START WITH manager_id IS NULL -- Indicate from wich level to start
ORDER SIBLINGS BY manager_id -- Mantain the natural order