ocp-047-112 conncet by prior

1 12. View  the Exhibit and examine the details of the EMPLOYEES table. 
Evaluate the following SQL  st atement s: 
S tatement 1: 
SELECT employee_id, last_name, job_id, manager_id 
FROM employees 
ST ART WITH employee_id = 101 
CONNECT BY  PRIOR employee_id = manager_id  AND manager_id  != 108 ; 
S tatement 2: 
SELECT employee_id, last_name, job_id, manager_id 
FROM employees 

WHERE manager_id  != 108

ST ART WITH employee_id = 101 
CONNECT BY  PRIOR employee_id = manager_id; 
Which two st atements are true regarding the above SQL  statements? (Choose two.)

A. S tatement  2 would  not  execute because the  WHERE clause condition  is not  allowed  in  a  statement  that 
has the ST ART W ITH clause. 
B.  The output for st atement 1 would display  the employee with MANAGER_ID 108 and all the employees 
below him or her in the hierarchy . 
C. The output of st atement 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  disp lay 
all the employees below him or her in the hierarchy . 
Answer: CD 

标准语法：

select col_name...
from table_name 
where 条件
start with 条件
connect by prior条件
where子句必须出现在start with和connect by子句之前，否则报错

实验：

SQL> select * from t3;ID         NAME       SA         MGR_ID---------- ---------- ---------- ----------1          刘备       10002          诸葛亮     800        13          马超       900        24          士兵甲     500        3SQL> select * from t3 start with id=2 connect by prior id=mgr_id;ID         NAME       SA         MGR_ID---------- ---------- ---------- ----------2          诸葛亮     800        13          马超       900        24          士兵甲     500        3SQL> select * from t3 start with id=2 connect by prior mgr_id=id;ID         NAME       SA         MGR_ID---------- ---------- ---------- ----------2          诸葛亮     800        11          刘备       1000SQL> select * from t3 start with mgr_id=2 connect by prior id=mgr_id;ID         NAME       SA         MGR_ID---------- ---------- ---------- ----------3          马超       900        24          士兵甲     500        3SQL> select * from t3 start with mgr_id=2 connect by prior mgr_id=id;ID         NAME       SA         MGR_ID---------- ---------- ---------- ----------3          马超       900        22          诸葛亮     800        11          刘备       1000

由以上实验得出结论：start with 只是选择了起点，id=mgr_id是自起点向下按照start whit 的字段遍历，mgr_id=id就是自起点向上按照start whit 的字段遍历。