Forum

Forums
SQL
Interview Questions...
Write an SQL Query ...
 
Share:
Notifications
Clear all

Write an SQL Query to Retrieve Departments with More Than Three Employees

 
    Last Post
RSS

Posts: 69
 Vinay Kumar
Guest
Topic starter
Nov 09, 2024 6:34 am
(@Vinay Kumar)
Trusted Member
Joined: 5 years ago

Introduction

In many real-world applications, businesses and organizations need to analyze their workforce distribution. One common requirement is identifying departments with more than three employees. This article provides a detailed SQL query to achieve this, explaining the logic and variations to cater to different database management systems (DBMS).

Understanding the Requirement

Before writing the SQL query, let’s clarify the objective:

  • We have a Departments table storing department details.

  • An Employees table contains information about employees and their respective departments.

  • We need to find departments that have more than three employees.

This can be done using SQL aggregation functions and the GROUP BY clause.

Database Schema

Consider the following table structures:

Employees Table

emp_id emp_name department_id
1 Alice 101
2 Bob 102
3 Charlie 101
4 David 103
5 Emma 101
6 Frank 102
7 Grace 101
8 Henry 103

Departments Table

department_id department_name
101 HR
102 IT
103 Finance

SQL Query to Retrieve Departments with More Than Three Employees

To retrieve the departments with more than three employees, we can use the COUNT() function along with GROUP BY and HAVING clauses:

SELECT d.department_id, d.department_name, COUNT(e.emp_id) AS employee_count
FROM Departments d
JOIN Employees e ON d.department_id = e.department_id
GROUP BY d.department_id, d.department_name
HAVING COUNT(e.emp_id) > 3;

Explanation:

  1. JOIN Departments and Employees Tables: We link both tables using department_id to fetch department details.

  2. COUNT() Function: Counts the number of employees per department.

  3. GROUP BY Clause: Groups records by department to compute the employee count per department.

  4. HAVING Clause: Filters only those departments where the employee count exceeds 3.

Query Output

Based on our sample data, the output will be:

department_id department_name employee_count
101 HR 4

Only the HR department appears in the result because it has 4 employees, while the IT and Finance departments have fewer than 3.

Alternative Approach Using Subqueries

Another way to achieve this is using a subquery:

SELECT department_id, department_name 
FROM Departments 
WHERE department_id IN (
    SELECT department_id FROM Employees 
    GROUP BY department_id 
    HAVING COUNT(emp_id) > 3
);

This approach first retrieves department IDs meeting the criteria and then fetches their names from the Departments table.

Performance Considerations

  • Indexing: Ensure indexes on department_id in both tables for efficient joins.

  • Database Engine Optimization: Some engines optimize HAVING queries better than subqueries.

  • Partitioning: If dealing with millions of records, partitioning can improve performance.

Real-World Use Cases

  • HR Analytics: Identify overstaffed departments for workforce planning.

  • Finance & Budgeting: Allocate resources efficiently based on department size.

  • Reporting Dashboards: Create reports showing departments with significant workforce.

Conclusion

Retrieving departments with more than three employees is a fundamental SQL operation. Using GROUP BY, HAVING, and JOIN, we can efficiently extract the required data. This approach applies to various RDBMS platforms like MySQL, PostgreSQL, SQL Server, and Oracle.

For more SQL-related tools and resources, check out these useful links:

For additional learning, refer to W3Schools SQL Guide.

This topic was modified 2 weeks ago 2 times by SQL Admin
Topic Tags
SQL query for department SQL query to filter depa Retrieve departments wit SQL group by department SQL having clause exampl

Leave a reply

Author Name

Author Email

Title *

Preview 0 Revisions Saved
Forum Jump:
  Previous Topic
Next Topic  
Topic Tags:  SQL query for department (1) , SQL query to filter depa (1) , Retrieve departments wit (1) , SQL group by department (1) , SQL having clause exampl (1) ,
Share: