Search BC Oracle Sites

# 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

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
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
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
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

SELECT
FROM
EMP
CONNECT BY
PRIOR MGR=EMPNO

-------------------------
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
ENAME='KING';

ENAME
----------
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
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%
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
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
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

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
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
7369 SMITH            7902

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

��

Burleson is the American Team

Note: This Oracle documentation was created as a support and Oracle training reference for use by our DBA performance tuning consulting professionals.  Feel free to ask questions on our Oracle forum.

Verify experience! Anyone considering using the services of an Oracle support expert should independently investigate their credentials and experience, and not rely on advertisements and self-proclaimed expertise. All legitimate Oracle experts publish their Oracle qualifications.

Errata?  Oracle technology is changing and we strive to update our BC Oracle support information.  If you find an error or have a suggestion for improving our content, we would appreciate your feedback.  Just  e-mail:

and include the URL for the page.

 Burleson Consulting The Oracle of Database Support Oracle Performance Tuning