Hierarchy Exercises and Solutions Oracle Tips by Laurent Schneider

 

Laurent Schneider is considered one of the top Oracle SQL experts, and he is the author of the book "Advanced SQL Programming" by Rampant TechPress.  The following is an excerpt from the book.

 

Exercises

1.      CONNECT BY, PRIOR and START WITH

Retrieve the top hierarchy of Adams. Use LPAD to format output.

2.      LEVEL

Starting with KING, retrieve all level-4 employees.

3.      PRIOR

For each employee, retrieve the ratio of his salary to the salary of his manager. Return the rows in descending order of the ratio.

4.      SYS_CONNECT_BY_PATH

Using SYS_CONNECT_BY_PATH and aggregation, return an aggregated list of employees grouped by department.

5.      CONNECT_BY_ROOT

For each employee, retrieve the sum of his salary and the salary of all his employees.

6.      Cycle

Retrieve the three employees that make the query fail.

SELECT
   SYS_CONNECT_BY_PATH(ENAME,'/')
FROM
   EMP
CONNECT BY
   PRIOR NVL(MGR,7788)=EMPNO
START WITH
   ENAME='ADAMS';
ERROR:
ORA-01436: CONNECT BY loop in user data

no rows selected

7.      Reverse the hierarchy:

CREATE TABLE
   HEMP
AS SELECT
   ROWNUM R,
   LEVEL L,
   ENAME,
   EMPNO
FROM
   EMP
CONNECT BY
   MGR=PRIOR EMPNO
START WITH
   MGR IS NULL;

The table HEMP contains the rows of employees in hierarchical order with the level:

         R          L ENAME           EMPNO
---------- ---------- ---------- ----------
         1          1 KING             7839
         2          2 JONES            7566
         3          3 SCOTT            7788
         4          4 ADAMS            7876
         5          3 FORD             7902
         6          4 SMITH            7369
         7          2 BLAKE            7698
         8          3 ALLEN            7499
         9          3 WARD             7521
        10          3 MARTIN           7654
        11          3 TURNER           7844
        12          3 JAMES            7900
        13          2 CLARK            7782
        14          3 MILLER           7934

Using an analytic function, retrieve the manager of each employee in the table HEMP.

Solutions

1.      CONNECT BY, PRIOR and START WITH

Retrieve the top hierarchy of Adams. Use LPAD to format output:

SELECT
   LPAD(' ',3*LEVEL-3)||ENAME
FROM
   EMP
CONNECT BY
   PRIOR MGR=EMPNO
START WITH
   ENAME='ADAMS';

LPAD('',3*LEVEL-3)||ENAME
                           
-------------------------
ADAMS
   SCOTT
      JONES
         KING

Starting with Adams, the bottom-up hierarchy is retrieved.

2.      LEVEL

Starting with KING, retrieve all level-4 employees.

SELECT
   ENAME
FROM
   EMP
WHERE
   LEVEL=4
CONNECT BY
   MGR=PRIOR EMPNO
START WITH
   ENAME='KING';  

ENAME
----------
ADAMS
SMITH

3.      PRIOR

For each employee, retrieve the ratio of his salary to the salary of his manager. Return the rows in descending order of the ratio.

SELECT
   ENAME,
   SAL,
   PRIOR ENAME MGRNAME,
   PRIOR SAL MGRSAL,
   TO_CHAR(100*SAL/PRIOR SAL,'990.00L','NLS_CURRENCY=%') RATIO
FROM
   EMP
CONNECT BY
   MGR=PRIOR EMPNO
START WITH
   MGR IS NULL
ORDER BY
   SAL/PRIOR SAL DESC NULLS FIRST; 

ENAME             SAL MGRNAME        MGRSAL             RATIO
---------- ---------- ---------- ---------- -----------------
KING             5000
SCOTT            3000 JONES            2975           100.84%
FORD             3000 JONES            2975           100.84%
JONES            2975 KING             5000            59.50%
BLAKE            2850 KING             5000            57.00%
ALLEN            1600 BLAKE            2850            56.14%
MILLER           1300 CLARK            2450            53.06%
TURNER           1500 BLAKE            2850            52.63%
CLARK            2450 KING             5000            49.00%
WARD             1250 BLAKE            2850            43.86%
MARTIN           1250 BLAKE            2850            43.86%
ADAMS            1100 SCOTT            3000            36.67%
JAMES             950 BLAKE            2850            33.33%
SMITH             800 FORD             3000            26.67%

