SQL Queries for Identifying High‑Salary, Low‑Performance Employees in Layoff Scenarios

A fictional HR scenario is presented where a manager wants to cut costs by laying off employees whose salaries are higher than the average salary of their department. The HR assistant, unfamiliar with such analysis, seeks help from a technically skilled colleague.

The colleague creates an t_employee table, inserts sample employee records, and writes a query that groups by dept_id to compute each department's average salary, then joins this result with the employee table to list employees earning above the average.

CREATE TABLE `t_employee` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '主键',
  `emp_name` varchar(32) NOT NULL COMMENT '姓名',
  `dept_id` int(11) NOT NULL COMMENT '部门id',
  `salary` decimal(11,2) NOT NULL COMMENT '薪资',
  `hire_date` timestamp NULL DEFAULT NULL COMMENT '入职时间',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT INTO `t_employee` VALUES
('1','李嘉薪','1','12000.00','2023-09-08 19:37:19'),
('2','张升直','1','23000.00','2023-09-08 19:37:52'),
('3','子由','2','5000.00','2023-09-08 19:38:18'),
('4','承路','2','6000.00','2023-09-08 19:38:44'),
('5','记建','2','6800.00','2023-09-08 19:39:14'),
('6','时面','2','7200.00','2023-09-08 19:40:23'),
('7','赵钱多','1','19000.00','2023-09-08 19:40:56'),
('8','孙肃然','1','21800.00','2023-09-08 19:41:30');

The initial query to find high‑salary employees is:

SELECT e.id, e.emp_name, e.dept_id, e.salary, g.avg_salary
FROM t_employee e,
     (SELECT dept_id, AVG(salary) avg_salary FROM t_employee GROUP BY dept_id) g
WHERE e.dept_id = g.dept_id AND e.salary > g.avg_salary
ORDER BY dept_id;

After the manager decides that employees with good performance should be retained even if their salary is high, a performance table is added.

CREATE TABLE `t_performance` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '主键',
  `emp_id` int(11) NOT NULL COMMENT '员工id',
  `result` varchar(10) DEFAULT NULL COMMENT '绩效',
  `year` int(4) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT INTO `t_performance` VALUES
('1','2','B','2022'),
('2','7','A','2022'),
('3','8','A','2022'),
('4','5','B','2022'),
('5','6','A','2022');

The final query combines the salary condition with a performance filter, selecting employees whose salary is above the department average and whose last‑year performance is not "A":

SELECT e.id, e.emp_name, e.dept_id, e.salary, g.avg_salary
FROM t_employee e,
     (SELECT dept_id, AVG(salary) avg_salary FROM t_employee GROUP BY dept_id) g
WHERE e.dept_id = g.dept_id
  AND e.salary > g.avg_salary
  AND EXISTS (
        SELECT id FROM t_performance p
        WHERE p.year + 1 = YEAR(NOW())
          AND e.id = p.emp_id
          AND p.result != 'A'
      )
ORDER BY dept_id;

The query returns the exact list the manager needs, demonstrating a practical use case of SQL for HR‑driven cost‑reduction decisions.