Hierarchical Query n Top n Analysis

--find manager against employee

select e.employee_id,e.first_name,m.first_name,e.manager_id

from employees e , employees m

where e.manager_id = m.employee_id ;

--Advance Queries...

http://psoug.org/reference/connectby.html 

-- Level -- To display/print list from 1 to  15 

    SELECT LEVEL

      FROM DUAL

CONNECT BY LEVEL <= 15;

--1. SELECT… CONNECT BY

Select data with a hierarchical (parent/child) relationship. (also used by EXPLAIN PLAN)

Syntax:

   SELECT…clause 

   [START WITH initial_condition]

   CONNECT BY [nocycle] PRIOR recurse_condition

   [ORDER SIBLINGS BY order_by_clause]

 Key:

    START WITH        : The row(s) to be used as the root of the hierarchy

    CONNECT BY        : Condition that identifies the relationship between

                        parent and child rows of the hierarchy

    NOCYCLE           : Do not circle around loops (where the current row has

                        a child which is also its ancestor.)

    ORDER SIBLINGS BY : Preserve ordering of the hierarchical query 

                        then apply the order_by_clause to the sibling rows

Examples

--Display employee and its manager 

    SELECT employee_id, manager_id

      FROM employees

CONNECT BY PRIOR employee_id = manager_id

START WITH employee_id = 100;

--Display the full tree 

    SELECT LEVEL emp_level,

  -- LPAD will just pad the spaces before the e_name as per the Level hierarchy 

           LPAD (' ', 2 * (LEVEL - 1)) || Last_name || ', ' || First_name s

      FROM employees

START WITH manager_id IS NULL

CONNECT BY PRIOR employee_id = manager_id;

--Specifies a condition that identifies the row(s) to be used as the root(s) of a hierarchical query

    SELECT last_name, employee_id, manager_id, LEVEL

      FROM employees

START WITH employee_id = 100

CONNECT BY PRIOR employee_id = manager_id;  

-- Connect_By_Root

-- The following example returns the last name of each employee in department 110, each manager above that employee in the hierarchy, the number of levels between manager and employee, and the path between the two:

    SELECT first_name || ', ' || last_name emp_name,

-- Connect_By_Root will give the list of Manager 

           CONNECT_BY_ROOT first_name || ', ' || last_name MGR_name,

           LEVEL - 1 PATHLEN,

           department_id

      FROM employees

-- Level >1 will give the employee list excluding the Top manager 

 WHERE LEVEL > 1 AND department_id = 110

CONNECT BY PRIOR employee_id = manager_id

;

    SELECT last_name,

           CONNECT_BY_ROOT last_name            MGR,

           LEVEL - 1                            PATHLEN,

-- SYS_CONNECT_BY_PATH will give the hierarchical structure of emp / manager relationship 

           SYS_CONNECT_BY_PATH (last_name, '/') PATH

      FROM employees

     WHERE LEVEL > 1 AND department_id = 110

CONNECT BY PRIOR employee_id = manager_id

  ORDER BY last_name, MGR, PATHLEN, PATH;

  

  

  

3. --Display the Hierarchical/ Tree structure of employees and there department

    SELECT LPAD (' ', LEVEL * 2, ' ') || last_name LNAME, d.department_id

      FROM employees e, departments d

     WHERE e.department_id = d.department_id

START WITH employee_id = 100

CONNECT BY PRIOR e.employee_id = e.manager_id;