多表练习

/*create table dept(dept1 int(4),dept_name varchar (20))default charset=utf8;
insert into dept(dept1,dept_name)values(101,'财务');
insert into dept(dept1,dept_name)values(102,'销售');
insert into dept(dept1,dept_name)values(103,'IT技术');
insert into dept(dept1,dept_name)values(104,'行政');

create table emp(sid int(4),name varchar(24),age int(4),worktime_start varchar(20),incoming int(8),dept2 int(4))default charset=utf8;
insert into emp(sid,name,age,worktime_start,incoming,dept2)values(1789,'张三',35,'1980-1-1',4000,101);
insert into emp(sid,name,age,worktime_start,incoming,dept2)values(1674,'李四',32,'1983-4-1',3500,101);
insert into emp(sid,name,age,worktime_start,incoming,dept2)values(1776,'王五',24,'1990-7-1',2000,101);
insert into emp(sid,name,age,worktime_start,incoming,dept2)values(1568,'赵六',57,'1970-10-11',7500,102);
insert into emp(sid,name,age,worktime_start,incoming,dept2)values(1564,'荣七',64,'1963-10-11',8500,102);
insert into emp(sid,name,age,worktime_start,incoming,dept2)values(1879,'牛八',55,'1971-10-20',7300,103);*/

查询表
#select * from dept; select * from emp;

1.列出每个部门的平均收入及部门名称;    --每个部门  平均收入和部门名称
/*select * from dept left join emp on dept.dept1=emp.dept2;*/
#select avg(incoming),dept_name from (dept left join emp on dept.dept1=emp.dept2) group by dept_name;

2.财务部门的收入总和;
/*select * from dept inner join emp on dept.dept1=emp.dept2;*/
#select sum(incoming) from (dept inner join emp on dept.dept1=emp.dept2) where dept_name = '财务';

3.It技术部入职员工的员工号;
/*select * from dept inner join emp on dept.dept1=emp.dept2;*/
#select sid from (dept inner join emp on dept.dept1=emp.dept2) where dept_name = 'IT技术';

4.财务部门收入超过2000元的员工姓名
/*select * from dept inner join emp on dept.dept1=emp.dept2;*/
#select name from (dept inner join emp on dept.dept1=emp.dept2) where dept_name = '财务' and incoming > 2000;

5.找出销售部收入最低的员工的入职时间;
/*select * from dept inner join emp on dept.dept1=emp.dept2;*/
#select t.worktime_start from (select min(incoming),worktime_start from (dept inner join emp on dept.dept1=emp.dept2) where dept_name = '销售')t  ;

6.找出年龄小于平均年龄的员工的姓名，员工号和部门名称
/*select avg(age) from dept inner join emp on dept.dept1=emp.dept2 ;*/
#select name,sid,dept_name from (dept inner join emp on dept.dept1=emp.dept2 ) where age < (select avg(age) from dept inner join emp on dept.dept1=emp.dept2 );

7.列出每个部门收入总和高于9000的部门名称
/*select * from dept left join emp on dept.dept1=emp.dept2;
select sum(incoming)o,dept_name from  dept left join emp on dept.dept1=emp.dept2 group by dept_name; */
#select j.dept_name from (select sum(incoming)o,dept_name from  dept left join emp on dept.dept1=emp.dept2 group by dept_name)j where j.o >9000;

8.查出财务部门工资少于3800元的员工姓名
/*select * from dept left join emp on dept.dept1=emp.dept2;*/
#select name from ( dept left join emp on dept.dept1=emp.dept2) where incoming <3800 and dept_name = '财务';

9.求财务部门最低工资的员工姓名；
#select name from (dept left join emp on dept.dept1=emp.dept2 ) where dept_name = '财务' and incoming =( select min(incoming) from dept left join emp on dept.dept1=emp.dept2 where dept_name = '财务');

10.找出销售部门中年纪最大的员工的姓名
#select name from (dept left join emp on dept.dept1=emp.dept2 ) where dept_name = '销售' and age =( select max(age) from dept left join emp on dept.dept1=emp.dept2 where dept_name = '销售');

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

12.求员工收入小于4000元的员工部门编号及其部门名称
/*select * from dept left join emp on dept.dept1=emp.dept2;*/
#select sid,dept_name from (dept left join emp on dept.dept1=emp.dept2) where incoming <4000;

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

