Data Structure
Networking
RDBMS
Operating System
Java
MS Excel
iOS
HTML
CSS
Android
Python
C Programming
C++
C#
MongoDB
MySQL
Javascript
PHP
Physics
Chemistry
Biology
Mathematics
English
Economics
Psychology
Social Studies
Fashion Studies
Legal Studies
- Selected Reading
- UPSC IAS Exams Notes
- Developer's Best Practices
- Questions and Answers
- Effective Resume Writing
- HR Interview Questions
- Computer Glossary
- Who is Who
How to traverse Hierarchical data in Oracle?
Problem Statement: You need to traverse the hierarchy data from top to bottom marking the level of each row in the hierarchy.
Solution:
Oracle provides CONNECT BY clause to specify a hierarchical query i.e. how to connect the parent nodes and child nodes and the PRIOR operator to define the join condition/s between the parent nodes, and the LEVEL pseudo-column to indicate how far from the root/parent row the current row is.
Additionally, we can use the START WITH clause to indicate where to start the tree navigation. We must use the PRIOR operator to specify the column/s in the parent row that have the same values as the linking columns in the child row.
In order to loop through the entire hieratchy, remove the START WITH clause. This will fetch all the students with their professor and their professor’s professor(i.e. Dean), and so on, until the top manager/director of the institute.
Example 1: Identify the hierarchical/structure for the professor_id = 100 SQL:
/*
Function - Identify the hierarchical/structure for the professor_id = 102
Tables Used - students
Data - documented below
*/
SELECT student_id,
level,
lpad('-',(level-1)*3) || first_name || ' ' || last_name AS full_name
FROM students
START WITH professor_id = 102
CONNECT BY professor_id = PRIOR student_id;Output
103 1 BROWN MICHAEL 104 2 -JONES WILLIAM 105 2 -MILLER DAVID 106 2 -DAVIS RICHARD 107 2 -GARCIA CHARLES
Example 2: Identify the hierarchical/structure where professor_id is NULL SQL:
/*
Function - Identify the hierarchical/structure professor_id is null
Tables Used - students
Data - documented below
*/
SELECT student_id,
level,
lpad('-',(level-1)*3) || first_name || ' ' || last_name AS full_name
FROM students
START WITH professor_id IS null
CONNECT BY professor_id = PRIOR student_id;Output : Few rows from above SQL
100 1 SMITH JAMES 101 2 -JOHNSON JOHN 108 3 -RODRIGUEZ JOSEPH 109 4 -WILSON THOMAS 110 4 -MARTINEZ CHRISTOPHER 111 4 -ANDERSON DANIEL 112 4 -TAYLOR PAUL 113 4 -THOMAS MARK 102 2 -WILLIAMS ROBERT 103 3 -BROWN MICHAEL 104 4 -JONES WILLIAM 105 4 -MILLER DAVID 106 4 -DAVIS RICHARD 107 4 -GARCIA CHARLES
Example 3: Identify the Institute entire hierarchical/structure SQL:
/*
Function - Identify the Institute entire hierarchical/structure
Tables Used - students
Data - documented below
*/
SELECT student_id,
level,
lpad('-',(level-1)*3) || first_name || ' ' || last_name AS full_name
FROM students
CONNECT BY student_id = PRIOR professor_id;Output: Few rows from above SQL
100 1 SMITH JAMES 101 1 JOHNSON JOHN 100 2 -SMITH JAMES 102 1 WILLIAMS ROBERT 100 2 -SMITH JAMES 103 1 BROWN MICHAEL 102 2 -WILLIAMS ROBERT 100 3 -SMITH JAMES 104 1 JONES WILLIAM 103 2 -BROWN MICHAEL 102 3 -WILLIAMS ROBERT 100 4 -SMITH JAMES 105 1 MILLER DAVID 103 2 -BROWN MICHAEL 102 3 -WILLIAMS ROBERT 100 4 -SMITH JAMES 106 1 DAVIS RICHARD 103 2 -BROWN MICHAEL 102 3 -WILLIAMS ROBERT 100 4 -SMITH JAMES 107 1 GARCIA CHARLES 103 2 -BROWN MICHAEL 102 3 -WILLIAMS ROBERT 100 4 -SMITH JAMES 108 1 RODRIGUEZ JOSEPH 101 2 -JOHNSON JOHN 100 3 -SMITH JAMES 109 1 WILSON THOMAS 108 2 -RODRIGUEZ JOSEPH 101 3 -JOHNSON JOHN 100 4 -SMITH JAMES 110 1 MARTINEZ CHRISTOPHER 108 2 -RODRIGUEZ JOSEPH 101 3 -JOHNSON JOHN 100 4 -SMITH JAMES 111 1 ANDERSON DANIEL 108 2 -RODRIGUEZ JOSEPH 101 3 -JOHNSON JOHN 100 4 -SMITH JAMES 112 1 TAYLOR PAUL 108 2 -RODRIGUEZ JOSEPH 101 3 -JOHNSON JOHN 100 4 -SMITH JAMES 113 1 THOMAS MARK 108 2 -RODRIGUEZ JOSEPH 101 3 -JOHNSON JOHN 100 4 -SMITH JAMES
Data Preparation: Data used for the problem is shown below. The data is completely made up for demonstration purpose.
Example
DROP TABLE students; COMMIT; CREATE TABLE students ( student_id NUMBER(6) , first_name VARCHAR2(20) , last_name VARCHAR2(25) , email VARCHAR2(40) , phone_number VARCHAR2(20) , join_date DATE , class_id VARCHAR2(20) , fees NUMBER(8,2) , professor_id NUMBER(6) , department_id NUMBER(4) ) ;
Example
CREATE UNIQUE INDEX stu_id_pk ON students (student_id) ;
INSERT INTO students VALUES (100,'SMITH','JAMES','SMITH.JAMES@notreal.com','111.111.1245',TO_DATE('17-06-2003','DD-MM-YYYY'),'INS_CHAIRMAN',24000,NULL,NULL);
INSERT INTO students VALUES (101,'JOHNSON','JOHN','JOHNSON.JOHN@notreal.com','111.111.1246',TO_DATE('21-09-2005','DD-MM-YYYY'),'INS_VP',17000,100,90);
INSERT INTO students VALUES (102,'WILLIAMS','ROBERT','WILLIAMS.ROBERT@notreal.com','111.111.1247',TO_DATE('13-01-2001','DD-MM-YYYY'),'INS_VP',17000,100,90);
INSERT INTO students VALUES (103,'BROWN','MICHAEL','BROWN.MICHAEL@notreal.com','111.111.1248',TO_DATE('03-01-2006','DD-MM-YYYY'),'INS_STAFF',9000,102,60);
INSERT INTO students VALUES (104,'JONES','WILLIAM','JONES.WILLIAM@notreal.com','111.111.1249',TO_DATE('21-05-2007','DD-MM-YYYY'),'INS_STAFF',6000,103,60);
INSERT INTO students VALUES (105,'MILLER','DAVID','MILLER.DAVID@notreal.com','111.111.1250',TO_DATE('25-06-2005','DD-MM-YYYY'),'INS_STAFF',4800,103,60);
INSERT INTO students VALUES (106,'DAVIS','RICHARD','DAVIS.RICHARD@notreal.com','111.111.1251',TO_DATE('05-02-2006','DD-MM-YYYY'),'INS_STAFF',4800,103,60);
INSERT INTO students VALUES (107,'GARCIA','CHARLES','GARCIA.CHARLES@notreal.com','111.111.1252',TO_DATE('07-02-2007','DD-MM-YYYY'),'INS_STAFF',4200,103,60);
INSERT INTO students VALUES (108,'RODRIGUEZ','JOSEPH','RODRIGUEZ.JOSEPH@notreal.com','111.111.1253',TO_DATE('17-08-2002','DD-MM-YYYY'),'CL_PHY',12008,101,100);
INSERT INTO students VALUES (109,'WILSON','THOMAS','WILSON.THOMAS@notreal.com','111.111.1254',TO_DATE('16-08-2002','DD-MM-YYYY'),'CL_MATH',9000,108,100);
INSERT INTO students VALUES (110,'MARTINEZ','CHRISTOPHER','MARTINEZ.CHRISTOPHER@notreal.com','111.111.1255',TO_DATE('28-09-2005','DD-MM-YYYY'),'CL_MATH',8200,108,100);
INSERT INTO students VALUES (111,'ANDERSON','DANIEL','ANDERSON.DANIEL@notreal.com','111.111.1256',TO_DATE('30-09-2005','DD-MM-YYYY'),'CL_MATH',7700,108,100);
INSERT INTO students VALUES (112,'TAYLOR','PAUL','TAYLOR.PAUL@notreal.com','111.111.1257',TO_DATE('07-03-2006','DD-MM-YYYY'),'CL_MATH',7800,108,100);
INSERT INTO students VALUES (113,'THOMAS','MARK','THOMAS.MARK@notreal.com','111.111.1258',TO_DATE('07-12-2007','DD-MM-YYYY'),'CL_MATH',6900,108,100);
COMMIT;
Updated on: 04-Dec-2020
530 Views
Advertisements