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

137. View the Exhibits and examine PRODUCTS and SALEStables.

You issue the following query to display product name andthe number of times the product has been

sold: 

SQL>SELECT p.prod_name,i.item_cnt    

FROM (SELECT  prod_id, COUNT(*)item_cnt               

FROMsales               

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 therequired output.

B. The statement produces an error because ITEM_CNT cannotbe displayed in the outer query.

C. The statement produces an error because a subquery in theFROM clause and outer-joins cannot be

used together.

D. The statement produces an error because the GROUP BYclause cannot be used in a subquery in the

FROM clause.

Answer: A

答案解析：

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

实验验证：

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

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

 3   i RIGHT OUTER JOIN  products p   

 4  ON   i.prod_id  = p.prod_id;

PROD_NAME                                         ITEM_CNT

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

EnvoyExternal Keyboard                                 3441

SIMM-8MB PCMCIAII card                                19557

MousePad                                             29282

External6X CD-ROM                                     13043

...

72rows selected.

132. View the Exhibit and examine the structure of theSALES and PRODUCTS tables.

In the SALES table, PROD_ID is the foreign keyreferencing PROD_ID in the PRODUCTS table,

You want to list each product ID and the number of timesit has been sold.

Evaluate the following query:

SQL>SELECT p.prod_id, COUNT(s.prod_id)  

FROM products p _____________ sales s  

ON p.prod_id  = s.prod_id  

GROUP BY p.prod_id;

Which two JOIN options can be used in the blank in theabove query to get the required output? (Choose

two.)

A. JOIN 

B. FULL OUTER JOIN

C. LEFT OUTER JOIN

D. RIGHT OUTER JOIN

Answer: BC

答案解析：

列出每一个产品ID和产品已销售的次数。

列出每一个产品ID，所以C正确，由于s表的prod_id是products表的外键，所以是products的子集，所以B也正确

PS:这两题是相似的，通过不同的方式实现的。