肖SIR__数据库之多表练习__12.2

已知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

 

 

1.列出每个部门的平均收入及部门名称;

结果：

emp： avg(incoming) ，dept_name     

dept:  dept_name        group   by 

条件：group  by   dept_name

 

方法1：【有链接+条件（分组）】

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

2.财务部门的收入总和；

 

结果：

emp：  sum（incoming）

dept  ：dept_name='财务'

方法1：select  sum(incoming ) from dept left join  emp  on  dept.dept1=emp.dept2  where dept_name="财务" ;

方法2:   select sum(incoming) from emp where dept2=(select dept1 from dept where dept_name='财务' );

方法3：select sum(incoming) from (select  *  from   dept    right    join emp   on   dept.dept1=emp.dept2 ) as a where dept_name='财务'
3.It技术部入职员工的员工号

结果：emp ：sid

条件：dept    dept_name='IT 技术部门’

方法1：select sid from dept inner join emp on dept.dept1=emp.dept2 where dept_name="IT技术";

方法2：select sid from emp where dept2=(select dept1 from dept where dept_name='IT技术' ) ;

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

结果：name

条件：dept ：  dept_name='财务'         incoming>2000

方法1：select name from  dept inner join emp on dept.dept1=emp.dept2  where dept_name="财务" and incoming>2000;

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

方法3：select a.name from (SELECT * from dept INNER JOIN emp on dept.dept1=emp.dept2  where dept_name='财务' )as a where a.incoming>2000

 

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

结果：worktime_start

条件：min(incoming)     ，dept_name='销售' 

方法1：SELECT woektime_start from dept left join emp on dept.dept1=emp.dept2 where dept_name="销售" and incoming=(SELECT min(incoming) from dept inner join emp on dept.dept1=emp.dept2 and dept2=(select dept1 from dept where dept_name='销售'))

 方法2：

select worktime_start from emp where (dept2,incoming)in(select dept2,min(incoming) from emp where dept2=(select dept1 from dept where dept_name="销售"));

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

 结果： name  sid  dept_name

条件  ：avg(age) ,  age

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

方法2：select name,dept_name,age from emp LEFT JOIN dept on emp.dept2=dept.dept1 where age<(SELECT avg(age) from dept INNER JOIN emp on dept.dept1=emp.dept2 )

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

结果：dept_name

条件：  sum（incoming）>9000  ，group  by   dept_name

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

方法2：select a.dept_name from   (select dept_name,sum(incoming) from dept LEFT JOIN emp on dept.dept1=emp.dept2 GROUP BY dept_name  HAVING sum(incoming)>9000)a;

方法3：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元的员工姓名

结果： name

条件：dept_name  =' 财务'     incoming<3800

方法1：select name,dept_name from dept inner join emp on dept.dept1=emp.dept2 where dept_name="财务" and incoming<3800;

方法2：select name from emp where dept2 =(select dept1 from dept where dept_name='财务') and incoming<3800;

9.求财务部门最低工资的员工姓名；

结果：name

条件：
dept：  dept_name='财务'
emp：  min（ incoming）

方法1：

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

方法2 ：（只能显示一个，有多个一样）

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

 方法3：

select name from emp where (incoming,dept2) = (select min(incoming),dept2 from dept join emp on dept1=dept2 group by dept_name having dept_name='财务');

 

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

结果： name

条件：  dept_name='销售'         max（age）

方法1：

select emp.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.dept_name="销售") and dept.dept_name="销售";

方法2：（缺陷：有多个结果，就显示一个）

select name from emp inner join dept on emp.dept2=dept.dept1 where dept_name='销售' ORDER BY age desc limit 1

方法3：

select b.name from (select * from dept join emp on dept1=dept2 where dept_name="销售") b where age=(select max(age) from dept join emp on dept1=dept2 where dept_name="销售");
11.求收入最低的员工姓名及所属部门名称：

结果： name  ，  dept _name

条件： min（incoming） 

方法1：

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

方法2：

SELECT name,dept_name from dept INNER JOIN emp on dept.dept1=emp.dept2  WHERE incoming=(SELECT min(incoming)from  emp );

 

12.求李四的收入及部门名称

结果：incoming   ，dept_name

条件：name='李四'

方法1：Select incoming,dept_name from dept inner join emp on dept1=dept2 where name='李四';

 

13.求员工收入小于4000元的员工部门编号及其部门名称
结果：   dept2   ，dept_name

条件：  incoming<4000

方法1：SELECT dept1,dept_name  from dept INNER JOIN emp on dept.dept1=emp.dept2 WHERE incoming<4000;

 方法2：

SELECT  dept_name,dept1   from dept WHERE dept1 in (select  dept2 from  emp  where incoming<4000)  ;

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

方法1：先求每个部门名称和最高工资，在使用合表区匹配（部门名称和薪资一样的员工）

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

方法2：

select name,incoming,dept_name from dept INNER JOIN
(SELECT emp.name,emp.incoming,emp.dept2 from emp RIGHT JOIN
(select MAX(incoming) as incoming ,dept2 from emp group by dept2)c
on emp.dept2=c.dept2 and emp.incoming=c.incoming)b
On dept.dept1=b.dept2 ORDER BY incoming DESC;

方法3：

