多表作业1

1. 列出每个部门的平均收入及部门名称;

Select dept_name,avg(incoming) from(select * from dept,emp where dept1=dept2)hh group by dept_name;

select dept_name,avg(incoming) from dept,emp where dept1=dept2 group by dept_name;

file:///C:\Users\ADMINI~1\AppData\Local\Temp\ksohtml6812\wps1.jpg

2. 财务部门的收入总和；

Select dept_name,sum(incoming) from(select * from dept,emp where dept1 =dept2)o where dept2 =101;

Select dept_name,sum(incoming) from(select * from dept,emp where dept1 =dept2)o where dept_name ='chaiwu';

select sum(incoming),dept_name from dept,emp where dept1=dept2 and dept_name ='chaiwu';

file:///C:\Users\ADMINI~1\AppData\Local\Temp\ksohtml6812\wps2.jpg

3. It技术部入职员工的员工号

Select sid,dept_name from(select * from dept,emp where dept1 =dept2)u where dept_name ='ITjishu';

select sid,dept_name from dept,emp where dept1=dept2 and dept_name ='ITjishu';

file:///C:\Users\ADMINI~1\AppData\Local\Temp\ksohtml6812\wps3.jpg

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

Select name,dept_name,incoming from(Select * from dept,emp where dept1=dept2)p where incoming >2000 and dept2 =101;

Select name,dept_name,incoming from(Select * from dept,emp where dept1=dept2)p where incoming >2000 and dept_name =chaiwu;

select dept_name,incoming,name from dept,emp where dept1=dept2 and dept_name ='chaiwu'and incoming >2000;

file:///C:\Users\ADMINI~1\AppData\Local\Temp\ksohtml6812\wps4.jpg

5. 找出销售部收入最低的员工的入职时间；

Select worktime_start,min(incoming) from(Select * from dept,emp where dept1=dept2)n where dept2 =102

select worktime_start,name,dept_name from (select * from dept,emp where dept1=dept2)aa where dept_name ='xiaoshou' order by incoming asc limit 1;

file:///C:\Users\ADMINI~1\AppData\Local\Temp\ksohtml6812\wps5.jpg

6. 找出年龄小于平均年龄的员工的姓名，ID和部门名称

select sid,dept_name,age from (select * from dept,emp where dept1=dept2)aa where age <(select avg(age) from dept,emp where dept1=dept2);

file:///C:\Users\ADMINI~1\AppData\Local\Temp\ksohtml6812\wps6.jpg

7. 列出部门收入总和高于9000的部门名称

select dept_name,sum(incoming)as c from(select * from dept,emp where dept1=dept2)a group by dept_name having c>9000;

select sum(incoming)as a,dept_name from dept,emp where dept1=dept2 group by dept_name having a>9000；

file:///C:\Users\ADMINI~1\AppData\Local\Temp\ksohtml6812\wps7.jpg

8. 查出财务部门工资少于3800元的员工姓名

Select name,incoming from(Select * from dept,emp where dept1=dept2)bb where incoming <3800

select name,incoming,dept_name from dept,emp where dept1=dept2 and incoming <3800;

file:///C:\Users\ADMINI~1\AppData\Local\Temp\ksohtml6812\wps8.jpg

9. 求财务部门最低工资的员工姓名；

select name,incoming from dept,emp where dept1=dept2 and incoming =(select min(incoming) from dept,emp where dept1=dept2 and dept_name='chaiwu');

Select name,incoming,dept_name from(Select * from dept,emp where dept1=dept2)bb where dept_name ='chaiwu' order by incoming asc limit 0,1;

file:///C:\Users\ADMINI~1\AppData\Local\Temp\ksohtml6812\wps9.jpg

10. 找出销售部门中年纪最大的员工的姓名

select name,dept_name from dept,emp where dept1=dept2 and age =(select max(age) from dept,emp where dept1=dept2) and dept_name ='xiaoshou';

Select name,dept_name from(Select * from dept,emp where dept1=dept2)bb where dept_name ='xiaoshou' order by age desc limit 1;

file:///C:\Users\ADMINI~1\AppData\Local\Temp\ksohtml6812\wps10.jpg

11. 求收入最低的员工姓名及所属部门名称：

select name,dept_name,incoming from dept,emp where dept1=dept2 and incoming =(select min(incoming) from dept,emp where dept1=dept2)

Select name,incoming,dept_name from(Select * from dept,emp where dept1=dept2)bb order by incoming asc limit 1;

file:///C:\Users\ADMINI~1\AppData\Local\Temp\ksohtml6812\wps11.jpg

12. 求李四的收入及部门名称

Select name,incoming,dept_name from(Select * from dept,emp where dept1=dept2)bb where name ='lisi';

file:///C:\Users\ADMINI~1\AppData\Local\Temp\ksohtml6812\wps12.jpg

13. 求员工收入小于4000元的员工部门编号及其部门名称；

Select name,incoming,dept_name,dept2 from(Select * from dept,emp where dept1=dept2)bb where incoming <4000;

file:///C:\Users\ADMINI~1\AppData\Local\Temp\ksohtml6812\wps13.jpg

14. 列出每个部门中收入最高的员工姓名，部门名称，收入，并按照收入降序；

Select max(incoming),dept_name,name from(Select * from dept,emp where dept1=dept2)bb group by dept_name order by incoming desc;

Select max(incoming),dept_name,name from dept,emp where dept1=dept2 group by dept_name order by incoming desc;

select name,dept_name,incoming,max(incoming) from emp,dept where dept1=dept2 and (select max(incoming) from emp group by dept_name ) group by dept_name order by incoming desc;

