JAVA sql 查询3

-- 1. 求各个月入职的的员工个数
select date_format(hiredate,'%m'),count(date_format(hiredate,'%m'))
from employees group by date_format(hiredate,'%m')
-- 2. 查询 50 号部门,60 号部门,70 号部门的各个部门的平均工资
SELECT department_id,avg(salary) FROM employees  WHERE department_id in(50,60,70) GROUP BY department_id

-- 3. 查询员工编号为奇数的员工人数
SELECT COUNT(employee_id) FROM employees  WHERE employee_id%2=1

-- 4. 查询工资最高的 3 名员工信息
select * from employees order by salary  DESC LIMIT 3

-- 5. 查询 first_name 是以大写 D 开头的第三到第五个员工信息
select * from employees WHERE binary first_name like 'D%'

-- 6. 查询工资最高的前三名员工的平均薪资和工资最低的前三名员工的平均薪资
select avg(salary) from (SELECT * from employees order by salary  DESC LIMIT 3) as e
select avg(salary) from (SELECT * from employees order by salary  asc LIMIT 3) as e

-- 7. 显示与first_name为'Steven'从事相同工作的员工的详细信息  注:employees 表的 JOB_ID 代表该员工的工种
select * from employees WHERE job_id in(select job_id from employees WHERE first_name='Steven')    

-- 8. 查询所有工资高于平均工资(平均工资包括所有员工)的销售人员('SA_REP')
select * from employees WHERE salary>=(SELECT avg(salary) from employees) and job_id='SA_REP'

-- 9. 查询在研发部('IT')工作员工的编号，姓名，工作部门名称
select employee_id,first_name,last_name,job_id from employees WHERE job_id like 'IT_%'

-- 10. 查询各个部门的名称和员工人数
select job_id,COUNT(employee_id)
from employees
where job_id in(select job_id from employees) GROUP BY job_id order by COUNT(employee_id)  DESC

-- 11. 查询平均薪资最高的前三个部门所有的员工信息及部门名称
select e.*,d.department_name
from employees e join departments d on e.department_id=d.department_id
group by e.department_id order by avg(e.salary) desc limit 3

-- 12. 显示各个部门经理的基本工资  注:部门表的 manager_id 代表该部门的领导编号
select salary
from employees e join departments d on e.department_id=d.department_id
where e.employee_id=d.manager_id
-- 13. 查询各个部门的信息及所在的地址
SELECT d.*,l.street_address
FROM departments d join locations l on d.location_id=l.location_id
-- 14. 查询拥有部门最多的地址信息和部门名称
SELECT  l.street_address,department_name
FROM departments d join locations l on d.location_id=l.location_id
GROUP BY street_address
ORDER BY COUNT(street_address)
LIMIT 1

-- 15. 查询工资最高的员工ID,姓名,工资,岗位ID,岗位全称
SELECT e.employee_id,e.first_name,e.salary,e.job_id,j.job_title
from employees e JOIN jobs j on e.job_id=j.job_id
ORDER BY salary DESC
LIMIT 1

-- 16. 查询拥有员工最多的岗位信息

SELECT j.*
from employees e JOIN jobs j on e.job_id=j.job_id
GROUP BY j.job_title
ORDER BY COUNT(e.employee_id)
LIMIT 1