SQL Exercise: List the departments where atleast 2 employees work

Last update on December 20 2024 11:39:59 (UTC/GMT +8 hours)

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

102. From the following table, write a SQL query to find which departments have at least two employees. Return department name, number of employees.

Sample table: employees

Sample table: department

Sample Solution:

SELECT d.dep_name,
       count(*)
FROM employees e,
     department d
WHERE e.dep_id = d.dep_id
GROUP BY d.dep_name
HAVING count(*) >= 2;

Sample Output:

 dep_name  | count
-----------+-------
 FINANCE   |     3
 MARKETING |     6
 AUDIT     |     5
(3 rows)s

Explanation:

The said query in SQL that selects the "dep_name" and counts the number of rows in each department from the ‘employees’ and 'department' tables that have a matching "dep_id" columns in both the tables.

The WHERE clause includes rows in the result where the "dep_id" in the 'employees' table matches the "dep_id" in the 'department' table.

The HAVING clause only includes the groups where the count of rows is greater than or equal to 2 that is departments that have at least 2 employees.

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: List the number of employees in each department.
Next SQL Exercise: Check if all the employees numbers are indeed unique.