sqlbeginner10 minutes

Create a Simple Employee Department Summary Query

Build a SQL query to summarize employee data by department with basic aggregation and grouping techniques.

Challenge prompt

You are given two tables: Employees and Departments. - Employees(employee_id, name, department_id, salary) - Departments(department_id, department_name) Write a SQL query to display each department's name alongside the number of employees working there and the average salary of these employees. Include all departments even if they have no employees (show zero count and NULL for average salary in that case). Order the results by department name alphabetically.

Guidance

• Use a LEFT JOIN to ensure all departments appear even if no employees are assigned.
• Group rows by department_id and department_name for aggregation.
• Use COUNT() to get the total employees and AVG() for the average salary.

Hints

• Remember to use COALESCE to convert NULL counts to zero if needed.
• Use GROUP BY clause to aggregate data per department.

Starter code

SELECT d.department_name,
       COUNT(e.employee_id) AS employee_count,
       AVG(e.salary) AS average_salary
FROM Departments d
LEFT JOIN Employees e ON d.department_id = e.department_id
GROUP BY d.department_id, d.department_name
ORDER BY d.department_name;

Expected output

A table with columns: department_name, employee_count, average_salary Example: | department_name | employee_count | average_salary | |-----------------|----------------|----------------| | Accounting | 3 | 55000 | | HR | 2 | 47000 | | IT | 0 | NULL |

Core concepts

SQL JOINGROUP BYAggregation functions

Challenge a Friend

Send this duel to someone else and see if they can solve it.