OCP 1Z0 051 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. 

from 后的子查询，可以直接当作一个view来看待
里面的别名外面可以引用、可以直接join、子查询里也可以有 group by 与 order by子句

SQL> SELECT p.prod_name, i.item_cnt
  2    FROM (SELECT prod_id, COUNT(*) item_cnt FROM sh.sales GROUP BY prod_id) i
  3   RIGHT OUTER JOIN sh.products p
  4      ON i.prod_id = p.prod_id
  5   WHERE rownum <= 5;
PROD_NAME                                            ITEM_CNT
-------------------------------------------------- ----------
5MP Telephoto Digital Camera                             6002
17" LCD w/built-in HDTV Tuner                            6010
Envoy 256MB - 40GB                                       5766
Y Box                                                    6929
Mini DV Camcorder with 3.5" Swivel LCD                   6160
5 rows selected

Answer: A