oracle 自联接

127. Examine the data in the CUSTOMERStable:

CUSTNO  CUSTNAME  CITY

1    KING    SEATTLE

2    GREEN   BOSTON

3    KOCHAR   SEATTLE

4    SMITH   NEW YORK

You want to list all cities that havemore 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 ANDc1.custname<>c2.custname);

Which two JOIN options can be used inthe 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 

SQL> select c1.custname,c1.city from Customers c1
  2  join
  3  Customers c2
  4  on (c1.city=c2.city and c1.custname<>c2.custname);

CUSTNAME   CITY
---------- ----------
KOCHAR     SEATTLE
KING       SEATTLE

SQL> select c1.custname,c1.city from Customers c1
  2  left outer join
  3    Customers c2 on (c1.city=c2.city and c1.custname<>c2.custname);

CUSTNAME   CITY
---------- ----------
KOCHAR     SEATTLE
KING       SEATTLE
GREEN      BOSTON
SMITH      NEW YORL

SQL> select c1.custname,c1.city from Customers c1
  2  right outer join 
  3    Customers c2 on (c1.city=c2.city and c1.custname<>c2.custname);

CUSTNAME   CITY
---------- ----------
KING       SEATTLE
KOCHAR     SEATTLE

SQL> select c1.custname,c1.city from Customers c1
  2  full outer join
  3   Customers c2 on (c1.city=c2.city and c1.custname<>c2.custname);

CUSTNAME   CITY
---------- ----------
KOCHAR     SEATTLE
KING       SEATTLE
GREEN      BOSTON
SMITH      NEW YORL

SQL> select c1.custname,c1.city from Customers c1
  2  natural join 
  3  Customers c2 on (c1.city=c2.city and c1.custname<>c2.custname);
Customers c2 on (c1.city=c2.city and c1.custname<>c2.custname)
             *
ERROR at line 3:
ORA-00933: SQL command not properly ended

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