SQL Analytical Functions - connect by (to represent hierarchical data)

Connect by : 

Used to represent hierarchical data. Applies conditions between 2 columns within the same table

EMPLOYEE_ID FIRST_NAME           MANAGER_ID
----------- -------------------- ----------
        100 Steven                          
        101 Neena                       100 
        102 Lex                         100 
        103 Alexander                   102 
        104 Bruce                       103 
        105 David                       103 
        106 Valli                       103 
        107 Diana                       103 

Below query returns the data in the form of hierarchy by connecting employee_id and manager_id columns in the same table.

Select  employee_id, first_name as emp_name,
       prior first_name as mngr_name, level
       from   employees 
       connect by prior employee_id = manager_id
       start with manager_id is null 
       order by employee_id;

connect by prior - specifies the relationship between the child and parent rows in a hierarchy
start with - Root row of the hierarchy

EMPLOYEE_ID EMP_NAME             MNGR_NAME                 LEVEL
----------- -------------------- -------------------- ----------
        100 Steven                                             1 
        101 Neena                Steven                        2 
        102 Lex                  Steven                        2 
        103 Alexander            Lex                           3 
        104 Bruce                Alexander                     4 
        105 David                Alexander                     4 
        106 Valli                Alexander                     4 
        107 Diana                Alexander                     4 
        108 Nancy                Neena                         3 

In this example, we start the hierarchy from another root

Select  employee_id, first_name as emp_name,
       prior first_name as mngr_name, level
       from   employees 
       connect by prior employee_id = manager_id
       start with first_name = 'Alexander' 
       order by employee_id;

EMPLOYEE_ID EMP_NAME             MNGR_NAME                 LEVEL
----------- -------------------- -------------------- ----------
        103 Alexander            (null)                        1 
        104 Bruce                Alexander                     2 
        105 David                Alexander                     2 
        106 Valli                Alexander                     2 
        107 Diana                Alexander                     2