/ DATABASE EXPERT
πŸ—„οΈ
DBA-DEV TOOLKIT
πŸ‘¨β€πŸ’» Vinayak Vishweshwara Dabgar
βš™οΈ Oracle Apps DBA - Consultant
πŸ’» DBA - Developer
πŸ—οΈ Database Architect
πŸš€ Performance Tuning Expert
πŸ—ƒοΈ Database Expertise
🟒 Oracle Database
πŸ”΅ Microsoft SQL Server
🟠 MySQL
🐘 PostgreSQL
☁️ Amazon RDS
πŸ“Š Amazon Redshift
πŸ”— Professional Links
🌐 Professional Website πŸŽ“ Corporate Training LMS πŸ“§ vdabgar.in@gmail.com πŸ’Ό LinkedIn Profile
πŸ“ Office Address
🏠 No.20, SAI SADAN
🏒 Near LVS Apartments
πŸ›£οΈ TC Palya Main Road
πŸ“Œ Krishnarajapura
πŸŒ† Bangalore - 560036
πŸŒ† Karnataka, India
β¬… Back

Oracle DQL 07 - Hierarchical Queries 


/*===============================================================
HIERARCHICAL QUERIES

Author : Vinayak Vishweshwara Dabgar
www.dabgarvinayakv.com
Oracle Apps DBA - Consultant | DBA - Developer | Database Architect | Performance Tuning Expert


PART 07A – HIERARCHICAL QUERIES

Topics Covered
--------------
β€’ Hierarchical query basics
β€’ CONNECT BY
β€’ START WITH
β€’ PRIOR operator
β€’ LEVEL pseudocolumn
β€’ Organizational reporting
===============================================================*/



/*---------------------------------------------------------------
HIERARCHICAL QUERY INTRODUCTION
---------------------------------------------------------------*/

/*
Hierarchical queries allow traversal
of parent-child relationships.

Common examples

Organization charts
Category trees
Bill of materials

Key keywords

START WITH
CONNECT BY
PRIOR
LEVEL
*/



/*---------------------------------------------------------------
QUESTION 1
Display employee hierarchy
---------------------------------------------------------------*/

SELECT
employee_id,
first_name,
manager_id
FROM hr.employees
START WITH manager_id IS NULL
CONNECT BY PRIOR employee_id = manager_id;



/*---------------------------------------------------------------
QUESTION 2
Display employee hierarchy with LEVEL
---------------------------------------------------------------*/

SELECT
LEVEL,
employee_id,
first_name,
manager_id
FROM hr.employees
START WITH manager_id IS NULL
CONNECT BY PRIOR employee_id = manager_id;



/*---------------------------------------------------------------
QUESTION 3
Display hierarchy ordered by level
---------------------------------------------------------------*/

SELECT
LEVEL,
employee_id,
first_name,
manager_id
FROM hr.employees
START WITH manager_id IS NULL
CONNECT BY PRIOR employee_id = manager_id
ORDER BY LEVEL;



/*---------------------------------------------------------------
QUESTION 4
Employees reporting under manager 100
---------------------------------------------------------------*/

SELECT
LEVEL,
employee_id,
first_name,
manager_id
FROM hr.employees
START WITH employee_id = 100
CONNECT BY PRIOR employee_id = manager_id;



/*---------------------------------------------------------------
QUESTION 5
Hierarchy with indentation
---------------------------------------------------------------*/

SELECT
LPAD(' ', LEVEL*2) || first_name AS employee_tree,
employee_id,
manager_id
FROM hr.employees
START WITH manager_id IS NULL
CONNECT BY PRIOR employee_id = manager_id;



/*---------------------------------------------------------------
QUESTION 6
Hierarchy showing employee path
---------------------------------------------------------------*/

SELECT
employee_id,
first_name,
SYS_CONNECT_BY_PATH(first_name, ' -> ') hierarchy_path
FROM hr.employees
START WITH manager_id IS NULL
CONNECT BY PRIOR employee_id = manager_id;



/*---------------------------------------------------------------
QUESTION 7
Hierarchy with manager names
---------------------------------------------------------------*/

SELECT
LEVEL,
employee_id,
first_name,
manager_id
FROM hr.employees
START WITH manager_id IS NULL
CONNECT BY PRIOR employee_id = manager_id;



/*---------------------------------------------------------------
QUESTION 8
Employees under department managers
---------------------------------------------------------------*/

SELECT
LEVEL,
employee_id,
first_name,
department_id
FROM hr.employees
START WITH manager_id IS NULL
CONNECT BY PRIOR employee_id = manager_id;



