2021.2.3—双表查询

1、列出每个部门的平均收入及部门名称
select dept_name,avg(incoming) from dept a inner join emp b on a.dept1=b.dept2 group by dept1;
2、财务部门的收入总和
select sum(incoming) from dept a inner join emp b on a.dept1=b.dept2 where dept_name='财务';
3、It技术部入职员工的员工号
select sid from dept a inner join emp b on a.dept1=b.dept2 where dept_name='IT技术';
4、财务部门收入超过2000元的员工姓名
select name from dept a inner join emp b on a.dept1=b.dept2 where dept_name='财务' and incoming>2000;
5、找出销售部收入最低的员工的入职时间
select worktime_start from dept a inner join emp b on a.dept1=b.dept2 where dept_name='销售' and incoming in(select min(incoming) from dept a inner join emp b on a.dept1=b.dept2 where dept_name='销售');
6、求工资在7500到8500元之间，年龄最大的人的姓名及部门
select name,dept_name from dept a inner join emp b on a.dept1=b.dept2 where incoming between 7500 and 8500 and age in (select max(age) from emp where incoming between 7500 and 8500);
7、列出每个部门收入总和高于9000的部门名称
select dept_name from dept a inner join emp b on a.dept1=b.dept2 group by dept1 having sum(incoming)>9000;
8、查出财务部门工资少于3800元的员工姓名
select name from dept a inner join emp b on a.dept1=b.dept2 where dept_name='财务' and incoming<3800;
9、求财务部门最低工资的员工姓名；
select name from dept a inner join emp b on a.dept1=b.dept2 where dept_name='财务' and incoming in(select min(incoming) from dept a inner join emp b on a.dept1=b.dept2 where dept_name='财务');
10、找出销售部门中年纪最大的员工的姓名
select name from dept a inner join emp b on a.dept1=b.dept2 where dept_name='销售' and age in(select max(age) from dept a inner join emp b on a.dept1=b.dept2 where dept_name='销售');
11、求收入最低的员工姓名及所属部门名称
select name,dept_name from dept a inner join emp b on a.dept1=b.dept2 where incoming in (select min(incoming) from emp);
12、求李四的收入及部门名称
select incoming,dept_name from dept a inner join emp b on a.dept1=b.dept2 where name='李四';
13、求员工收入小于4000元的员工、部门编号及其部门名称
select name,dept1,dept_name from dept a inner join emp b on a.dept1=b.dept2 where incoming<4000;
14、列出每个部门中收入最高的员工姓名，部门名称，收入，并按照收入降序
select name,dept_name,incoming from dept a inner join (select * from emp order by incoming desc)b on a.dept1=b.dept2 group by dept1 order by incoming desc;
15、求出财务部门收益最高的俩位员工的姓名，工号，收益
select name,sid,incoming from dept a inner join emp b on a.dept1=b.dept2 where dept_name='财务' order by incoming desc limit 2;
16、查询财务部低于平均收入的员工号与员工姓名
select sid,name from dept a inner join emp b on a.dept1=b.dept2 where dept_name='财务' and incoming<(select avg(incoming) from emp);
17、列出部门员工数大于1个的部门名称
select dept_name from dept a inner join emp b on a.dept1=b.dept2 group by dept1 having count(sid)>1;
18、列出部门员工收入不超过7500，且大于3000的员工年纪及部门名称
select age,dept_name from dept a inner join emp b on a.dept1=b.dept2 where incoming<=7500 and incoming>3000;
19、求入职于20世纪70年代的员工所属部门名称
select dept_name from dept a inner join emp b on a.dept1=b.dept2 where worktime_start like '197%';
20、查找张三所在的部门名称
select dept_name from dept a inner join emp b on a.dept1=b.dept2 where name='张三';
21、列出每一个部门中年纪最大的员工姓名，部门名称
select name,dept_name from dept a inner join (select * from emp order by age desc)b on a.dept1=b.dept2 group by dept2;
22、列出每一个部门的员工总收入及部门名称
select dept_name,sum(incoming) from dept a inner join emp b on a.dept1=b.dept2 group by dept2;
23、列出部门员工收入大于7000的部门号，部门名称
select dept1,dept_name from dept a inner join emp b on a.dept1=b.dept2 where incoming>7000 group by dept2;
24、找出哪个部门还没有员工入职
select dept_name from dept a left join emp b on a.dept1=b.dept2 where sid is null;
25、先按部门号大小排序，再依据入职时间由早到晚排序员工信息表
select * from dept a left join (select * from emp order by dept2 desc)b on a.dept1=b.dept2 order by worktime_start;
26、找出年龄小于平均年龄的员工的姓名，ID和部门名称
select name,sid,dept_name from dept a left join emp b on a.dept1=b.dept2 where age<(select avg(age) from emp);
27、查出大于自己部门平均工资的员工编号及部门名称
select sid,dept_name from dept a inner join (select * from (select * from emp where dept2=101 and incoming>(select avg(incoming) from emp where dept2=101))b union select * from (select * from emp where dept2=102 and incoming>(select avg(incoming) from emp where dept2=102))c union select * from (select * from emp where dept2=103 and incoming>(select avg(incoming) from emp where dept2=103))d)e on a.dept1=e.dept2;