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�
Explanation:
Prefix the column in the ORDER BY Clause would cause the statement to succeed, assuming that
the statement failed because the dept_name existed in employee & department tables.Not C: Removing the alias from the columns in the SELECT clause would cause the Statement to
fail if the columns existing in both tables.
correct answers: B,E
rules:Do not qualify a column that is used in the USING clause.
If the same column is used elsewhere in the SQL statement, do not alias it.
more explanation why it is B not C:
The columns that are common in both the tables, but not used in the USING clause, must be prefixed with a table alias.
ANS : B only
Ans.: A, E
A – The statement fails, so if you remove “ORDER BY dept_name;” it should help….
THE ANSWER IS B….I tried the following query and it worked
SELECT e.last_name, d.department_name
FROM employees e
JOIN departments d
USING (department_id)
WHERE department_id NOT IN (10,40)
ORDER BY department_name;
Yes u r right
@kabelo, You are right. Excellent!!!!!
The same opinion with me!
Column (or columns) appear in Using(…) clause cannot be qualified by table name.
I press the Answer button with amazing feeling, but read some comments and test with real code the answer is B.
only B is correct
Yop, also from my testing b is fixes the issue, regarding e, order by works for both with alias and without alias column name.
SELECT e.last_name, d.DEPARTMENT_NAME
FROM employees e
JOIN departments d
USING (department_id)
WHERE department_id NOT IN (10,40)
ORDER BY MANAGER_ID;
i try to execute this query, it generate an error because manager_id exist in both tables, and column for join in using clause, could not be used with qualifier
so that
Answer: B, E
Only option B will rectify the above query, no other option is needed.
b is the only correct answer.
Indeed! “B” is the only one
Please explain why E is correct?
prefixed column in ORDER BY is needed depending on what column you will display.
As for this example table alias was not needed in department_name, it only exist in departments table. Assuming we all have the same HR’s table structure.
B is the only one Correct Answer.
B is not the correct one, here is the example that prefix alias works:
SQL> select t.col1
from tab1 t
where t.col1 > 3;
COL1
———-
4
5
6
7
only column alias can’t be used in where clause like here:
SQL> select t.col1 as TEST from tab1 t where TEST > 3;
select t.col1 as TEST from tab1 t where TEST > 3
*
ERROR at line 1:
ORA-00904: “TEST”: invalid identifier
Only E is correct.
Only “E” is the correct answer:
SQL> l
1 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
SQL> /
WHERE d.department_id NOT IN (10,40)
*
ERROR at line 5:
ORA-25154: column part of USING clause cannot have qualifier
FIRST_NAME DEPARTMENT_NAME
——————– ——————————
Donald Shipping
TJ Shipping
Joshua Shipping
Randall Shipping
Hazel Shipping
Trenna Shipping
Michael Shipping
Nandita Shipping
John Shipping
Stephen Shipping
Martha Shipping
FIRST_NAME DEPARTMENT_NAME
——————– ——————————
Winston Shipping
Peter Shipping
Shanta Shipping
Alana Shipping
Sarah Shipping
104 rows selected.
SQL> l
1 SELECT e.FIRST_NAME , d.DEPARTMENT_NAME
2 FROM employees e
3 JOIN departments d
4 USING (department_id)
5 WHERE department_id NOT IN (10,40)
6* ORDER BY DEPARTMENT_NAME
SQL>
I change the column names by match with my tables.
Correct answer B — works in all the cases;
There are several cases : if both employees,departments table has a column dept_name the select must be prefixed but the order by can not be prefixed (it will take department name from select columns). If employees table does not have a dept_name column then you do not have to prefix the select columns.
Regards,
The answer is B, it can be the E although it is not neccesary.
According to SQL Fundamentals Oracle 11g “Do not qualify a column that is used in the USING clause. If the same column is used elsewhere in the SQL statement, do not alias it”
I tried all the given options. as both the tables are available in HR schema, just adjusted the dept_name with department_name and emp_name by FIRST_NAME
Error with A:
SQL> SELECT e.FIRST_NAME, d.department_name
2 FROM employees e
3 JOIN departments d
4 USING (department_id)
5 WHERE d.department_id NOT IN (10,40);
WHERE d.department_id NOT IN (10,40)
*
ERROR at line 5:
ORA-25154: column part of USING clause cannot have qualifier
Option B executes well.
Option C :
SQL> SELECT FIRST_NAME, department_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 department_name;
WHERE d.department_id NOT IN (10,40)
*
ERROR at line 5:
ORA-25154: column part of USING clause cannot have qualifier
Option D :
SQL> SELECT e.FIRST_NAME, d.department_name
2 FROM employees e
3 JOIN departments d
4 USING (d.department_id)
5 WHERE d.department_id NOT IN (10,40)
6 ORDER BY department_name;
USING (d.department_id)
*
ERROR at line 4:
ORA-01748: only simple column names allowed here
E and F also shares the same result as A,C.
Thus only B is correct.
corect is B !!! tested
describe employees;
describe departments;
SELECT e.first_name, d.department_name
FROM employees e
JOIN departments d
USING (department_id)
WHERE d.department_id NOT IN (10,40)
ORDER BY department_name;
Error starting at line : 3 in command –
SELECT e.first_name, d.department_name
FROM employees e
JOIN departments d
USING (department_id)
WHERE d.department_id NOT IN (10,40)
ORDER BY department_name
Error at Command Line : 7 Column : 7
Error report –
SQL Error: ORA-25154: column part of USING clause cannot have qualifier
25154. 00000 – “column part of USING clause cannot have qualifier”
*Cause: Columns that are used for a named-join (either a NATURAL join
or a join with a USING clause) cannot have an explicit qualifier.
*Action: Remove the qualifier. – from where!!!!