View the Exhibit and examine the structure of the SALES table.
The following query is written to retrieve all those product ID s from the SALES table that have
more than 55000 sold and have been ordered more than 10 times.
Which statement is true regarding this SQL statement?
A.
It executes successfully and generates the required result.
B.
It produces an error because COUNT(*) should be specified in the SELECT clause also.
C.
It produces an error because COUNT(*) should be only in the HAVING clause and not in the
WHERE clause.
D.
It executes successfully but produces no result because COUNT(prod_id) should be used instead
of COUNT(*).
Explanation:
Restricting Group Results with the HAVING Clause
You use the HAVING clause to specify the groups that are to be displayed, thus further restricting
the groups on the basis of aggregate information.
In the syntax, group_condition restricts the groups of rows returned to those groups for which the
specified condition is true.
The Oracle server performs the following steps when you use the HAVING clause:
1. Rows are grouped.
2. The group function is applied to the group.
3. The groups that match the criteria in the HAVING clause are displayed.
The HAVING clause can precede the GROUP BY clause, but it is recommended that you place the
GROUP BY clause first because it is more logical. Groups are formed and group functions are
calculated before the HAVING clause is applied to the groups in the SELECT list.
Note: The WHERE clause restricts rows, whereas the HAVING clause restricts groups.