OCP-1Z0-051 第123题 JOIN中的USING子句，ON子句

一、原题

View the Exhibits and examine the structures of the PRODUCTS, SALES, and CUSTOMERS tables.

You need to generate a report that gives details of the customer's last name, name of the product, and the quantity sold for all customers in ' Tokyo' .
Which two queries give the required result? (Choose two.)
A. SELECT c.cust_last_name,p.prod_name, s.quantity_sold
        FROM sales s JOIN products p
       USING(prod_id)
          JOIN customers c
       USING(cust_id)
     WHERE c.cust_city='Tokyo';

B. SELECT c.cust_last_name, p.prod_name, s.quantity_sold
       FROM products p JOIN sales s JOIN customers c
            ON(p.prod_id=s.prod_id)
            ON(s.cust_id=c.cust_id)
    WHERE c.cust_city='Tokyo';

C. SELECT c.cust_last_name, p.prod_name, s.quantity_sold
        FROM products p JOIN sales s
             ON(p.prod_id=s.prod_id)
          JOIN customers c
             ON(s.cust_id=c.cust_id)
           AND c.cust_city='Tokyo';

D. SELECT c.cust_id,c.cust_last_name,p.prod_id, p.prod_name, s.quantity_sold
        FROM products p JOIN sales s
       USING(prod_id)
          JOIN customers c
      USING(cust_id)
    WHERE c.cust_city='Tokyo';

答案：AC

二、题目翻译

查看PRODUCTS、SALES和CUSTOMERS表的结构
要生成一个报表，显示在Tokyo的客户的last name,product name,和the quantity sold（销售数量）的详细信息。
哪两个给出所需结果？（选择2个）

三、题目解析

B选项不正确，语法错误。
D选项不正确，因为有USING子句，不能使用限定词。

四、测试

SQL> select d.deptno,e.ename
  2  from emp e join dept d
  3  using(deptno);
select d.deptno,e.ename
       *
ERROR at line 1:
ORA-25154: column part of USING clause cannot have qualifier

SQL> select deptno,ename
  2  from emp e join dept d
  3  using(deptno);

    DEPTNO ENAME
---------- ----------
        20 SMITH
        30 ALLEN
        30 WARD
        20 JONES
        30 MARTIN
        30 BLAKE
        10 CLARK
        20 SCOTT
        10 KING
        30 TURNER
        20 ADAMS
        30 JAMES
        20 FORD
        10 MILLER

14 rows selected.

      表连接的用法，详见：
             http://blog.csdn.net/holly2008/article/details/25704471