SQL Exercises: Find departments with more than two employees

SQL SUBQUERY: Exercise-38 with Solution

38. From the following tables write a SQL query to find which departments have more than two employees. Return dpt_name.

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

 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 'dpt_name' column from the 'emp_department' table
SELECT dpt_name FROM emp_department
-- Filtering the results based on the condition that 'dpt_code' is in the set of 'emp_dept' values returned by a subquery
  WHERE dpt_code IN
  (
    -- Subquery: Selecting 'emp_dept' values from 'emp_details' and grouping by 'emp_dept' with a condition on the count of occurrences
    SELECT emp_dept
      FROM emp_details
      GROUP BY emp_dept
      HAVING COUNT(*) > 2
  );

Output of the Query:

dpt_name
IT
HR
Finance

Explanation:

The said SQL query that retrieves the names of departments from the 'emp_department' table where the department code is found in a subquery.
Using the subquery, all employees' department codes are selected from the 'emp_details' table, grouped by department, and then only those with more than two employees are selected.
The main query then uses the IN operator to check if the department codes from the subquery match any department codes in the 'emp_department' table and returns the corresponding department names.

Visual Explanation:

Practice Online

Sample Database:

Query Visualization:

Duration:

Rows:

Cost:

Contribute your code and comments through Disqus.

Previous SQL Exercise: Departments with a sanction amount higher than average.
Next SQL Exercise: Employees working with the lowest sanction amount.