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.
“An important difference between the HAVING clause and the other SELECT statement clauses is that it may only be specified if a GROUP BY clause is present. This dependency is sensible since group-level rows must exist before they can be restricted. The HAVING clause can occur before the GROUP BY clause in the SELECT statement. However, it is more common to place the HAVING clause after the GROUP BY clause. All grouping is performed and group functions are executed prior to evaluating the HAVING clause.”
– Oracle 12C Fundamental Exam Guide
A. false
Answer is C