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.
SQL> SELECT prod_id
FROM sales
WHERE quantity_sold > 55000 AND COUNT(*)>10
GROUP BY prod_id
HAVING COUNT(*)>10;
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(*).
C
Isn’t it mandatory to specify count function in select clause ?
NO
Though the HAVING clause specifies a condition that is similar to the purpose of a WHERE clause, the two clauses are not interchangeable. Listed below are some differences to help distinguish between the two:
The WHERE clause specifies the criteria which individual records must meet to be selcted by a query. It can be used without the GROUP BY clause. The HAVING clause cannot be used without the GROUP BY clause.
The WHERE clause selects rows before grouping. The HAVING clause selects rows after grouping.
The WHERE clause cannot contain aggregate functions. The HAVING clause can contain aggregate functions.
Following Prince jain’s correct advice… All answers are incorrect.
The where clause SHOULD NOT include the “where quantity_sold > 55000″…. this eliminates products whose TOTAL sales may exceed 55000. A prod_id may be listed several times in the Sales table with a “grouped” quantity_sold exceeding 55000.
The correct answer is not listed.
“It produces an error because the WHERE clause should be removed ENTIRELY, and the HAVING clause should contain BOTH quantity_sold > 55000 AND COUNT(*)>10
as in: Having sum(quantity_sold) > 55000 and count(*) > 10