MySQL习题练习

根据以上三张表格，对三张表格进行不同的联立，查询并显示符合条件的内容。
	3
	4	1. 查出至少有一个员工的部门。显示部门编号、部门名称、部门位置、部门人数。
	5
	6
	7	mysql> SELECT d.deptno AS 部门编号, d.dname as 部门名称, d.loc as 部门位置, COUNT(e.empno) as 部门人数 FROM dept d
	8	JOIN employee e ON d.deptno = e.deptno
	9	GROUP BY d.deptno, d.dname, d.loc;
	10
	11	+----------+----------+----------+----------+
	12	| 部门编号 | 部门名称 | 部门位置 | 部门人数 |
	13	+----------+----------+----------+----------+
	14	|       10 | 教研部   | 北京     |        3 |
	15	|       20 | 学工部   | 上海     |        5 |
	16	|       30 | 销售部   | 广州     |        6 |
	17	|       50 | 保洁部   | 武汉     |        1 |
	18	+----------+----------+----------+----------+
	19	4 rows in set
	20
	21	2. 列出所有员工的姓名及其直接上级的姓名。
	22
	23
	24	mysql> SELECT e1.ename AS 员工姓名, e2.ename AS 上级姓名
	25	FROM employee e1
	26	LEFT JOIN employee e2 ON e1.mgr = e2.empno;
	27
	28	+----------+----------+
	29	| 员工姓名 | 上级姓名 |
	30	+----------+----------+
	31	| 甘宁     | 庞统     |
	32	| 黛琦丝   | 关羽     |
	33	| 殷天正   | 关羽     |
	34	| 刘备     | 曾阿牛   |
	35	| 谢逊     | 关羽     |
	36	| 关羽     | 曾阿牛   |
	37	| 张飞     | 曾阿牛   |
	38	| 诸葛亮   | 刘备     |
	39	| 曾阿牛   | NULL     |
	40	| 韦一笑   | 关羽     |
	41	| 周泰     | 诸葛亮   |
	42	| 程普     | 关羽     |
	43	| 庞统     | 刘备     |
	44	| 黄盖     | 张飞     |
	45	| 张三     | 甘宁     |
	46	+----------+----------+
	47	15 rows in set
	48
	49
	50	3. 列出受雇日期早于直接上级的所有员工的编号、姓名、部门名称。
	51
	52
	53	mysql> SELECT e1.empno as 员工编号, e1.ename as 员工姓名, d.dname as 部门名称
	54	FROM employee e1
	55	JOIN employee e2 ON e1.mgr = e2.empno
	56	JOIN dept d ON e1.deptno = d.deptno
	57	WHERE e1.hiredate < e2.hiredate;
	58
	59	+----------+----------+----------+
	60	| 员工编号 | 员工姓名 | 部门名称 |
	61	+----------+----------+----------+
	62	|     1007 | 张飞     | 教研部   |
	63	|     1001 | 甘宁     | 学工部   |
	64	|     1004 | 刘备     | 学工部   |
	65	|     1002 | 黛琦丝   | 销售部   |
	66	|     1003 | 殷天正   | 销售部   |
	67	|     1006 | 关羽     | 销售部   |
	68	+----------+----------+----------+
	69	6 rows in set
	70
	71
	72	4. 列出部门名称和这些部门的员工信息，同时列出那些没有员工的部门。
	73
	74
	75	mysql> SELECT d.dname, e.empno, e.ename
	76	FROM dept d
	77	LEFT JOIN employee e ON d.deptno = e.deptno;
	78
	79	+--------+-------+--------+
	80	| dname  | empno | ename  |
	81	+--------+-------+--------+
	82	| 教研部 |  1007 | 张飞   |
	83	| 教研部 |  1009 | 曾阿牛 |
	84	| 教研部 |  1014 | 黄盖   |
	85	| 学工部 |  1001 | 甘宁   |
	86	| 学工部 |  1004 | 刘备   |
	87	| 学工部 |  1008 | 诸葛亮 |
	88	| 学工部 |  1011 | 周泰   |
	89	| 学工部 |  1013 | 庞统   |
	90	| 销售部 |  1002 | 黛琦丝 |
	91	| 销售部 |  1003 | 殷天正 |
	92	| 销售部 |  1005 | 谢逊   |
	93	| 销售部 |  1006 | 关羽   |
	94	| 销售部 |  1010 | 韦一笑 |
	95	| 销售部 |  1012 | 程普   |
	96	| 财务部 | NULL  | NULL   |
	97	| 保洁部 |  1015 | 张三   |
	98	+--------+-------+--------+
	99	16 rows in set
	100
	101
	102	5. 列出最低薪金大于15000的各种工作及从事此工作的员工人数。
	103
	104
	105	mysql> SELECT job, COUNT(*) AS 员工人数
	106	FROM employee
	107	WHERE job IN (SELECT DISTINCT job FROM employee WHERE sal > 15000)
	108	GROUP BY job;
	109
	110	+--------+----------+
	111	| job    | 员工人数 |
	112	+--------+----------+
	113	| 销售员 |        4 |
	114	| 经理   |        3 |
	115	| 分析师 |        2 |
	116	| 董事长 |        1 |
	117	| 保洁员 |        1 |
	118	+--------+----------+
	119	5 rows in set
	120
	121
	122	6. 列出在销售部工作的员工的姓名，假定不知道销售部的部门编号。
	123
	124
	125	mysql> SELECT ename
	126	FROM employee
	127	WHERE deptno = (SELECT deptno FROM dept WHERE dname = '销售部');
	128
	129	+--------+
	130	| ename  |
	131	+--------+
	132	| 黛琦丝 |
	133	| 殷天正 |
	134	| 谢逊   |
	135	| 关羽   |
	136	| 韦一笑 |
	137	| 程普   |
	138	+--------+
	139	6 rows in set
	140
	141
	142	7. 列出薪金高于公司平均薪金的所有员工信息，所在部门名称，上级领导，工资等级。
	143
	144
	145	mysql> SELECT e.ename, e.job, e.sal, e.mgr, e2.ename AS 上级领导,d.dname
	146	FROM employee e
	147	JOIN dept d ON e.deptno = d.deptno
	148	JoIN employee e2 oN e.mgr = e2 .empno
	149	JOIN (
	150	SELECT AVG(sal) AS average_salary
	151	FROM employee
	152	) AS a ON e.sal > a.average_salary;
	153	+--------+--------+-------+------+----------+--------+
	154	| ename  | job    | sal   | mgr  | 上级领导 | dname  |
	155	+--------+--------+-------+------+----------+--------+
	156	| 刘备   | 经理   | 29750 | 1009 | 曾阿牛   | 学工部 |
	157	| 关羽   | 经理   | 28500 | 1009 | 曾阿牛   | 销售部 |
	158	| 诸葛亮 | 分析师 | 30000 | 1004 | 刘备     | 学工部 |
	159	| 庞统   | 分析师 | 30000 | 1004 | 刘备     | 学工部 |
	160	| 张三   | 保洁员 | 80000 | 1001 | 甘宁     | 保洁部 |
	161	+--------+--------+-------+------+----------+--------+
	162	5 rows in set
	163
	164	8.列出与庞统从事相同工作的所有员工及部门名称。
	165
	166
	167	mysql> SELECT e.ename, d.dname AS 部门名称
	168	FROM employee e
	169	JOIN dept d ON e.deptno = d.deptno
	170	WHERE e.job = (SELECT job FROM employee
	171	WHERE ename = '庞统');
	172	+--------+----------+
	173	| ename  | 部门名称 |
	174	+--------+----------+
	175	| 诸葛亮 | 学工部   |
	176	| 庞统   | 学工部   |
	177	+--------+----------+
	178	2 rows in set
	179	9.列出薪金高于部门30工作的所有员工的薪金的员工姓名和薪金、部门名称。
	180	mysql> SELECT e.ename, e.sal, d.dname
	181	FROM employee e
	182	JOIN dept d ON e.deptno = d.deptno
	183	WHERE e.sal > (
	184	SELECT MAX(sal)
	185	FROM employee
	186	WHERE deptno = 30
	187	);
	188	+--------+-------+--------+
	189	| ename  | sal   | dname  |
	190	+--------+-------+--------+
	191	| 刘备   | 29750 | 学工部 |
	192	| 诸葛亮 | 30000 | 学工部 |
	193	| 曾阿牛 | 50000 | 教研部 |
	194	| 庞统   | 30000 | 学工部 |
	195	| 张三   | 80000 | 保洁部 |
	196	+--------+-------+--------+
	197	5 rows in set
	198
	199	10.查出年份、利润、年度增长比。
	200
	201
	202	mysql> SELECT year,zz,
	203	(zz - LAG(zz, 1, 0) OVER (ORDER BY year)) / LAG(zz, 1, 0) OVER (ORDER BY year) AS 年度增长比
	204	FROM lirun;
	205
	206	+------+------+------------+
	207	| year | zz   | 年度增长比 |
	208	+------+------+------------+
	209	| 2010 |  100 | NULL       |
	210	| 2011 |  150 | 0.5000     |
	211	| 2012 |  250 | 0.6667     |
	212	| 2013 |  800 | 2.2000     |
	213	| 2014 | 1000 | 0.2500     |
	214	+------+------+------------+
	215	5 rows in set