116.Examine the data in the ORD_ITEMS table:

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
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.
答案：B
解析：这里AVG(MAX(qty))，max是聚合函数，avg又是聚合函数，但是这里使用了group by，因此
第一次max聚合的是按照item_no进行分组的每个item_no的max，然后对这个max列表进行avg，所以这里是正确的
having avg(max(qty))>50;这里就有问题了，having是对group by后的记录集进行过滤
having max(qty)>50,指的按照item_no 分组后，只保留最大的qty大于50的，
如果在增加avg(max(qty))>50，这里其实已经不能再继续分组了，有问题