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 identify rows that are not parents of any other rows in a Hierarchy table in Oracle?
Problem Statement: How to identify leaf rows in a Hierarchy table i.e. rows that are not parents of any other rows.
Solution: Oracle provides CONNECT_BY_ISLEAF clause to identify rows that are not parents of any other rows. To begin with let us see how does a connect_by_isleaf works.
SQL:
/*
Function - Example to show if the row is parent of any other rows or not
Tables Used - students Data - Documented below
*/
SELECT student_id,
level,
CASE WHEN connect_by_isleaf = 0
THEN 'Yes'
ELSE 'No'
END AS is_parent,
lpad('-',(level-1)*3) || first_name || ' ' || last_name AS full_name
FROM students
START WITH professor_id IS null
CONNECT BY PRIOR student_id = professor_id;Output: Few rows from above SQL
100 1 Yes SMITH JAMES 101 2 Yes -JOHNSON JOHN 108 3 Yes -RODRIGUEZ JOSEPH 109 4 No -WILSON THOMAS 110 4 No -MARTINEZ CHRISTOPHER 111 4 No -ANDERSON DANIEL 112 4 No -TAYLOR PAUL 113 4 No -THOMAS MARK 102 2 Yes -WILLIAMS ROBERT 103 3 Yes -BROWN MICHAEL 104 4 No -JONES WILLIAM 105 4 No -MILLER DAVID
In the above SQL the value 0 for connect_by_isleaf represents that the row is parent and have further child rows while a value of 1 represent the leaf node/record which is not a parent of anyother rows.
One of the interesting implementation of connect_by_isleaf is to identify the students that are not professors/adminstrators. Below SQL will show how to acheive it.
SQL:
/* Function - Example to identify students that are not professors/adminstrators Tables Used - students Data - Documented below */ SELECT student_id,first_name || ' ' || last_name AS student_name, connect_by_root first_name || ' ' || last_name AS manager_name FROM students WHERE connect_by_isleaf = 1 START WITH professor_id IS null CONNECT BY PRIOR student_id = professor_id;
Output: Few rows from above SQL
109 WILSON THOMAS SMITH THOMAS 110 MARTINEZ CHRISTOPHER SMITH CHRISTOPHER 111 ANDERSON DANIEL SMITH DANIEL 112 TAYLOR PAUL SMITH PAUL 113 THOMAS MARK SMITH MARK 104 JONES WILLIAM SMITH WILLIAM 105 MILLER DAVID SMITH DAVID 106 DAVIS RICHARD SMITH RICHARD 107 GARCIA CHARLES SMITH CHARLES
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;
170 Views
