OCP 1Z0 051 119

花蜻宽

119. 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
Evaluate the following query:  

SQL>SELECT item_no, AVG(qty)

FROM ord_items

HAVING AVG(qty) > MIN(qty) * 2

GROUP BY item_no;

Which statement is true regarding the outcome of the above query?

A. It gives an error because the HAVING clause should be specified after the GROUP BY clause.

B. It gives an error because all the aggregate functions used in the HAVING clause must be specified in

the SELECT list.

C. It displays the item nos with their average quantity where the average quantity is more than double the

minimum quantity of that item in the table.

D. It displays the item nos with their average quantity where the average quantity is more than double the

overall minimum quantity of
all the items in the table.



hainvg 放group by 前与放group by 后都可以

SQL> SELECT item_no, AVG(qty)
2    FROM ord_items
3  HAVING AVG(qty) > MIN(qty) * 2
4   GROUP BY item_no;
ITEM_NO   AVG(QTY)
---------- ----------
111         25
1 row selected
Executed in 0.015 seconds
SQL> SELECT item_no, AVG(qty)
2    FROM ord_items
3   GROUP BY item_no
4  HAVING AVG(qty) > MIN(qty) * 2;
ITEM_NO   AVG(QTY)
---------- ----------
111         25
1 row selected

该语句相当于

SQL> SELECT item_no, avg_qty
2    FROM (SELECT item_no, AVG(qty) AS avg_qty, MIN(qty) * 2 AS min_qty
3            FROM ord_items
4           GROUP BY item_no)
5   WHERE avg_qty > min_qty;
ITEM_NO    AVG_QTY
---------- ----------
111         25
1 row selected

要注意Where语句只能放group by 之前

SQL> SELECT item_no, AVG(qty) FROM ord_items WHERE qty > 0 GROUP BY item_no;
ITEM_NO   AVG(QTY)
---------- ----------
222         20
444         40
333         30
111         25
4 rows selected
Executed in 0.031 seconds
SQL> SELECT item_no, AVG(qty) FROM ord_items GROUP BY item_no WHERE qty > 0;
SELECT item_no, AVG(qty) FROM ord_items GROUP BY item_no WHERE qty > 0
ORA-00933: SQL 命令未正确结束



Answer: C

版权声明：本文为博主原创文章，未经博主允许不得转载。