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.
Group functions are nested too deeply in HAVING clause.
“Group functions may only be nested two levels deep. Three formats using group
functions are shown here:
G1(group_item) = result
G1(G2(group_item ) = result
G1(G2(G3(group_item))) is NOT allowed.
Group functions are represented by the letter G followed by a number. The first
simple form contains no nested functions. Examples include the SUM(group_item)
or AVG(group_item) functions that return a single result per group. The second form
supports two nested group functions, like SUM(AVG(group_item)). In this case, a
GROUP BY clause is mandatory since the average value of the group_item per group
is calculated before being aggregated by the SUM function.”
https://equizzing.com/oracle/which-statement-is-true-regarding-the-outcome-of-this-query/
B