作 业

1. 查出至少有一个员工的部门。显示部门编号、部门名称、部门位置、部门人数。

mysql> SELECT d.deptno AS 部门编号, d.dname as 部门名称, d.loc as 部门位置, COUNT(e.empno) as 部门人数 FROM dept d
JOIN employee e ON d.deptno = e.deptno
GROUP BY d.deptno, d.dname, d.loc;

+----------+----------+----------+----------+
| 部门编号 | 部门名称 | 部门位置 | 部门人数 |
+----------+----------+----------+----------+
|       10 | 教研部   | 北京     |        3 |
|       20 | 学工部   | 上海     |        5 |
|       30 | 销售部   | 广州     |        6 |
|       50 | 保洁部   | 武汉     |        1 |
+----------+----------+----------+----------+
4 rows in set

2. 列出所有员工的姓名及其直接上级的姓名。

mysql> SELECT e1.ename AS 员工姓名, e2.ename AS 上级姓名
FROM employee e1
LEFT JOIN employee e2 ON e1.mgr = e2.empno;

+----------+----------+
| 员工姓名 | 上级姓名 |
+----------+----------+
| 甘宁     | 庞统     |
| 黛琦丝   | 关羽     |
| 殷天正   | 关羽     |
| 刘备     | 曾阿牛   |
| 谢逊     | 关羽     |
| 关羽     | 曾阿牛   |
| 张飞     | 曾阿牛   |
| 诸葛亮   | 刘备     |
| 曾阿牛   | NULL     |
| 韦一笑   | 关羽     |
| 周泰     | 诸葛亮   |
| 程普     | 关羽     |
| 庞统     | 刘备     |
| 黄盖     | 张飞     |
| 张三     | 甘宁     |
+----------+----------+
15 rows in set

3. 列出受雇日期早于直接上级的所有员工的编号、姓名、部门名称。

mysql> SELECT e1.empno as 员工编号, e1.ename as 员工姓名, d.dname as 部门名称 
FROM employee e1
JOIN employee e2 ON e1.mgr = e2.empno
JOIN dept d ON e1.deptno = d.deptno
WHERE e1.hiredate < e2.hiredate;

+----------+----------+----------+
| 员工编号 | 员工姓名 | 部门名称 |
+----------+----------+----------+
|     1007 | 张飞     | 教研部   |
|     1001 | 甘宁     | 学工部   |
|     1004 | 刘备     | 学工部   |
|     1002 | 黛琦丝   | 销售部   |
|     1003 | 殷天正   | 销售部   |
|     1006 | 关羽     | 销售部   |
+----------+----------+----------+
6 rows in set

4. 列出部门名称和这些部门的员工信息，同时列出那些没有员工的部门。

mysql> SELECT d.dname, e.empno, e.ename
FROM dept d
LEFT JOIN employee e ON d.deptno = e.deptno;

+--------+-------+--------+
| dname  | empno | ename  |
+--------+-------+--------+
| 教研部 |  1007 | 张飞   |
| 教研部 |  1009 | 曾阿牛 |
| 教研部 |  1014 | 黄盖   |
| 学工部 |  1001 | 甘宁   |
| 学工部 |  1004 | 刘备   |
| 学工部 |  1008 | 诸葛亮 |
| 学工部 |  1011 | 周泰   |
| 学工部 |  1013 | 庞统   |
| 销售部 |  1002 | 黛琦丝 |
| 销售部 |  1003 | 殷天正 |
| 销售部 |  1005 | 谢逊   |
| 销售部 |  1006 | 关羽   |
| 销售部 |  1010 | 韦一笑 |
| 销售部 |  1012 | 程普   |
| 财务部 | NULL  | NULL   |
| 保洁部 |  1015 | 张三   |
+--------+-------+--------+
16 rows in set

5. 列出最低薪金大于15000的各种工作及从事此工作的员工人数。

