You are given two tables: Employee and Department. Each employee is associated with a department through the dept_id column. Write an SQL query to retrieve the number of employees in each department.Ensure that the result includes all departments, even those that currently have no employees.

Assuming Tables:

Department Table

| dept_id | department_name |

| ------- | --------------- |

| 1 | HR |

| 2 | IT |

| 3 | Finance |

| 4 | Marketing |

Employee Table

| employee_id | employee_name | dept_id |

| ----------- | ------------- | ------- |

| 101 | John | 1 |

| 102 | Alice | 2 |

| 103 | Bob | 2 |

| 104 | David | 3 |

| 105 | Emma | 2 |

SQL Query

SELECT

d.dept_id,

d.department_name,

COUNT(e.employee_id) AS employee_count

FROM department d

LEFT JOIN employee e

ON d.dept_id = e.dept_id

GROUP BY

d.dept_id,

d.department_name

ORDER BY d.dept_id;

This query joins department and employee tables using a LEFT JOIN to ensure all departments are listed and uses COUNT with GROUP BY to calculate the number of employees in each department.

Output

| dept_id | department_name | employee_count |

| ------- | --------------- | -------------- |

| 1 | HR | 1 |

| 2 | IT | 3 |

| 3 | Finance | 1 |

| 4 | Marketing | 0 |

LEFT JOIN ensures departments without employees are included

COUNT(employee_id) ignores NULL values, returning 0 when no employees exist

Answer

Assuming Tables: