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.
Explanation:
The general form of the SELECT statement is further enhanced by the addition of the HAVING
clause and becomes:
SELECT column|expression|group_function(column|expression [alias]),}
FROM table[WHERE condition(s)]
[GROUP BY {col(s)|expr}]
[HAVING group_condition(s)]
[ORDER BY {col(s)|expr|numeric_pos} [ASC|DESC] [NULLS FIRST|LAST]];
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.
Having AVG(MAX(qty))>50 should have group by clause
The HAVING clause doesn’t allows nesting of aggregate functions.
SQL> desc ORDER_ITEMS
Name
———————————————
ORDER_ID
LINE_ITEM_ID
PRODUCT_ID
UNIT_PRICE
QUANTITY
SQL> SELECT AVG(MAX(qty))
2 FROM ord_items
3 GROUP BY item_no
4 HAVING AVG(MAX(qty))>50;
HAVING AVG(MAX(qty))>50
*
ERROR at line 4:
ORA-00935: group function is nested too deeply
Option C really makes me laugh.
“It executes successfully but does not give the correct output.”
What exactly is this “correct output”, the question itself does not specify what this should be. Thank you Mr Examiner for making that one easy one to strike of my list.