多测师肖老师__多表练习（8.1）

多表练习

已知2张基本表：部门表：dept （部门号，部门名称）;员工表 emp（员工号，员工姓名，年龄，入职时间，收入，部门号）

1：dept表中有4条记录：

     部门号(dept1)  部门名称(dept_name )

     101     财务

     102     销售

     103     IT技术

     104     行政

2：emp表中有6条记录：

      员工号员工姓名年龄入职时间收入部门号对应字段名称为： (sid name age worktime_start incoming dept2)

      1789    张三 35 1980/1/1 4000 101

      1674    李四 32 1983/4/1 3500 101

      1776    王五 24 1990/7/1 2000 101

      1568    赵六 57 1970/10/11 7500 102

      1564    荣七 64 1963/10/11 8500 102

      1879    牛八 55 1971/10/20 7300 103

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),woektime_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','刘十',55,'1971/10/21',7000,'105');
insert into emp VALUES ('1881','十一',55,'1971/10/21',7000,'106');
drop  table  dept ;
drop  table  emp ;
select  *  from dept;
select *  from  emp ;

表1：

表2：

========================================

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

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

3.It技术部入职员工的员工号
emp sid
dept it技术部门

name ，sid

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

方法一：

SELECT name ,incoming FROM emp INNER JOIN dept ON dept.dept1 = emp.dept2 WHERE dept_name = "财务" AND incoming >2000;

方法二：

Select name from (select * from dept left join emp on dept.dept1=emp.dept2) A where dept_name='财务' and incoming>2000;

方法三：select name from emp where incoming>2000 and dept2=(select dept1 from dept where dept_name="财务")

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

方法一：

select woektime_start from emp ,dept where dept1=dept2 and incoming=(select min(incoming) from dept INNER JOIN emp on dept.dept1=emp.dept2 WHERE dept_name='销售') and dept_name='销售' ;

6.找出年龄小于平均年龄的员工的姓名，ID和部门名称（部门平均，所有工资平均*）

方法一：select name,sid,dept_name from dept left JOIN emp on dept1=dept2 where age<(SELECT AVG(age) from emp)

方法二：select dept_name,sid,name from emp INNER JOIN dept on dept1=emp.dept2 where age<(select avg(age) from emp INNER JOIN dept on dept1=emp.dept2) ; 可以简化方法一

方法三：Select name,dept_name,sid from (select * from dept left join emp on dept.dept1=emp.dept2) A where age<(select avg(age) from emp);

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

方法一：

select dept_name from emp INNER JOIN dept on dept.dept1=emp.dept2 GROUP BY dept_name having sum(incoming)>9000 ;

方法二：

select s.dept_name from (SELECT dept_name,SUM(incoming) from dept INNER JOIN emp on dept.dept1=emp.dept2 GROUP BY dept_name having SUM(incoming)>9000 )s

方法三：
8.查出财务部门工资少于3800元的员工姓名
财务 dept
incoming emp

name
<

方法一：

select name from(select * from dept INNER JOIN emp on dept.dept1=emp.dept2) s where dept_name = "财务" AND incoming < 3800;

方法二：

SELECT name FROM dept left JOIN emp on dept.dept1=emp.dept2 WHERE dept_name="财务" and incoming<3800

方法三：

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

方法一：

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

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

方法一：select name from dept INNER JOIN emp on dept.dept1=emp.dept2 where dept_name="销售" and age=(select max(age) from dept INNER JOIN emp on dept.dept1=emp.dept2 where dept_name="销售")

方法二：

SELECT name,age from emp WHERE age=(SELECT MAX(age) FROM dept LEFT JOIN emp ON dept.dept1=emp.dept2 WHERE dept_name="销售") and dept2=(select dept1 from dept where dept_name="销售" );

方法三：

select name from (select * from (select * from dept left join emp on dept.dept1=emp.dept2 ) A where dept_name='销售') B order by age desc limit 0,1;

简化：

select name from (select * from dept left join emp on dept1=dept2 where dept_name='销售') B order by age desc limit 0,1;

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

方法一：SELECT name,dept_name from dept,emp WHERE dept1=dept2 ORDER BY incoming LIMIT 0,1;

方法二：

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

方法三：SELECT name,dept_name FROM dept LEFT JOIN emp ON dept.dept1=emp.dept2 WHERE incoming=(SELECT min(incoming) from emp);

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

方法五：

select name,dept_name from (select * from dept left join emp on dept.dept1=emp.dept2 ) B where B.incoming=(SELECT min(incoming) from (select * from dept left join emp on dept.dept1=emp.dept2 ) A)

12.求李四的收入及部门名称
方法一：SELECT incoming,dept_name FROM dept LEFT JOIN emp ON dept.dept1=emp.dept2 WHERE name="李四";

方法二：Select incoming,dept_name from (select * from dept left join emp on dept.dept1=emp.dept2 ) A where name='李四'

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

方法一：SELECT sid,dept_name from dept left JOIN emp on dept.dept1=emp.dept2 WHERE incoming<4000

方法二：select dept1,dept_name from (select * from (select * from dept left join emp on dept.dept1=emp.dept2) A where incoming<4000) B

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

方法一：

select dept_name,name,incoming from(SELECT * FROM dept INNER JOIN emp on dept.dept1=emp.dept2 ORDER BY incoming DESC) a GROUP BY dept_name ORDER BY a.incoming desc

方法二：

SELECT name,dept_name FROM dept,emp WHERE dept1=dept2 and (age,dept_name) in(
SELECT MAX(age),dept_name FROM dept,emp WHERE dept1=dept2 GROUP BY dept_name)

