mysql 34道作业题

mysql 34道作业题

1、取得每个部门最高薪水的人员名称

第一步：取得每个部门最高薪水

select deptno,max(sal) as maxsal from emp group by deptno;

第二步：将以上结果当做临时表t，t表和emp e表进行连接，条件是：t.deptno = e.deptno and t.maxsal = e.sal

select

​ e.ename,t.*

from

​ (select deptno,max(sal) as maxsal from emp group by deptno) t

join

​ emp e

on

​ t.deptno = e.deptno and t.maxsal = e.sal;

2、哪些人的薪水在部门的平均薪水之上

第一步：找出每个部门的平均薪水

select deptno,avg(sal) as avgsal from emp group by deptno;

第二步：将以上查询结果当做t表，t表和emp表连接

条件：部门编号相同，并且emp的sal大于t表的avgsal

select

​ t.* ,e.ename,e.sal

from

​ emp e

join

​ (select deptno,avg(sal) as avgsal from emp group by deptno) t

on

​ e.deptno = t.deptno and e.sal > t.avgsal;

3、取得部门中（所有人的)平均的薪水等级

平均的薪水等级：先计算每一个薪水的等级，然后找出薪水等级的平均值。

平均薪水的等级：先计算平均薪水，然后找出每个平均薪水的等级值。

第一步：找出每个人的薪水等级

emp e和salgrade s表连接

条件：e.sal between s.losal and s.hisal

select

​ e.ename,e.sal,e.deptno,s.grade

from

​ emp e

join

​ salgrade s

on

​ e.sal between s.losal and s.hisal;

第二步：基于以上结果继续按照deptno分组，求grade的平均值。

select

​ e.deptno,avg(s.grade)

from

​ emp e

join

​ salgrade s

on

​ e.sal between s.losal and s.hisal;

group by

​ e.deptno;

4、不准用组函数（max),取得最高薪水

第一种方案：sal降序，limit 1

select ename,sal from emp order by sal desc limit 1;

第二种方案：select max(sal) from emp;

第三种方案：表的自连接

select sal from emp where sal not in(select distinct a.sal from emp a join emp b on a.sal < b.sal);

5、取得平均薪水最高的部门编号

第一种方案：降序取第一个

第一步：找出每个部门的平均薪水

select deptno,avg(sal) as avgsal from emp group by deptno;

第二步：降序选第一个

select deptno,avg(sal) as avgsal from emp group by deptno order by avgsal desc limit 1;

第二种方案：max

select max(t.avgsal) from (select avg(sal) as avgsal from emp group by deptno) t;

select

​ deptno,avg(sal) as avgsal

from

​ emp

group by

​ deptno

having

​ avgsal = (select max(t.avgsal) from (select avg(sal) as avgsal from emp group by deptno) t);

6、取得平均薪水最高的部门名称

select

​ d.dname,avg(e.sal) as avgsal

from

​ emp e

join

​ dept d

on

​ e.deptno = d.deptno

group by

​ d.dname

order by

​ avgsal desc

limit

​ 1;

7、求平均薪水的等级最低的部门的部门名称

第一步：找出每个部门的平均薪水

select deptno,avg(sal) as avgsal from emp group by deptno;

第二步：找出每个部门的平均薪水的等级

以上t表和salgrade表连接，条件：t.avgsal between s.losal and s.hisal

select

​ t.*,s.grade

from

​ (select d.dname,avg(sal) as avgsal from emp e join dept d on e.deptno = d.deptno group by d.dname) t

join

​ salgrade s

on

​ t.avgsal between s.losal and s.hisal

where

​ s.grade = (select grade from salgrade where (select avg(sal) as avgsal from emp group by deptno order by avgsal asc limit 1));

8、取得比普通员工的最高薪水还要高的领导人姓名

第一步：找出普通员工的最高薪水

select max(sal) from emp where empno not in(select distinct mgr from emp where mgr is not null); //not in在使用的时候，后面小括号中记得排除null

第二步：找出高于1600的

select ename,sal from emp where sal>(select max(sal) from emp where empno not in(select distinct mgr from emp where mgr is not null));

9、取得薪水最高的前五名员工

select ename,sal from emp order by sal desc limit 5;

10、取得薪水最高的第六到第十名员工

select ename,sal from emp order by sal desc limit 5,5;

11、取得最后入职的5名员工

日期也可以升序降序

select ename,hiredate from emp order by hiredate desc limit 5;

12、取得每个薪水等级有多少员工

分组count

第一步：找出每个员工的薪水等级

select

​ e.ename,e.sal,s.grade

from

​ emp e

join

​ salgrade s

on

​ e.sal between s.losal and s.hisal;

第二步：继续按照grade分组统计数量

select

​ e.ename,e.sal,s.grade

from

​ emp e

join

​ salgrade s

on

​ e.sal between s.losal and s.hisal

group by

​ s.grade;

13、面试题

14、列出所有员工及领导的姓名

select

​ a.ename'员工',b.ename'领导'

from

​ emp a

left join

​ emp b

on

​ a.mgr = b.empno;

15、列出受雇日期早于其直接上级的所有员工的编号，姓名，部门名称

emp a 员工表

emp b 领导表

a.mgr = b.empno and a.hiredate < b.hiredate

select

​ a.ename'员工‘，a.hiredate,b.ename'领导’,b.hiredate,d.dname

from

​ emp a

join

​ emp b

on

​ a.mgr = b.empno

join

