最先答对且答案未经编辑的puber将获得纪念章一枚(答案不可编辑但可发新贴补充或纠正),其他会员如果提供有价值的分析、讨论也可获得纪念章一枚。
每两周的优胜者可获得itpub奖励的技术图书一本。
以往旧题索引:
http://www.itpub.net/forum.php?m ... eid&typeid=1808
原始出处:
http://www.plsqlchallenge.com/
作者:Kim Berg Hansen
运行环境:SQLPLUS, SERVEROUTPUT已打开
注:本题给出答案时候要求给予简要说明才能得到奖品
我有员工和项目表:
create table qz_emp (
id integer primary key
, name varchar2(20)
)
/
create table qz_project (
id integer primary key
, description varchar2(20)
, manager references qz_emp
)
/
insert into qz_emp values (42, 'Bill Bowman')
/
insert into qz_emp values (43, 'Chen Lo Chai')
/
insert into qz_emp values (47, 'Dorothea Dalmeyer')
/
insert into qz_project values (184, 'Cloud Migration' , 47)
/
insert into qz_project values (185, 'Plug-in Development', 43)
/
insert into qz_project values (187, 'Backup Strategy' , 47)
/
insert into qz_project values (188, 'Roadmap Review' , NULL)
/
commit
/
项目可能是被一名员工所管理,或者被项目团队自己管理(MANAGER列为NULL)
我想要一个员工列表,这些员工至少管理一个项目。即使他或她管理多个项目,这名员工也应该只被列出一次。
哪些项目可以执行不出错并且产生这个所要求的输出:
ID NAME
---------- --------------------
43 Chen Lo Chai
47 Dorothea Dalmeyer
(A)
select e.id, e.name
from qz_emp e
where exists (
select null
from qz_project p
where p.manager = e.id
)
order by e.id
/
(B)
select e.id, e.name
from qz_emp e
where e.id in (
select p.manager
from qz_project p
)
order by e.id
/
(C)
select e.id, e.name
from qz_emp e
where e.id in (
select p.manager
from qz_project p
where p.manager is not null
)
order by e.id
/
(D)
select e.id, e.name
from qz_emp e
join qz_project p
on p.manager = e.id
order by e.id
/
(E)
select e.id, e.name
from qz_emp e
left outer join qz_project p
on p.manager = e.id
where p.manager is not null
order by e.id
/
(F)
select distinct e.id, e.name
from qz_emp e
join qz_project p
on p.manager = e.id
order by e.id
/