clear markdown compare help best fiddles feedback dbanow.uk a leap of faith?
clear markdown donate comments/suggestions/bugs a leap of faith? bible365 diddy dollings
By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0. 2249191 fiddles created (32144 in the last week).

CREATE TABLE employees ( "EMPLOYEE_ID" int PRIMARY KEY, "LAST_NAME" varchar2(20), "FIRST_NAME" varchar2(20), "MANAGER_ID" int ) ;
 hidden batch(es)


INSERT ALL INTO employees VALUES (100, 'King', 'Gerald', NULL) INTO employees VALUES (102, 'De Haan', 'Jan', 100) INTO employees VALUES (103, 'Hunold', 'Francois', 102) INTO employees VALUES (105, 'Austin', 'A', 103) INTO employees VALUES (104, 'Ernst', 'E', 103) INTO employees VALUES (107, 'Lorentz', 'L', 103) INTO employees VALUES (106, 'Pataballa', 'P', 103) INTO employees VALUES (148, 'Cambrault', 'Gerald', 100) INTO employees VALUES (172, 'Bates', 'B', 148) INTO employees VALUES (169, 'Bloom', 'B', 148) INTO employees VALUES (170, 'Fox', 'F', 148) INTO employees VALUES (173, 'Kumar', 'K', 148) INTO employees VALUES (168, 'Ozer', 'O', 148) INTO employees VALUES (171, 'Smith', 'S', 148) INTO employees VALUES (110, 'Alex', 'A', NULL) INTO employees VALUES (111, 'Alex Jr', 'A', 110) INTO employees VALUES (112, 'Bill', 'B', NULL) SELECT * FROM dual ;
17 rows affected
 hidden batch(es)


SELECT employee_id, lpad(' ', level*2-1,' ') || last_name, manager_id, level, SYS_CONNECT_BY_PATH(last_name, '/') "Path" --returns the full path of the child FROM employees start with employee_id = 100 CONNECT BY PRIOR employee_id = manager_id AND NOT (last_name = 'Cambrault' and first_name = 'Gerald') order siblings by last_name ;
EMPLOYEE_ID LPAD('',LEVEL*2-1,'')||LAST_NAME MANAGER_ID LEVEL Path
100 King 1 /King
102 De Haan 100 2 /King/De Haan
103 Hunold 102 3 /King/De Haan/Hunold
105 Austin 103 4 /King/De Haan/Hunold/Austin
104 Ernst 103 4 /King/De Haan/Hunold/Ernst
107 Lorentz 103 4 /King/De Haan/Hunold/Lorentz
106 Pataballa 103 4 /King/De Haan/Hunold/Pataballa
 hidden batch(es)


SELECT employee_id, lpad(' ', level*2-1,' ') || last_name, manager_id, level, SYS_CONNECT_BY_PATH(last_name, '/') "Path" --returns the full path of the child FROM employees -- everything, empty START WITH CONNECT BY PRIOR employee_id = manager_id AND NOT (last_name = 'Cambrault' and first_name = 'Gerald') order siblings by last_name ;
EMPLOYEE_ID LPAD('',LEVEL*2-1,'')||LAST_NAME MANAGER_ID LEVEL Path
148 Cambrault 100 1 /Cambrault
172 Bates 148 2 /Cambrault/Bates
169 Bloom 148 2 /Cambrault/Bloom
170 Fox 148 2 /Cambrault/Fox
173 Kumar 148 2 /Cambrault/Kumar
168 Ozer 148 2 /Cambrault/Ozer
171 Smith 148 2 /Cambrault/Smith
102 De Haan 100 1 /De Haan
103 Hunold 102 2 /De Haan/Hunold
105 Austin 103 3 /De Haan/Hunold/Austin
104 Ernst 103 3 /De Haan/Hunold/Ernst
107 Lorentz 103 3 /De Haan/Hunold/Lorentz
106 Pataballa 103 3 /De Haan/Hunold/Pataballa
103 Hunold 102 1 /Hunold
105 Austin 103 2 /Hunold/Austin
104 Ernst 103 2 /Hunold/Ernst
107 Lorentz 103 2 /Hunold/Lorentz
106 Pataballa 103 2 /Hunold/Pataballa
105 Austin 103 1 /Austin
104 Ernst 103 1 /Ernst
107 Lorentz 103 1 /Lorentz
106 Pataballa 103 1 /Pataballa
111 Alex Jr 110 1 /Alex Jr
172 Bates 148 1 /Bates
169 Bloom 148 1 /Bloom
170 Fox 148 1 /Fox
173 Kumar 148 1 /Kumar
168 Ozer 148 1 /Ozer
171 Smith 148 1 /Smith
110 Alex 1 /Alex
111 Alex Jr 110 2 /Alex/Alex Jr
112 Bill 1 /Bill
100 King 1 /King
102 De Haan 100 2 /King/De Haan
103 Hunold 102 3 /King/De Haan/Hunold
105 Austin 103 4 /King/De Haan/Hunold/Austin
104 Ernst 103 4 /King/De Haan/Hunold/Ernst
107 Lorentz 103 4 /King/De Haan/Hunold/Lorentz
106 Pataballa 103 4 /King/De Haan/Hunold/Pataballa
 hidden batch(es)


