Assume we have created a table employees with the columns: employee_id, employee_name, salary, designation and manager_id. Here employee_id is set as primary key and manager_id is a foreign key which refers to employee_id in the employees. Manju wants to display the employee details along with the manager id and manager name. Select which query suits the above requirement.
Answer options
A
select employee_id, employee_name, salary, employee_id "Mgr-id", employee_name "Mgr-
name" FROM employees;
B
select e.employee_id, e.employee_name, e.salary, m.employee_id "Mgr-id", m.employee_name
"Mgr-name" FROM employees e, employees m Where e.manager_id = m.manager_id;
C
select e.employee_id, e.employee_name, e.salary, m.employee_id "Mgr-id", m.employee_name
"Mgr-name" FROM employees e, employees m Where e.manager_id = m.employee_id;
D
select e.employee_id, e.employee_name, e.salary, m.manager_id "Mgr-id", m.employee_name
"Mgr-name" FROM employees e, employees m Where e.manager_id = m.employee_id;
Correct answer: select e.employee_id, e.employee_name, e.salary, m.employee_id "Mgr-id", m.employee_name "Mgr-name" FROM employees e, employees m Where e.manager_id = m.employee_id;
Explanation
The correct self-join condition is e.manager_id = m.employee_id (employee's manager_id links to the manager's employee_id). Option 2 correctly selects e.employee_id, e.employee_name, e.salary, and manager's employee_id and employee_name using this condition.