OCP 1Z0 051 127

来源：互联网发布：mac版梦幻西游更新失败编辑：程序博客网时间：2024/04/20 04:34

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

NATURAL JOIN 后不能有 on子句

SQL> SELECT c1.custname, c1.city  2    FROM test.customers c1  3    NATURAL JOIN  test.customers c2  4      ON (c1.city = c2.city AND c1.custname <> c2.custname);SELECT c1.custname, c1.city  FROM test.customers c1  NATURAL JOIN  test.customers c2    ON (c1.city = c2.city AND c1.custname <> c2.custname)ORA-00933: SQL 命令未正确结束

LEFT JOIN 以左表为主，条件c1.custname<>c2.custname起不到过滤作用

SQL> SELECT c1.custname, c1.city  2    FROM test.customers c1  3    LEFT JOIN  test.customers c2  4      ON (c1.city = c2.city AND c1.custname <> c2.custname);CUSTNAME CITY-------- --------KOCHAR   SEATTLEKING     SEATTLESMITH    NEW YORKGREEN    BOSTON4 rows selected

同样，这儿FULL JOIN 也没有过滤作用

SQL> SELECT c1.custname, c1.city  2    FROM test.customers c1  3    FULL OUTER JOIN   test.customers c2  4      ON (c1.city = c2.city AND c1.custname <> c2.custname);CUSTNAME CITY-------- --------KOCHAR   SEATTLEKING     SEATTLESMITH    NEW YORKGREEN    BOSTON6 rows selected

SQL> SELECT c1.custname, c1.city  2    FROM test.customers c1  3    JOIN  test.customers c2  4      ON (c1.city = c2.city AND c1.custname <> c2.custname);CUSTNAME CITY-------- --------KOCHAR   SEATTLEKING     SEATTLE2 rows selected

SQL> SELECT c1.custname, c1.city  2    FROM test.customers c1  3    RIGHT OUTER JOIN   test.customers c2  4      ON (c1.city = c2.city AND c1.custname <> c2.custname);CUSTNAME CITY-------- --------KING     SEATTLEKOCHAR   SEATTLE4 rows selected

Answer: AE

严格来说，该题中所述语句有问题
我们先新增一条

SQL> INSERT INTO test.CUSTOMERS  2  (CUSTNO,CUSTNAME,CITY)  3  VALUES(5,'TEST','SEATTLE');1 row insertedExecuted in 0.016 seconds

SQL> SELECT c1.custname, c1.city  2    FROM test.customers c1  3    JOIN test.customers c2  4      ON (c1.city = c2.city AND c1.custname <> c2.custname);CUSTNAME CITY-------- --------TEST     SEATTLEKOCHAR   SEATTLETEST     SEATTLEKING     SEATTLEKOCHAR   SEATTLEKING     SEATTLE6 rows selected

可以看到数据重复了，应用下面方法查询

SQL> SELECT c1.custname, c1.city  2    FROM test.customers c1  3   WHERE EXISTS (SELECT NULL  4            FROM test.customers c2  5           WHERE c1.city = c2.city  6             AND c1.custname <> c2.custname);CUSTNAME CITY-------- --------TEST     SEATTLEKOCHAR   SEATTLEKING     SEATTLE3 rows selected

0 0