MySQL-35个DQL练手题(难)

2024-09-06 07:28

本文主要是介绍MySQL-35个DQL练手题(难)，希望对大家解决编程问题提供一定的参考价值，需要的开发者们随着小编来一起学习吧！

第1题

取得每个部门最高薪水的人员名称
第一步：取得每个部门最高薪水

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

在这里插入图片描述
第二步：将上面第一步的查询结果当做一张临时表t，进行表连接，条件是：t.deptno=e.deptno and t.maxsal=e.sal

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

在这里插入图片描述

第2题

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

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

在这里插入图片描述

第3题

取得每个部门平均薪水的等级

select t.*, s.grade from salgrade s join (select deptno, avg(sal) avgsal from emp group by deptno) t on t.avgsal between s.losal and s.hisal;

在这里插入图片描述

第4题

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

select avg(t.grade), t.deptno from (select e.ename, e.sal, e.deptno, s.grade from emp e join salgrade s on e.sal between s.losal and s.hisal) t group by t.deptno;
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;

在这里插入图片描述

第5题

不准用组函数（Max），取得最高薪水（给出两种解决方案）
自己想的：

select max(sal) from emp;
select a.sal from emp a where not exists (select b.sal from emp b where b.sal > a.sal);

在这里插入图片描述
第一种方案：按照薪资降序排列，取第一个。

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

第二种方案：采用表的自连接方式。

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

在这里插入图片描述

第6题

取得平均薪水最高的部门的部门编号（至少给出两种解决方案）
第一种方案：降序排列取第一个（其实不准确，比如有多个部门平均工资都是最高，这种情况可以用 having）

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

在这里插入图片描述
第二种方案：max函数。
第一步：获取不同部门平均工资

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

在这里插入图片描述
第二步：获取最大工资
这个重命名 t 是必需的

select max(avgsal) maxsal from (select deptno, avg(sal) avgsal from emp group by deptno) t;

在这里插入图片描述
第三步：

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

在这里插入图片描述

第7题

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

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

在这里插入图片描述

select t.avgsal, d.dname from dept d join (select deptno, avg(sal) avgsal from emp group by deptno having avgsal=(select avg(sal) avgsal from emp group by deptno order by avgsal desc limit 1)) t on t.deptno=d.deptno;
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;

在这里插入图片描述

第8题

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

select t.*,s.grade from (select d.dname,avg(e.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 order by s.grade asc limit 1;