（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博客

OCP 1Z0-051-127