9.3.1sql多表综合运用

CREATE table dept(dept1 VARCHAR(6),dept_name VARCHAR(20))  default charset=utf8;

INSERT into dept VALUES ('101','财务');
INSERT into dept VALUES ('102','销售');

INSERT into dept VALUES ('103','IT技术');

INSERT into dept VALUES ('104','行政');

CREATE table emp (sid VARCHAR(6),name VARCHAR(20),age TINYINT(2),worktime_start VARCHAR(10),incoming SMALLINT(10),dept2 VARCHAR(6))default charset=utf8;

insert into emp VALUES ('1789','张三',35,'1980/1/1',4000,'101');

insert into emp VALUES ('1674','李四',32,'1983/4/1',3500,'101');

insert into emp VALUES ('1776','王五',24,'1990/7/1',2000,'101');

insert into emp VALUES ('1568','赵六',57,'1970/10/11',7500,'102');
insert into emp VALUES ('1564','荣七',64,'1963/10/11',8500,'102');

insert into emp VALUES ('1879','牛八',55,'1971/10/20',7300,'103');
insert into emp VALUES ('1880','刘九',64,'1971/10/20',9000,'105');

-- drop  table  dept ;
drop  table  emp ;

select  *  from dept;
select *  from  emp ;

#笔记的练习

select  * from   dept  inner  join  emp  on  dept.dept1=emp.dept2 ;

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

select  *  from   dept    right    join emp   on   dept.dept1=emp.dept2;

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

select  *  from   dept    right   join  emp   on   dept.dept1=emp.dept2  where   dept1  is  null;

select * from dept inner join emp on dept.dept1=emp.dept2 
UNION
select * from dept left join emp on dept.dept1=emp.dept2 where name is null 
UNION
select * from dept right join emp on dept.dept1=emp.dept2 where dept1 is null;

select * from dept left join emp on dept.dept1=emp.dept2
UNION
select * from dept right join emp on dept.dept1=emp.dept2 where dept1 is null ;

select * from dept right JOIN emp on dept.dept1=emp.dept2
UNION
select * from dept left join emp on dept.dept1=emp.dept2 where name is null ;

select  *  from dept;
select *  from  emp ;

#多表练习题

1.列出每个部门的平均收入及部门名称; 
结果：avg(incoming),dept_name
条件：左连接，avg函数,group by
方法：

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

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

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

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

select  name from   dept  inner  join  emp  on  dept.dept1=emp.dept2 where incoming>2000 and dept_name='财务';

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

select  worktime_start from   dept  inner  join  emp  on  dept.dept1=emp.dept2  where dept_name='销售' order by incoming limit 0,1 ;

select worktime_start  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='销售' );

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

select  name,sid,dept_name  from   dept    right    join emp   on   dept.dept1=emp.dept2  where age< (select avg(age) from emp);

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

select dept_name from (select sum(incoming) sum1,dept_name from dept left join emp on dept.dept1=emp.dept2 group by dept_name)
s where s.sum1>9000;
8.查出财务部门工资少于3800元的员工姓名 
财务  dept
 incoming  emp

name
<
select  name  from   dept    right    join emp   on   dept.dept1=emp.dept2 where dept_name='财务' and incoming<3800;

9.求财务部门最低工资的员工姓名；
min   
dept  dept_name
emp  incoming  min

select  name,incoming  from   dept    right    join emp   on   dept.dept1=emp.dept2 where dept_name='财务' order by incoming limit 0,1 ;

10.找出销售部门中年纪最大的员工的姓名   
select  name,age  from   dept    right    join emp   on   dept.dept1=emp.dept2 where dept_name='销售' order by age desc limit 0,1;

select  name,age  from   dept    right    join emp   on   dept.dept1=emp.dept2 where (dept_name,age) 
in ('销售',(select max(age) from emp where dept2=102));

???

11.求收入最低的员工姓名及所属部门名称：
select  name,dept_name  from   dept    right    join emp   on   dept.dept1=emp.dept2 order by incoming limit 0,1;

#select min(incoming),name,dept_name from dept left join emp on dept.dept1=emp.dept2  ;
12.求李四的收入及部门名称
select  name,incoming,dept_name  from   dept    right    join emp   on   dept.dept1=emp.dept2 where name ='李四';

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

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

select name,dept_name,incoming  from dept left join emp on dept.dept1=emp.dept2 where (incoming,dept_name) in (select max(incoming) s, dept_name from dept left join emp on dept.dept1=emp.dept2  group by dept_name ) order by incoming desc ;

15.求出财务部门收益最高的俩位员工的姓名，工号，收益
select  name,sid,incoming  from   dept    right    join emp   on   dept.dept1=emp.dept2 where dept_name='财务' order by incoming desc limit 0,2;
16.查询财务部低于总平均收入的员工号与员工姓名：
select  sid,name  from   dept    right    join emp   on   dept.dept1=emp.dept2 
where dept_name='财务' and incoming<(select avg(incoming) from emp);

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

select s.dept_name from (select count(*) total ,dept_name from dept left join emp on dept.dept1=emp.dept2 where sid is not null  group by dept_name) s where s.total>1 ;

select dept_name from emp LEFT JOIN dept on emp.dept2=dept.dept1 group by dept_name having count(dept_name)>1;

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

select  name,age,dept2  from   dept    right    join emp   on   dept.dept1=emp.dept2 where incoming<=7500 and incoming>3000;
19.求入职于20世纪70年代的员工所属部门名称；
select  name,dept_name,worktime_start  from   dept    right    join emp   on   dept.dept1=emp.dept2 where worktime_start like '197%';

20.查找张三所在的部门名称；
select  name,dept_name  from   dept    right    join emp   on   dept.dept1=emp.dept2 where name ='张三';
21.列出每一个部门中年纪最大的员工姓名，部门名称；

select name,dept_name from dept left join emp on dept.dept1=emp.dept2 where (age,dept_name) in(select max(age),dept_name from dept left join emp on dept.dept1=emp.dept2 where age is not null group by dept_name);

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

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

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

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

26.求出财务部门工资最高员工的姓名和员工号
select  name,sid,incoming  from   dept    right    join emp   on   dept.dept1=emp.dept2 where dept_name='财务' order by incoming desc limit 0,1;

27.求出工资在7500到8500之间，年龄最大的员工的姓名和部门名称。
select  name,dept_name,age  from   dept    right    join emp   on   dept.dept1=emp.dept2 where incoming between 7500 and 8500  order by age desc limit 0,1 ;

子查询
select  *  from dept;
select *  from  emp ;

select  * from   dept  inner  join  emp  on  dept.dept1=emp.dept2 ;

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

select  *  from   dept    right    join emp   on   dept.dept1=emp.dept2;

财务部门的收入总和；

select sum(incoming) from (select  *  from   dept    right    join emp   on   dept.dept1=emp.dept2 ) as a where dept_name='财务'