SQL subqueries on employee Database: List the details of the departments where maximum number of employees are working

SQL subqueries on employee Database: Exercise-29 with Solution

[An editor is available at the bottom of the page to write and execute the scripts.]

29. From the following table, write a SQL query to find those departments where maximum number of employees work. Return department ID, department name, location and number of employees.

Sample table: employees

Sample table: department

Sample Solution:

SELECT d.dep_id,
       d.dep_name,
       d.dep_location,
       count(*)
FROM employees e,
     department d
WHERE e.dep_id = d.dep_id
GROUP BY d.dep_id
HAVING count(*) =
  (SELECT MAX (mycount)
   FROM
     (SELECT COUNT(*) mycount
      FROM employees
      GROUP BY dep_id) a);

OR

SELECT *
FROM department
WHERE dep_id IN
    (SELECT dep_id
     FROM employees
     GROUP BY dep_id
     HAVING count(*) IN
       (SELECT MAX (mycount)
        FROM
          (SELECT COUNT(*) mycount
           FROM employees
           GROUP BY dep_id) a));

Sample Output:

 dep_id | dep_name  | dep_location | count
--------+-----------+--------------+-------
   3001 | MARKETING | PERTH        |     6
(1 row)

Practice Online

Structure of employee Database:

Have another way to solve this solution? Contribute your code (and comments) through Disqus.

Previous: From the following table, write a SQL query to find those employees of department 1001 and whose salary is more than the average salary of employees in department 2001. Return complete information about the employees.
Next: From the following table, write a SQL query to find those employees whose manager is JONAS. Return complete information about the employees.