多表

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

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

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

select sum(emp.incoming) from emp where dept2=(select dept1 from dept where dept_name='财务');

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

select emp.sid from emp where dept2=(select dept1 from dept where dept_name='IT技术');

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

select emp.name from emp where dept2=(select dept1 from dept where dept_name='财务' and incoming>2000);

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

select emp.worktime_start from emp where dept2=(select dept1 from dept where dept_name='销售') and incoming=(select min(incoming) from emp where dept2=(select dept1 from dept where dept_name='销售'));

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

select emp.name,emp.sid,dept.dept_name from dept left join emp on dept.dept1=emp.dept2 where age<(select avg(age) from emp);
7.列出每个部门收入总和高于9000的部门名称
select dept.dept_name from dept,(select dept2,sum(incoming) from emp group by dept2 having sum(incoming)>9000)a where dept.dept1=a.dept2;

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

select emp.name from emp where dept2=(select dept1 from dept where dept_name='财务') and incoming<3800;

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

select emp.name from emp where incoming=((select min(incoming) from emp where dept2=(select dept1 from dept where dept_name='财务'))) and dept2=(select dept1 from dept where dept_name='财务');

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

select emp.name from emp where dept2=(select dept1 from dept where dept_name='销售') and age=(select max(age) from emp where dept2=(select dept1 from dept where dept_name='销售'));

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

select emp.name,dept.dept_name from dept left join emp on dept.dept1=emp.dept2 where emp.incoming=(select min(incoming) from emp);

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

select t.dept2,dept.dept_name from (select dept2 from emp where incoming<4000)t,dept where t.dept2=dept.dept1;

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

select m.name,m.dept_name,m.incoming from (select dept2,max(incoming)a from emp group by dept2)t,(select * from dept,emp where dept.dept1=emp.dept2)m where t.dept2=m.dept1 and t.a=m.incoming order by m.incoming desc;
                                                                                                                                               
14.查询财务部低于平均收入的员工号与员工姓名：

select emp.sid,emp.name from emp where dept2=(select dept1 from dept where dept_name='财务') and emp.incoming<(select avg(incoming) from emp);

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

select dept.dept_name from dept,(select count(*),dept2 from emp group by dept2 having count(*)>1)t where dept.dept1=t.dept2;

16.列出部门员工收入不超过7500，且大于3000的员工年纪及部门名称；

select emp.age,dept.dept_name from dept left join emp on dept.dept1=emp.dept2 where incoming<7500 and incoming>3000;

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

select dept.dept_name from dept left join emp on dept.dept1=emp.dept2 where worktime_start like '197%';

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

select dept.dept_name from dept left join emp on dept.dept1=emp.dept2 where emp.name='张三';

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

select m.name,m.dept_name from (select dept2,max(age)a from emp group by dept2)t,(select * from dept,emp where dept.dept1=emp.dept2)m where t.dept2=m.dept1 and t.a=m.age;

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

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

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

select t.sid,dept.dept_name from (select * from emp where incoming>7000)t,dept where dept.dept1=t.dept2;

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

select dept.dept_name from dept left join emp on dept.dept1=emp.dept2 where emp.name is null;

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

select * from dept left join emp on dept.dept1=emp.dept2 order by dept.dept1 desc,emp.worktime_start asc;

24.求出财务部门工资最高员工的姓名和员工号   -

select name,sid from emp where incoming=(select max(incoming) from emp where dept2=(select dept1 from dept where dept_name='财务')) and dept2=(select dept1 from dept where dept_name='财务');

25.求出工资在7500到8500之间，年龄最大的员工的姓名和部门名称

select emp.name,dept.dept_name from dept,emp where dept.dept1=emp.dept2 and emp.age=(select max(age) from emp where incoming between 7500 and 8500);

26.查出大于自己部门平均工资的员工编号及部门名称

select emp.sid,dept.dept_name from dept left join emp on dept.dept1=emp.dept2 left join (select dept2,avg(incoming)g from emp group by dept2)w on dept.dept1=w.dept2 where emp.incoming>w.g;