OCP 1Z0 051 QUESTION NO: 23

QUESTION NO: 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” 

Answer: C,E

DROP TABLE employees PURGE;CREATE TABLE employeesASSELECT e.first_name || '' || e.last_name AS emp_name, e.department_id  FROM hr.employees e WHERE rownum <= 5;DROP TABLE departments PURGE;CREATE TABLE departments ASSELECT d.department_name AS dept_name, d.department_id  FROM hr.departments d;

当join中使用using子句时，using里出现的列，在整个查询中都不能加前缀。
所以答案应该是B

SQL> SELECT e.emp_name, d.dept_name  2    FROM employees e  3    JOIN departments d  4   USING (department_id)  5   WHERE d.department_id NOT IN (10, 40)  6   ORDER BY dept_name;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_nameORA-25154: column part of USING clause cannot have qualifier

SQL> SELECT e.emp_name, d.dept_name  2    FROM employees e  3    JOIN departments d  4   USING (department_id)  5   WHERE department_id NOT IN (10, 40)  6   ORDER BY dept_name;EMP_NAME                                      DEPT_NAME--------------------------------------------- ------------------------------PatFay                                        MarketingMichaelHartstein                              MarketingDouglasGrant                                  ShippingDonaldOConnell                                Shipping4 rows selected