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

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

第一步: 取得每个部门最高薪水 [按照部门编号分组求最大值]

SELECT deptno, MAX(sal) as maxsal FROM emp GROUP BY deptno;

+--------+---------+
| deptno | maxsal  |
+--------+---------+
|     10 | 5000.00 |
|     20 | 3000.00 |
|     30 | 2850.00 |
+--------+---------+

第二步: 将上面的查询结果当作临时表t, t表和emp e表进行表连接
条件: t.deptno = e.deptno AND t.maxsal = e.sal

SELECT
	e.ename,
	t.*
FROM
	emp e
JOIN (
	SELECT
		deptno,
		MAX(sal) AS maxsal
	FROM
		emp
	GROUP BY
		deptno
) t ON t.deptno = e.deptno
AND t.maxsal = e.sal;

+-------+--------+---------+
| ename | deptno | maxsal  |
+-------+--------+---------+
| BLAKE |     30 | 2850.00 |
| SCOTT |     20 | 3000.00 |
| KING  |     10 | 5000.00 |
| FORD  |     20 | 3000.00 |
+-------+--------+---------+

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

第一步: 找出部门的平均薪水 按照部门编号分组求平均值]

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

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

第二步: 将上面的查询结果当作临时表t, t表和emp e表进行表连接
条件: t.deptno = e.deptno AND e.sal > t.avgsal

SELECT
	e.ename,
	e.sal,
	t.*
FROM
	emp e
JOIN (
	SELECT
		deptno,
		avg(sal) AS avgsal
	FROM
		emp
	GROUP BY
		deptno
) t ON t.deptno = e.deptno
AND e.sal > t.avgsal;

+-------+---------+--------+-------------+
| ename | sal     | deptno | avgsal      |
+-------+---------+--------+-------------+
| ALLEN | 1600.00 |     30 | 1566.666667 |
| JONES | 2975.00 |     20 | 2175.000000 |
| BLAKE | 2850.00 |     30 | 1566.666667 |
| SCOTT | 3000.00 |     20 | 2175.000000 |
| KING  | 5000.00 |     10 | 2916.666667 |
| FORD  | 3000.00 |     20 | 2175.000000 |
+-------+---------+--------+-------------+

3. 取得部门中(所有人的)平均的薪水等级

(就是薪水等级的平均值)
第一步: 取得每个员工的薪水等级

SELECT
	e.ename,
	e.sal,
	e.deptno,
	s.grade
FROM
	emp e
JOIN salgrade s ON e.sal BETWEEN s.losal
AND hisal;

+--------+---------+--------+-------+
| ename  | sal     | deptno | grade |
+--------+---------+--------+-------+
| SMITH  |  800.00 |     20 |     1 |
| ALLEN  | 1600.00 |     30 |     3 |
| WARD   | 1250.00 |     30 |     2 |
| JONES  | 2975.00 |     20 |     4 |
| MARTIN | 1250.00 |     30 |     2 |
| BLAKE  | 2850.00 |     30 |     4 |
| CLARK  | 2450.00 |     10 |     4 |
| SCOTT  | 3000.00 |     20 |     4 |
| KING   | 5000.00 |     10 |     5 |
| TURNER | 1500.00 |     30 |     3 |
| ADAMS  | 1100.00 |     20 |     1 |
| JAMES  |  950.00 |     30 |     1 |
| FORD   | 3000.00 |     20 |     4 |
| MILLER | 1300.00 |     10 |     2 |
+--------+---------+--------+-------+

第二步: 在上面的基础之上,按部门编号分组,求等级的平均值

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. 取得部门中(所有人的)平均薪水的等级

第一步: 取得部门的平均薪水

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

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

第二步: 将上面的查询结果当作临时表t, t表和salgrade s表进行表连接
条件: t.avgsal between s.losal and s.hisal

SELECT
	 s.grade,
	 t.*
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;

+-------+--------+-------------+
| grade | deptno | avgsal      |
+-------+--------+-------------+
|     4 |     10 | 2916.666667 |
|     4 |     20 | 2175.000000 |
|     3 |     30 | 1566.666667 |
+-------+--------+-------------+

5. 不准用组函数(max),取得最高薪水(给出两种解决方案)

第一种: 按照薪水降序排列,取第一个

SELECT
	sal
FROM
	emp
ORDER BY
	sal DESC
LIMIT 1;

+---------+
| sal     |
+---------+
| 5000.00 |
+---------+

第二种: 自连接

SELECT
	a.sal
FROM
	emp a
JOIN emp b ON a.sal < b.sal;

上面的结果,肯定没有5000的
然后

SELECT
	sal
FROM
	emp
WHERE
	sal NOT IN (
		SELECT DISTINCT
			a.sal
		FROM
			emp a
		JOIN emp b ON a.sal < b.sal
	);

+---------+
| sal     |
+---------+
| 5000.00 |
+---------+