OCP 1Z0 051 144

144. View the Exhibit and examine the structure of the PRODUCTS table. 
You want to display the category with the maximum number of items. 
You issue the following query: 
SQL>SELECT COUNT(*),prod_category_id 
FROM products 
GROUP BY prod_category_id 
HAVING COUNT(*) = (SELECT MAX(COUNT(*)) FROM products); 
What is the outcome?  

A. It executes successfully and gives the correct output. 
B. It executes successfully but does not give the correct output. 
C. It generates an error because the subquery does not have a GROUP BY clause. 
D. It generates an error because = is not valid and should be replaced   by the   IN operator. 

the category with the maximum number of items
需要先按category 分组汇总取条数，才能再取max(条数)
本题中的聚合函数嵌套要报错

SQL> SELECT MAX(COUNT(*)) FROM sh.products;
SELECT MAX(COUNT(*)) FROM sh.products
ORA-00978: nested group function without GROUP BY

应按需求增加group by

SQL> SELECT MAX(COUNT(*)) FROM sh.products GROUP BY prod_category;
MAX(COUNT(*))
-------------
           26
1 row selected

Answer: C