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
3HAVING 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
4HAVING 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
版权声明：本文为博主原创文章，未经博主允许不得转载。
页:
[1]