Which statement lists the ID, name, and salary of the employee, and the ID and name of the employee’s manager, for all the employees who have a manager and earn more than 4000?

Click the Exhibit button to examine the data of the EMPLOYEES table.

Which statement lists the ID, name, and salary of the employee, and the ID and name of the
employee’s manager, for all the employees who have a manager and earn more than 4000?

A.
SELECT employee_id “Emp_id”, emp_name “Employee”,
salary,
employee_id “Mgr_id”, emp_name “Manager”
FROM employees
WHERE salary > 4000;

B.
SELECT e.employee_id “Emp_id”, e.emp_name “Employee”,
e.salary,
m.employee_id “Mgr_id”, m.emp_name “Manager”
FROM employees e, employees m
WHERE e.mgr_id = m.mgr_id
AND e.salary > 4000;

C.
SELECT e.employee_id “Emp_id”, e.emp_name “Employee”,
e.salary,
m.employee_id “Mgr_id”, m.emp_name “Manager”
FROM employees e, employees m
WHERE e.mgr_id = m.employee_id
AND e.salary > 4000;

D.
SELECT e.employee_id “Emp_id”, e.emp_name “Employee”,
e.salary,
m.mgr_id “Mgr_id”, m.emp_name “manager”
FROM employees e, employees m
WHERE e.mgr_id = m.employee_id
AND e.salary > 4000;

E.
SELECT e.employee_id “Emp_id”, e.emp_name “Employee”,
e.salary,
m.mgr_id “Mgr_id”, m.emp_name “Manager”
FROM employees e, employees m
WHERE e.employee_id = m.employee_id
AND e.salary > 4000;

Explanation:

This statement lists the ID, name, and salary of the employee, and the ID and name of the
employee’s manager, for all the employees who have a manager and earn more than 4000
Incorrect Answers:
A: This statement does not check does employee have a manager or not, so it will not provide
correct result.
B: Usage of “e.mgr_id = m.mgr_id” condition is wrong to achieve required result.
D: This statement uses “m.mgr_id” to show manager’s manager, not employ’s manager.
E: Usage of “WHERE e.employee_id = m.employee_id” condition is wrong to achieve required
result.OCP Introduction to Oracle 9i: SQL Exam Guide, Jason Couchman, p. 118-122Chapter 3:
Advanced Data Selection in Oracle