Question

An Employees table was added to the JustLee Books database to track employee information. Display a list of each employee's name, job title, and manager's name. Use column aliases to clearly identify employee and manager name values. Include all employees in the list and sort by manager name.

Answer 1

Suppose JustLee Books database contains an Employee table with fields emp_id, name, job_title, mgr_id (at minimum) where emp_id is the primary key and mgr_id is the foreign key which references the Employee table .

SQL query to display a list of each employee's name, job title, and manager's name :

select e.name as "Employee Name", e.job_title as "Job Title", m.name as "Manager Name" from Employee e, m where e.mgr_id = m.emp_id sort by m.name;

The above statement displays the employee name, job title and manager name of all employees in the Employee table and the results are sorted by manager name. To obtain manager name from Employee table we join Employee table with itself using mgr_id and emp_id fields since both of them refers to emp_id of an employee.