63题的第50个，题目如下：

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就得加上才能运行~欢迎讨论