/*---------------------------------------------------------------
QUESTION 9
Hierarchy sorted by employee name
---------------------------------------------------------------*/

SELECT
LEVEL,
employee_id,
first_name
FROM hr.employees
START WITH manager_id IS NULL
CONNECT BY PRIOR employee_id = manager_id
ORDER SIBLINGS BY first_name;



/*---------------------------------------------------------------
QUESTION 10
Employees with hierarchy depth
---------------------------------------------------------------*/

SELECT
employee_id,
first_name,
LEVEL hierarchy_level
FROM hr.employees
START WITH manager_id IS NULL
CONNECT BY PRIOR employee_id = manager_id;



/*---------------------------------------------------------------
QUESTION 11
Limit hierarchy to first 3 levels
---------------------------------------------------------------*/

SELECT
LEVEL,
employee_id,
first_name
FROM hr.employees
WHERE LEVEL <= 3
START WITH manager_id IS NULL
CONNECT BY PRIOR employee_id = manager_id;



/*---------------------------------------------------------------
QUESTION 12
Show manager chain for employee 206
---------------------------------------------------------------*/

SELECT
employee_id,
first_name,
manager_id
FROM hr.employees
START WITH employee_id = 206
CONNECT BY PRIOR manager_id = employee_id;



/*---------------------------------------------------------------
QUESTION 13
Employee reporting chain
---------------------------------------------------------------*/

SELECT
employee_id,
first_name,
manager_id,
LEVEL
FROM hr.employees
START WITH employee_id = 206
CONNECT BY PRIOR manager_id = employee_id;



/*---------------------------------------------------------------
QUESTION 14
Employee reporting hierarchy with path
---------------------------------------------------------------*/

SELECT
employee_id,
first_name,
SYS_CONNECT_BY_PATH(first_name, ' -> ') chain
FROM hr.employees
START WITH employee_id = 206
CONNECT BY PRIOR manager_id = employee_id;



/*---------------------------------------------------------------
QUESTION 15
Top level managers
---------------------------------------------------------------*/

SELECT
employee_id,
first_name
FROM hr.employees
WHERE manager_id IS NULL;



/*---------------------------------------------------------------
QUESTION 16
Employees with hierarchy levels
---------------------------------------------------------------*/

SELECT
employee_id,
first_name,
manager_id,
LEVEL
FROM hr.employees
START WITH manager_id IS NULL
CONNECT BY PRIOR employee_id = manager_id;



/*---------------------------------------------------------------
QUESTION 17
Employees with manager tree
---------------------------------------------------------------*/

SELECT
LPAD(' ',LEVEL*3) || first_name AS hierarchy
FROM hr.employees
START WITH manager_id IS NULL
CONNECT BY PRIOR employee_id = manager_id;



/*---------------------------------------------------------------
QUESTION 18
Hierarchy including job titles
---------------------------------------------------------------*/

SELECT
LEVEL,
e.employee_id,
e.first_name,
j.job_title
FROM hr.employees e
JOIN hr.jobs j
ON e.job_id = j.job_id
START WITH manager_id IS NULL
CONNECT BY PRIOR e.employee_id = e.manager_id;



/*---------------------------------------------------------------
QUESTION 19
Hierarchy including department names
---------------------------------------------------------------*/

SELECT
LEVEL,
e.employee_id,
e.first_name,
d.department_name
FROM hr.employees e
JOIN hr.departments d
ON e.department_id = d.department_id
START WITH manager_id IS NULL
CONNECT BY PRIOR e.employee_id = e.manager_id;



/*---------------------------------------------------------------
QUESTION 20
Hierarchy including department and salary
---------------------------------------------------------------*/

SELECT
LEVEL,
e.employee_id,
e.first_name,
e.salary,
d.department_name
FROM hr.employees e
JOIN hr.departments d
ON e.department_id = d.department_id
START WITH manager_id IS NULL
CONNECT BY PRIOR e.employee_id = e.manager_id;



/*---------------------------------------------------------------
END OF PART 07A
---------------------------------------------------------------*/

/*===============================================================
PART 07B – ADVANCED HIERARCHICAL QUERIES

Continuation of PART 07A

Topics Covered
--------------
β€’ CONNECT_BY_ROOT
β€’ CONNECT_BY_ISLEAF
β€’ CONNECT_BY_ISCYCLE
β€’ Organizational analytics
β€’ Hierarchical reporting
===============================================================*/



