多表查询练习

已知2张基本表：部门表：dept （部门号，部门名称）;员工表 emp（员工号，员工姓名，年龄，入职时间，收入，部门号）
1：dept表中有4条记录：
部门号(dept1) 部门名称(dept_name )
101 财务
102 销售
103 IT技术
104 行政
2：emp表中有6条记录：
员工号 员工姓名 年龄 入职时间 收入 部门号对应字段名称为： (sid name age worktime_start incoming dept2)
1789 张三 35 1980/1/1 4000 101
1674 李四 32 1983/4/1 3500 101
1776 王五 24 1990/7/1 2000 101
1568 赵六 57 1970/10/11 7500 102
1564 荣七 64 1963/10/11 8500 102
1879 牛八 55 1971/10/20 7300 103

1.列出每个部门的平均收入及部门名称;
select dept_name, avg(incoming) from deptt left join emp1 on deptt.dept1=emp1.dept2 group by dept_name
2.财务部门的收入总和；
select dept_name,sum(incoming) from deptt inner join emp1 on deptt.dept1=emp1.dept2 where dept_name=“财务”;
3.It技术部入职员工的员工号
select emp1.sid from deptt inner join emp1 on deptt.dept1=emp1.dept2 where dept_name="IT技术 ";
4.财务部门收入超过2000元的员工姓名
select name from deptt inner join emp1 on emp1.dept2=deptt.dept1 where dept_name=‘财务’ and incoming>2000;

5.找出销售部收入最低的员工的入职时间；
select worktime from emp1 where incoming=(select min(incoming) from emp1 left join dept on emp1.dept2=deptt.dept1 where dept_name=‘销售’);

6.找出年龄小于平均年龄的员工的姓名，ID和部门名称
select emp1.name, emp1.sid,deptt.dept_name from deptt left join emp1 on deptt.dept1=emp1.dept2 where age<(select avg(age) from emp1);

7.列出每个部门收入总和高于9000的部门名称
select dept_name from emp1 left join deptt on emp1.dept2=deptt.dept1 group by dept_name having sum(incoming)>9000;

8.查出财务部门工资少于3800元的员工姓名
select emp1.name from emp1 left join deptt on emp1.dept2=deptt.dept1 where incoming < ‘3800’ && deptt.dept_name=“财务”;
9.求财务部门最低工资的员工姓名；
select emp1.name from emp1 left join deptt on emp1.dept2=deptt.dept1 where incoming=(select min(incoming) from emp1 left join deptt on emp1.dept2=deptt.dept1 where deptt.dept_name=“财务”)&& deptt.dept_name=“财务”;
10.找出销售部门中年纪最大的员工的姓名
select emp1.name from emp1 left join deptt on emp1.dept2=deptt.dept1 where age=(select max(age) from emp1 left join deptt on emp1.dept2=deptt.dept1 where deptt.dept_name=“销售”)&& deptt.dept_name=“销售”;

11.求收入最低的员工姓名及所属部门名称：
select emp1.name,deptt.dept_name from emp1 left join deptt on emp1.dept2=deptt.dept1 where incoming=(select min(incoming) from emp1 left join deptt on emp1.dept2=deptt.dept1);

12.求李四的收入及部门名称
select emp1.incoming,deptt.dept_name from emp1 left join deptt on emp1.dept2=deptt.dept1 where emp1.name=“李四”;

13.求员工收入小于4000元的员工部门编号及其部门名称
select deptt.dept1,deptt.dept_name from emp1 left join deptt on emp1.dept2=deptt.dept1 where incoming<4000;

14.列出每个部门中收入最高的员工姓名，部门名称，收入，并按照收入降序；
select name,dept_name, max(incoming) from deptt left join emp1 on deptt.dept1=emp1.dept2 group by dept_name order by incoming desc;

15.求出财务部门收益最高的俩位员工的姓名，工号，收益
select * from (select name,sid,incoming from deptt left join emp1 on deptt.dept1=emp1.dept2 where dept_name=“财务” order by incoming desc)as n where sid limit 0,2;

16.查询财务部低于平均收入的员工号与员工姓名：
select sid,name from emp1 left join deptt on emp1.dept2=deptt.dept1 where dept_name=“财务”&&incoming<(select avg(incoming) from emp1 left join deptt on deptt.dept1=emp1.dept2);

17.列出部门员工数大于1个的部门名称；
select distinct(dept_name)
from deptt left join emp1 on deptt.dept1=emp1.dept2 where dept1 in(select dept2 from deptt left join emp1 on deptt.dept1=emp1.dept2 group by dept2 HAVING(count(dept2)>1));
18.列出部门员工收入不超过7500，且大于3000的员工年纪及部门编号；
select age,sid from deptt left join emp1 on deptt.dept1=emp1.dept2 where incoming<=7000&&incoming>3000;

19.求入职于20世纪70年代的员工所属部门名称；
select dept_name from emp1 left join deptt on emp1.dept2=deptt.dept1 where worktime>=19700101 and worktime< 19800101;
20.查找张三所在的部门名称；
select dept_name from deptt left join emp1 on deptt.dept1=emp1.dept2 where name=“张三”;
21.列出每一个部门中年纪最大的员工姓名，部门名称；
select name,dept_name
from deptt left join emp1 on deptt.dept1=emp1.dept2 where age in(select max(age) from deptt left join emp1 on deptt.dept1=emp1.dept2 group by dept2 );

22.列出每一个部门的员工总收入及部门名称；
select sum(incoming),dept_name
from deptt left join emp1 on deptt.dept1=emp1.dept2 group by dept1;

23.列出部门员工收入大于7000的员工号，部门名称；
select sid,dept_name
from deptt left join emp1 on deptt.dept1=emp1.dept2 where incoming>7000;
24.找出哪个部门还没有员工入职；
select dept_name
from deptt left join emp1 on deptt.dept1=emp1.dept2 group by dept1 having (count(sid)=0);
25.先按部门号大小排序，再依据入职时间由早到晚排序员工信息表 ；
select *
from deptt left join emp1 on deptt.dept1=emp1.dept2 order by dept1 desc,worktime asc;
26.求出财务部门工资最高员工的姓名和员工号
select name,sid from emp1,deptt where emp1.dept2=deptt.dept1 and incoming=(select max(incoming) from emp1 left join deptt on emp1.dept2=deptt.dept1 where dept_name=‘财务’);

27.求出工资在7500到8500之间，年龄最大的员工的姓名和部门名称。
select name,dept_name
from deptt left join emp1 on deptt.dept1=emp1.dept2 where age= (select max(age) from emp1 where incoming between 7500 and 8500) ;