数据库之多表练习

create table dept (dept1 int(10) PRIMARY key,dept_name VARCHAR(20));
desc dept;
SELECT * from dept;
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 int(10) PRIMARY key,name VARCHAR(20),age int(20),worktime_start date,incoming int(20),dept2 int(20));
desc emp;
SELECT * from emp;
INSERT INTO emp VALUES(1789,'张三',35,"1980-01-01",4000,101);
INSERT into emp VALUES(1674,'李四',32,"1983-04-01",3500,101);
INSERT into emp VALUES(1776,'王五',24,"1990-07-01",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);

SELECT * from dept;
SELECT * from emp;

1.列出每个部门的平均收入及部门名称; 
求：group by dept1,avg(incoming),dept_name,

方法1（基本法）
select avg(incoming),dept_name from dept,emp where dept.dept1=emp.dept2 group by dept1;
方法2（内连接）
select avg(incoming),dept_name from dept join emp on dept.dept1=emp.dept2 group by dept1;
方法3（左连接）
select avg(incoming),dept_name from dept left join emp on dept.dept1=emp.dept2 group by dept1;
方法4（右连接）
select avg(incoming),dept_name from dept right join emp on dept.dept1=emp.dept2 group by dept1;

方法5
临时表法
select * from dept right join emp on dept.dept1=emp.dept2
select  avg(incoming),t.dept_name from (select * from dept right join emp on dept.dept1=emp.dept2)t group by t.dept_name;

2.财务部门的收入总和； 
已知：dept_name='财务'
求：sum(incoming)

方法1（基本法）
select sum(incoming) from dept,emp where dept.dept1=emp.dept2 and dept_name='财务';
方法2（内连接）
select sum(incoming) from dept inner join emp on dept.dept1=emp.dept2 where dept_name='财务';
方法3（左连接）
select sum(incoming) from dept left join emp on dept.dept1=emp.dept2 where dept_name='财务';
方法4（右链接）
select sum(incoming) from dept right join emp on dept.dept1=emp.dept2 where dept_name='财务';

3.It技术部入职员工的员工号   
已知：dept_name='IT技术'
求：sid

方法1（基本法）
select sid from dept,emp where dept.dept1=emp.dept2 and dept_name='IT技术';
方法2（内连接）
select sid from dept inner join emp on dept.dept1=emp.dept2 where dept_name='IT技术';
方法3（左连接）
select sid from dept left join emp on dept.dept1=emp.dept2 where dept_name='IT技术';
方法4（右链接）
select sid from dept right join emp on dept.dept1=emp.dept2 where dept_name='IT技术';

4.财务部门收入超过2000元的员工姓名 
已知：dept_name='财务'，incoming>2000;
求：name

方法1（基本法）
select name from dept,emp where dept.dept1=emp.dept2 and dept_name='财务' and incoming>2000;
方法2（内连接）
select name from dept inner join emp on dept.dept1=emp.dept2 where dept_name='财务' and incoming>2000; 
方法3（左连接）
select name from dept left join emp on dept.dept1=emp.dept2 where dept_name='财务' and incoming>2000;
方法4（右连接）
select name from dept right join emp on dept.dept1=emp.dept2 where dept_name='财务' and incoming>2000;

5.找出销售部收入最低的员工的入职时间；  
已知：dept_name='销售'，min（incoming）;
求：worktime_start

方法1（基本法）
select worktime_start from dept,emp where dept.dept1=emp.dept2 and dept_name='销售' and incoming=(select min(incoming) from dept,emp where dept.dept1=emp.dept2 and dept_name='销售');
方法2（内连接）
select worktime_start from dept inner join emp on dept.dept1=emp.dept2 where dept_name='销售' and incoming=(select min(incoming) from dept,emp where dept.dept1=emp.dept2 and dept_name='销售');
方法3（左连接）
select worktime_start from dept left join emp on dept.dept1=emp.dept2 where dept_name='销售' and incoming=(select min(incoming) from dept,emp where dept.dept1=emp.dept2 and dept_name='销售');

6.找出年龄小于平均年龄的员工的姓名，ID和部门名称  
已知：age<avg(age)
select avg(age) from emp;
求：name,sid,dept_name

方法1（内连接）
select name,sid,dept_name from dept join emp on dept.dept1=emp.dept2 where age<(select avg(age) from emp);
方法2（左连接）
select name,sid,dept_name from dept left join emp on dept.dept1=emp.dept2 where age<(select avg(age) from emp);

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

7.列出每个部门收入总和高于9000的部门名称
已知：group by dept2,sum(incoming)>9000,  
求：dept_name

方法
select dept_name from dept join emp on dept.dept1=emp.dept2 group by dept_name having sum(incoming)>9000;

SELECT dept_name from dept join emp on dept.dept1=emp.dept2 group by dept2 having sum(incoming)>9000;

8.查出财务部门工资少于3800元的员工姓名
已知：dept_name='财务',incoming<3800 ==》且关系
求：name

方法（内连接） 
select name from dept join emp on dept.dept1=emp.dept2 where dept_name='财务' and incoming<3800;

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

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

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

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