方法三：

SELECT name,dept_name,incoming from dept left join emp on dept.dept1=emp.dept2 where ('name',dept_name,incoming)=any(SELECT 'name',dept_name,max(incoming) from dept left join emp on dept.dept1=emp.dept2 group by dept_name) ORDER BY incoming desc;

15.求出财务部门收益最高的俩位员工的姓名，工号，收益

条件： dept_name ="财务" order by desc

结果： name sid incoming

方法一：

select sid,name,incoming from dept INNER JOIN emp on dept.dept1=emp.dept2 where dept_name='财务' ORDER BY incoming desc limit 0,2

方法二：select name,sid,incoming from (select * from (select * from dept left join emp on dept.dept1=emp.dept2 ) A where dept_name='财务') B order by incoming desc limit 0,2

16.查询财务部低于平均收入的员工号与员工姓名：

条件： dept_name ="财务" incoming <avg（incoming）

结果：sid name

方法一：

select sid,name from dept left JOIN emp on dept1=dept2 where incoming<(SELECT AVG(incoming) from emp) and dept_name='财务'

方法二：

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

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

方法一：select dept_name from(select dept_name, count(name)a from emp left join dept on dept1=dept2 GROUP BY dept_name HAVING a>1 ) s;

方法二：select dept_name from dept left JOIN emp on dept1=dept2 GROUP BY dept_name HAVING COUNT(name)>1

方法三：select dept_name from dept where dept1 in( select dept2 from emp group by dept2 having count(dept2)>1 );

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

方法一：

SELECT age,dept1 from dept,emp WHERE dept1=dept2 and incoming<=7500 and incoming>3000;

方法二：

select age,sid from (select * from dept left join emp on dept.dept1=emp.dept2) A where incoming>3000 and incoming<=7500;

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

条件：20世纪：1970-1979

结果：dept_name

方法1：SELECT DISTINCT(dept_name) from dept,emp WHERE dept1=dept2 and woektime_start LIKE '197%'

方法2：SELECT DISTINCT(dept_name) from dept where dept1 in (select dept2 from emp where woektime_start like '197%')

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

条件： name='张三'

结果：dept_name

方法一：select dept_name from (select * from dept left join emp on dept.dept1=emp.dept2) A where name='张三'

方法二：select dept_name from dept RIGHT join emp on dept.dept1=emp.dept2 where name="张三"

方法三：SELECT dept_name from dept where dept1=(SELECT dept2 from emp where name='张三');

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

方法一：

select name ,dept_name from dept,emp where dept1=dept2 and (dept_name,age)in(SELECT dept_name,max(age) from dept,emp where dept1=dept2 GROUP BY dept_name);

方法二：排序只能取一个（当相同的数据只取一个）

select name ,dept_name from (select name ,dept_name from dept,emp where dept1=dept2 order by age desc)a group by a.dept_name ;

方法三：

select c.name, c.dept_name from (SELECT dept_name,max(age)a from dept,emp where dept1=dept2 GROUP BY dept_name) s LEFT JOIN (SELECT * from dept,emp where dept1=dept2)c on s.dept_name=c.dept_name and s.a=c.age ;

当做一个表

另一张表

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

SELECT dept_name ,sum(incoming) from dept LEFT JOIN emp on dept.dept1=emp.dept2 group by dept_name;

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

条件： incoming>7000

结果：sid ，dept_name

方法一：SELECT dept_name,sid from (select * from dept left join emp on dept.dept1=emp.dept2) A where incoming>7000

方法二：
SELECT sid,dept_name from dept left JOIN emp on dept.dept1=emp.dept2 WHERE incoming>7000

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

条件： is null 判断： nane sid （实际求左独有）

结果：dept_name

方法一：select dept_name from(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) as a where name is null

方法二：

SELECT dept_name from dept left JOIN emp on dept.dept1=emp.dept2 WHERE name is NULL

方法三：

select dept_name from (select * from dept left join emp on dept.dept1=emp.dept2) A where name is null

方法四：

SELECT dept_name from dept LEFT JOIN emp on dept.dept1=emp.dept2 GROUP BY dept_name having count(sid)<1;

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

条件： order by desc 时间：asc 早数值就越小

d结果：员工信息表 *

方法一：

SELECT * from emp INNER JOIN dept ON emp.dept2 = dept.dept1 ORDER BY dept1 desc ,woektime_start asc ;

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

方法一：（重复最高工资就显示一个，缺陷）

SELECT name,sid FROM dept LEFT JOIN emp on dept1=dept2 WHERE dept_name='财务' ORDER BY incoming DESC LIMIT 0,1;

方法二：

SELECT name,sid from dept LEFT JOIN emp on dept.dept1=emp.dept2 where incoming=(SELECT max(incoming) from dept LEFT JOIN emp on dept.dept1=emp.dept2 where dept_name='财务') and dept_name='财务';

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

方法一：

SELECT name,dept_name FROM dept LEFT JOIN emp ON dept.dept1=emp.dept2 WHERE incoming<=8500 AND incoming>=7500 and age=(SELECT MAX(age) FROM emp where incoming<=8500 AND incoming>=7500 ) ;

方法二：

SELECT name,dept_name FROM dept INNER JOIN emp on dept1=dept2 WHERE incoming>=7500 and incoming<=8500 ORDER BY age DESC LIMIT 0,1;

SELECT dept_name from dept left JOIN emp on dept.dept1=emp.dept2 WHERE name is NULL