select  b.name,b.incoming,b.dept_name from  (select MAX(incoming) as incoming ,dept2 from emp group by dept2)a INNER JOIN (select * from dept INNER JOIN emp where  dept1=dept2 )b  on a.incoming=b.incoming and  a.dept2=b.dept2  ORDER BY b.incoming desc ;

方法4：（有缺陷的方法）先降序排出最高工资的人员，在分组，在派排序

select name,dept_name,incoming from (select *from dept a inner join emp b ON
a.dept1=b.dept2 ORDER BY incoming desc )s group by s.dept_name order by incoming
desc

 

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

结果： name  ， sid ，incoming

条件：dept_name='财务'       limit   0,2

方法1：

SELECT name,sid,incoming  from dept INNER JOIN emp on dept.dept1=emp.dept2 WHERE dept_name="财务" ORDER BY incoming  desc  limit 0,2 ;

方法2：

select name,sid,incoming from (select * from emp join dept on dept1=dept2 where dept_name='财务' order by incoming desc )as a limit 0,2;

方法3：

SELECT NAME,sid,incoming from emp  WHERE dept2=(SELECT dept1 FROM dept WHERE dept_name='财务') ORDER BY incoming desc LIMIT 2;

 

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

结果： sid ，name

条件：dept_name='财务'      avg（incoming）

方法1：

select sid,name from dept inner join emp on dept.dept1=emp.dept2 where incoming<(select avg(incoming)from dept right join emp on dept.dept1=emp.dept2) and dept_name="财务";

方法2：

select sid,name from (select * from dept LEFT JOIN emp on dept.dept1=emp.dept2 WHERE dept_name="财务")q where q.incoming<(select avg(incoming) from emp);

方法3：

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

方法4：

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

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

结果： dept_name

条件： count（name）>1   ，group  by  dept_name

方法1：select dept_name from emp INNER JOIN dept on dept1=dept2 GROUP BY dept_name HAVING count(name)>1;

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

方法3：SELECT dept_name FROM (select dept_name,count(dept_name) c from dept INNER JOIN emp ON dept.dept1=emp.dept2 GROUP BY dept_name) a WHERE a.c>1;

方法4：select dept_name from dept inner join (SELECT count(dept2),dept2 from emp GROUP BY dept2 having count(dept2)>1)as a on dept.dept1=a.dept2

 

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

结果：age ， dept2 或dept1

条件：incoming<=7500  ,incoming>3000

方法1：select age,dept2,incoming from emp where incoming<=7500 and incoming>3000; （单表）

方法2：select age,sid from dept left join emp on dept.dept1=emp.dept2 where incoming<=7000&&incoming>3000 ;

 

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

结果： dept_name

条件：woek_time like     197%

方法1：SELECT dept_name from dept INNER JOIN emp on dept.dept1=emp.dept2 WHERE woektime_start BETWEEN 1970 and 1979;

方法2：select dept_name from dept inner join emp on dept.dept1=emp.dept2 where woektime_start>=1970 and  woektime_start<1980;

方法3：

select dept_name from dept left join emp on dept.dept1=emp.dept2 where woektime_start like "197%";

  

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

结果：dept_name

条件：name='张三'

select dept_name from dept left join emp on dept.dept1=emp.dept2 where name="张三"

方法2：

select dept_name from dept where dept1=(select dept2 from emp where name='张三') ;

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

结果： name  ，dept_name

条件： group  by     dept_name        max（age）

方法1：SELECT dept_name,name from dept INNER JOIN   emp on dept.dept1=emp.dept2 where (dept_name,age ) in  (SELECT dept_name ,max(age) from dept INNER JOIN   emp on dept.dept1=emp.dept2 GROUP BY dept_name);

方法2：select  b.name,b.dept_name from  (select MAX(age) as age ,dept2 from emp group by dept2)a INNER JOIN (select * from dept INNER JOIN emp where  dept1=dept2 )b  on a.age=b.age and  a.dept2=b.dept2   ;

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

结果：dept_name ,  sum(incomig)

条件：group  by    dept_name  ,

方法1：select sum(incoming),dept_name  from dept left join emp on dept.dept1=emp.dept2 group by dept_name;

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

结果：sid    ，dept_name

条件：incoming>7000

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

 

24.找出哪个部门还没有员工入职；（左独有数据）

结果： dept_name

条件：name  is  null

方法1：SELECT dept_name from dept LEFT JOIN emp on dept.dept1=emp.dept2 WHERE name is null;

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

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

结果：员工信息表       所有信息    * 

emp   ：dept2     order  by    desc  （降序）

emp ： woek_time    时间是越小就越早(升序)

方法1：select *  from dept left join emp on dept.dept1=emp.dept2 order by  dept1 desc  ,worktime_start asc;

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

 

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

结果：name  ，sid

条件：max（incomg） ，dept_name='财务'

方法1：

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

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

结果：name，dept_name

条件：   incoming=< 7500, incoming>=8000   max（age）

方法1：SELECT name,dept_name FROM dept right 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 ;、

方法2：

select name,dept_name from dept join emp on dept1=dept2 where incoming between 7500 and 8500 and (dept_name,age) in (select dept_name,max(age) from emp join dept on dept1=dept2 group by dept_name);