动力节点经典34道MySQL面试题(上)

表dept

表emp

表salgrade

1~取得每个部门的最高薪水的人员名称
SELECT t.*,e.ENAME
FROM (SELECT DEPTNO,max(SAL) maxsal FROM emp GROUP BY DEPTNO) t
JOIN emp e
ON e.DEPTNO=t.DEPTNO and e.SAL=t.maxsal
2~哪些人的薪水在部门平均薪水之上
SELECT t.*,e.ENAME,e.SAL
FROM (SELECT DEPTNO,avg(SAL) avgsal
FROM emp
GROUP BY DEPTNO
) t
JOIN emp e
ON e.DEPTNO=t.DEPTNO AND e.SAL > t.avgsal
ORDER BY DEPTNO
3~取得部门中(所有人的)平均薪水等级
SELECT t.*,s.GRADE
FROM (SELECT DEPTNO,avg(SAL) avgsal
FROM emp e
GROUP BY DEPTNO
) t
JOIN salgrade s
ON t.avgsal BETWEEN s.LOSAL AND s.HISAL
ORDER BY DEPTNO
4~取得部门中所有人的平均的薪水等级
SELECT t.DEPTNO,avg(salgrade)
FROM (SELECT e.SAL,s.GRADE salgrade,e.DEPTNO
FROM emp e
JOIN salgrade s
ON e.SAL BETWEEN s.LOSAL AND s.HISAL
) t
GROUP BY DEPTNO
ORDER BY DEPTNO
5~不准用组函数(max),取得最高的薪水(给出两种解决方案)
方法一:
SELECT SAL,ENAME
FROM emp
ORDER BY SAL DESC
LIMIT 1
方法二:
SELECT SAL maxsal
FROM emp
WHERE SAL NOT IN(SELECT distinct e1.SAL
FROM emp e1
JOIN emp e2
ON e1.SAL < e2.SAL)
6~取得平均薪水最高的部门的部门编号
方法一:
SELECT t.DEPTNO,t.avgsal avgsal
FROM (SELECT DEPTNO,avg(sal) avgsal
FROM emp
GROUP BY DEPTNO
ORDER BY avgsal DESC
)t
LIMIT 1
方法二:
SELECT DEPTNO,avg(sal) avgsal
FROM emp
GROUP BY DEPTNO
HAVING avgsal =(SELECT max(t.avgsal)
FROM (SELECT DEPTNO,avg(sal) avgsal
FROM emp
GROUP BY DEPTNO
ORDER BY DEPTNO) t)
7~取得平均薪水最高的部门的部门名称
方法一:
SELECT d.DNAME
FROM(SELECT DEPTNO,avg(sal) avgsal
FROM emp
GROUP BY DEPTNO
ORDER BY avgsal DESC
) t
JOIN dept d
ON d.DEPTNO=t.DEPTNO
LIMIT 1
方法二:
SELECT d.DNAME
FROM dept d
JOIN (SELECT DEPTNO,avg(sal) avgsal
FROM emp
GROUP BY DEPTNO
HAVING avgsal =(SELECT max(t.avgsal)
FROM (SELECT DEPTNO,avg(sal) avgsal
FROM emp
GROUP BY DEPTNO
ORDER BY DEPTNO) t)) t2
ON t2.DEPTNO=d.DEPTNO
8~求平均薪水的等级最低的部门的部门名称
SELECT d.DEPTNO,d.DNAME
FROM dept d
GROUP BY DEPTNO,DNAME
HAVING DEPTNO=(SELECT t.DEPTNO
FROM (SELECT e.DEPTNO,avg(SAL) avgsal
FROM emp e
GROUP BY DEPTNO
) t
JOIN salgrade s
ON t.avgsal BETWEEN s.LOSAL AND s.HISAL
ORDER BY GRADE
LIMIT 1)
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


 

  • 0
    点赞
  • 3
    收藏
    觉得还不错? 一键收藏
  • 1
    评论

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论 1
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值