Home » Oracle » 1z0-061 » Which statement is true regarding the outcome of this query?
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.
Correct Answer: B
Explanation/Reference:
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.
|
Download Printable PDF. VALID exam to help you PASS.
|
 |
The reason why HAVING AVG(MAX(qty))>50; would not work is because statement below would not work either.
SELECT AVG(MAX(qty)) FROM order_items;
When nesting group functions, you must aggregate via GROUP BY. Without it nested group functions won’t work.
So, when you left HAVING AVG(MAX(qty))>50 as is , it is equivalent to SELECT AVG(MAX(qty)) FROM ord_items;
Don’t forget you can’t nest group functions more than two levels.
b