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

128. View the Exhibits  and examine thestructures of the  CUSTOMERS,  SALES,  andCOUNTRIES

tables.

You need to generate a report that shows all country names,  with correspondingcustomers (if any) and 

sales details (if any), for all customers.

Which FROM clause gives the required result?

A. FROM sales JOIN customers USING (cust_id)

FULL OUTER JOIN countries USING (country_id);

B. FROM sales JOIN customers USING (cust_id)

RIGHT OUTER JOIN countries USING (country_id);

C. FROM customers LEFT OUTER JOIN sales USING (cust_id)

RIGHT OUTER JOIN countries USING (country_id);

D. FROM customers LEFT OUTER JOIN sales USING (cust_id)

LEFT OUTER JOIN countries USING (country_id);

Answer:C

答案解析：

根据题意要求，要求显示所有的用户的国家名称，而相匹配的用户名和销售信息有可能有，也有可能没有，即为null也要显示，所以客户表和销售表左连接，显示所有的用户，不管有没有销售信息匹配，然后再用右连接，关联countries表，这样显示所有的countryname，不管有没有客户匹配上，或是销售信息匹配上，都显示出来。

SQL:

SELECT customers.cust_last_name,countries.country_name,sales.amount_sold

FROM customers

LEFT OUTER JOINsales USING(cust_id)

RIGHT OUTER JOINcountries USING(country_id)

where rownum<=3;

即是左外连接customers ，显示所有客户，右外连接countries ，显示所有 country names

CUST_LAST_NAME                           COUNTRY_NAME                             AMOUNT_SOLD

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

Hurst                                    UnitedStates of America                    1232.16

Fepoi                                    UnitedStates of America                    1232.16

Berry                                    UnitedStates of America                    1232.16