Given an Employee table with Manager_ID as column, print First name, Manager ID and Level of employees in Organization Structure?

Answer: In Oracle, we can CONNECT BY clause for this.

The starting point will be the employee who does not have a manager. Below that we can connect the employee IDs with their Manager IDs and keep printing the records.

Oracle provides a pseudocolumn LEVEL that gives the level of each record in hierarchy.

Query will be as follows:

SELECT f_name, emp_id, manager_id, LEVEL
FROM Employee
START WITH emp_id = 10
CONNECT BY PRIOR emp_id = manager_id;
John                10                                          1
George           14                   10                    2
Jill                   16                    14                   3
Bill                   15                    14                   3
Jay                  18                   14                    3


Leave a Reply

Your email address will not be published. Required fields are marked *