OCP-1Z0-051 第137题 子查询的应用

一、原题

View the Exhibits and examine PRODUCTS and SALES tables.

You issue the following query to display product name and the number of times the product has been sold:
SQL>SELECT p.prod_name, i.item_cnt
             FROM  (SELECT prod_id, COUNT(*) item_cnt
                             FROM sales
                             GROUP BY prod_id) i
                        RIGHT OUTER JOIN products p
                 ON i.prod_id = p.prod_id;
What happens when the above statement is executed?
A. The statement executes successfully and produces the required output.
B. The statement produces an error because ITEM_CNT cannot be displayed in the outer query.
C. The statement produces an error because a subquery in the FROM clause and outer-joins cannot be used together.
D. The statement produces an error because the GROUP BY clause cannot be used in a subquery in the FROM clause.

答案：A

二、题目翻译

查看PRODUCTS和SALES两个表:
执行下面的查询，显示产品名称与产品销售的次数
执行上面的语句的结果是什么?
A.语句执行成功并给出正确结果。
B.报错，因为ITEM_CNT不能在外层查询显示。
C.报错，因为FROM子句中的子查询不能与outer-joins一起使用。
D.报错，因为FROM子句中的子查询不能使用GROUP BY子句。

三、题目解析

先通过子查询，求出prod_id和销售的次数，然后再和products表右连接，求出产品的名称和销售的次数。