Write SQL Query to get Employee Name, Manager ID and number of employees in the department?

Given Employee Table:
1 | John | 3 | 10
2 | Smith | 3 | 10
3 | Jane | 4 | 20

Answer: We can use WITH clause and SELF JOIN to get the required data. By WITH clause we get the count of employees in each department. Then we use SELF JOIN to get name of Manager because manager is also an employee.

Query will be as follows:

WITH d_count AS (
SELECT deptID, COUNT(*) AS d_count
FROM employee
GROUP BY deptno)
SELECT e.name AS Employee_name,
m.name AS Manager_name
dc.d_count AS Dept_count
FROM employee e,
d_count dc,
employee m
WHERE e.deptID = dc.deptID
AND e.mgrID = m.ID;