[打卡]2024.5.7 题

#2.查询公司员工工资的最大值，最小值，平均值，总和

#3.查询各job_id的员工工资的最大值，最小值，平均值，总和

#4.选择具有各个job_id的员工人数

#5.查询员工最高工资和最低工资的差距

-- 2
SELECT MAX(salary),MIN(salary),AVG(salary),SUM(salary)
from employees
-- 3
SELECT job_id,MAX(salary),MIN(salary),AVG(salary),SUM(salary)
from employees
GROUP BY job_id
-- 4
SELECT job_id,COUNT(employee_id)
from employees
GROUP BY job_id
-- 5
SELECT MAX(salary)-MIN(salary)
as salary_defference
FROM employees

-- 查询各个管理者手下的最低工资，其中最低不<6K，没有管理者的员工不参与
SELECT manager_id,MIN(salary)
from employees
where manager_id is not null
GROUP BY manager_id
HAVING MIN(salary)>=6000

-- 查询所有部门的名字， location_id,员工数量和平均工资，并按平均工资降序排序
SELECT department_name,location_id,count(*),AVG(salary)
from employees e RIGHT JOIN departments d
on e.department_id=d.department_id
GROUP BY department_name,location_id
ORDER BY AVG(salary) desc