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;
F_NAME   EMP_ID        MANAGER_ID LEVEL
John                10                                          1
George           14                   10                    2
Jill                   16                    14                   3
Bill                   15                    14                   3
Jay                  18                   14                    3

 

Read the full book at www.amazon.com
Posted in SQL, SQL Interview Questions

Leave a Reply

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

*