最先答对且答案未经编辑的puber将获得纪念章一枚(答案不可编辑但可发新贴补充或纠正)，其他会员如果提供有价值的分析、讨论也可获得纪念章一枚。
每两周的优胜者可获得itpub奖励的技术图书一本。
以往旧题索引：
http://www.itpub.net/forum.php?m ... eid&typeid=1808
原始出处：
http://www.plsqlchallenge.com/
作者：
Kim Berg Hansen
运行环境：SQLPLUS, SERVEROUTPUT已打开
注：本题给出答案时候要求给予简要说明才能得到奖品
我有两张表，保存着高尔夫(golf)和飞镖(dart)的选手：
create table qz_golf_players (
lastnamevarchar2(20)
, firstname varchar2(20)
, unique (lastname, firstname)
)
/
create table qz_dart_players (
lastnamevarchar2(20)
, firstname varchar2(20)
, unique (lastname, firstname)
)
/
insert into qz_golf_players values ('Hammersmith', 'Christopher')
/
insert into qz_golf_players values ('Lovejoy', 'Julian' )
/
insert into qz_golf_players values ('Sinclair' , 'Michael')
/
insert into qz_dart_players values ('Carlisle' , 'Edmund' )
/
insert into qz_dart_players values ('Hammersmith', 'Christopher')
/
insert into qz_dart_players values ('Lovejoy', 'Julian' )
/
commit
/
有些人两种都玩，但是我想要一个清单，列出哪些人仅仅只玩一种。
哪些选项包含的查询会返回这个输出：
LASTNAME
FIRSTNAME
-------------------- --------------------
Carlisle
Edmund
Sinclair
Michael
(A)
select lastname, firstname
from (
select lastname, firstname
from qz_golf_players
UNION ALL
select lastname, firstname
from qz_dart_players
)
group by lastname, firstname
having count(*) = 1
order by lastname, firstname
/
(B)
select lastname, firstname
from (
select lastname, firstname
from qz_golf_players
UNION
select lastname, firstname
from qz_dart_players
)
group by lastname, firstname
having count(*) = 1
order by lastname, firstname
/
(C)
select lastname, firstname
from qz_golf_players
UNION
select lastname, firstname
from qz_dart_players
MINUS
select lastname, firstname
from qz_golf_players
INTERSECT
select lastname, firstname
from qz_dart_players
order by lastname, firstname
/
(D)
(
select lastname, firstname
from qz_golf_players
UNION
select lastname, firstname
from qz_dart_players
)
MINUS
(
select lastname, firstname
from qz_golf_players
INTERSECT
select lastname, firstname
from qz_dart_players
)
order by lastname, firstname
/
(E)
(
select lastname, firstname
from qz_golf_players
UNION ALL
select lastname, firstname
from qz_dart_players
)
MINUS
(
select lastname, firstname
from qz_golf_players
INTERSECT
select lastname, firstname
from qz_dart_players
)
order by lastname, firstname
/
(F)
(
select lastname, firstname
from qz_golf_players
MINUS
select lastname, firstname
from qz_dart_players
)
UNION ALL
(
select lastname, firstname
from qz_dart_players
MINUS
select lastname, firstname
from qz_golf_players
)
order by lastname, firstname
/
(G)
select lastname, firstname
from qz_golf_players g
where not exists (
select null
from qz_dart_players d
where d.lastname= g.lastname
and d.firstname = g.firstname
)
UNION ALL
select lastname, firstname
from qz_dart_players d
where not exists (
select null
from qz_golf_players g
where g.lastname= d.lastname
and g.firstname = d.firstname
)
order by lastname, firstname
/
(H)
select coalesce(g.lastname , d.lastname ) as lastname
, coalesce(g.firstname, d.firstname) as firstname
from qz_golf_players g
FULL OUTER JOIN qz_dart_players d
on d.lastname= g.lastname
and d.firstname = g.firstname
where g.lastname is null
or d.lastname is null
order by lastname, firstname
/
|