PRIOR is used in the SELECT and in the ORDER BY clauses to retrieve the salary of the manager.

4.      SYS_CONNECT_BY_PATH

Using SYS_CONNECT_BY_PATH and aggregation, return an aggregated list of employees grouped by department.

SELECT
   DEPTNO,
   SUBSTR(MAX(SYS_CONNECT_BY_PATH(ENAME,';')),2) ENAME
FROM
   EMP
CONNECT BY
   ENAME<PRIOR ENAME
   AND
   DEPTNO=PRIOR DEPTNO
GROUP BY
   DEPTNO;

    DEPTNO ENAME
---------- ------------------------------------------------------------
        30 WARD;TURNER;MARTIN;JAMES;BLAKE;ALLEN
        20 SMITH;SCOTT;JONES;FORD;ADAMS
        10 MILLER;KING;CLARK

The hierarchy is defined within a department with the condition that the name of the parent must be bigger, alphabetically, than the child. As a result, the names within the aggregated string are alphabetically sorted in descending order.

5.      CONNECT_BY_ROOT

For each employee, retrieve the sum of his salary and the salary of all his employees.

WITH
   T
AS
(
   SELECT
      CONNECT_BY_ROOT ENAME ENAME,
      SAL
   FROM
      EMP
   CONNECT BY
      MGR=PRIOR EMPNO
)
SELECT
   ENAME,
   SUM(SAL)
FROM
   T
GROUP BY
   ENAME;

ENAME        SUM(SAL)
---------- ----------
ALLEN            1600
JONES           10875
FORD             3800
MILLER           1300
CLARK            3750
WARD             1250
SMITH             800
SCOTT            4100
MARTIN           1250
TURNER           1500
ADAMS            1100
JAMES             950
BLAKE            9400
KING            29025

For each employee, a hierarchy is built in the subquery T and aggregated in the main query

6.      Cycle

Retrieve the three employees that make the query fail.

SELECT
   SYS_CONNECT_BY_PATH(ENAME,'/')
FROM
   EMP
CONNECT BY
   PRIOR NVL(MGR,7788)=EMPNO
START WITH
   ENAME='ADAMS';

ERROR:
ORA-01436: CONNECT BY loop in user data

no rows selected

SELECT
   DISTINCT ENAME MGR, PRIOR ENAME EMP
FROM
   EMP
WHERE
   CONNECT_BY_ISCYCLE=1
CONNECT BY
NOCYCLE
   PRIOR NVL(MGR,7788)=EMPNO;  

MGR        EMP
---------- ----------
KING       JONES
JONES      SCOTT
SCOTT      KING

In this query, Scott (7788) is the manager of the top manager King; King is the manager of Jones, who is the manager of Scott. CONNECT_BY_ISCYCLE returns 1 for cycling rows.

7.      Reverse the hierarchy:

         R          L ENAME           EMPNO
---------- ---------- ---------- ----------
         1          1 KING             7839
         2          2 JONES            7566
         3          3 SCOTT            7788
         4          4 ADAMS            7876
         5          3 FORD             7902
         6          4 SMITH            7369
         7          2 BLAKE            7698
         8          3 ALLEN            7499
         9          3 WARD             7521
        10          3 MARTIN           7654
        11          3 TURNER           7844
        12          3 JAMES            7900
        13          2 CLARK            7782
        14          3 MILLER           7934

Using an analytic function, retrieve the manager of each employee in the table HEMP.

SELECT
   EMPNO,
   ENAME,
   LAST_VALUE(EMPNO)
   OVER
   (
      ORDER BY L*1E12+R
      RANGE BETWEEN UNBOUNDED PRECEDING AND 1E12 PRECEDING
   ) MGR
FROM
   HEMP;

     EMPNO ENAME             MGR
---------- ---------- ----------
      7839 KING
      7566 JONES            7839
      7698 BLAKE            7839
      7782 CLARK            7839
      7788 SCOTT            7566
      7902 FORD             7566
      7499 ALLEN            7698
      7521 WARD             7698
      7654 MARTIN           7698
      7844 TURNER           7698
      7900 JAMES            7698
      7934 MILLER           7782
      7876 ADAMS            7788
      7369 SMITH            7902

The manager is the last value that has a level higher than the current row.