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

127. Examine the data in the CUSTOMERS table:

CUSTNO    CUSTNAME    CITY

1         KING        SEATTLE

2         GREEN       BOSTON

3         KOCHAR      SEATTLE

4         SMITH       NEW YORK

You want to list all cities that have more than one customer along with the customer details. 

Evaluate the following query:

SQL>SELECT c1.custname, c1.city

FROM Customers c1 __________________ Customers c2

ON (c1.city=c2.city AND c1.custname<>c2.custname);

Which two JOIN options can be used in the blank in the above query to give the correct output? (Choose

two.)

A. JOIN

B. NATURAL JOIN  

C. LEFT OUTER JOIN   

D. FULL OUTER JOIN  

E. RIGHT OUTER JOIN

Answer: AE

 答案解析：

参考： http://blog.csdn.net/rlhua/article/details/12877591

scott@TESTDB> SELECT * FROM CUSTOMERS;

 

    CUSTNO CUSTNAME   CITY

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

         1 KING       SEATTLE

         2 GREEN      BOSTON

         3 KOCHAR     SEATTLE

         4 SMITH      NEW YORK

你想要列出所有的城市,有一个以上的客户以及客户的细节

A

scott@TESTDB>  select c1.custname,c1.city from customers c1 join customers c2

  2  on (c1.city=c2.city and c1.custname<>c2.custname);

 

CUSTNAME   CITY

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

KOCHAR     SEATTLE

KING       SEATTLE

B，select列表中c1.city有限定

scott@TESTDB>  select c1.custname,c1.city from customers c1 natural join customers c2

  2  on (c1.city=c2.city and c1.custname<>c2.custname);

on (c1.city=c2.city and c1.custname<>c2.custname)

*

ERROR at line 2:

ORA-00933: SQL command not properly ended

 

 

 C

scott@TESTDB>  select c1.custname,c1.city from customers c1 left outer join customers c2

  2  on (c1.city=c2.city and c1.custname<>c2.custname);

 

CUSTNAME   CITY

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

KOCHAR     SEATTLE

KING       SEATTLE

SMITH      NEW YORK

GREEN      BOSTON

 D

scott@TESTDB>  select c1.custname,c1.city from customers c1 full outer join customers c2

  2  on (c1.city=c2.city and c1.custname<>c2.custname);

 

CUSTNAME   CITY

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

KOCHAR     SEATTLE

KING       SEATTLE

SMITH      NEW YORK

GREEN      BOSTON

E

 scott@TESTDB>  select c1.custname,c1.city from customers c1 right outer join customers c2

  2  on (c1.city=c2.city and c1.custname<>c2.custname);

 

CUSTNAME   CITY

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

KING       SEATTLE

KOCHAR     SEATTLE