OCP-1Z0-051 补充题库 第23题 表连接中别名的使用

一、原题

Evaluate this SQL statement:
SELECT e.emp_name, d.dept_name
FROM employees e JOIN departments d
USING (department_id)
WHERE d.department_id NOT IN (10,40)
ORDER BY dept_name;
The statement fails when executed. Which change fixes the error?
A. remove the ORDER BY clause
B. remove the table alias prefix from the WHERE clause
C. remove the table alias from the SELECT clause
D. prefix the column in the USING clause with the table alias
E. prefix the column in the ORDER BY clause with the table alias
F. replace the condition
     ”d.department_id NOT IN (10,40)”
      in the WHERE clause with
      ”d.department_id <> 10 AND d.department_id <> 40”

答案: C,E
(题库中的答案是CE，但我的实验答案是B，欢迎大家讨论)

二、题目翻译

下面的SQL语句:
SELECT e.emp_name, d.dept_name
FROM employees e JOIN departments d
USING (department_id)
WHERE d.department_id NOT IN (10,40)
ORDER BY dept_name;
会执行失败，下面哪个变动能修正这个错误?
A.删除ORDER BY子句。
B.删除WHERE子句中表名前的别名。
C.删除SELECT子句中表的别名。
D.USING子句中列名前加表的别名。
E.ORDER BY 子句中列名前加表的别名。
F.用”d.department_id <> 10 AND d.department_id <> 40”替换WHERE子句中的”d.department_id NOT IN (10,40)”。

三、题目解析

      直接使用HR用户中的示例表做测试,因为列名有所不同，将上面的emp_name改成了FIRST_NAME，dept_name改成了DEPARTMENT_NAME。

实验如下：

SQL> SELECT e.FIRST_NAME,d.DEPARTMENT_NAME       
  2  FROM employees e JOIN departments d
  3  USING (department_id)
  4  WHERE d.department_id NOT IN (10,40)
  5  ORDER BY d.DEPARTMENT_NAME;
WHERE d.department_id NOT IN (10,40)
      *
ERROR at line 4:
ORA-25154: column part of USING clause cannot have qualifier

 

      两张表连接使用的是两张表都有的字段department_id，所以在使用这列的时候，前面不能带表别名，所以去掉WHERE子句中的表别名即可。

 

SQL> SELECT e.FIRST_NAME,d.DEPARTMENT_NAME       
  2  FROM employees e JOIN departments d
  3  USING (department_id)
  4  WHERE department_id NOT IN (10,40)
  5  ORDER BY d.DEPARTMENT_NAME;

FIRST_NAME                               DEPARTMENT_NAME
---------------------------------------- ----------------
Shelley                                  Accounting
William                                  Accounting
Lex                                      Executive
Neena                                    Executive
Steven                                   Executive
.....
Alana                                    Shipping
Douglas                                  Shipping

104 rows selected.

        所以，答案应该是B。