多表练习作业

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,'行政');
select * from dept;

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 emp;

SELECT * from emp left join dept on emp.dept2 = dept.dept1;

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

2.财务部门的收入总和；
select sum(incoming) from emp,dept where emp.dept2 = dept.dept1 and dept_name = '财务';

3.It技术部入职员工的员工号
select emp.sid from emp,dept where emp.dept2 = dept.dept1 and dept_name = 'IT技术';

4.财务部门收入超过2000元的员工姓名
select emp.name from emp,dept where emp.dept2 = dept.dept1 and incoming >2000 and dept_name = '财务';

5.找出销售部收入最低的员工的入职时间;
select min(incoming) from emp where dept2 = '102';#找出销售部最低工资
select  emp.worktime_start from emp where incoming = (select min(incoming) from emp where dept2 = '102');

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


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

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

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

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

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

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


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

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

15.列出部门员工数大于1个的部门名称；
select dept_name,count(1) from emp left join dept on emp.dept2 = dept.dept1 group by dept_name#求出每个部门的人数
select dept_name from (select dept_name,count(1)t from emp left join dept on emp.dept2 = dept.dept1 group by dept_name)y where t>1;

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

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


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


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

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

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


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

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


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

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

26.查出大于自己部门平均工资的员工编号及部门名称
select dept1,avg(incoming)k from emp left join dept on emp.dept2 = dept.dept1 group by dept_name;#求出每个部门的平均工资

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