SQL Exercise: List the number of employees in each department

SQL employee Database: Exercise-101 with Solution

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

101. From the following table, write a SQL query to identify departments with fewer than four employees. Return department ID, number of employees.

Sample table: employees

Sample Solution:

SELECT dep_id,
       count(*)
FROM employees
GROUP BY dep_id
HAVING count(*)<4;

Sample Output:

 dep_id | count
--------+-------
   1001 |     3
(1 row)

Explanation:

The said query in SQL that retrieves the "dep_id" column and counts the number of rows in the 'employees' table for each unique "dep_id".

The HAVING clause include only those groups where the count of rows is less than 4.

Relational Algebra Expression:

Relational Algebra Tree:

Practice Online

Sample Database: employee

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

Previous SQL Exercise: Display department, grade, and number of SALESMEN.
Next SQL Exercise: List the departments where atleast 2 employees work.