mysql练习题 -- 第二天

• 表信息

dept 部门表

字段
DEPTNO 部门编号
DNAME 部门名称
LOC 位置

emp 员工表

字段
EMPNO 员工编号
ENAME 员工名称
JOB 职位
MGR 上级
HIREDATE 入职时间
SAL 薪水
COMM 奖金
DEPTNO 部门编号

salgrade 薪水等级表

字段
GRADE 等级编号
LOSAL 最低薪水
HISAL 最高薪水

文件下载: https://download.csdn.net/download/eebaicai/12626751

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

第一种方案
第一步: 取得每个部门的平均薪水

SELECT
	deptno,
	avg(sal) as avgsal
FROM
	emp
GROUP BY
	deptno;

+--------+-------------+
| deptno | avgsal      |
+--------+-------------+
|     10 | 2916.666667 |
|     20 | 2175.000000 |
|     30 | 1566.666667 |
+--------+-------------+

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

SELECT
	avg(sal) AS avgsal
FROM
	emp
GROUP BY
	deptno
ORDER BY
	avgsal DESC
LIMIT 1;

+-------------+
| avgsal      |
+-------------+
| 2916.666667 |
+-------------+

第三步: 第一步和第二步联合

SELECT
	deptno,
	avg(sal) AS avgsal
FROM
	emp
GROUP BY
	deptno
HAVING
	avgsal = (
		SELECT
			avg(sal) AS avgsal
		FROM
			emp
		GROUP BY
			deptno
		ORDER BY
			avgsal DESC
		LIMIT 1
	);

+--------+-------------+
| deptno | avgsal      |
+--------+-------------+
|     10 | 2916.666667 |
+--------+-------------+

第二种方案: max函数

SELECT
	deptno,
	avg(sal) AS avgsal
FROM
	emp
GROUP BY
	deptno
HAVING
	avgsal = (
		SELECT
			MAX(t.avgsal)
		FROM
			(
				SELECT
					deptno,
					avg(sal) AS avgsal
				FROM
					emp
				GROUP BY
					deptno
			) t
	);

+--------+-------------+
| deptno | avgsal      |
+--------+-------------+
|     10 | 2916.666667 |
+--------+-------------+

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

SELECT
	d.dname,
	avg(e.sal) AS avgsal
FROM
	emp e
JOIN dept d ON d.deptno = e.deptno
GROUP BY
	d.dname
HAVING
	avgsal = (
		SELECT
			avg(sal) AS avgsal
		FROM
			emp
		GROUP BY
			deptno
		ORDER BY
			avgsal DESC
		LIMIT 1
	);

+------------+-------------+
| dname      | avgsal      |
+------------+-------------+
| ACCOUNTING | 2916.666667 |
+------------+-------------+

8.求平均薪水的等级最高的部门的部门名称

第一步: 求各个部门平均薪水的等级 (第四题)

SELECT
 	 t.*,
	 s.grade
FROM
	salgrade s
JOIN (
	SELECT
		deptno,
		avg(sal) AS avgsal
	FROM
		emp
	GROUP BY
		deptno
) t ON t.avgsal BETWEEN s.losal
AND s.hisal;

加上求部门名称

SELECT
	t.*,
	s.grade
FROM
	salgrade s
JOIN (
	SELECT
		d.dname,
		avg(e.sal) AS avgsal
	FROM
		emp e
	JOIN dept d ON e.deptno = d.deptno
	GROUP BY
		d.dname
) t ON t.avgsal BETWEEN s.losal
AND s.hisal;

+------------+-------------+-------+
| dname      | avgsal      | grade |
+------------+-------------+-------+
| ACCOUNTING | 2916.666667 |     4 |
| RESEARCH   | 2175.000000 |     4 |
| SALES      | 1566.666667 |     3 |
+------------+-------------+-------+

第二步: 最高等级 max函数

SELECT
	max( s.grade)
FROM
	salgrade s
JOIN (
	SELECT
		deptno,
		avg(sal) AS avgsal
	FROM
		emp
	GROUP BY
		deptno
) t ON t.avgsal BETWEEN s.losal
AND s.hisal;

+---------------+
| max( s.grade) |
+---------------+
|             4 |
+---------------+

第三步: 第一步和第二步联合

SELECT
	t.*, s.grade
FROM
	salgrade s
