mysql作业_MySQL-34道作业题（1）

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

第一步：取得每个部门最高薪水【按照部门分组求最大值】

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

第二步：将上面的查询结果当作临时表题，T表和emp e表进行表连接，条件：t.deptno=e.deptno and t.maxsal=e.sal;

select

t.*,e.name

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

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 e 表进行连接:

条件是：t.deptno=e.deptno and e.avg>t.avg

select e.ename,e.sal,t.*

from

emp e

join

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

on

t.deptno=e.deptno and e.avg>t.avg

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

3.1、取得部门中(所有人的)平均薪水等级？

第一步：取得部门的平均薪水

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

第二步：将上面的查询结果当作临时表t ,t 表和salgrade s表进行连接，

条件是：t.avgsal between s.losal and s.hisal ;

select t.*,s.grade

from

salgrade s

join

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

on r.avgsal between s.losal and s.hisal

3.2取得部门中(所有人的)平均的薪水等级

第一步；每一个员工的薪水等级

select

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

from emp e

join

salgrade s

on

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

第二部：在以上的SQL语句基础上，继续以部门编号分组，求等级的平均值

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)，取得最高薪水(给出两种解决方案)

第一种方案：是按薪水的降序排序取第一个

select sal from emp order by desc limit 1;

第二种方案：自连接

select sal from emp ;

select sal from emp;

a表和表进行表连接：条件是a.sal

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

5、取得平均薪水最高的部门编号(至少给出两种解决方案)

第一步：取得每一个部门的平均薪水

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

第二步：取得平均薪水的最大值

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

第三步：第一步和第三步联合

select

deptno,avg(sal) as avgsal

from

emp

group by deptno

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

第二种方法：

select

deptno,avg(sal) as avgsal

from

emp

group by deptno

having avg(sal)=(select acg(sal)avgsal from emp group by

deptno order by avgsal desc limit1);

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

select

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

from emp e

join dept d

on e.deptno=d.deptno

group by . d.dnam

having

avg(e.sal)=(select avg(sal) avgsal from emp group by deptno order by avgsal desc limit 1);

7、求平均薪水的等级最高的部门的部门名称(平均薪水最低的部门等级肯定最低，等级最低部门的薪水薪水不一定最低)

第一步：求各个部门平均薪水的等级

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

select t.name ,t.avgsal,s.grade

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

join salgrade s

on t.avgsal between s.losal and s.hisal;

第二 步：获取最高等级值

select

max(s.grade)

from

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

join salgrade s

on

t.avgsal between s.losal and s.hisal;《得出最高的等级是4》

第三步：第一步和第二部联合

select t.name ,t.avgsal,s.grade

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

join salgrade s

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

where

s.grade=(select

max(s.grade)

from

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

join salgrade s

on

t.avgsal between s.losal and s.hisal);

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

第一步：找出普通员工

select * from emp where empno not in (select distinct mgr from emp);

以上语句无法查询结果，因为not in不会自动忽略null需要程序猿手动排null；

select * from emp where empno not in (select distinct mgr from emp where mgr is not null);(手动加上条件排除NULL之后查询结果会排除NULL

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

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

第三步：找出薪水高于上面的即可

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

not in (不在某某范围内)不会自动排除NUL；但是in(在某某范围内)会自动忽略口值

补充：case .... when ....then ..........when ....then....end使用在DQL语句中类似于 java中的switch.case

select

me,sal,(case job when 'MANAGER' then sal*1.1 when 'SALESMAN' then sal 4 end) as newsal

from emp ;(匹配工作岗位当岗位是MANAGER时薪水乘以1.1当是SALEESMAN时薪水乘以4

me,sal,(case job when 'MANAGER' then sal1.1 when 'SALESMAN' then sal *4 else sal . end) as newsal(

9