MySQL 基础练习题

最新推荐文章于 2024-07-22 14:36:59 发布

叫我贺词就好

最新推荐文章于 2024-07-22 14:36:59 发布

阅读量121

点赞数

分类专栏：笔记文章标签： mysql

本文链接：https://blog.csdn.net/weixin_55047656/article/details/126088828

版权

本文介绍了多个关于MySQL的查询实例，包括获取每个部门最高薪资的员工、薪水高于部门平均薪资的员工、平均薪水等级、最高和最低薪水的员工、平均薪水最高的部门以及其名称、最低薪水等级的部门、比普通员工薪资高的领导人、最高和最低薪资的员工列表等。内容涵盖员工薪水、部门平均薪水、部门人数、工资等级等多个方面。

摘要由CSDN通过智能技术生成

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

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

+-------+--------+---------+

| ename | deptno | maxsal |

+-------+--------+---------+

| BLAKE | 30 | 2850.00 |

| SCOTT | 20 | 3000.00 |

| KING | 10 | 5000.00 |

| FORD | 20 | 3000.00 |

+-------+--------+---------+

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

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

+--------+-------------+-------+---------+

+--------+-------------+-------+---------+

| 30 | 1566.666667 | ALLEN | 1600.00 |

| 20 | 2175.000000 | JONES | 2975.00 |

| 30 | 1566.666667 | BLAKE | 2850.00 |

| 20 | 2175.000000 | SCOTT | 3000.00 |

| 10 | 2916.666667 | KING | 5000.00 |

| 20 | 2175.000000 | FORD | 3000.00 |

+--------+-------------+-------+---------+

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

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

+--------+--------------+

| deptno | avg(s.grade) |

+--------+--------------+

| 10 | 3.6667 |

| 20 | 2.8000 |

| 30 | 2.5000 |

+--------+--------------+

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

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

+-------+---------+

| ename | sal |

+-------+---------+

| KING | 5000.00 |

+-------+---------+

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

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

+--------+-------------+

| deptno | avgsal |

+--------+-------------+

| 10 | 2916.666667 |

+--------+-------------+

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

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

+------------+-------------+

| dname | avgsal |

+------------+-------------+

| ACCOUNTING | 2916.666667 |

+------------+-------------+

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

select d.dname,a.avgsal,s.grade from (select deptno,avg(sal) avgsal from emp group by deptno order by avg(sal) desc) a join dept d on a.deptno=d.deptno join salgrade s on a.avgsal between s.losal and s.hisal order by avgsal limit 1;

+-------+-------------+-------+

| dname | avgsal | grade |

+-------+-------------+-------+

| SALES | 1566.666667 | 3 |

+-------+-------------+-------+

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

select distinct d.ename,d.sal from emp g join emp d on g.mgr=d.empno where d.sal>(select y.sal from emp y join emp l on y.mgr=l.empno where y.ename not in( select l.ename from emp y join emp l on y.mgr=l.empno) order by y.sal desc limit 1);

+-------+---------+

| ename | sal |

+-------+---------+

| JONES | 2975.00 |

| BLAKE | 2850.00 |

| CLARK | 2450.00 |

| SCOTT | 3000.00 |

| KING | 5000.00 |

| FORD | 3000.00 |

+-------+---------+