14.查询财务部低于平均收入的员工号与员工姓名：
/*select * from dept inner join emp on dept.dept1=emp.dept2;
select avg(incoming) from (dept inner join emp on dept.dept1=emp.dept2) where dept_name = '财务';*/
#select sid,name from (dept inner join emp on dept.dept1=emp.dept2) where dept_name = '财务' and incoming < (select avg(incoming) from (dept inner join emp on dept.dept1=emp.dept2) where dept_name = '财务');

15.列出部门员工数大于1个的部门名称；
/*select * from dept inner join emp on dept.dept1=emp.dept2;
select dept_name,count(*)o from (dept inner join emp on dept.dept1=emp.dept2) group by dept_name;*/
#select j.dept_name from (select dept_name,count(*)o from (dept inner join emp on dept.dept1=emp.dept2) group by dept_name )j where j.o >1;

16.列出部门员工收入不超过7500，且大于3000的员工年纪及部门名称；
/*select * from dept inner join emp on dept.dept1=emp.dept2;
select age,dept_name,incoming from ( dept inner join emp on dept.dept1=emp.dept2);*/
#select age,dept_name from ( select age,dept_name,(incoming)n from ( dept inner join emp on dept.dept1=emp.dept2) )m where 3000 < m.n and m.n<7500;

17.求入职于20世纪70年代的员工所属部门名称；#############
/*select * from dept inner join emp on dept.dept1=emp.dept2;*/
#select dept_name from (dept inner join emp on dept.dept1=emp.dept2) where worktime_start like '197%';

18.查找张三所在的部门名称；
#select dept_name from  dept inner join emp on dept.dept1=emp.dept2 where name = '张三';

19.列出每一个部门中年纪最大的员工姓名，部门名称；
#select name,dept_name from (dept inner join emp on dept.dept1=emp.dept2) where age in (select max(age) from (dept inner join emp on dept.dept1=emp.dept2) group by dept_name) ;

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

21.列出部门员工收入大于7000的员工号，部门名称；
/*select * from dept inner join emp on dept.dept1=emp.dept2;*/
#select sid,dept_name from (dept inner join emp on dept.dept1=emp.dept2) where incoming > 7000;

22.找出哪个部门还没有员工入职；
#select dept_name from (dept left join emp on dept.dept1=emp.dept2) where name is null;

23.先按部门号大小排序，再依据入职时间由早到晚排序员工信息表 ；###########
#select * from dept inner join emp on dept.dept1=emp.dept2 order by dept1 desc,worktime_start asc;

24.求出财务部门工资最高员工的姓名和员工号
/*select * from dept inner join emp on dept.dept1=emp.dept2;*/
#select name,sid from (dept inner join emp on dept.dept1=emp.dept2) where incoming = (select max(incoming) from (dept inner join emp on dept.dept1=emp.dept2) where dept_name = '财务') ;

25.求出工资在7500到8500之间，年龄最大的员工的姓名和部门名称
#select name,dept_name from (dept inner join emp on dept.dept1=emp.dept2)  where age = (select max(t.age) from (select incoming,age from (dept inner join emp on dept.dept1=emp.dept2) where incoming between 7500 and 8500)t);

26.查出大于自己部门平均工资的员工编号及部门名称
/*select * from dept inner join emp on dept.dept1=emp.dept2;
select avg(incoming),dept_name from (dept inner join emp on dept.dept1=emp.dept2) group by dept_name;
select sid,dept_name from (dept inner join emp on dept.dept1=emp.dept2);
select avg(incoming) from (dept inner join emp on dept.dept1=emp.dept2) where dept_name ='财务';
select avg(incoming) from (dept inner join emp on dept.dept1=emp.dept2) where dept_name ='销售';
select avg(incoming) from (dept inner join emp on dept.dept1=emp.dept2) where dept_name ='IT技术';*/
#select sid,dept_name from (dept inner join emp on dept.dept1=emp.dept2) where (dept_name ='财务' and incoming > (select avg(incoming) from (dept inner join emp on dept.dept1=emp.dept2) where dept_name = '财务')) or  (dept_name = '销售' and  incoming > (select avg(incoming) from (dept inner join emp on dept.dept1=emp.dept2) where dept_name ='销售'))  or  (dept_name = 'IT技术' and incoming > (select avg(incoming) from (dept inner join emp on dept.dept1=emp.dept2) where dept_name ='IT技术') )