SELECT employee_id, lpad(' ', level*2-1,' ') || last_name, manager_id, level, SYS_CONNECT_BY_PATH(last_name, '/') "Path" --returns the full path of the child FROM employees start with NOT (last_name = 'Cambrault' and first_name = 'Gerald') -- All except 'Cambrault Gerald' and all beneath him CONNECT BY PRIOR employee_id = manager_id AND NOT (last_name = 'Cambrault' and first_name = 'Gerald') order siblings by last_name ;
EMPLOYEE_ID LPAD('',LEVEL*2-1,'')||LAST_NAME MANAGER_ID LEVEL Path
110 Alex 1 /Alex
111 Alex Jr 110 2 /Alex/Alex Jr
111 Alex Jr 110 1 /Alex Jr
105 Austin 103 1 /Austin
172 Bates 148 1 /Bates
112 Bill 1 /Bill
169 Bloom 148 1 /Bloom
102 De Haan 100 1 /De Haan
103 Hunold 102 2 /De Haan/Hunold
105 Austin 103 3 /De Haan/Hunold/Austin
104 Ernst 103 3 /De Haan/Hunold/Ernst
107 Lorentz 103 3 /De Haan/Hunold/Lorentz
106 Pataballa 103 3 /De Haan/Hunold/Pataballa
104 Ernst 103 1 /Ernst
170 Fox 148 1 /Fox
103 Hunold 102 1 /Hunold
105 Austin 103 2 /Hunold/Austin
104 Ernst 103 2 /Hunold/Ernst
107 Lorentz 103 2 /Hunold/Lorentz
106 Pataballa 103 2 /Hunold/Pataballa
100 King 1 /King
102 De Haan 100 2 /King/De Haan
103 Hunold 102 3 /King/De Haan/Hunold
105 Austin 103 4 /King/De Haan/Hunold/Austin
104 Ernst 103 4 /King/De Haan/Hunold/Ernst
107 Lorentz 103 4 /King/De Haan/Hunold/Lorentz
106 Pataballa 103 4 /King/De Haan/Hunold/Pataballa
173 Kumar 148 1 /Kumar
107 Lorentz 103 1 /Lorentz
168 Ozer 148 1 /Ozer
106 Pataballa 103 1 /Pataballa
171 Smith 148 1 /Smith
 hidden batch(es)


SELECT employee_id, lpad(' ', level*2-1,' ') || last_name, manager_id, level, SYS_CONNECT_BY_PATH(last_name, '/') "Path" --returns the full path of the child FROM employees start with manager_id IS NULL -- All hierarchies, starting from top level managers CONNECT BY PRIOR employee_id = manager_id AND NOT (last_name = 'Cambrault' and first_name = 'Gerald') order siblings by last_name ;
EMPLOYEE_ID LPAD('',LEVEL*2-1,'')||LAST_NAME MANAGER_ID LEVEL Path
110 Alex 1 /Alex
111 Alex Jr 110 2 /Alex/Alex Jr
112 Bill 1 /Bill
100 King 1 /King
102 De Haan 100 2 /King/De Haan
103 Hunold 102 3 /King/De Haan/Hunold
105 Austin 103 4 /King/De Haan/Hunold/Austin
104 Ernst 103 4 /King/De Haan/Hunold/Ernst
107 Lorentz 103 4 /King/De Haan/Hunold/Lorentz
106 Pataballa 103 4 /King/De Haan/Hunold/Pataballa
 hidden batch(es)


