Question

The Employee table consists of the following data fields: Employee ID, Employee Name, Employee Department, Employee Manager (EMP_ID of the Employee to whom the employee reports) and Employee DOH (Date of Hire). Using the following data table, answer the questions with appropriate SQL queries.

EMP_ID	EMP_NAME	EMP_DEPT	EMP_MANAGER	EMP_DOH
1	John	IT	9	05-08-2010
2	Alex	Finance	0	06-03-2008
3	Linda	IT	9	07-02-2010
4	Robin	Purchase	8	08-12-2010
5	Maki	Purchase	8	09-08-2012
6	Ross	Sales	7	10-05-2012
7	Reiki	Sales	2	11-02-2009
8	Anna	Sales	2	12-08-2011
9	Jena	IT	2	01-01-2008
10	Bonny	IT	9	01-01-2008

Find the manager with the most reportees.

List managers who joined after all their reportees.

  Which department has the most managers, and how many?

Answer 1

1)

SELECT M.* FROM EMPLOYEE E, EMPLOYEE M WHERE E.EMP_MANAGER = M.EMP_ID GROUP BY M.EMP_ID ORDER BY COUNT(*) DESC LIMIT 1;

2)

SELECT * FROM EMPLOYEE M WHERE EMP_DOH > (SELECT MAX(EMP_DOH) FROM EMPLOYEE E WHERE E.EMP_MANAGER = M.EMP_ID);

3)

SELECT EMP_DEPT, COUNT(DISTINCT EMP_MANAGER) FROM EMPLOYEE GROUP BY EMP_DEPT;