Given three tables: employees, departments, and projects, write a query to find the total salary expense for each department that has more than 5 employees working on projects. Show the department name and total salary.
In this post we are going to solve SQL Interview Questions and Answers which asked to experienced and fresher candidate.
To solve this query, we’ll break it down step-by-step, based on the given tables: employees, departments, and projects. Here’s how the problem can be approached.
Table of Contents
Assumptions About Table Structure:
- employees: contains employee details, including salary and department.
- employee_id: Unique identifier for an employee.
- department_id: The department to which the employee belongs.
- salary: The employee’s salary.
- departments: contains department details.
- department_id: Unique identifier for a department.
- department_name: The name of the department.
- projects: contains project assignments showing which employees are working on which projects.
- employee_id: The employee assigned to the project.
- project_id: Unique identifier for a project.
Steps Before writing the Query
We need to:
- Find the departments where more than 5 employees are working on projects.
- Calculate the total salary expense for these departments.
- Display the department name and total salary.
Steps to Achieve This:
- Join the three tables to relate employees, their departments, and their project assignments.
- Group by department to aggregate the number of employees working in each department on projects.
- Filter the departments that have more than 5 employees working on projects.
- Sum the salaries of employees in these departments.
SQL Query:
SELECT d.department_name, SUM(e.salary) AS total_salary
FROM employees e
JOIN departments d ON e.department_id = d.department_id
JOIN projects p ON e.employee_id = p.employee_id
GROUP BY d.department_name
HAVING COUNT(DISTINCT e.employee_id) > 5;
Analysis of the Query Execution
1. FROM Clause:
We start by specifying the tables we need data from. We’re using the employees, departments, and projects tables:
- The employees table provides the employee_id, salary, and department_id.
- The departments table gives the department_name.
- The projects table gives the employee_id for each project assignment.
2. JOIN Statements:
We join the tables to combine information across them:
- JOIN departments d ON e.department_id = d.department_id: This joins the employees table with the departments table using the department_id, allowing us to retrieve the department’s name.
- JOIN projects p ON e.employee_id = p.employee_id: This joins the employees table with the projects table using employee_id, so we can find employees working on projects.
3. GROUP BY Clause:
We use GROUP BY d.department_name to group the result set by the department name. This allows us to aggregate values (like salary) for each department.
4. HAVING Clause:
The HAVING COUNT(DISTINCT e.employee_id) > 5 filters the groups (departments) to only include those where more than 5 distinct employees are working on projects. This ensures that only departments with more than 5 employees on projects are included.
- COUNT(DISTINCT e.employee_id): Counts the number of unique employees working in a department on projects. The DISTINCT ensures we count each employee only once, even if they’re assigned to multiple projects.
5. SELECT Clause:
Finally, we select:
- d.department_name: To display the department name.
- SUM(e.salary) AS total_salary: To calculate the total salary expense for the employees working in that department.
Example:
Let’s assume we have the following simplified data:
Step By Step Query Execution Analysis:
For department_id = 1 (HR):
- Employees 1, 2, 3, 7, and 8 are assigned to projects.
- There are 5 employees, but the query filters for departments with more than 5 employees, so this department will be excluded from the result.
For department_id = 2 (IT):
- Employees 4 and 5 are assigned to projects.
- There are only 2 employees, so this department will be excluded from the result.
For department_id = 3 (Finance):
- Employee 6 is assigned to projects.
- There is only 1 employee, so this department will be excluded from the result.
In this case, no department satisfies the condition of having more than 5 employees working on projects, so no rows would be returned.