数据库---34练习题

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

select e.ename ,t.* 
from emp e 
join (select deptno,max(sal) maxsal from emp group by deptno) t 
on t.deptno=e.deptno and t.maxsal=e.sal;

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

select e.ename, e.sal 
from emp e 
join (select deptno,avg(sal) avgsal  from emp group by deptno) t 
on( e.deptno=t.deptno and (e.sal > t.avgsal));

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

select e.ename,e.deptno,s.grade 
from emp e
join salgrade s 
on e.sal between s.losal and s.hisal;
select t.deptno,avg(grade) from  t group by t.deptno;//第一条语句看作表t

4、不准用分组函数（Max），取得最高薪水

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

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

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

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

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

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

select e.deptno,avg(sal) as avgsal from emp e group by e.deptno order by avgsal limit 0,1;
select e.deptno,d.dname,avg(sal) as avgsal from emp e join dept d on e.deptno=d.deptno group by e.deptno order by avgsal limit 0,1;

8、取得比普通员工(员工代码没有在mgr字段上出现的)的最高薪水还要高的领导人姓名

第一步，找出普通员工的最高工资
not in在用的时候记得排除null

select max(sal) from emp where empno not in (select distinct mgr from emp where mgr is not 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 e.ename ,e.sal from emp e order by e.sal desc limit 0,5;

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

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

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

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

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

select s.grade ,count(grade) from emp e join salgrade s on e.sal between s.losal and s.hisal group by grade;

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

select a.ename '员工',b.ename '领导' from emp a left join emp b on a.mgr =b.empno;

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

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 d.dname,e.* from emp e right join dept d on d.deptno=e.deptno;

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

select e.deptno,d.dname,count(e.ename) as sum from emp e join dept d on e.deptno=d.deptno group by e.deptno having sum >=5 ;

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

select e.sal from emp e where e.ename ='smith';
select e.* from emp e where e.sal>(select e.sal from emp e where e.ename ='smith');

19、列出所有"CLERK"(办事员)的姓名及其部门名称,部门的人数
select e.name,d.dname from emp e where e.job=‘clerk’ join dept d on e.deptno=d.deptno ;

select e.ename,d.dname,e.deptno from emp e join dept d on e.deptno=d.deptno where e.job='clerk'; m
select e.deptno,count(*) as sum from emp e group by e.deptno; n

from后面接where语句，后面有可能连接就是 from …join …on…where

select m.ename ,m.dname  ,sum from (select e.ename,d.dname,e.deptno from emp e join dept d on e.deptno=d.deptno where e.job='clerk')m join (select e.deptno,count(*) as sum from emp e group by e.deptno)n on m.deptno =n.deptno;

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

select e.job,e.sal from emp e where e.sal>1500; m
select e.job,count(*) as sum from emp e group by e.job; n
select distinct m.job,n.sum from (select e.job,e.sal from emp e where e.sal>1500)m join (select e.job,count(*) as sum from emp e group by e.job)n on m.job=n.job;

以上没考虑最低薪资

select e.job,min(sal) as minsal from emp e group by e.job having minsal >1500; m

select distinct m.job,n.sum from (select e.job,min(sal) as minsal from emp e group by e.job having minsal>1500)m join (select e.job,count(*) as sum from emp e group by e.job)n on m.job=n.job;

21、列出在部门"SALES"<销售部>工作的员工的姓名,假定不知道销售部的部门编号
知道部门编号

select e.ename,d.dname 
from emp e 
join dept d 
on e.deptno =d.deptno 
where e.deptno=30;

不知道编号

	select e.ename from emp e where e.deptno=(select deptno from dept  where dname ='sales');

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

select avg(sal)avgsal from emp ;//找出平均工资
select e.ename as '员工',a.ename as '领导', d.dname,s.grade from emp e 
left join emp a  //左外链接
on e.mgr=a.empno
join dept d 
on e.deptno=d.deptno 
join salgrade s 
on e.sal between s.losal and s.hisal
where e.sal>(select avg(sal)avgsal from emp)

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

select e.ename,d.dname from emp e 
join dept d
on d.deptno=e.deptno
where e.job=(select e.job from emp e where e.ename='scott')
and e.ename !='scott';

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

select distinct e.sal from emp e where e.deptno =30;

select e.ename,e.sal from emp e 
where sal in (select distinct e.sal from emp e where 	e.deptno =30)
and deptno !=30;

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

select max(sal) maxsal from emp where deptno =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) maxsal from emp where deptno =30);

26、列出在每个部门工作的员工数量,平均工资和平均服务期限
计算年差TimeStampDiff(间隔类型，前一个日期，后一个日期)
例如距离现在差了多少年：TimeStampDiff(year, 前一个日期, now())
select TimeStampDiff(year, hiredate, now()) from emp;
间隔类型有：second秒，minute分，hour时，day天，week周，month月，quarter季度，year年

select d.dname,
count(e.ename) as sum,i
fnull(avg(e.sal),0),
ifnull(avg(TimeStampDiff(year, hiredate, now())) ,0) as avgyear
from emp e
right join dept d
on e.deptno =d.deptno
group by d.dname;

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

select e.ename,d.dname,e.sal 
from emp e
join dept d
on e.deptno =d.deptno;

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

select d.* ,t.sum from dept d
left join (select e.deptno,count(e.ename) as sum from emp e
group by e.deptno) t

on t.deptno =d.deptno;

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

select e.ename,t.* from emp e 
join (select e.job,min(e.sal) as minsal from emp e group by e.job) t
on  t.job=e.job and t.minsal=e.sal;

30、列出各个部门的MANAGER(领导)的最低薪金

select  e.deptno,min(sal) from emp e 
where job='MANAGER'
group by e.deptno;

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

select e.ename,sal*12+ifnull(comm,0))as '年薪' from emp e order by '年薪' asc;
//'年薪'这样不行

select e.ename,(sal+ifnull(comm,0))*12 as yearsal from emp e order by yearsal asc;

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

select a.ename '员工',b.ename'领导',b.sal as mgrsal
from emp a
left join emp b
on a.mgr=b.empno;

select t.* 
from ()t 
where t.mgrsal>3000;

select a.ename '员工',b.ename'领导',b.sal as mgrsal
from emp a
left join emp b
on a.mgr=b.empno
where b.sal >3000;
//如果重命名为条件就是刚查出的数据看作临时表了，如果直接b.sal是在查原表中领导的钱大于3000

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

select d.deptno,d.dname,d.loc,count(e.ename),sum(e.sal)
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;