【无标题】

1.列出每个部门里面有那些员工及部门名称;
SELECT name,dept_name

from dept left JOIN emp on dept.dept1 = emp.dept2

2.运维部门的收入总和；
SELECT sum(incoming),dept_name

from emp RIGHT JOIN dept ON emp.dept2 = dept.dept1

where dept_name=‘yunwei’

3.HR部入职员工的员工号
SELECT dept_name,id

from emp RIGHT JOIN dept on emp.dept2 = dept.dept1

WHERE dept_name = ‘HR’

4.财务部门收入超过5000元的员工姓名
select name

from dept LEFT JOIN emp on emp.dept2=dept.dept1

where dept_name=‘chaiwu’ and incoming>5000

5.找出销售部收入最低的员工的入职时间；
select min(incoming),emp.worktime from dept RIGHT JOIN emp on dept.dept1 = emp.dept2 where dept_name=‘xiaoshou’ GROUP BY worktime

6.找出年龄小于平均年龄的员工的姓名，ID和部门名称
select name,id,dept_name from dept left JOIN emp on dept.dept1 = emp.dept2 WHERE age<(SELECT avg(age) from emp)

7.列出每个部门收入总和高于10000的部门名称
SELECT dept_name from emp LEFT JOIN dept on emp.dept2 GROUP BY dept_name HAVING sum(incoming)>10000

8.查出财务部门工资少于10000元的员工姓名
SELECT name from emp LEFT JOIN dept on emp.dept2 = dept.dept1 WHERE incoming < ‘10000’ && dept.dept_name=‘chaiwu’

9.求财务部门最低工资的员工姓名；
SELECT name,min(incoming) from emp LEFT JOIN dept on emp.dept2 = dept.dept1 where dept_name=‘chaiwu’ GROUP BY name

10.找出运维部门中年纪最大的员工的姓名
SELECT emp.name from emp LEFT JOIN dept on emp.dept2 = dept.dept1 where age=(SELECT max(age) from emp LEFT JOIN dept on emp.dept2 = dept.dept1 WHERE dept.dept_name =‘yunwei’) and dept.dept_name=‘yunwei’

11.求收入最低的员工姓名及所属部门名称：
SELECT emp.name,dept.dept_name from emp LEFT JOIN dept on emp.dept2 = dept.dept1 where incoming=(SELECT min(incoming)from emp join dept on emp.dept2 = dept.dept1)

12.求雷蕾的收入及部门名称
SELECT emp.incoming,dept.dept_name from emp LEFT JOIN dept on emp.dept2 = dept.dept1 where emp.name=‘leilei’

13.求员工收入小于8000元的员工部门编号名字及其部门名称；
SELECT emp.id,dept.dept_name,name from emp LEFT JOIN dept on emp.dept2 = dept.dept1 where emp.incoming<8000

14.列出每个部门中收入最高的员工姓名，部门名称，收入，并按照收入降序；
SELECT any_value(name),max(incoming),any_value(dept_name) from dept LEFT JOIN emp on dept.dept1 = emp.dept2 GROUP BY dept_name
由于5.7mysql以上版本不对不需要group by进行去掉会报错，所以需要在不需要group by的名字前添加any_value（），（）里面时你的字段

15.求出运维部门收益最高的俩位员工的姓名，工号，收益
select * from (select name,id,incoming from dept left join emp on dept.dept1=emp.dept2 where dept_name=‘yunwei’ order by incoming desc)as n where id limit 0,2;

16.查询运维部低于平均收入的员工号与员工姓名：
SELECT id,name from emp LEFT JOIN dept on emp.dept2 = dept.dept1 WHERE dept_name=‘yunwei’ and incoming<(
SELECT avg(incoming) from emp LEFT JOIN dept on dept.dept1 = emp.dept2)

17.列出部门员工数大于1个的部门名称；
SELECT DISTINCT(dept_name) from dept LEFT JOIN emp on dept.dept1=emp.dept2 where dept1 in(SELECT dept2 from dept LEFT JOIN emp on dept.dept1 = emp.dept2 GROUP BY dept2 HAVING(COUNT(dept2)>1))

18.列出部门员工收入不超过10000，且大于5000的员工年纪及部门编号；
SELECT age,id from dept LEFT JOIN emp on dept.dept1 = emp.dept2 where incoming<=7500 and incoming>=3000

19.求入职于2022年的员工所属部门名称；
SELECT DISTINCT(dept_name) from emp LEFT JOIN dept on emp.dept2 = dept.dept1 WHERE worktime=2022

20.查找haiwu所在的部门名称；
SELECT dept_name from emp LEFT JOIN dept on emp.dept2 = dept.dept1 WHERE name=‘haiwu’

21.列出每一个部门中年纪最小的员工姓名，部门名称；
SELECT name,dept_name from dept LEFT JOIN emp on dept.dept1 = emp.dept2 where age in(SELECT min(age) from dept LEFT JOIN emp on dept.dept1 = emp.dept2 GROUP BY dept_name)

22.列出每一个部门的员工总收入及部门名称；
SELECT sum(incoming),any_value(dept_name) from dept LEFT JOIN emp ON dept.dept1 = emp.dept2 GROUP BY dept1

23.列出部门员工收入大于6000的员工号，部门名称；
SELECT id,dept_name from dept LEFT JOIN emp on dept.dept1=emp.dept2 where incoming >6000

24.找出哪个部门还没有员工入职；
SELECT any_value(dept_name) from dept LEFT JOIN emp on dept.dept1=emp.dept2 GROUP BY dept1 HAVING (COUNT(id)=0)

25.先按部门号大小排序，再依据入职时间由早到晚排序员工信息表 ；
SELECT * FROM dept LEFT JOIN emp on dept.dept1=emp.dept2 ORDER BY dept1 desc,worktime DESC

26.找出运维部门收入大于7500,显示他们的ID和姓名和年纪；
SELECT id,name,age from dept LEFT JOIN emp on dept.dept1=emp.dept2 where dept_name=‘yunwei’ and incoming>=7500

27.找出入职于2022年以前的员工，且显示他们的姓名和入职时间，按照先后循序进行排序展示
SELECT name,worktime from emp LEFT JOIN dept on emp.dept2 = dept.dept1 WHERE worktime>=2022 ORDER BY worktime

28.找出入职于2022年1月以后的员工，并显示年龄，姓名，收入
SELECT name,age,incoming from emp LEFT JOIN dept on emp.dept2 = dept.dept1 where worktime>=2022/01/01

29.查找收入大于8000元以上的人的姓名和收入
SELECT name,incoming FROM emp where incoming>4000

30.统计各个部门的收入总和并按照从大到小方式排序进行显示
SELECT sum(incoming),any_value(dept_name) from emp LEFT JOIN dept on emp.dept2 = dept.dept1 GROUP BY dept_name ORDER BY any_value(incoming) desc