SELECT employee_id, lpad(' ', level*2-1,' ') || last_name, manager_id, level, SYS_CONNECT_BY_PATH(last_name, '/') "Path" --returns the full path of the child FROM employees start with manager_id IS NULL -- All hierarchies, starting from top level managers AND NOT (last_name = 'Cambrault' and first_name = 'Gerald') -- except 'Cambrault Gerald' and all beneath him CONNECT BY PRIOR employee_id = manager_id AND NOT (last_name = 'Cambrault' and first_name = 'Gerald') order siblings by last_name ;
EMPLOYEE_ID LPAD('',LEVEL*2-1,'')||LAST_NAME MANAGER_ID LEVEL Path
110 Alex 1 /Alex
111 Alex Jr 110 2 /Alex/Alex Jr
112 Bill 1 /Bill
100 King 1 /King
102 De Haan 100 2 /King/De Haan
103 Hunold 102 3 /King/De Haan/Hunold
105 Austin 103 4 /King/De Haan/Hunold/Austin
104 Ernst 103 4 /King/De Haan/Hunold/Ernst
107 Lorentz 103 4 /King/De Haan/Hunold/Lorentz
106 Pataballa 103 4 /King/De Haan/Hunold/Pataballa
 hidden batch(es)


update employees set manager_id = null -- lets promote Cambrault where (last_name = 'Cambrault' and first_name = 'Gerald') ;
1 rows affected
 hidden batch(es)


SELECT employee_id, lpad(' ', level*2-1,' ') || last_name, manager_id, level, SYS_CONNECT_BY_PATH(last_name, '/') "Path" --returns the full path of the child FROM employees start with manager_id IS NULL -- All hierarchies, starting from top level managers (Cambrault should appear in the result) CONNECT BY PRIOR employee_id = manager_id AND NOT (last_name = 'Cambrault' and first_name = 'Gerald') order siblings by last_name ;
EMPLOYEE_ID LPAD('',LEVEL*2-1,'')||LAST_NAME MANAGER_ID LEVEL Path
110 Alex 1 /Alex
111 Alex Jr 110 2 /Alex/Alex Jr
112 Bill 1 /Bill
148 Cambrault 1 /Cambrault
172 Bates 148 2 /Cambrault/Bates
169 Bloom 148 2 /Cambrault/Bloom
170 Fox 148 2 /Cambrault/Fox
173 Kumar 148 2 /Cambrault/Kumar
168 Ozer 148 2 /Cambrault/Ozer
171 Smith 148 2 /Cambrault/Smith
100 King 1 /King
102 De Haan 100 2 /King/De Haan
103 Hunold 102 3 /King/De Haan/Hunold
105 Austin 103 4 /King/De Haan/Hunold/Austin
104 Ernst 103 4 /King/De Haan/Hunold/Ernst
107 Lorentz 103 4 /King/De Haan/Hunold/Lorentz
106 Pataballa 103 4 /King/De Haan/Hunold/Pataballa
 hidden batch(es)


SELECT -- (Cambrault should NOT appear in the result) employee_id, lpad(' ', level*2-1,' ') || last_name, manager_id, level, SYS_CONNECT_BY_PATH(last_name, '/') "Path" --returns the full path of the child FROM employees start with manager_id IS NULL -- All hierarchies, starting from top level managers AND NOT (last_name = 'Cambrault' and first_name = 'Gerald') -- except 'Cambrault Gerald' and all beneath him CONNECT BY PRIOR employee_id = manager_id AND NOT (last_name = 'Cambrault' and first_name = 'Gerald') order siblings by last_name ;
EMPLOYEE_ID LPAD('',LEVEL*2-1,'')||LAST_NAME MANAGER_ID LEVEL Path
110 Alex 1 /Alex
111 Alex Jr 110 2 /Alex/Alex Jr
112 Bill 1 /Bill
100 King 1 /King
102 De Haan 100 2 /King/De Haan
103 Hunold 102 3 /King/De Haan/Hunold
105 Austin 103 4 /King/De Haan/Hunold/Austin
104 Ernst 103 4 /King/De Haan/Hunold/Ernst
107 Lorentz 103 4 /King/De Haan/Hunold/Lorentz
106 Pataballa 103 4 /King/De Haan/Hunold/Pataballa
 hidden batch(es)