file:///C:\Users\ADMINI~1\AppData\Local\Temp\ksohtml6812\wps14.jpg

15. 求出财务部门收益最高的俩位员工的姓名，工号，收益

Select name,incoming,dept_name,sid from(Select * from dept,emp where dept1=dept2)bb where dept_name ='chaiwu' order by incoming desc limit 0,2;

file:///C:\Users\ADMINI~1\AppData\Local\Temp\ksohtml6812\wps15.jpg

16. 查询财务部低于平均收入的员工号与员工姓名：

Select name,incoming,dept_name,sid,avg(incoming)as b from(Select * from dept,emp where dept1=dept2)bb where dept_name =chaiwu

Select name,sid,avg(incoming)as b from dept,emp wehre dept1=dept2

Select sid,name,dept_name from (Select * from dept,emp where dept1=dept2)a where incoming <(select avg(incoming) from(Select * from dept,emp where dept1=dept2)a where dept_name ='chaiwu');file:///C:\Users\ADMINI~1\AppData\Local\Temp\ksohtml6812\wps16.jpg

17. 列出部门员工数大于1个的部门名称；

Select dept_name,name from(Select * from dept,emp where dept1=dept2)bb group by dept2

Select count(*)as c,dept_name from(Select * from dept,emp where dept1=dept2)bb where c !=1

Select count(*)as c,dept_name from dept,emp where dept1=dept2 group by dept_name having c >1

file:///C:\Users\ADMINI~1\AppData\Local\Temp\ksohtml6812\wps17.jpg

18. 列出部门员工收入不超过7500，且大于3000的员工年纪及部门编号；

Select age,dept2,name,incoming from (select * from dept,emp where dept1=dept2)b where incoming >3000 and incoming <=7500;

file:///C:\Users\ADMINI~1\AppData\Local\Temp\ksohtml6812\wps18.jpg

19. 求入职于20世纪70年代的员工所属部门名称；

Select dept_name,name,worktime_start from(select * from dept,emp where dept1=dept2)b where worktime_start >=1970 and worktime_start <1980

Select dept_name,name,worktime_start from(select * from dept,emp where dept1=dept2)b where worktime_start like ‘197%’

file:///C:\Users\ADMINI~1\AppData\Local\Temp\ksohtml6812\wps19.jpg

20. 查找张三所在的部门名称；

Select dept_name,name from(select * from dept,emp where dept1=dept2)b where name ='zhangsan';

file:///C:\Users\ADMINI~1\AppData\Local\Temp\ksohtml6812\wps20.jpg

21. 列出每一个部门中年纪最大的员工姓名，部门名称；

Select name,dept_name,count(*) from(select * from dept,emp where dept1=dept2)b group by age order by age desc

Select name,dept_name from

select name,dept_name from(select * from dept,emp where dept1=dept2 order by age desc)a group by dept_name;

select name,dept_name,age from(select * from dept,emp where dept1=dept2)b where age in(select max(age) from (select * from dept,emp where dept1=dept2)b group by dept_name) group by dept_name;

file:///C:\Users\ADMINI~1\AppData\Local\Temp\ksohtml6812\wps21.jpg

22. 列出每一个部门的员工总收入及部门名称；

Select dept_name,sum(incoming) from dept,emp where dept1=dept2 group by dept_name

file:///C:\Users\ADMINI~1\AppData\Local\Temp\ksohtml6812\wps22.jpg

23. 列出部门员工收入大于7000的员工号，部门名称；

Select name,dept2,dept_name,incoming from dept,emp where dept1=dept2 and incoming >7000

file:///C:\Users\ADMINI~1\AppData\Local\Temp\ksohtml6812\wps23.jpg

24. 找出哪个部门还没有员工入职；

Select name,dept_name from dept left join emp on dept1=dept2 where name is null;

file:///C:\Users\ADMINI~1\AppData\Local\Temp\ksohtml6812\wps24.jpg

25. 先按部门号大小排序，再依据入职时间由早到晚排序员工信息表；

Select * from(select * from dept,emp where dept1=dept2 order by dept2 desc)b order by worktime_start asc and (select * from dept,emp where dept1=dept2 order by dept2 desc)

select * from dept,emp where dept1=dept2 order by dept2 desc

select * from dept,emp where dept1=dept2 order by dept2 desc and (worktime_start asc

order by dept2 desc)

group by dept_name

select * from ((select* from dept,emp where dept1=dept2) order by dept2 desc) and ( (select * from dept,emp where dept1=dept2) order by worktime_start asc)

26. 找出销售部门收入大于7500,显示他们的SID和姓名和年纪；

select name,age,incoming from dept,emp where dept1=dept2 and incoming >7500 and dept_name ='xiaoshou';

file:///C:\Users\ADMINI~1\AppData\Local\Temp\ksohtml6812\wps25.jpg

27. 找出入职于80年代的人，且显示他们的姓名和入职时间，按照先后循序进行排序展示

select name,worktime_start from(select * from dept,emp where dept1=dept2)a where worktime_start like '198%';

file:///C:\Users\ADMINI~1\AppData\Local\Temp\ksohtml6812\wps26.jpg

28. 找出财务部门大于平均收入的人的姓名和收入

select name,incoming from dept left join emp on dept1=dept2 where dept_name='chaiwu' and incoming >(select avg(incoming) from dept left join emp on dept1=dept2 where dept_name='chaiwu');

29. 查找收入大于4000元以上的人的姓名和收入

select name,incoming from dept,emp where dept1=dept2 and incoming >4000;

30. 统计各个部门的收入总和并按照从大到小方式排序进行显示

31. select sum(incoming)a,incoming,dept_name from dept,emp where dept1=dept2 group by dept_name order by a desc;