11.求收入最低的员工姓名及所属部门名称：
已知：min(incoming)
select min(incoming) from emp;
求：name，dept_name

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

12.求李四的收入及部门名称
已知：name='李四'
求：incoming，dept_name

方法
select incoming,dept_name from dept join emp on dept.dept1=emp.dept2 where name='李四';

13.求员工收入小于4000元的员工部门编号及其部门名称
已知：incoming<4000;
求:dept1,dept_name

方法(内连接）
select dept1,dept_name from dept join emp on dept.dept1=emp.dept2 where incoming<4000;

14.列出每个部门中收入最高的员工姓名，部门名称，收入，并按照收入降序；
已知：max(incoming),group by dept_name;
select max(incoming),dept2 from emp group by dept2;
求：name(emp),dept_name(emp),incoming(dept)desc;

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

15.求出财务部门收益最高的俩位员工的姓名，工号，收益
已知：dept_name='财务',order by incoming desc,limit 0,2
求：name,sid,incoming

方法
select name,sid,incoming from dept left join emp on dept.dept1=emp.dept2 where dept_name='财务' order by incoming desc limit 2;

16.查询财务部低于平均收入的员工号与员工姓名：
已知：dept_name='财务',incoming <avg(incoming)
select avg(incoming) from dept join emp on dept.dept1=emp.dept2 ;
求：sid,name 

方法1（内连接）
select sid,name from dept join emp on dept.dept1=emp.dept2 where incoming < (select avg(incoming) from dept join emp on dept.dept1=emp.dept2) and dept_name='财务');

select sid,name from dept join emp on dept.dept1=emp.dept2 where dept_name='财务' and incoming < (select avg(incoming) from dept join emp on dept.dept1=emp.dept2 );
方法2（左连接）
select sid,name from dept  left join emp on dept.dept1=emp.dept2 where dept_name='财务' and incoming < (select avg(incoming) from dept left join emp on dept.dept1=emp.dept2);

17.列出部门员工数大于1个的部门名称； 
已知：count(dept2)>1
求：dept_name
select dept_name,count(dept2) from dept join emp on dept.dept1=emp.dept2 group by dept2 having count(dept2)>1;

18.列出部门员工收入不超过7500，且大于3000的员工年纪及部门编号；
已知：incoming>3000 and incoming<=7500
求：age,dept2
select age,dept2 from dept join emp on dept.dept1=emp.dept2 where incoming>3000 and incoming<=7500;

19.求入职于20世纪70年代的员工所属部门名称；
已知：'1970%'
求：dept_name

方法1（内连接）
select dept_name from dept join emp on dept.dept1=emp.dept2 where worktime_start like '197%';
方法2（左连接）
select dept_name from dept right join emp on dept.dept1=emp.dept2 where worktime_start like '197%';
方法3（右连接）
select dept_name from dept left join emp on dept.dept1=emp.dept2 where worktime_start like '197%';

20.查找张三所在的部门名称；
已知：name='张三'
求：dept_name

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

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

方法
select name,dept_name from dept join emp on dept.dept1=emp.dept2 where (age,dept_name) in (select max(age),dept_name from emp join dept on dept.dept1=emp.dept2 group by dept_name);

22.列出每一个部门的员工总收入及部门名称；
求：sum(incoming);dept_name,group by dept2

方法1（内连接）：
select sum(incoming),dept_name from dept join emp on dept.dept1=emp.dept2 group by dept2;
方法2（左连接）：
select sum(incoming),dept_name from dept left join emp on emp.dept2=dept.dept1 group by dept_name;
方法3（右连接）：
select sum(incoming),dept_name from dept right join emp on emp.dept2=dept.dept1 group by dept_name;

23.列出部门员工收入大于7000的员工号，部门名称；
已知：incoming>7000
求：sid,dept_name

方法1：
select sid,dept_name from dept join emp on dept.dept1=emp.dept2 where incoming>7000;
方法2：
select sid,dept_name from dept left join emp on emp.dept2=dept.dept1 where incoming>7000;

24.找出哪个部门还没有员工入职；
求：name is null,dept_name not in;

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

select * from emp right join dept on emp.dept2=dept.dept1 group by dept_name having count(dept2)=0;

select dept_name from dept left join emp on dept.dept1=emp.dept2 group by dept1 having (count(sid)=0);

select dept_name from dept where dept_name not in (select distinct(dept_name) from emp join dept on dept.dept1=emp.dept2);

25.先按部门号大小排序，再依据入职时间由早到晚排序员工信息表 ；
①部门降序： order by dept2;
②入职时间升序： order by worktime_start asc;

方法：
select * from emp order by dept2 desc,worktime_start asc;

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

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

27.求出工资在7500到8500之间，年龄最大的员工的姓名和部门名称。
已知：incoming>=7500 and incoming<=8500(或者 between 7500 and 8500) ,max(age)
select max(age) from emp where incoming between 7500 and 8500;

求：name，dept_name

方法（内连接）：
select name,dept_name from dept join emp on dept.dept1=emp.dept2 where age=(select max(age) from emp where incoming between 7500 and 8500) and incoming between 7500 and 8500;