Create the following statistical report for the HR departmen

Create the following statistical report for the HR department: Include the department number, department name, and the number of employees in each department that: Has the highest number of employees. Hint: Use a join with a GROUP BY clause and a HAVING clause. Use a subquery in the HAVING clause. This is a brain buster! Code up your subquery first to look at its results, then add it to your main query.

Solution

SELECT d.deptNo,d.deptName,COUNT(e.empId) as empCount FROM employee e
inner join department d
on e.deptId = d.deptId
GROUP BY d.deptId
HAVING COUNT(*) = (SELECT MAX(COUNT(*)) FROM employee e GROUP BY e.deptId)

Note: I do not have schema, but I have assumed a relation between employee and department table to give you this query.