Hierarchical Queries

As name indicates when there is parent - child relation ship in a table or a view, that is where hierarchical queries are mostly used.

Common Example
General Ledger.
President, Manager, employee relationship etc.

In this example we will be dealing with Scott schema and emp table specifically (SQL script for Scott schema attached).

EMPNOENAMEJOBMGRHIREDATESALCOMMDEPTNO
7369SMITHCLERK790217/12/198080020
7499ALLENSALESMAN 769820/02/1981160030030
7521WARDSALESMAN  769822/02/1981125050030
7566JONESMANAGER 78392/4/1981297520
7654MARTINSALESMAN 769828/09/19811250140030
7698BLAKEMANAGER78391/5/1981285030
7782CLARKMANAGER78399/6/1981245010
7788SCOTTANALYST756619/04/1987300020
7839KINGPRESIDENT17/11/1981500010
7844TURNERSALESMAN 76988/9/19811500030
7876ADAMSCLERK778823/05/1987110020
7900JAMESCLERK76983/12/198195030
7902FORDANALYST75663/12/1981300020
7934MILLERCLERK778223/01/1982130010

  

First to write query we need to understand the data and its structure, here we need to focus on the emp and his manager,
we need to write query which could display employee and its manger.

 

select a.ename,level,(select ename from emp where empno=a.mgr) manager
from emp a
connect by
prior EMPNO=MGR
start with mgr is null;

here we have to make note of the
connect by empno=mgr  -- This indicates the relationship.
start with mgr=null;  -- Where to start from

We will get the required output as below, i.e  employee and its maanger. Now to understand it better, Let see how the query does
it operation first it will start with mgr is null, i.e where there is no manager and that is King with level 1, then it will relationship s
earch where manager is King's Empno and get those employee and so forth.


ENAME LEVEL MANAGER
KING 1
JONES 2 KING
SCOTT 3 JONES
ADAMS 4 SCOTT
FORD 3 JONES
SMITH 4 FORD
BLAKE 2 KING
ALLEN 3 BLAKE
WARD 3 BLAKE
MARTIN 3 BLAKE
TURNER 3 BLAKE
JAMES 3 BLAKE
CLARK 2 KING
MILLER 3 CLARK

 


ċ
Scott schema.sql
(4k)
rahan nix,
Jan 9, 2016, 1:07 AM
Comments