The table WORK.PILOTS contains the following data:
Id Name Jobcode Salary
--- ------ ------- ------
001 Albert PT1 50000
002 Brenda PT1 70000
003 Carl PT1 60000
004 Donna PT2 80000
005 Edward PT2 90000
006 Flora PT3 100000
A query was constructed to display the pilot salary
means at each level of Jobcode and the difference to
the overall mean salary:
Jobcode Average Difference
------------------------------
PT1 60000 -15000
PT2 85000 10000
PT3 100000 25000
Which select statement could NOT have
produced this output?
A.
select
Jobcode,
avg(Salary) as Average,
calculated Average - Overall as difference
from
WORK.PILOTS,
(select avg(Salary) as Overall from WORK.PILOTS)
group by jobcode
;
B.
select
Jobcode,
avg(Salary) as Average,
(select avg(Salary) from WORK.PILOTS) as Overall,
calculated Average - Overall as Difference
from WORK.PILOTS
group by 1
;
C.
select
Jobcode,
Average,
Average-Overall as Difference
from
(select Jobcode, avg(Salary) as Average
from WORK.PILOTS
group by 1),
(select avg(Salary) as Overall
from WORK.PILOTS)
;
D.
select
Jobcode,
avg(Salary) as Average,
calculated Average-(select avg(Salary) from WORK.PILOTS)
as Difference
from WORK.PILOTS
group by 1
;
答案选B,有的解析是是B在Overall前加上没有加calculated,加上便能够实现desired output。所以请问各位高手,本题目怎么破?A中使用的Overall前面也没有专门加上calculated啊,但是B就得加上才能运行~欢迎讨论