​ dept d

on

​ a.deptno = d.deptno

where

​ a.hiredate < b.hiredate;

16、列出部门名称和这些部门的员工信息，同时列出那些没有员工的部门

select

​ e.*,d.dname

from

​ emp e

right join

​ dept d

on

​ e.deptno = d.deptno;

17、列出至少有5个员工的所有部门

按照部门编号分组，计数，筛选出>=5

select

​ deptno

from

​ emp

group by

​ deptno

having

​ count(*)>=5;

18、列出薪金比“SMITH"多的所有员工信息

select ename,sal from emp where sal >(select sal from emp where ename ='SMITH');

19、列出所有“CLERK”（办事员）的姓名及其部门名称，部门人数

select

​ t1.*,t2.deptcount

from

​ (select

​ ​ e.ename,e.job,d.dname,d.deptno

​ from

​ ​ emp e

​ join

​ ​ dept d

​ on

​ e.deptno = d.deptno

​ where

​ e.job = 'CLERK') t1

join

​ (select deptno,count(*) as deptcount from emp group by deptno) t2

on

​ t1.deptno = t2.deptno;

20、列出最低薪资大于1500的各种工作及从事次工作的全部雇员人数

select job,count(*) from emp group by job having min(sal) > 1500;

21、列出在部门“SALES”（销售部）工作的员工的姓名，假定不知道销售部的部门编号

select ename from emp where deptno = (select deptno from dept where dname = 'SALES');

22、列出薪资高于公司平均薪资的所有员工，所在部门，上级领导，雇员的工资等级

select

​ ​ e.ename'员工‘，d.dname,1.ename'领导',s.grade

from

​ ​ emp e

join

​ ​ dept d

on

​ ​ e.deptno = d.deptno

left join

​ ​ emp 1

on

​ ​ e.mgr = 1.empno

join

​ ​ salgrade s

on

​ ​ e.sal between s.losal and s.hisal

where

​ ​ e.sal > (select avg(sal) from emp);

23、列出与“SCOTT”从事相同工作的所有员工及其部门名称

select

​ ​ e.ename,e.job,d.dname

from

​​ emp e

join

​​ dept d

on

​ ​ e.deptno = d.deptno

where

​ ​ e.job = (select job from emp where ename = 'SCOTT')

and

​​ e.ename<>'SCOTT';

24、列出薪资等于部门30中员工的薪资的其他员工的姓名和薪资

select

​​ ename,sal

from

​​ emp

where

​​ sal in(select distinct sal from emp where deptno = 30)

and

​​ deptno<>30;

25、列出薪资高于在部门30工作的所有员工的薪资的员工姓名和薪资，部门名称

select

​ ​ e.ename,e.sal,d.dname

from

​ ​ emp e

join

​ ​ dept d

on

​ ​ e.deptno = d.deptno

where

​​ e.sal>(select max(sal) from emp where deptno =30);

26、列出在每个部门工作的员工数量，平均工资和平均服务期限

select

​ ​ d.deptno,count(e.ename) ecount,ifnull(avg(e.sal),0) as avgsal,ifnull(avg(timestampdiff(YEAR,hiredate,now)),0) as avgservicetime

from

​ ​ emp e

right join

​​ dept d

on

​ ​ e.deptno = d.deptno

group by

​​ d.deptno;

27、列出所有员工的姓名、部门名称和工资

select

​ ​ e.ename,d.dname,e.sal

from

​ ​ emp e

join

​​ dept d

on

​ ​ e.deptno = d.deptno;

28、列出所有部门的详细信息和人数

select

​​ d.deptno,d.dname,d.loc,count(e.ename)

from

​​ emp e

right join

​​ dept d

on

​​ e.deptno = d.deptno

group by

​​ d.deptno,d.dname,d.loc;

29、列出各种工作的最低工资及从事此工作的雇员姓名

select

​ ​ job,min(sal) as minsal

from

​ ​ emp

group by

​ ​ job;

emp e和以上t连接

select

​ ​ e.ename,t.*

from

​ ​ emp e

join

​ ​ (select

​ job,min(sal) as minsal

​​ from

​ ​ emp

​​ group by

​ ​ job) t

on

​​ e.job = t.job and e.sal =t.minsal;

30、列出各个部门的MANAGER的最低薪资

select

​​ deptno,min(sal)

from

​ ​ emp

where

​ ​ job = 'MANAGER'

group by

​ ​ deptno;

31列出所有员工的年薪资，按年薪从低到高排序

select

​ ​ ename,(sal + ifnull(comm,0) ) * 12 as yearsal

from

​ ​ emp

order by

​ ​ yearsal asc;

32、求出员工领导薪水超过3000的员工名称与领导

select

​ ​ a.ename'员工',b.ename'领导'

from

​ ​ emp a

join

​ ​ emp b

on

​ ​ a.mgr = b.empno

where

​ ​ b.sal >3000;

33、求出部门名称中带'S'字符的部门员工的工资合计，部门人数

select

​ ​ d.deptno,d.dname,d.loc,count(e.ename),ifnull(sum(e.sal),0) as sumsal

from

​ ​ emp e

right join

​ ​ dept d

on

​ ​ e.deptno = d.deptno

where

​ ​ d.dname like '%s%'

group by

​ ​ d.deptno,d.dname,d.loc;

34、给任职日期超过30年的员工加薪10%

update emp set sal = sal * 1.1 where timestampdiff(YEAR,hiredate,now()) > 30