Oracle 18c

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

CREATE TABLE employees
  (
    "EMPLOYEE_ID" int PRIMARY KEY,
    "LAST_NAME" varchar2(20),
    "FIRST_NAME" varchar2(20),
    "MANAGER_ID" int
  ) ;
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

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
null
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


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
null
1
/Alex

111
   Alex Jr
110
2
/Alex/Alex Jr

112
 Bill
null
1
/Bill

100
 King
null
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


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
null
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
null
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
null
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


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
null
1
/Alex

111
   Alex Jr
110
2
/Alex/Alex Jr

112
 Bill
null
1
/Bill

100
 King
null
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


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
null
1
/Alex

111
   Alex Jr
110
2
/Alex/Alex Jr

112
 Bill
null
1
/Bill

100
 King
null
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


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

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
null
1
/Alex

111
   Alex Jr
110
2
/Alex/Alex Jr

112
 Bill
null
1
/Bill

148
 Cambrault
null
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
null
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


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
null
1
/Alex

111
   Alex Jr
110
2
/Alex/Alex Jr

112
 Bill
null
1
/Bill

100
 King
null
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

EMPLOYEE_ID	LPAD('',LEVEL*2-1,'')\|\|LAST_NAME	MANAGER_ID	LEVEL	Path
100	King	null	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

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	null	1	/Alex
111	Alex Jr	110	2	/Alex/Alex Jr
112	Bill	null	1	/Bill
100	King	null	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