Exam 1z0-071 topic 1 question 41 discussion

C may contain employees without a mamager , that is, the bosa of the bosses ;)

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.

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.

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.