| My OracleGuide |
Hierarchial Queries | About Feedback | Products |
The queries shown below demonstrates how to query an hierarchial database. The queries works on employees tables. The script for creating the table and inserting data is available here Query to list hierachially starting from TOP
The following query lists all employees hierarchially starting with "Steven King".
SELECT lpad(' ', level*4) ||' Emp ID : '||EMPLOYEE_ID||' Name : '||FIRST_NAME||' '||LAST_NAME||' Manager ID : '||MANAGER_ID
FROM employees
CONNECT BY PRIOR EMPLOYEE_ID = MANAGER_ID
START WITH MANAGER_ID IS NULL;
Emp ID : 100 Name : Steven King Manager ID :
Emp ID : 101 Name : Neena Kochhar Manager ID : 100
Emp ID : 108 Name : Nancy Greenberg Manager ID : 101
Emp ID : 109 Name : Daniel Faviet Manager ID : 108
Emp ID : 110 Name : John Chen Manager ID : 108
Emp ID : 111 Name : Ismael Sciarra Manager ID : 108
Emp ID : 112 Name : Jose Manuel Urman Manager ID : 108
Emp ID : 113 Name : Luis Popp Manager ID : 108
Emp ID : 200 Name : Jennifer Whalen Manager ID : 101
Emp ID : 203 Name : Susan Mavris Manager ID : 101
Emp ID : 204 Name : Hermann Baer Manager ID : 101
Emp ID : 205 Name : Shelley Higgins Manager ID : 101
Emp ID : 206 Name : William Gietz Manager ID : 205
Emp ID : 102 Name : Lex De Haan Manager ID : 100
Emp ID : 103 Name : Alexander Hunold Manager ID : 102
Emp ID : 104 Name : Bruce Ernst Manager ID : 103
Emp ID : 105 Name : David Austin Manager ID : 103
Emp ID : 106 Name : Valli Pataballa Manager ID : 103
Emp ID : 107 Name : Diana Lorentz Manager ID : 103
Emp ID : 114 Name : Den Raphaely Manager ID : 100
Emp ID : 115 Name : Alexander Khoo Manager ID : 114
Emp ID : 116 Name : Shelli Baida Manager ID : 114
Emp ID : 117 Name : Sigal Tobias Manager ID : 114
Emp ID : 118 Name : Guy Himuro Manager ID : 114
Emp ID : 119 Name : Karen Colmenares Manager ID : 114
Emp ID : 201 Name : Michael Hartstein Manager ID : 100
Emp ID : 202 Name : Pat Fay Manager ID : 201
27 rows selected.
Query listing from BOTTOM to TOP
The following query lists all managers for "Luis Popp" hierarchially. Note the use of Key word PRIOR before MANAGER_ID.
SELECT rpad(' ', level*4) ||' '||EMPLOYEE_ID||' -- '||FIRST_NAME||' '||LAST_NAME||' -- '||MANAGER_ID
FROM employees
CONNECT BY EMPLOYEE_ID = PRIOR MANAGER_ID
START WITH EMPLOYEE_ID=113;
113 -- Luis Popp -- 108
108 -- Nancy Greenberg -- 101
101 -- Neena Kochhar -- 100
100 -- Steven King --
Starting with a particular Manager
The following query lists all employees hierarchially starting with "Neena Kochhar".
SELECT lpad(' ', level*4) ||' '||EMPLOYEE_ID||' -- '||FIRST_NAME||' '||LAST_NAME||' -- '||MANAGER_ID
FROM employees
CONNECT BY PRIOR EMPLOYEE_ID = MANAGER_ID
START WITH EMPLOYEE_ID = 101;
101 -- Neena Kochhar -- 100
108 -- Nancy Greenberg -- 101
109 -- Daniel Faviet -- 108
110 -- John Chen -- 108
111 -- Ismael Sciarra -- 108
112 -- Jose Manuel Urman -- 108
113 -- Luis Popp -- 108
200 -- Jennifer Whalen -- 101
203 -- Susan Mavris -- 101
204 -- Hermann Baer -- 101
205 -- Shelley Higgins -- 101
206 -- William Gietz -- 205
12 rows selected.
Concatenating the fields in the hierarchy
The SQL function SYS_CONNECT_BY_PATH( can be used to get a concatenated string using all columns in the hierarchy
SELECT LPAD(' ', 4*level-1)||SYS_CONNECT_BY_PATH(last_name, '/') "Path"
FROM employees
START WITH last_name = 'Kochhar'
CONNECT BY PRIOR employee_id = manager_id;
/Kochhar
/Kochhar/Greenberg
/Kochhar/Greenberg/Faviet
/Kochhar/Greenberg/Chen
/Kochhar/Greenberg/Sciarra
/Kochhar/Greenberg/Urman
/Kochhar/Greenberg/Popp
/Kochhar/Whalen
/Kochhar/Mavris
/Kochhar/Baer
/Kochhar/Higgins
/Kochhar/Higgins/Gietz
12 rows selected.
Create Table
CREATE TABLE employees
( employee_id NUMBER(6)
, first_name VARCHAR2(20)
, last_name VARCHAR2(25)
CONSTRAINT emp_last_name_nn NOT NULL
, email VARCHAR2(25)
CONSTRAINT emp_email_nn NOT NULL
, phone_number VARCHAR2(20)
, hire_date DATE
CONSTRAINT emp_hire_date_nn NOT NULL
, job_id VARCHAR2(10)
CONSTRAINT emp_job_nn NOT NULL
, salary NUMBER(8,2)
, commission_pct NUMBER(2,2)
, manager_id NUMBER(6)
, department_id NUMBER(4)
, CONSTRAINT emp_salary_min
CHECK (salary > 0)
, CONSTRAINT emp_email_uk
UNIQUE (email)
) ;
Insert Values
insert into employees values(100,'Steven','King','SKING','515.123.4567',
to_date('17-JUN-87','dd-mon-yy'),'AD_PRES',24000,null,null,90 );
insert into employees values(101,'Neena','Kochhar','NKOCHHAR','515.123.4568',
to_date('21-SEP-89','dd-mon-yy'),'AD_VP',17000,null,100,90 );
insert into employees values(102,'Lex','De Haan','LDEHAAN','515.123.4569',
to_date('13-JAN-93','dd-mon-yy'),'AD_VP',17000,null,100,90 );
insert into employees values(103,'Alexander','Hunold','AHUNOLD','590.423.4567',
to_date('03-JAN-90','dd-mon-yy'),'IT_PROG',9000,null,102,60 );
insert into employees values(104,'Bruce','Ernst','BERNST','590.423.4568',
to_date('21-MAY-91','dd-mon-yy'),'IT_PROG',6000,null,103,60 );
insert into employees values(105,'David','Austin','DAUSTIN','590.423.4569',
to_date('25-JUN-97','dd-mon-yy'),'IT_PROG',4800,null,103,60 );
insert into employees values(106,'Valli','Pataballa','VPATABAL','590.423.4560'
,to_date('05-FEB-98','dd-mon-yy'),'IT_PROG',4800,null,103,60 );
insert into employees values(107,'Diana','Lorentz','DLORENTZ','590.423.5567',
to_date('07-FEB-99','dd-mon-yy'),'IT_PROG',4200,null,103,60 );
insert into employees values(108,'Nancy','Greenberg','NGREENBE','515.124.4569',
to_date('17-AUG-94','dd-mon-yy'),'FI_MGR',12000,null,101,100 );
insert into employees values(109,'Daniel','Faviet','DFAVIET','515.124.4169',
to_date('16-AUG-94','dd-mon-yy'),'FI_ACCOUNT',9000,null,108,100 );
insert into employees values(110,'John','Chen','JCHEN','515.124.4269',
to_date('28-SEP-97','dd-mon-yy'),'FI_ACCOUNT',8200,null,108,100 );
insert into employees values(111,'Ismael','Sciarra','ISCIARRA','515.124.4369',
to_date('30-SEP-97','dd-mon-yy'),'FI_ACCOUNT',7700,null,108,100 );
insert into employees values(112,'Jose Manuel','Urman','JMURMAN','515.124.4469',
to_date('07-MAR-98','dd-mon-yy'),'FI_ACCOUNT',7800,null,108,100 );
insert into employees values(113,'Luis','Popp','LPOPP','515.124.4567',
to_date('07-DEC-99','dd-mon-yy'),'FI_ACCOUNT',6900,null,108,100 );
insert into employees values(114,'Den','Raphaely','DRAPHEAL','515.127.4561',
to_date('07-DEC-94','dd-mon-yy'),'PU_MAN',11000,null,100,30 );
insert into employees values(115,'Alexander','Khoo','AKHOO','515.127.4562',
to_date('18-MAY-95','dd-mon-yy'),'PU_CLERK',3100,null,114,30 );
insert into employees values(116,'Shelli','Baida','SBAIDA','515.127.4563',
to_date('24-DEC-97','dd-mon-yy'),'PU_CLERK',2900,null,114,30 );
insert into employees values(117,'Sigal','Tobias','STOBIAS','515.127.4564',
to_date('24-JUL-97','dd-mon-yy'),'PU_CLERK',2800,null,114,30 );
insert into employees values(118,'Guy','Himuro','GHIMURO','515.127.4565',
to_date('15-NOV-98','dd-mon-yy'),'PU_CLERK',2600,null,114,30 );
insert into employees values(119,'Karen','Colmenares','KCOLMENA','515.127.4566',
to_date('10-AUG-99','dd-mon-yy'),'PU_CLERK',2500,null,114,30 );
insert into employees values(200,'Jennifer','Whalen','JWHALEN','515.123.4444',
to_date('17-SEP-87','dd-mon-yy'),'AD_ASST',4400,null,101,10 );
insert into employees values(201,'Michael','Hartstein','MHARTSTE','515.123.5555',
to_date('17-FEB-96','dd-mon-yy'),'MK_MAN',13000,null,100,20 );
insert into employees values(202,'Pat','Fay','PFAY','603.123.6666',
to_date('17-AUG-97','dd-mon-yy'),'MK_REP',6000,null,201,20 );
insert into employees values(203,'Susan','Mavris','SMAVRIS','515.123.7777',
to_date('07-JUN-94','dd-mon-yy'),'HR_REP',6500,null,101,40 );
insert into employees values(204,'Hermann','Baer','HBAER','515.123.8888',
to_date('07-JUN-94','dd-mon-yy'),'PR_REP',10000,null,101,70 );
insert into employees values(205,'Shelley','Higgins','SHIGGINS','515.123.8080',
to_date('07-JUN-94','dd-mon-yy'),'AC_MGR',12000,null,101,110 );
insert into employees values(206,'William','Gietz','WGIETZ','515.123.8181',
to_date('07-JUN-94','dd-mon-yy'),'AC_ACCOUNT',8300,null,205,110 );
|