﻿ SQL: List the departments where atleast 2 employees work

# SQL Exercise: List the departments where atleast 2 employees work

## SQL employee Database: Exercise-102 with Solution

[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.

What is the difficulty level of this exercise?

Test your Programming skills with w3resource's quiz.

﻿