SQL Query to Find Departments with More Than 5 Employees

Understanding the Requirement

We need to write a SQL query that identifies departments that have more than 5 employees. This involves:

1. Counting employees per department
2. Filtering departments based on the count
3. Displaying the relevant department information

Database Schema Setup

First, let's create sample tables with dummy data to demonstrate this query.

-- Create departments table
CREATE TABLE departments (
    dept_id INT PRIMARY KEY,
    dept_name VARCHAR(50) NOT NULL,
    location VARCHAR(50)
);

-- Create employees table
CREATE TABLE employees (
    emp_id INT PRIMARY KEY,
    emp_name VARCHAR(50) NOT NULL,
    dept_id INT,
    salary DECIMAL(10,2),
    hire_date DATE,
    FOREIGN KEY (dept_id) REFERENCES departments(dept_id)
);

-- Insert dummy data into departments
INSERT INTO departments (dept_id, dept_name, location) VALUES
(1, 'IT', 'New York'),
(2, 'HR', 'Chicago'),
(3, 'Finance', 'Boston'),
(4, 'Marketing', 'Los Angeles'),
(5, 'Operations', 'Seattle');

-- Insert dummy data into employees
INSERT INTO employees (emp_id, emp_name, dept_id, salary, hire_date) VALUES
(101, 'John Smith', 1, 75000, '2020-01-15'),
(102, 'Jane Doe', 1, 82000, '2019-05-22'),
(103, 'Robert Johnson', 1, 68000, '2021-03-10'),
(104, 'Emily Davis', 1, 91000, '2018-11-05'),
(105, 'Michael Brown', 1, 79000, '2020-07-30'),
(106, 'Sarah Wilson', 1, 85000, '2019-09-12'),
(107, 'David Taylor', 2, 72000, '2021-02-18'),
(108, 'Jessica Martinez', 2, 65000, '2022-01-25'),
(109, 'James Anderson', 2, 78000, '2020-06-14'),
(110, 'Lisa Thomas', 3, 88000, '2019-04-03'),
(111, 'Daniel Jackson', 3, 95000, '2018-08-19'),
(112, 'Karen White', 3, 82000, '2021-07-22'),
(113, 'Matthew Harris', 3, 76000, '2020-05-11'),
(114, 'Nancy Martin', 4, 69000, '2022-03-08'),
(115, 'Kevin Garcia', 4, 81000, '2021-09-15'),
(116, 'Laura Robinson', 5, 74000, '2020-12-01'),
(117, 'Steven Clark', 5, 87000, '2019-11-27');

The Solution Query

Here's the SQL query to find departments with more than 5 employees:

SELECT 
    d.dept_id,
    d.dept_name,
    d.location,
    COUNT(e.emp_id) AS employee_count
FROM 
    departments d
JOIN 
    employees e ON d.dept_id = e.dept_id
GROUP BY 
    d.dept_id, d.dept_name, d.location
HAVING 
    COUNT(e.emp_id) > 5
ORDER BY 
    employee_count DESC;

Explanation of the Query

1. SELECT clause: We select department ID, name, location, and count of employees
2. FROM and JOIN: We join departments with employees on department ID
3. GROUP BY: We group results by department attributes to get counts per department
4. HAVING: This filters groups to only include departments with more than 5 employees
5. ORDER BY: Sorts results by employee count in descending order

Expected Output

Based on our dummy data, the output would be:

dept_id | dept_name | location  | employee_count
--------|-----------|-----------|---------------
1       | IT        | New York  | 6

Only the IT department has more than 5 employees (exactly 6 in our dummy data).

Alternative Query (Using Subquery)

Here's another way to write the same query using a subquery:

SELECT 
    d.dept_id,
    d.dept_name,
    d.location,
    (SELECT COUNT(*) FROM employees e WHERE e.dept_id = d.dept_id) AS employee_count
FROM 
    departments d
WHERE 
    (SELECT COUNT(*) FROM employees e WHERE e.dept_id = d.dept_id) > 5
ORDER BY 
    employee_count DESC;

This version might be less efficient for large datasets but demonstrates another approach to solving the problem.

Conclusion

The query effectively identifies departments exceeding a specific employee count threshold by combining grouping, aggregation, and filtering techniques in SQL. The HAVING clause is particularly important here as it allows us to filter based on aggregate functions.