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

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.