This tutorial will show how to find the managers who are having specified minimum number of employees working under them.
Prerequisites
MySQL 8.0.17
Creating Table in MySQL
Create below employee table in MySQL server under database roytuts.
CREATE TABLE `employee` (
`emp_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`emp_first_name` varchar(30) COLLATE utf8mb4_unicode_ci NOT NULL,
`emp_last_name` varchar(20) COLLATE utf8mb4_unicode_ci NOT NULL,
`emp_mgr_id` int(11) DEFAULT NULL,
`emp_designation` varchar(30) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
PRIMARY KEY (`emp_id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
Dumping Data into Table
Insert below data into the employee table.
insert into `employee`(`emp_id`,`emp_first_name`,`emp_last_name`,`emp_mgr_id`,`emp_designation`)
values
(7369,'SMITH','JHON',7902,'CLERK'),
(7499,'ALLEN','BORDER',7698,'SALESMAN'),
(7521,'WARD','SPACE',7698,'SALESMAN'),
(7654,'MARTIN','FOWLER',7698,'SALESMAN'),
(7698,'BLAKE','RAY',NULL,'MANAGER'),
(7782,'CLARK','MICHAEL',NULL,'MANAGER'),
(7788,'SCOTT','TIGER',7566,'ANALYST'),
(7839,'KING','ROY',NULL,'VICE PRESIDENT'),
(7844,'TURNER','RICK',7698,'SALESMAN'),
(7876,'ADAMS','EVE',7788,'CLERK'),
(7900,'JAMES','BOND',7698,'CLERK'),
(7902,'FORD','LAMBDA',7566,'ANALYST'),
(7934,'MILLER','JOHN',7782,'CLERK'),
(7954,'FRANK','JOHN',7782,'MANAGER'),
(7964,'MARTIN','HIKMAN',NULL,'CLERK'),
(7974,'APRIL','HICKMAN',7782,'SALESMAN');
Building Query
Build below query to find out minimum two employees working under each manager.
SELECT mgr.emp_id manager_id, mgr.emp_first_name manager_first_name, mgr.emp_last_name manager_last_name
FROM employee mgr, employee e
WHERE e.emp_mgr_id = mgr.emp_id
GROUP BY mgr.emp_id
HAVING COUNT(e.emp_id) > 2;
Output
Execute the above query, you will get below output:
manager_id manager_first_name manager_last_name
7698 BLAKE RAY
7782 CLARK MICHAEL
Thanks for reading.