MySQL 8.0

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE `departments` (
department_id INT(2) NOT NULL AUTO_INCREMENT,
department_name VARCHAR(30) NOT NULL,
PRIMARY KEY (department_id),
UNIQUE (department_name));

CREATE TABLE `employees` (
employee_id INT(4) NOT NULL AUTO_INCREMENT,
employee_email VARCHAR(30) NOT NULL,
employee_first_name VARCHAR(30) NOT NULL,
employee_last_name VARCHAR(30) NOT NULL,
department_name VARCHAR(30) NOT NULL,
PRIMARY KEY (employee_id),
UNIQUE (employee_email),
FOREIGN KEY (department_name)
    REFERENCES departments (department_name)
    ON DELETE CASCADE);
INSERT INTO `departments` 
VALUES 
(1,'HRM'),(2,'Accounting'),(3,'Engineering');
INSERT INTO `employees` 
VALUES 
(1,'bh@gmail.com','A','B','HRM'),
(2,'ak@gmail.com','C','D','HRM'),
(3,'mr@gmail.com','E','F','HRM'), 
(4,'pr@gmail.com','G','H','Accounting');
CREATE VIEW departments_view AS
SELECT d.department_id, d.department_name, COUNT(e.employee_id) AS total_employees
FROM departments d
LEFT JOIN employees e ON e.department_name = d.department_name
GROUP BY d.department_id
SELECT * 
FROM departments_view


department_id
department_name
total_employees


1
HRM
3

2
Accounting
1

3
Engineering
0