/*---------------------------------------------------------------
CONNECT_BY_ROOT
---------------------------------------------------------------*/

/*
CONNECT_BY_ROOT returns the root row
for the current hierarchical branch.
*/



/*---------------------------------------------------------------
QUESTION 21
Show root manager for each employee
---------------------------------------------------------------*/

SELECT
employee_id,
first_name,
CONNECT_BY_ROOT first_name root_manager
FROM hr.employees
START WITH manager_id IS NULL
CONNECT BY PRIOR employee_id = manager_id;



/*---------------------------------------------------------------
QUESTION 22
Employee hierarchy showing root manager
---------------------------------------------------------------*/

SELECT
employee_id,
first_name,
CONNECT_BY_ROOT employee_id root_employee
FROM hr.employees
START WITH manager_id IS NULL
CONNECT BY PRIOR employee_id = manager_id;



/*---------------------------------------------------------------
QUESTION 23
Employee path with root employee
---------------------------------------------------------------*/

SELECT
employee_id,
first_name,
CONNECT_BY_ROOT first_name root_manager,
SYS_CONNECT_BY_PATH(first_name,' -> ') hierarchy_path
FROM hr.employees
START WITH manager_id IS NULL
CONNECT BY PRIOR employee_id = manager_id;



/*---------------------------------------------------------------
CONNECT_BY_ISLEAF
---------------------------------------------------------------*/

/*
CONNECT_BY_ISLEAF identifies leaf nodes
(end of hierarchy branches).
*/



/*---------------------------------------------------------------
QUESTION 24
Identify leaf employees
---------------------------------------------------------------*/

SELECT
employee_id,
first_name,
CONNECT_BY_ISLEAF is_leaf
FROM hr.employees
START WITH manager_id IS NULL
CONNECT BY PRIOR employee_id = manager_id;



/*---------------------------------------------------------------
QUESTION 25
Display only leaf employees
---------------------------------------------------------------*/

SELECT
employee_id,
first_name
FROM hr.employees
WHERE CONNECT_BY_ISLEAF = 1
START WITH manager_id IS NULL
CONNECT BY PRIOR employee_id = manager_id;



/*---------------------------------------------------------------
QUESTION 26
Hierarchy with leaf identification
---------------------------------------------------------------*/

SELECT
employee_id,
first_name,
LEVEL,
CONNECT_BY_ISLEAF leaf_node
FROM hr.employees
START WITH manager_id IS NULL
CONNECT BY PRIOR employee_id = manager_id;



/*---------------------------------------------------------------
CONNECT_BY_ISCYCLE
---------------------------------------------------------------*/

/*
CONNECT_BY_ISCYCLE identifies cycles
in hierarchical relationships.
*/



/*---------------------------------------------------------------
QUESTION 27
Detect hierarchy cycles
---------------------------------------------------------------*/

SELECT
employee_id,
first_name,
CONNECT_BY_ISCYCLE cycle_flag
FROM hr.employees
START WITH manager_id IS NULL
CONNECT BY NOCYCLE PRIOR employee_id = manager_id;



/*---------------------------------------------------------------
QUESTION 28
Hierarchy avoiding cycles
---------------------------------------------------------------*/

SELECT
employee_id,
first_name,
manager_id
FROM hr.employees
START WITH manager_id IS NULL
CONNECT BY NOCYCLE PRIOR employee_id = manager_id;



/*---------------------------------------------------------------
ADVANCED ORGANIZATIONAL REPORTS
---------------------------------------------------------------*/



/*---------------------------------------------------------------
QUESTION 29
Employee hierarchy with department
---------------------------------------------------------------*/

SELECT
LEVEL,
e.employee_id,
e.first_name,
d.department_name
FROM hr.employees e
JOIN hr.departments d
ON e.department_id = d.department_id
START WITH e.manager_id IS NULL
CONNECT BY PRIOR e.employee_id = e.manager_id;



/*---------------------------------------------------------------
QUESTION 30
Employee hierarchy with job titles
---------------------------------------------------------------*/

SELECT
LEVEL,
e.employee_id,
e.first_name,
j.job_title
FROM hr.employees e
JOIN hr.jobs j
ON e.job_id = j.job_id
START WITH e.manager_id IS NULL
CONNECT BY PRIOR e.employee_id = e.manager_id;



/*---------------------------------------------------------------
QUESTION 31
Hierarchy with salary information
---------------------------------------------------------------*/

