SQL queries with a unary relationship: Download the sample database emp‐self.accdb database from the book website. Write and run SQL code for each of the queries specified below. Make sure all your queries would still work correctly if two different employees can have the same first and last names.

A.      All cases in which an employee has a higher salary than his or her supervisor. Include the first name, last name, and salary for both the employee and the supervisor.

B.      The first name, last name, and salary of all supervisors, and the average salary of the employees they directly supervise (labeled AvgReportSalary).

C.      Show the employee first name, employee last name, supervisor first name, supervisor last name, and job title (not TitleID) for all employees who have the same job title as their supervisor. Display the list in alphabetical order by employee last name.

D.      A more advanced exercise: Enhance the query of part (a) to show the number of people directly supervised by the employee and the number of people directly supervised by the employee’s supervisor, in both cases showing “0” if there are no such people. (Hint: Use the query created in part (d) as if it were a table, a form of query chaining).