OCP-1Z0-051-V9.02-137题

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.

Answer: A

答案解析：

通过子查询，查询出产品卖出去的次数。

在通过右连接产品表，显示出所有产品。

实验验证：

sh@TEST0924> SELECT p.prod_name, i.item_cnt 

  2  FROM (SELECT  prod_id, COUNT(*) item_cnt FROM sales GROUP BY prod_id)

  3   i RIGHT OUTER JOIN  products p   

  4  ON   i.prod_id  = p.prod_id;

PROD_NAME                                            ITEM_CNT

-------------------------------------------------- ----------

Envoy External Keyboard                                  3441

SIMM- 8MB PCMCIAII card                                 19557

Mouse Pad                                               29282

External 6X CD-ROM                                      13043

...

72 rows selected.