mysql> SELECT job, COUNT(*) AS 员工人数
FROM employee
WHERE job IN (SELECT DISTINCT job FROM employee WHERE sal > 15000)
GROUP BY job;

+--------+----------+
| job    | 员工人数 |
+--------+----------+
| 销售员 |        4 |
| 经理   |        3 |
| 分析师 |        2 |
| 董事长 |        1 |
| 保洁员 |        1 |
+--------+----------+
5 rows in set

6. 列出在销售部工作的员工的姓名，假定不知道销售部的部门编号。

mysql> SELECT ename
FROM employee
WHERE deptno = (SELECT deptno FROM dept WHERE dname = '销售部');

+--------+
| ename  |
+--------+
| 黛琦丝 |
| 殷天正 |
| 谢逊   |
| 关羽   |
| 韦一笑 |
| 程普   |
+--------+
6 rows in set

7. 列出薪金高于公司平均薪金的所有员工信息，所在部门名称，上级领导，工资等级。

mysql> SELECT e.ename, e.job, e.sal, e.mgr, e2.ename AS 上级领导,d.dname
FROM employee e
JOIN dept d ON e.deptno = d.deptno
JoIN employee e2 oN e.mgr = e2 .empno
JOIN (
  SELECT AVG(sal) AS average_salary
  FROM employee
) AS a ON e.sal > a.average_salary;
+--------+--------+-------+------+----------+--------+
| ename  | job    | sal   | mgr  | 上级领导 | dname  |
+--------+--------+-------+------+----------+--------+
| 刘备   | 经理   | 29750 | 1009 | 曾阿牛   | 学工部 |
| 关羽   | 经理   | 28500 | 1009 | 曾阿牛   | 销售部 |
| 诸葛亮 | 分析师 | 30000 | 1004 | 刘备     | 学工部 |
| 庞统   | 分析师 | 30000 | 1004 | 刘备     | 学工部 |
| 张三   | 保洁员 | 80000 | 1001 | 甘宁     | 保洁部 |
+--------+--------+-------+------+----------+--------+
5 rows in set

8.列出与庞统从事相同工作的所有员工及部门名称。

mysql> SELECT e.ename, d.dname AS 部门名称
FROM employee e
JOIN dept d ON e.deptno = d.deptno
WHERE e.job = (SELECT job FROM employee
WHERE ename = '庞统');
+--------+----------+
| ename  | 部门名称 |
+--------+----------+
| 诸葛亮 | 学工部   |
| 庞统   | 学工部   |
+--------+----------+
2 rows in set
9.列出薪金高于部门30工作的所有员工的薪金的员工姓名和薪金、部门名称。
mysql> SELECT e.ename, e.sal, d.dname
FROM employee e
JOIN dept d ON e.deptno = d.deptno
WHERE e.sal > (
  SELECT MAX(sal)
  FROM employee
  WHERE deptno = 30
);
+--------+-------+--------+
| ename  | sal   | dname  |
+--------+-------+--------+
| 刘备   | 29750 | 学工部 |
| 诸葛亮 | 30000 | 学工部 |
| 曾阿牛 | 50000 | 教研部 |
| 庞统   | 30000 | 学工部 |
| 张三   | 80000 | 保洁部 |
+--------+-------+--------+
5 rows in set

10.查出年份、利润、年度增长比。

mysql> SELECT year,zz, 
(zz - LAG(zz, 1, 0) OVER (ORDER BY year)) / LAG(zz, 1, 0) OVER (ORDER BY year) AS 年度增长比 
FROM lirun;

+------+------+------------+
| year | zz   | 年度增长比 |
+------+------+------------+
| 2010 |  100 | NULL       |
| 2011 |  150 | 0.5000     |
| 2012 |  250 | 0.6667     |
| 2013 |  800 | 2.2000     |
| 2014 | 1000 | 0.2500     |
+------+------+------------+
5 rows in set

详情在：作业. · 8239ace · WL—code/WL仓库 - Gitee.com