|
最先答对且答案未经编辑的puber将获得纪念章一枚(答案不可编辑但可发新贴补充或纠正),其他会员如果提供有价值的分析、讨论也可获得纪念章一枚。
每两周的优胜者可获得itpub奖励的技术图书一本。
以往旧题索引:
http://www.itpub.net/forum.php?m ... eid&typeid=1808
原始出处:
http://www.plsqlchallenge.com/
作者:Kim Berg Hansen
运行环境:SQLPLUS, SERVEROUTPUT已打开
注:本题给出答案时候要求给予简要说明才能得到奖品
我有两张表,部门和员工:
create table plch_dept (
dept_id integer primary key
, name varchar2(10) unique
)
/
create table plch_emp (
emp_id integer primary key
, name varchar2(10) not null
, dept_id integer not null references plch_dept
)
/
insert into plch_dept values (100, 'Marketing')
/
insert into plch_dept values (200, 'Service')
/
insert into plch_emp values (10, 'Jones', 100)
/
insert into plch_emp values (15, 'Smith', 100)
/
insert into plch_emp values (20, 'Baker', 200)
/
insert into plch_emp values (25, 'Saxon', 200)
/
commit
/
公司要将市场部(Marketing)外包,所以我们需要删除所有在部门'Marketing'工作的员工。
哪些选项包含一个删除语句可以执行不出错,成功将Jones 和 Smith删除,使得选项执行之后,下列的测试查询会产生如下的输出:
select emp_id, name, dept_id
from plch_emp
order by emp_id
/
EMP_ID NAME DEPT_ID
---------- ---------- ----------
20 Baker 200
25 Saxon 200
(A)
delete plch_emp
where dept_id = (
select dept_id
from plch_dept
where name = 'Marketing'
)
/
(B)
delete plch_emp
where emp_id = (
select emp_id
from plch_dept
where name = 'Marketing'
)
/
(C)
delete plch_emp e
where e.dept_id = (
select d.dept_id
from plch_dept d
where d.name = 'Marketing'
)
/
(D)
delete plch_emp e
where e.emp_id = (
select d.emp_id
from plch_dept d
where d.name = 'Marketing'
)
/
(E)
delete plch_emp
where dept_id IN (
select dept_id
from plch_dept
where name = 'Marketing'
)
/
(F)
delete plch_emp
where emp_id IN (
select emp_id
from plch_dept
where name = 'Marketing'
)
/
(G)
delete plch_emp
where EXISTS (
select null
from plch_dept
where name = 'Marketing'
and dept_id = dept_id
)
/
(H)
delete plch_emp e
where EXISTS (
select null
from plch_dept d
where d.name = 'Marketing'
and d.dept_id = e.dept_id
)
/
|
|