OCP 1Z0 051 112

来源：互联网发布：淘宝网针织衫女编辑：程序博客网时间：2024/06/05 18:42

112. View the Exhibit and examine the structure of the PROMOTIONS table.
Evaluate the following SQL statement:
SQL>SELECT promo_category, AVG(promo_cost) Avg_Cost, AVG(promo_cost)*.25 Avg_Overhead
FROM promotions
WHERE UPPER(promo_category) IN ('TV', 'INTERNET','POST')
GROUP BY Avg_Cost
ORDER BY Avg_Overhead;
The above query generates an error on execution.
Which clause in the above SQL statement causes the error?

A. WHERE
B. SELECT
C. GROUP BY
D. ORDER BY

1、group by 里不能引用别名
2、group by 里不能使用聚合函数（如果不用别名就是 AVG(promo_cost) ）
3、promo_category 要么在select里去掉，要么加入到 group by 后面

SQL> SELECT promo_category,  2         AVG(promo_cost) avg_cost,  3         AVG(promo_cost) * .25 avg_overhead  4    FROM sh.promotions  5   WHERE upper(promo_category) IN ('TV', 'INTERNET', 'POST')  6   GROUP BY avg_cost  7   ORDER BY avg_overhead;SELECT promo_category,       AVG(promo_cost) avg_cost,       AVG(promo_cost) * .25 avg_overhead  FROM sh.promotions WHERE upper(promo_category) IN ('TV', 'INTERNET', 'POST') GROUP BY avg_cost ORDER BY avg_overheadORA-00904: "AVG_COST": 标识符无效

SQL> SELECT promo_category,  2         AVG(promo_cost) avg_cost,  3         AVG(promo_cost) * .25 avg_overhead  4    FROM sh.promotions  5   WHERE upper(promo_category) IN ('TV', 'INTERNET', 'POST')  6   GROUP BY AVG(promo_cost)  7   ORDER BY avg_overhead;SELECT promo_category,       AVG(promo_cost) avg_cost,       AVG(promo_cost) * .25 avg_overhead  FROM sh.promotions WHERE upper(promo_category) IN ('TV', 'INTERNET', 'POST') GROUP BY AVG(promo_cost) ORDER BY avg_overheadORA-00934: 此处不允许使用分组函数

SQL> SELECT promo_category,  2         AVG(promo_cost) avg_cost,  3         AVG(promo_cost) * .25 avg_overhead  4    FROM sh.promotions  5   WHERE upper(promo_category) IN ('TV', 'INTERNET', 'POST')  6   /*GROUP BY AVG(promo_cost)*/  7   ORDER BY avg_overhead;SELECT promo_category,       AVG(promo_cost) avg_cost,       AVG(promo_cost) * .25 avg_overhead  FROM sh.promotions WHERE upper(promo_category) IN ('TV', 'INTERNET', 'POST') /*GROUP BY AVG(promo_cost)*/ ORDER BY avg_overheadORA-00937: 不是单组分组函数

正确的写法应是

SQL> SELECT promo_category,  2         AVG(promo_cost) avg_cost,  3         AVG(promo_cost) * .25 avg_overhead  4    FROM sh.promotions  5   WHERE upper(promo_category) IN ('TV', 'INTERNET', 'POST')  6   GROUP BY promo_category  7   ORDER BY avg_overhead;PROMO_CATEGORY                   AVG_COST AVG_OVERHEAD------------------------------ ---------- ------------TV                             46260.8695 11565.217391internet                       50072.9411 12518.235294post                           50970.7692 12742.6923073 rows selected

这个答案有点牵强

Answer: C

0 0