047-148

112. 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.

Answer: CD

DEPTID PAREDEPTID NAME

NUMBER NUMBER CHAR (40 Byte)

部门id 父部门id(所属部门id) 部门名称

通过子节点向根节点追朔.

Sql代码 select * from dept start with deptid=76 connect by prior paredeptid=deptid

通过根节点遍历子节点.

Sql代码

select * from dept start with paredeptid=0 connect by prior deptid=paredeptid

可通过level 关键字查询所在层次.

Sql代码

select a.*,level from persons.dept a start with paredeptid=0 connect by prior dept

id=paredeptid

再次复习一下:start with ...connect by 的用法， start with 后面所跟的就是就是递归的

种子。 递归的种子也就是递归开始的地方 connect by 后面的"prior" 如果缺省：则只能查询

到符合条件的起始行，并不进行递归查询；

connect by prior 后面所放的字段是有关系的，它指明了查询的方向。

PRIORY运算符必须放置在连接关系的两列中某一个的前面。对于节点间的父子关系，PRIOR

运算符在一侧表示父节点，在另一侧表示子节点，从而确定查找树结构是的顺序是自顶向下还

是自底向上。

SQL> select * from dept;

DEPTID PAREDEPTID NAME

---------- ---------- ----------

1 0 tt

2 1 tt

3 2 tt

4 2 tt

5 3 tt

SQL> select * from dept start with deptid=3 connect by prior deptid=paredeptid;

DEPTID PAREDEPTID NAME

---------- ---------- ----------

3 2 tt

5 3 tt

SQL> select * from dept start with deptid=3 connect by prior paredeptid=deptid;

DEPTID PAREDEPTID NAME

---------- ---------- ----------

3 2 tt

2 1 tt

1 0 tt

SQL> select * from dept where paredeptid !=2 start with deptid=3

connect by prior paredeptid=deptid ;

DEPTID PAREDEPTID NAME

---------- ---------- ----------

2 1 tt

1 0 tt

SQL> select * from dept start with deptid=3

connect by prior paredeptid=deptid where paredeptid !=2;

ORA-00933: SQL 命令未正确结束