SELECT
LEVEL,
employee_id,
first_name,
salary
FROM hr.employees
START WITH manager_id IS NULL
CONNECT BY PRIOR employee_id = manager_id;



/*---------------------------------------------------------------
QUESTION 32
Manager hierarchy report
---------------------------------------------------------------*/

SELECT
LPAD(' ',LEVEL*3)||first_name hierarchy,
employee_id,
manager_id
FROM hr.employees
START WITH manager_id IS NULL
CONNECT BY PRIOR employee_id = manager_id;



/*---------------------------------------------------------------
QUESTION 33
Manager employee count
---------------------------------------------------------------*/

SELECT
manager_id,
COUNT(*) employee_count
FROM hr.employees
GROUP BY manager_id;



/*---------------------------------------------------------------
QUESTION 34
Hierarchy with employee count per manager
---------------------------------------------------------------*/

SELECT
manager_id,
COUNT(employee_id)
FROM hr.employees
GROUP BY manager_id
ORDER BY manager_id;



/*---------------------------------------------------------------
QUESTION 35
Hierarchy ordered by sibling names
---------------------------------------------------------------*/

SELECT
LEVEL,
employee_id,
first_name
FROM hr.employees
START WITH manager_id IS NULL
CONNECT BY PRIOR employee_id = manager_id
ORDER SIBLINGS BY first_name;



/*---------------------------------------------------------------
QUESTION 36
Hierarchy limited to 4 levels
---------------------------------------------------------------*/

SELECT
LEVEL,
employee_id,
first_name
FROM hr.employees
WHERE LEVEL <= 4
START WITH manager_id IS NULL
CONNECT BY PRIOR employee_id = manager_id;



/*---------------------------------------------------------------
QUESTION 37
Hierarchy showing employee path
---------------------------------------------------------------*/

SELECT
employee_id,
first_name,
SYS_CONNECT_BY_PATH(first_name,' -> ') path
FROM hr.employees
START WITH manager_id IS NULL
CONNECT BY PRIOR employee_id = manager_id;



/*---------------------------------------------------------------
QUESTION 38
Hierarchy showing root manager
---------------------------------------------------------------*/

SELECT
employee_id,
first_name,
CONNECT_BY_ROOT first_name root_manager
FROM hr.employees
START WITH manager_id IS NULL
CONNECT BY PRIOR employee_id = manager_id;



/*---------------------------------------------------------------
QUESTION 39
Hierarchy showing leaf employees only
---------------------------------------------------------------*/

SELECT
employee_id,
first_name
FROM hr.employees
WHERE CONNECT_BY_ISLEAF = 1
START WITH manager_id IS NULL
CONNECT BY PRIOR employee_id = manager_id;



/*---------------------------------------------------------------
QUESTION 40
Hierarchy including department and salary
---------------------------------------------------------------*/

SELECT
LEVEL,
e.employee_id,
e.first_name,
e.salary,
d.department_name
FROM hr.employees e
JOIN hr.departments d
ON e.department_id = d.department_id
START WITH e.manager_id IS NULL
CONNECT BY PRIOR e.employee_id = e.manager_id;



/*---------------------------------------------------------------
HIERARCHICAL QUERY PERFORMANCE NOTES
---------------------------------------------------------------*/

/*
Optimization Tips

1 Use START WITH carefully
2 Avoid deep recursion when possible
3 Use NOCYCLE to prevent loops
4 Filter rows before hierarchy expansion
5 Index parent-child columns
*/


/*---------------------------------------------------------------
END OF PART 07

Disclaimer and Limitation of Liability

The author provides all scripts, queries, and related materials β€œas is”, without any express 
or implied warranties, including but not limited to warranties of accuracy, completeness, 
reliability, merchantability, or fitness for a particular purpose.

By using, executing, or implementing any part of this material, the user acknowledges 
and agrees that they do so at their own risk. The author shall not be held liable for any 
direct, indirect, incidental, consequential, special, or exemplary damages, including but 
not limited to loss of data, loss of profits, system failures, security breaches, or any 
other damages or losses arising from the use or misuse of these materials.

Users are strongly advised to thoroughly review, validate, and test all scripts, queries, 
and configurations in a controlled, non-production (test) environment prior to deploying 
them in any live or production systems.

It is the sole responsibility of the user to ensure that the use of these materials 
complies with all applicable local, national, and international laws, regulations, 
and organizational policies.

By proceeding to use these materials, the user agrees to indemnify and hold harmless 
the author from any claims, damages, liabilities, or expenses arising from their use.
---------------------------------------------------------------*/