OCP 1Z0-051-127

（127）Examine the data in the CUSTOMERS table:


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

答案：（A、E）

解析：

-- 实验数据 CREATE TABLE Customers (   custno NUMBER(2),   custname VARCHAR2(10),   city VARCHAR2(10) ); INSERT INTO Customers VALUES(1, 'KING', 'SEATTLE'); INSERT INTO Customers VALUES(2, 'GREEN', 'BOSTON'); INSERT INTO Customers VALUES(3, 'KOCHAR', 'SEATTLE'); INSERT INTO Customers VALUES(4, 'SMITH', 'NEWYORK'); -- B（X）, 因为有不等于比较，所以不能使用自然连接 -- C（X）,左外连接结果不正确 scott@TESTDB11>select c1.custname, c1.city   2  from customers c1 left outer join customers c2   3  on(c1.city = c2.city and c1.custname <> c2.custname); CUSTNAME   CITY ---------- ---------- KOCHAR     SEATTLE KING       SEATTLE GREEN      BOSTON SMITH      NEWYORK -- E（X）,右外连接，结果正确(很巧妙) scott@TESTDB11>select c1.custname, c1.city   2  from customers c1 right outer join customers c2   3  on(c1.city = c2.city and c1.custname <> c2.custname); CUSTNAME   CITY ---------- ---------- KING       SEATTLE KOCHAR     SEATTLE

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/17013648/viewspace-1167126/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/17013648/viewspace-1167126/