Examine the description of the EMPLOYEES table:
Which two queries return rows for employees whose manager works in a different department? (Choose two.)
A.
B.
C.
D.
E.
Exam 1z0-071 All QuestionsBrowse all questions from this exam
Question 41
Correct Answer:
The correct queries to return rows for employees whose managers work in a different department are B and E. The query in B performs an inner join on the employees table with itself, matching employees to their managers and ensuring they are in different departments, thereby providing the desired results. The query in E performs a right join and then filters out any rows where the employee ID is null, ensuring that only rows where the employee's manager works in a different department are returned.
Discussion
NI_maruf
Can anyone please explain why option E is the right answer here? why not C?
Shrimathi
create table emp(emp_id number,dept_id number,manager_id number); insert all into emp values(1,5,50) into emp values(2,5,50) into emp values(3,5,5) into emp values(50,5,null) into emp values(5,4,null) select * from dual; commit; select e.* from emp e right join emp m on e.manager_id=m.emp_id and e.dept_id<>m.dept_id where e.emp_id is not null; output: 3 5 5 E is correct, since it is mentioned e.employee_id is not null Right out join it will return matched row of both table and unmatch row that is all row from the emp m table. e.emp_id is not null this condition retrieves only the employee whose manager works in different department.
Lydia1054
C has to change AND to WHERE then the result will be correct. Use AND then the emp.department_id <> mgr.department_id is part of the JOIN and it will show all the rows in the left table, it not filter out any rows.
Lydia1054
To the E part. AND is part of JOIN and it is RIGHT JOIN, so in the left table (emp) only the result meet the condition emp.manager_id = mgr.employee_id and mgr.department_id <> emp.department_id will show up in the left table; otherwise will be NULL. Therefore, adding WHERE to filter the NULL out of the result.
ogi33
C may contain employees without a mamager , that is, the bosa of the bosses ;)