mysql练习题二
来源：互联网 发布：城市安防监控网络 编辑：程序博客网 时间：2024/04/30 02:22
题目
5、取得平均薪水最高的部门的部门编号（至少给出两种解决方案）
第一种方法：平均薪水最大值的获取方式为排序，然后取第一个而得到。
第一步：取每个部门的平均薪水。
mysql> select -> deptno, avg(sal) as avgsal -> from -> emp -> group by -> deptno;+--------+-------------+| deptno | avgsal |+--------+-------------+| 10 | 2916.666667 || 20 | 2175.000000 || 30 | 1566.666667 |+--------+-------------+第二步：取平均薪水的最大值。
mysql> select -> avg(sal) avgsal -> from -> emp -> group by -> deptno -> order by -> avgsal desc -> limit -> 1;+-------------+| avgsal |+-------------+| 2916.666667 |+-------------+第三步：联合第一步和第二步。
mysql> select -> deptno -> from -> emp e -> group by -> deptno -> having -> avg(sal) = (select avg(sal) avgsal from emp group by deptno order by avgsal desc limit 1);+--------+| deptno |+--------+| 10 |+--------+第二种方法：平均薪水最大值的获取方式为运用分组函数max，然后取第一个而得到。
mysql> select -> deptno -> from -> emp e -> group by -> deptno -> having -> avg(sal) = (select max(t.avgsal) from (select avg(sal) avgsal from emp group by deptno) t);+--------+| deptno |+--------+| 10 |+--------+6、取得平均薪水最高的部门的部门名称。
第一步：取得每个部门的平均薪水。
mysql> select -> deptno, avg(sal) avgsal -> from -> emp -> group by -> deptno;+--------+-------------+| deptno | avgsal |+--------+-------------+| 10 | 2916.666667 || 20 | 2175.000000 || 30 | 1566.666667 |+--------+-------------+第二步：取得平均薪水最高的薪水值。
mysql> SELECT -> avg(sal) AS avgsal -> FROM -> emp -> GROUP BY -> deptno -> ORDER BY -> avgsal DESC -> LIMIT 1;+-------------+| avgsal |+-------------+| 2916.666667 |+-------------+第三步：联合第一步和第二步。
mysql> SELECT -> d.dname -> FROM -> emp e -> JOIN dept d ON e.deptno = d.deptno -> GROUP BY -> d.dname -> HAVING -> avg(e.sal) = ( -> SELECT -> avg(sal) AS avgsal -> FROM -> emp -> GROUP BY -> deptno -> ORDER BY -> avgsal DESC -> LIMIT 1 -> );+------------+| dname |+------------+| ACCOUNTING |+------------+7、求平均薪水的等级最低的部门的部门名称。
第一步：求部门的平均薪水。
mysql> SELECT -> deptno, -> avg(sal) avgsal -> FROM -> emp -> GROUP BY -> deptno;+--------+-------------+| deptno | avgsal |+--------+-------------+| 10 | 2916.666667 || 20 | 2175.000000 || 30 | 1566.666667 |+--------+-------------+第二步：取部门平均薪水的等级。
mysql> SELECT -> t.deptno, -> s.grade -> FROM -> salgrade s -> JOIN ( -> SELECT -> deptno, -> avg(sal) avgsal -> FROM -> emp -> GROUP BY -> deptno -> ) t ON t.avgsal BETWEEN s.losal -> AND hisal;+--------+-------+| deptno | grade |+--------+-------+| 30 | 3 || 10 | 4 || 20 | 4 |+--------+-------+第三步：取得部门的平均薪水的等级，然后取最低等级的部门编号。
mysql> SELECT -> deptno, -> min(m.grade) AS mingrade -> FROM -> ( -> SELECT -> t.deptno AS deptno, -> s.grade AS grade -> FROM -> salgrade s -> JOIN ( -> SELECT -> deptno, -> avg(sal) avgsal -> FROM -> emp -> GROUP BY -> deptno -> ) t ON t.avgsal BETWEEN s.losal -> AND hisal -> ) m;+--------+----------+| deptno | mingrade |+--------+----------+| 30 | 3 |+--------+----------+第四步：将上面的结果作为临时表与dept表进行内连接，条件为dept表的deptno和上面的deptno相等。
mysql> SELECT -> d.deptno, -> d.dname -> FROM -> dept d -> JOIN ( -> SELECT -> deptno, -> min(m.grade) AS mingrade -> FROM -> ( -> SELECT -> t.deptno AS deptno, -> s.grade AS grade -> FROM -> salgrade s -> JOIN ( -> SELECT -> deptno, -> avg(sal) avgsal -> FROM -> emp -> GROUP BY -> deptno -> ) t ON t.avgsal BETWEEN s.losal -> AND hisal -> ) m -> ) n ON d.deptno = n.deptno;+--------+-------+| deptno | dname |+--------+-------+| 30 | SALES |+--------+-------+8、取得比普通员工(员工代码没有在mgr字段上出现的)的最高薪水还要高的领导人姓名。
第一步：取得普通员工的员工编号，员工姓名，员工薪水。
mysql> SELECT -> empno, -> ename, -> sal, -> mgr -> FROM -> emp -> WHERE -> empno NOT IN ( -> SELECT DISTINCT -> mgr -> FROM -> emp -> WHERE -> mgr IS NOT NULL -> );+-------+--------+---------+------+| empno | ename | sal | mgr |+-------+--------+---------+------+| 7369 | SMITH | 800.00 | 7902 || 7499 | ALLEN | 1600.00 | 7698 || 7521 | WARD | 1250.00 | 7698 || 7654 | MARTIN | 1250.00 | 7698 || 7844 | TURNER | 1500.00 | 7698 || 7876 | ADAMS | 1100.00 | 7788 || 7900 | JAMES | 950.00 | 7698 || 7934 | MILLER | 1300.00 | 7782 |+-------+--------+---------+------+
注意：not in不会自动忽略空值(NULL)，需要手工处理，in会自动忽略空值(NULL)。
第二步：取普通员工的最高薪水。mysql> SELECT -> empno, -> ename, -> sal, -> mgr -> FROM -> emp -> WHERE -> empno NOT IN ( -> SELECT DISTINCT -> mgr -> FROM -> emp -> WHERE -> mgr IS NOT NULL -> ) -> ORDER BY -> sal DESC -> LIMIT 1;+-------+-------+---------+------+| empno | ename | sal | mgr |+-------+-------+---------+------+| 7499 | ALLEN | 1600.00 | 7698 |+-------+-------+---------+------+第三步：取比普通员工薪水最高的领导人的姓名。
mysql> SELECT -> ename -> FROM -> emp -> WHERE -> empno IN ( -> SELECT DISTINCT -> mgr -> FROM -> emp -> WHERE -> mgr IS NOT NULL -> ) -> AND sal > ( -> SELECT -> sal -> FROM -> emp -> WHERE -> empno NOT IN ( -> SELECT DISTINCT -> mgr -> FROM -> emp -> WHERE -> mgr IS NOT NULL -> ) -> ORDER BY -> sal DESC -> LIMIT 1 -> );+-------+| ename |+-------+| JONES || BLAKE || CLARK || SCOTT || KING || FORD |+-------+9、取得薪水最高的前五名员工。
mysql> SELECT -> * -> FROM -> emp -> ORDER BY -> sal DESC -> LIMIT 5;+-------+-------+-----------+------+------------+---------+------+--------+| EMPNO | ENAME | JOB | MGR | HIREDATE | SAL | COMM | DEPTNO |+-------+-------+-----------+------+------------+---------+------+--------+| 7839 | KING | PRESIDENT | NULL | 1981-11-17 | 5000.00 | NULL | 10 || 7788 | SCOTT | ANALYST | 7566 | 1987-04-19 | 3000.00 | NULL | 20 || 7902 | FORD | ANALYST | 7566 | 1981-12-03 | 3000.00 | NULL | 20 || 7566 | JONES | MANAGER | 7839 | 1981-04-02 | 2975.00 | NULL | 20 || 7698 | BLAKE | MANAGER | 7839 | 1981-05-01 | 2850.00 | NULL | 30 |+-------+-------+-----------+------+------------+---------+------+--------+
10、取得薪水最高的第六到第十名员工。
mysql> SELECT -> * -> FROM -> emp -> ORDER BY -> sal DESC -> LIMIT 5, -> 5;+-------+--------+----------+------+------------+---------+---------+--------+| EMPNO | ENAME | JOB | MGR | HIREDATE | SAL | COMM | DEPTNO |+-------+--------+----------+------+------------+---------+---------+--------+| 7782 | CLARK | MANAGER | 7839 | 1981-06-09 | 2450.00 | NULL | 10 || 7499 | ALLEN | SALESMAN | 7698 | 1981-02-20 | 1600.00 | 300.00 | 30 || 7844 | TURNER | SALESMAN | 7698 | 1981-09-08 | 1500.00 | 0.00 | 30 || 7934 | MILLER | CLERK | 7782 | 1982-01-23 | 1300.00 | NULL | 10 || 7654 | MARTIN | SALESMAN | 7698 | 1981-09-28 | 1250.00 | 1400.00 | 30 |+-------+--------+----------+------+------------+---------+---------+--------+注意：limit的用法，limit 起始位置，个数。如果省略了起始位置，则默认从最开始开始，最开始的编号为0。
11、取得最后入职的5名员工。
mysql> SELECT -> * -> FROM -> emp -> ORDER BY -> hiredate DESC -> LIMIT 5;+-------+--------+-----------+------+------------+---------+------+--------+| EMPNO | ENAME | JOB | MGR | HIREDATE | SAL | COMM | DEPTNO |+-------+--------+-----------+------+------------+---------+------+--------+| 7788 | SCOTT | ANALYST | 7566 | 1987-04-19 | 3000.00 | NULL | 20 || 7934 | MILLER | CLERK | 7782 | 1982-01-23 | 1300.00 | NULL | 10 || 7902 | FORD | ANALYST | 7566 | 1981-12-03 | 3000.00 | NULL | 20 || 7900 | JAMES | CLERK | 7698 | 1981-12-03 | 950.00 | NULL | 30 || 7839 | KING | PRESIDENT | NULL | 1981-11-17 | 5000.00 | NULL | 10 |+-------+--------+-----------+------+------------+---------+------+--------+
0 0
- mysql练习题二
- 练习题：二
- mysql 练习题
- MySQL练习题
- MySQL练习题
- mysql 练习题
- MySQL 练习题
- Mysql 练习题
- MySql练习题
- mysql练习题
- MySQL练习题
- mysql练习题
- mysql练习题
- MYSQL基础上机练习题（二） 数据插入、修改、删除
- 数据库练习题二
- 基础练习题（二）
- 351课后练习题二
- 机试练习题二
- mapminmax数据归一化（第一次完整看好help文档）
- java排序算法总结
- std::accumulate（容器求和）
- 手把手Maven搭建SpringMVC+Spring+MyBatis框架(超级详细版)
- c++学习笔记
- mysql练习题二
- 【Js应用实例】限制上传图片大小
- JavaSE_23th_final关键字
- draw9patch.bat配置for Windows
- opencv学习笔记
- git 常用命令
- Android基础--Activity
- 图像类滤波
- Day 19 对象关系映射文件