JOIN (
	SELECT
		d.dname,
		avg(e.sal) AS avgsal
	FROM
		emp e
	JOIN dept d ON e.deptno = d.deptno
	GROUP BY
		d.dname
) t ON t.avgsal BETWEEN s.losal
AND s.hisal
WHERE
	s.grade = (
		SELECT
			max(s.grade)
		FROM
			salgrade s
		JOIN (
			SELECT
				deptno,
				avg(sal) AS avgsal
			FROM
				emp
			GROUP BY
				deptno
		) t ON t.avgsal BETWEEN s.losal
		AND s.hisal
	);

+------------+-------------+-------+
| dname      | avgsal      | grade |
+------------+-------------+-------+
| ACCOUNTING | 2916.666667 |     4 |
| RESEARCH   | 2175.000000 |     4 |
+------------+-------------+-------+

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

第一步: 找出普通员工

SELECT DISTINCT
	mgr
FROM
	emp
WHERE
	mgr IS NOT NULL;

+------+
| mgr  |
+------+
| 7902 |
| 7698 |
| 7839 |
| 7566 |
| 7788 |
| 7782 |
+------+

记住: not in 不会自动忽略NULL(空值),需要我们自己筛选排除 但是in会自动忽略NULL(空值)

SELECT
	*
FROM
	emp
WHERE
	empno NOT IN (
		SELECT DISTINCT
			mgr
		FROM
			emp
		WHERE
			mgr IS NOT NULL
	);

+-------+--------+----------+------+------------+---------+---------+--------+
| EMPNO | ENAME  | JOB      | MGR  | HIREDATE   | SAL     | COMM    | DEPTNO |
+-------+--------+----------+------+------------+---------+---------+--------+
|  7369 | SMITH  | CLERK    | 7902 | 1980-12-17 |  800.00 |    NULL |     20 |
|  7499 | ALLEN  | SALESMAN | 7698 | 1981-02-20 | 1600.00 |  300.00 |     30 |
|  7521 | WARD   | SALESMAN | 7698 | 1981-02-22 | 1250.00 |  500.00 |     30 |
|  7654 | MARTIN | SALESMAN | 7698 | 1981-09-28 | 1250.00 | 1400.00 |     30 |
|  7844 | TURNER | SALESMAN | 7698 | 1981-09-08 | 1500.00 |    0.00 |     30 |
|  7876 | ADAMS  | CLERK    | 7788 | 1987-05-23 | 1100.00 |    NULL |     20 |
|  7900 | JAMES  | CLERK    | 7698 | 1981-12-03 |  950.00 |    NULL |     30 |
|  7934 | MILLER | CLERK    | 7782 | 1982-01-23 | 1300.00 |    NULL |     10 |
+-------+--------+----------+------+------------+---------+---------+--------+

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

SELECT
	max(sal)
FROM
	emp
WHERE
	empno NOT IN (
		SELECT DISTINCT
			mgr
		FROM
			emp
		WHERE
			mgr IS NOT NULL
	);

+----------+  
| max(sal) |  
+----------+  
|  1600.00 |  
+----------+  

第三步: 找出薪水高于1600的

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
			)
	);
	
+-------+---------+ 
| ename | sal     | 
+-------+---------+ 
| JONES | 2975.00 | 
| BLAKE | 2850.00 | 
| CLARK | 2450.00 | 
| SCOTT | 3000.00 | 
| KING  | 5000.00 | 
| FORD  | 3000.00 | 
+-------+---------+ 

10. 取得薪水最高的前五名员工

SELECT
	ename,
	sal
FROM
	emp
ORDER BY
	sal DESC
LIMIT 5;

+-------+---------+
| ename | sal     |
+-------+---------+
| KING  | 5000.00 |
| FORD  | 3000.00 |
| SCOTT | 3000.00 |
| JONES | 2975.00 |
| BLAKE | 2850.00 |
+-------+---------+

11. 取得薪水最高的第六名到第十名员工

SELECT
	ename,
	sal
FROM
	emp
ORDER BY
	sal DESC
LIMIT 5,5;

+--------+---------+ 
| ename  | sal     | 
+--------+---------+ 
| CLARK  | 2450.00 | 
| ALLEN  | 1600.00 | 
| TURNER | 1500.00 | 
| MILLER | 1300.00 | 
| WARD   | 1250.00 | 
+--------+---------+ 

12. 取得最后入职的5名员工

SELECT
	ename,
	hiredate
FROM
	emp
ORDER BY
	hiredate DESC
LIMIT 5;

+--------+------------+
| ename  | hiredate   |
+--------+------------+
| ADAMS  | 1987-05-23 |
| SCOTT  | 1987-04-19 |
| MILLER | 1982-01-23 |
| JAMES  | 1981-12-03 |
| FORD   | 1981-12-03 |
+--------+------------+