笔记11.26

1.列出每个部门的平均收入及部门名称;
        select avg(incoming),dept_name from dept left join emp on dept1=dept2 group by dept1;
2.财务部门的收入总和；
        select sum(incoming) from dept left join emp on dept1=dept2 where dept_name='chaiwu';
3.It技术部入职员工的员工号
        select sid from emp where dept2=(select dept1 from dept where dept_name='ITjishu');
        select sid,dept_name from dept left join emp on dept1=dept2 where dept_name='ITjishu';
4.财务部门收入超过2000元的员工姓名
        select dept_name,name,incoming from dept left join emp on dept1=dept2 where incoming > 2000 and dept_name='chaiwu';
5.找出销售部收入最低的员工的入职时间；
        select worktime_start from (select * from dept left join emp on dept1=dept2 order by incoming)aa where dept_name in('xiaoshou') limit 1;
       
6.找出年龄小于平均年龄的员工的姓名，ID和部门名称
        select name,sid,dept_name from dept left join emp on dept1=dept2 where age < (select avg(age) from emp);
7.列出每个部门收入总和高于9000的部门名称
        select dept_name from dept left join emp on dept1=dept2 group by dept1 having sum(incoming) > 9000;
8.查出财务部门工资少于3800元的员工姓名
        select dept_name,incoming from dept left join emp on dept1=dept2 where dept_name='chaiwu' and incoming < 3800;
9.求财务部门最低工资的员工姓名；
        select name,incoming from dept left join emp on dept1=dept2 where incoming in(select min(incoming) from dept left join emp on dept1=dept2 where dept_name='chaiwu');
10.找出销售部门中年纪最大的员工的姓名  
        select name,age from dept left join emp on dept1=dept2 where age in(select max(age) from dept left join emp on dept1=dept2 where dept_name='xiaoshou');
11.求收入最低的员工姓名及所属部门名称：
        select name,dept_name from dept left join emp on dept1=dept2 where incoming in(select min(incoming) from emp);
12.求李四的收入及部门名称
        select incoming,dept_name from dept left join emp on dept1=dept2 where name in('lishi');
13.求员工收入小于4000元的员工部门编号及其部门名称；
        select sid,dept_name from dept left join emp on dept1=dept2 where incoming < 4000;
14.列出每个部门中收入最高的员工姓名，部门名称，收入，并按照收入降序；
        select name,dept_name,incoming from (select * from dept left join emp on dept1=dept2 order by incoming desc)as a group by dept1 order by incoming desc;
15.求出财务部门收益最高的俩位员工的姓名，工号，收益
        select name,sid,incoming from (select * from dept left join emp on dept1=dept2 order by incoming desc)as a where dept_name in('chaiwu') limit 2;
16.查询财务部低于平均收入的员工号与员工姓名：
        select sid,name,incoming from dept left join emp on dept1=dept2 where incoming < (select avg(incoming) from dept left join emp on dept1=dept2 where dept_name='chaiwu') and dept_name='chaiwu';
17.列出部门员工数大于1个的部门名称；   
        select dept_name from dept left join emp on dept1=dept2 group by dept_name having count(*)>1;

18.列出部门员工收入不超过7500，且大于3000的员工年纪及部门编号；
        select age,dept1 from dept left join emp on dept1=dept2 where incoming < 7500 and incoming > 3000;
19.求入职于20世纪70年代的员工所属部门名称；
        select name,dept_name from dept left join emp on dept1=dept2 where worktime_start like '197%';
20.查找张三所在的部门名称；
        select dept_name from dept left join emp on dept1=dept2 where name='zhangsan';
21.列出每一个部门中年纪最大的员工姓名，部门名称；
        select name,dept_name from (select * from dept left join emp on dept1=dept2 order by dept1 desc)as a group by dept1;
22.列出每一个部门的员工总收入及部门名称；
        select sum(incoming),dept_name from dept left join emp on dept1=dept2 group by dept1;
23.列出部门员工收入大于7000的员工号，部门名称；
        select sid,dept_name from dept_name from dept left join emp where incoming > 7000;
24.找出哪个部门还没有员工入职；
        select dept_name,name from dept left join emp where name is null;
25.先按部门号大小排序，再依据入职时间由早到晚排序员工信息表 ；
        select * from (select * from emp order by worktime_start asc)as a order by dept1 asc;
        select * from dept left join emp on dept1=dept2 order by dept1,worktime_start;
       
26.找出销售部门收入大于7500,显示他们的SID和姓名和年纪；
        select sid,name,age from dept left join emp on dept1=dept2 where dept_name='xiaoshou' and incoming > 7500;
27.找出入职于80年代的人，且显示他们的姓名和入职时间，按照先后循序进行排序展示
        select name,worktime_start from emp where worktime_start like '198&' order by worktime_start desc;
28.找出财务部门大于平均收入的人的姓名和收入
        select name,incoming from dept left join emp on dept1=dept2 where incoming > (select avg(incoming) from dept left join emp on dept1=dept2 where dept_name='chaiwu') and dept_name='chaiwu';
29.查找收入大于4000元以上的人的姓名和收入
        select name,incoming from emp where incoming > 4000;
30.统计各个部门的收入总和并按照从大到小方式排序进行显示
        select dept_name,sum(incoming) from dept left join emp on dept1=dept2 group by dept1 order by incoming desc;