OCP-1Z0-051-V9.02-116题

最新推荐文章于 2026-02-01 13:18:42 发布

原创最新推荐文章于 2026-02-01 13:18:42 发布 · 1.1w 阅读

·

0

·

本内容遵循CC 4.0 BY-SA版权协议

版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。

OCP11g解析同时被 2 个专栏收录

870 篇文章

订阅专栏

239 篇文章

订阅专栏

本文探讨了一个特定的SQL查询案例，旨在找出某表中项的最大平均数量是否超过50。通过逐步展示查询过程及其结果，揭示了错误的SQL语法，并解释了原因。

116. Examine the data in the ORD_ITEMS table:

ORD_NO ITEM_NO QTY

1 111 10

1 222 20

1 333 30

2 333 30

2 444 40

3 111 40

You want to find out if there is any item in the table for which the average maximum quantity is more than

50.

You issue the following query:

SQL> SELECT AVG(MAX(qty))

FROM ord_items

GROUP BY item_no

HAVING AVG(MAX(qty))>50;

Which statement is true regarding the outcome of this query?

A. It executes successfully and gives the correct output.

B. It gives an error because the HAVING clause is not valid.

C. It executes successfully but does not give the correct output.

D. It gives an error because the GROUP BY expression is not valid.

Answer: B

答案解析：

sh@TESTDB> select * from ord_items;

ORD_NO ITEM_NO QTY

---------- ---------- ----------

1 111 10

1 222 20

1 333 30

2 333 30

2 444 40

3 111 40

6 rows selected.

sh@TESTDB> select max(qty) from ord_items group by item_no;

MAX(QTY)

----------

40

20

30

40

sh@TESTDB> select avg(max(qty)) from ord_items group by item_no;

AVG(MAX(QTY))

-------------

32.5

sh@TESTDB> select avg(max(qty)) from ord_items group by item_no having avg(max(qty))>50;

select avg(max(qty)) from ord_items group by item_no having avg(max(qty))>50

*

ERROR at line 1:

ORA-00935: group function is nested too deeply

HAVING子句后面不能有好几个组函数嵌套，只能使用一个组函数。

评论

被折叠的条评论为什么被折叠?

到【灌水乐园】发言

查看更多评论

添加红包

成就一亿技术人!

hope_wisdom

发出的红包

实付元

使用余额支付

点击重新获取

扫码支付

钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。