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