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.)
A.
Statement 2 would not execute because the WHERE clause condition is not allowed in a statement that has the START WITH clause.
B.
The output for statement 1 would display the employee with MANAGER_ID 108 and all the employees below him or her in the hierarchy.
C.
The output of statement 1 would neither display the employee with MANAGER_ID 108 nor any employee below him or her in the hierarchy.
D.
The output for statement 2 would not display the employee with MANAGER_ID 108 but it would display all the employees below him or her in the hierarchy.
C is good. How about answer D? Will not display the employee with MANAGER_ID 108 but it would display everyone below him or her. How does it work?
Both statements simply remove rows with MANAGER_ID 108 from the hierarchy, which does not match the answers.
C. and D. would actually make sense if:
1) in both statements ‘manager_id != 108′ is replaced by ’employee_id !=108′
2) in C. and D. the text MANAGER_ID 108 is replaced by EMPLOYEE_ID 108
I believe only C is correct.
SQL> 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 != 1
08 ; — Statement 1
EMPLOYEE_ID LAST_NAME JOB_ID MANAGER_ID
———– ————————- ———- ———-
101 Kochhar AD_VP 100
108 Greenberg FI_MGR 101
200 Whalen AD_ASST 101
203 Mavris HR_REP 101
204 Baer PR_REP 101
205 Higgins AC_MGR 101
206 Gietz AC_ACCOUNT 205
7 rows selected.
SQL> SELECT employee_id, last_name, job_id, manager_id FROM employees WHERE mana
ger_id != 108 START WITH employee_id = 101 CONNECT BY PRIOR employee_id = manage
r_id; — Statement 2
EMPLOYEE_ID LAST_NAME JOB_ID MANAGER_ID
———– ————————- ———- ———-
101 Kochhar AD_VP 100
108 Greenberg FI_MGR 101
200 Whalen AD_ASST 101
203 Mavris HR_REP 101
204 Baer PR_REP 101
205 Higgins AC_MGR 101
206 Gietz AC_ACCOUNT 205
7 rows selected.
SQL> select employee_id, last_name, job_id, manager_id FROM employees WHERE mana
ger_id = 108;
EMPLOYEE_ID LAST_NAME JOB_ID MANAGER_ID
———– ————————- ———- ———-
109 Faviet FI_ACCOUNT 108
110 Chen FI_ACCOUNT 108
111 Sciarra FI_ACCOUNT 108
112 Urman FI_ACCOUNT 108
113 Popp FI_ACCOUNT 108
C and D are correct for sure. On sample HR schema there is not enough levels in the hierarchy to see the difference between queries. Just add one employee on 4th level under manager 108 (eg. under emplooyee 110) to have 4th level in the hierarchy.