一、原题
二、题目翻译
三、题目解析
View the Exhibits and examine PRODUCTS and SALES tables.
![](https://img-blog.csdn.net/20140516144438609?watermark/2/text/aHR0cDovL2Jsb2cuY3Nkbi5uZXQvaG9sbHkyMDA4/font/5a6L5L2T/fontsize/400/fill/I0JBQkFCMA==/dissolve/70/gravity/SouthEast)
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
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子句。
执行下面的查询,显示产品名称与产品销售的次数
执行上面的语句的结果是什么?
A.语句执行成功并给出正确结果。
B.报错,因为ITEM_CNT不能在外层查询显示。
C.报错,因为FROM子句中的子查询不能与outer-joins一起使用。
D.报错,因为FROM子句中的子查询不能使用GROUP BY子句。
三、题目解析
先通过子查询,求出prod_id和销售的次数,然后再和products表右连接,求出产品的名称和销售的次数。