﻿ SQL: Departments where more than two employees are working

# SQL Exercise: Departments where more than two employees are working

## SQL JOINS: Exercise-29 with Solution

From the following tables write a SQL query to find the names of departments where more than two employees are employed. Return dpt_name.

Sample table: emp_department

```DPT_CODE DPT_NAME        DPT_ALLOTMENT
-------- --------------- -------------
57 IT                      65000
63 Finance                 15000
47 HR                     240000
27 RD                      55000
89 QC                      75000
```

Sample table: emp_details

``` EMP_IDNO EMP_FNAME       EMP_LNAME         EMP_DEPT
--------- --------------- --------------- ----------
127323 Michale         Robbin                  57
526689 Carlos          Snares                  63
843795 Enric           Dosio                   57
328717 Jhon            Snares                  63
444527 Joseph          Dosni                   47
659831 Zanifer         Emily                   47
847674 Kuleswar        Sitaraman               57
748681 Henrey          Gabriel                 47
555935 Alex            Manuel                  57
539569 George          Mardy                   27
733843 Mario           Saule                   63
631548 Alan            Snappy                  27
839139 Maria           Foster                  57
```

Sample Solution:

``````-- Selecting the department name from the result of an inner join between 'emp_details' and 'emp_department' tables
SELECT emp_department.dpt_name
-- Specifying the tables to retrieve data from ('emp_details' and 'emp_department')
FROM emp_details
-- Performing an inner join based on the equality of 'emp_dept' in 'emp_details' and 'dpt_code' in 'emp_department'
INNER JOIN emp_department
ON emp_dept = dpt_code
-- Grouping the results by the department name
GROUP BY emp_department.dpt_name
-- Applying a filter to the grouped results, including only those with a count greater than 2
HAVING COUNT(*) > 2;
``````

Output of the Query:

```dpt_name
Finance
IT
HR
```

Explanation:

The said SQL query is selecting the 'dpt_name' column from the emp_department table.
This code is joining the emp_details table with the emp_department table on the matching value in the 'emp_dept' column of the emp_details table and the 'dpt_code' column of the emp_department table.
The query also use GROUP BY clause that groups the result-set by one or more columns.
The HAVING clause is used in conjunction with the GROUP BY clause. It is used to filter groups based on a specified condition, in this case, the condition is COUNT(*) > 2, which means it will only return the groups that have more than 2 rows.
It returns the name of the department whose count of employees is more than 2.

Relational Algebra Expression:

Relational Algebra Tree:

## Query Visualization:

Duration:

Rows:

Cost:

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

Previous SQL Exercise: Find employees and departments with a given budget.
Next SQL Exercise: SQL SUBQUERIES Exercises Home

What is the difficulty level of this exercise?

Test your Programming skills with w3resource's quiz.

﻿