1Z0-047 · Question #211
View the Exhibit and examine the details of the EMPLOYEES table. Evaluate the following SQL statements: Statement 1: SELECT employee_id, last_name, job_id, manager_id FROM employees START WITH…
The correct answer is C. The output of statement 1 would neither display the employee with MANAGER_ID 108 nor any employee D. The output for statement 2 would not display the employee with MANAGER_ID 108 but it would display. See the full explanation below for the reasoning.
Question
View the Exhibit and examine the details of the EMPLOYEES table. Evaluate the following SQL statements:
Statement 1:
SELECT employee_id, last_name, job_id, manager_id FROM employees START WITH employee_id = 101 CONNECT BY PRIOR employee_id = manager_id AND manager_id != 108; Statement 2:
SELECT employee_id, last_name, job_id, manager_id FROM employees WHERE manager_id != 108 START WITH employee_id = 101 CONNECT BY PRIOR employee_id = manager_id; Which two statements are true regarding the above SQL statements? (Choose two.)
Options
- AStatement 2 would not execute because the WHERE clause condition is not allowed in a statement that
- BThe output for statement 1 would display the employee with MANAGER_ID 108 and all the employees
- CThe output of statement 1 would neither display the employee with MANAGER_ID 108 nor any employee
- DThe output for statement 2 would not display the employee with MANAGER_ID 108 but it would display
How the community answered
(21 responses)- A5% (1)
- B10% (2)
- C86% (18)
Community Discussion
6
Imani T.Jun 26, 2026C and D are correct. In Statement 1, the manager_id != 108 condition is in the CONNECT BY clause, which prunes the tree at that branch entirely, so employee 108 and all subordinates under that branch are cut off from the result set. In Statement 2, the WHERE clause filters rows after the hierarchy is built, so the employee with manager_id 108 is excluded from display but their subordinates who don't have manager_id 108 are still shown because the traversal still went through that path.
Toby R.Jun 2, 2026C and D are right, CONNECT BY pruning cuts the whole branch.
Samuel O.Jun 21, 2026Option B is the classic trap here because people assume blocking the CONNECT BY condition just prunes that one row, but what it actually does is stop the traversal cold at that branch, meaning 108's reports never even get walked. Statement 1 kills the whole subtree under 108, while Statement 2 lets the hierarchy walk fully and then filters 108 out as a post-processing step, so his subordinates still show up, which is exactly why C and D are the right picks.
Brenda K.Jun 9, 2026If pruning cuts the branch, do subtree rows vanish with it or survive?
Grace U.Jun 26, 2026Which part trips you up, the WHERE filter pruning branches versus CONNECT BY stopping traversal?
Marisol N.Jun 8, 2026I almost circled B because I kept thinking the CONNECT BY condition would still let employee 108 show up and just stop branching there, but then I remembered the key rule tested on 1Z0-047 topic "Hierarchical Retrieval": a condition inside CONNECT BY prunes the entire subtree at that node, so neither the employee with MANAGER_ID 108 nor any of that employee's reports appear in Statement 1 (C), while the WHERE clause in Statement 2 only filters rows after the full hierarchy is walked, meaning the manager_id 108 row is hidden but the subordinates under that manager still surface (D).