ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 047-112

047-112

原创 Linux操作系统 作者:jbymy2000 时间:2012-03-19 09:15:07 0 删除 编辑
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 命令未正确结束
(没看明白)

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/11312660/viewspace-718920/,如需转载,请注明出处,否则将追究法律责任。

上一篇: 047-111
下一篇: 047-113
请登录后发表评论 登录
全部评论

注册时间:2012-01-10

  • 博文量
    416
  • 访问量
    203171