多测师肖老师__多表练习（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