第一部分导入数据库Mysql 数据中；

第一步：将以上数据库存在本地，注意目录不要有中文；本次是存在虚拟机 /opt/module/software/bjpowernode.sql

第二步：在mysql数据库新建对应的数据库，并通过source本地上传到mysql数据库中并检查是否上传成功；

mysql> create database bjpowernode;
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| bjpowernode        |
| hivemeta           |
| mysql              |
| performance_schema |
| sys                |
| test               |
+--------------------+
7 rows in set (0.01 sec)
mysql> use bjpowernode;
mysql> create database bjpowernode;  #在mysql，新建数据库
Query OK, 1 row affected (0.00 sec)
mysql> use bjpowernode ;   #在mysql，选择数据库
Database changed
mysql> source /opt/module/software/bjpowernode.sql;    #在mysql，通过source 把虚拟机的文件传送到 bjpowernode数据库
Query OK, 0 rows affected, 1 warning (0.00 sec)
Query OK, 0 rows affected, 1 warning (0.00 sec)
Query OK, 0 rows affected, 1 warning (0.00 sec)
Query OK, 0 rows affected (0.01 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 1 row affected (0.03 sec)
Query OK, 1 row affected (0.00 sec)
Query OK, 1 row affected (0.00 sec)
Query OK, 1 row affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 1 row affected (0.00 sec)
Query OK, 1 row affected (0.00 sec)
Query OK, 1 row affected (0.01 sec)
Query OK, 1 row affected (0.00 sec)
Query OK, 1 row affected (0.00 sec)
Query OK, 1 row affected (0.00 sec)
Query OK, 1 row affected (0.00 sec)
Query OK, 1 row affected (0.00 sec)
Query OK, 1 row affected (0.00 sec)
Query OK, 1 row affected (0.00 sec)
Query OK, 1 row affected (0.00 sec)
Query OK, 1 row affected (0.00 sec)
Query OK, 1 row affected (0.00 sec)
Query OK, 1 row affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 1 row affected (0.00 sec)
Query OK, 1 row affected (0.00 sec)
Query OK, 1 row affected (0.00 sec)
Query OK, 1 row affected (0.00 sec)
Query OK, 1 row affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
mysql> show tables;        #在mysql，检查 bjpowernode数据库的数据表，上传成功
+-----------------------+
| Tables_in_bjpowernode |
+-----------------------+
| DEPT                  |
| EMP                   |
| SALGRADE              |
+-----------------------+
3 rows in set (0.00 sec)

第二部分答题；

34道题目
1、取得每个部门最高薪水的人员名称
2、哪些人的薪水在部门的平均薪水之上
3、取得部门中（所有人的）平均的薪水等级，如下：
4、不准用组函数（Max），取得最高薪水（给出两种解决方案）(还差一种)
5、取得平均薪水最高的部门的部门编号（至少给出两种解决方案）
第一步部门的平均薪水
6、取得平均薪水最高的部门的部门名称
7、求平均薪水的等级最低的部门的部门名称
8、取得比普通员工(员工代码没有在 mgr 字段上出现的)的最高薪水还要高的
领导人姓名
9、取得薪水最高的前五名员工
10、取得薪水最高的第六到第十名员工
11、取得最后入职的 5 名员工
12、取得每个薪水等级有多少员工
13、面试题
有 3 个表 S(学生表)，C（课程表），SC（学生选课表）
S（SNO，SNAME）代表（学号，姓名）
95 / 103
C（CNO，CNAME，CTEACHER）代表（课号，课名，教师）
SC（SNO，CNO，SCGRADE）代表（学号，课号，成绩）
问题：
1，找出没选过“黎明”老师的所有学生姓名。
2，列出 2 门以上（含 2 门）不及格学生姓名及平均成绩。
3，即学过 1 号课程又学过 2 号课所有学生的姓名。
请用标准 SQL 语言写出答案，方言也行（请说明是使用什么方言）。
-----------------------------------------------------------------------------
CREATE TABLE SC
(
SNO VARCHAR(200),
CNO VARCHAR(200),
SCGRADE VARCHAR(200)
);
CREATE TABLE S
(
SNO VARCHAR(200 ),
SNAME VARCHAR(200)
);
CREATE TABLE C
(
CNO VARCHAR(200),
CNAME VARCHAR(200),
CTEACHER VARCHAR(200)
);
INSERT INTO C ( CNO, CNAME, CTEACHER ) VALUES ( '1', '语文', '张');
INSERT INTO C ( CNO, CNAME, CTEACHER ) VALUES ( '2', '政治', '王');
INSERT INTO C ( CNO, CNAME, CTEACHER ) VALUES ( '3', '英语', '李');
INSERT INTO C ( CNO, CNAME, CTEACHER ) VALUES ( '4', '数学', '赵');
INSERT INTO C ( CNO, CNAME, CTEACHER ) VALUES ( '5', '物理', '黎明');
commit;
INSERT INTO S ( SNO, SNAME ) VALUES ( '1', '学生 1');
INSERT INTO S ( SNO, SNAME ) VALUES ( '2', '学生 2');
INSERT INTO S ( SNO, SNAME ) VALUES ( '3', '学生 3');
INSERT INTO S ( SNO, SNAME ) VALUES ( '4', '学生 4');
commit;
INSERT INTO SC ( SNO, CNO, SCGRADE ) VALUES ( '1', '1', '40');
INSERT INTO SC ( SNO, CNO, SCGRADE ) VALUES ( '1', '2', '30');
INSERT INTO SC ( SNO, CNO, SCGRADE ) VALUES ( '1', '3', '20');
INSERT INTO SC ( SNO, CNO, SCGRADE ) VALUES ( '1', '4', '80');
96 / 103
INSERT INTO SC ( SNO, CNO, SCGRADE ) VALUES ( '1', '5', '60');
INSERT INTO SC ( SNO, CNO, SCGRADE ) VALUES ( '2', '1', '60');
INSERT INTO SC ( SNO, CNO, SCGRADE ) VALUES ( '2', '2', '60');
INSERT INTO SC ( SNO, CNO, SCGRADE ) VALUES ( '2', '3', '60');
INSERT INTO SC ( SNO, CNO, SCGRADE ) VALUES ( '2', '4', '60');
INSERT INTO SC ( SNO, CNO, SCGRADE ) VALUES ( '2', '5', '40');
INSERT INTO SC ( SNO, CNO, SCGRADE ) VALUES ( '3', '1', '60');
INSERT INTO SC ( SNO, CNO, SCGRADE ) VALUES ( '3', '3', '80');
commit;

14、列出所有员工及领导的姓名
15、列出受雇日期早于其直接上级的所有员工的编号,姓名,部门名称
16、列出部门名称和这些部门的员工信息,同时列出那些没有员工的部门.
17、列出至少有 5 个员工的所有部门
18、列出薪金比"SMITH"多的所有员工信息.
19、列出所有"CLERK"(办事员)的姓名及其部门名称,部门的人数.
20、列出最低薪金大于 1500 的各种工作及从事此工作的全部雇员人数.
21列出在部门"SALES"<销售部>工作的员工的姓名,假定不知道销售部的部
门编号.
22、列出薪金高于公司平均薪金的所有员工,所在部门,上级领导,雇员的工资等
级.
23、列出与"SCOTT"从事相同工作的所有员工及部门名称.
24、列出薪金等于部门 30 中员工的薪金的其他员工的姓名和薪金.
25、列出薪金高于在部门 30 工作的所有员工的薪金的员工姓名和薪金.部门名
称.
26、列出在每个部门工作的员工数量,平均工资和平均服务期限.
27、列出所有员工的姓名、部门名称和工资。
28、列出所有部门的详细信息和人数
29、列出各种工作的最低工资及从事此工作的雇员姓名
30、列出各个部门的 MANAGER(领导)的最低薪金
31、列出所有员工的年工资,按年薪从低到高排序
32、求出员工领导的薪水超过 3000 的员工名称与领导名称
33、求出部门名称中,带'S'字符的部门员工的工资合计、部门人数.34、给任职日期超过 30 年的员工加薪 10%

解题过程

解题过程

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

#第一步：取得每个部门最高薪水(按照部门编号分组，找出每一组最大值)
mysql> 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表连接，条件：t.deptno = e.deptno and t.maxsal = e.salselect 
    e.ename, t.*from 
    emp ejoin    (select deptno,max(sal) as maxsal from emp group by deptno) ton    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表连接
条件：部门编号相同，并且emp的sal大于t表的avgsal
select 
    t.*, e.ename, e.sal
from
    emp e
join
    (select deptno,avg(sal) as avgsal from emp group by deptno) t
on
    e.deptno = t.deptno and e.sal > t.avgsal;
+--------+-------------+-------+---------+
| deptno | avgsal      | ename | sal     |
+--------+-------------+-------+---------+
|     30 | 1566.666667 | ALLEN | 1600.00 |
|     20 | 2175.000000 | JONES | 2975.00 |
|     30 | 1566.666667 | BLAKE | 2850.00 |
|     20 | 2175.000000 | SCOTT | 3000.00 |
|     10 | 2916.666667 | KING  | 5000.00 |
|     20 | 2175.000000 | FORD  | 3000.00 |
+--------+-------------+-------+---------+

3、取得部门中（所有人的）平均的薪水等级

平均的薪水等级：先计算每一个薪水的等级，然后找出薪水等级的平均值。

平均薪水的等级：先计算平均薪水，然后找出每个平均薪水的等级值。

#第一步：找出每个人的薪水等级
    //emp e和salgrade s表连接。
    //连接条件：e.sal between s.losal and s.hisal
    
    select 
        e.ename,e.sal,e.deptno,s.grade
    from
        emp e
    join
        salgrade s
    on
        e.sal between s.losal and s.hisal;
    +--------+---------+--------+-------+
    | ename  | sal     | deptno | grade |
    +--------+---------+--------+-------+
    | CLARK  | 2450.00 |     10 |     4 |
    | KING   | 5000.00 |     10 |     5 |
    | MILLER | 1300.00 |     10 |     2 |
    | SMITH  |  800.00 |     20 |     1 |
    | ADAMS  | 1100.00 |     20 |     1 |
    | SCOTT  | 3000.00 |     20 |     4 |
    | FORD   | 3000.00 |     20 |     4 |
    | JONES  | 2975.00 |     20 |     4 |
    | MARTIN | 1250.00 |     30 |     2 |
    | TURNER | 1500.00 |     30 |     3 |
    | BLAKE  | 2850.00 |     30 |     4 |
    | ALLEN  | 1600.00 |     30 |     3 |
    | JAMES  |  950.00 |     30 |     1 |
    | WARD   | 1250.00 |     30 |     2 |
    +--------+---------+--------+-------+
    #第二步：基于以上的结果继续按照deptno分组，求grade的平均值。
    select 
        e.deptno,avg(s.grade)
    from
        emp e
    join
        salgrade s
    on
        e.sal between s.losal and s.hisal
    group by
        e.deptno;
    +--------+--------------+
    | deptno | avg(s.grade) |
    +--------+--------------+
    |     10 |       3.6667 |
    |     20 |       2.8000 |
    |     30 |       2.5000 |
    +--------+--------------+
4、不准用组函数（Max ），取得最高薪水
第一种：sal降序，limit 1
select ename,sal from emp order by sal desc limit 1;
+-------+---------+
| ename | sal     |
+-------+---------+
| KING  | 5000.00 |
+-------+---------+

第二种方案：select max(sal) from emp;第三种方案：表的自连接

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 |
+---------+
select 
    distinct a.sal 
from 
    emp a 
join 
    emp b 
on 
    a.sal < b.sal
a表
+---------+
| sal     |
+---------+
|  800.00 |
| 1600.00 |
| 1250.00 |
| 2975.00 |
| 1250.00 |
| 2850.00 |
| 2450.00 |
| 3000.00 |
| 5000.00 |
| 1500.00 |
| 1100.00 |
|  950.00 |
| 3000.00 |
| 1300.00 |
+---------+
b表
+---------+
| sal     |
+---------+
|  800.00 |
| 1600.00 |
| 1250.00 |
| 2975.00 |
| 1250.00 |
| 2850.00 |
| 2450.00 |
| 3000.00 |
| 5000.00 |
| 1500.00 |
| 1100.00 |
|  950.00 |
| 3000.00 |
| 1300.00 |
+---------+

5、取得平均薪水最高的部门的部门编号

第一种方案：降序取第一个。

#  第一步：找出每个部门的平均薪水
        select deptno,avg(sal) as avgsal from emp group by deptno;
        +--------+-------------+
        | deptno | avgsal      |
        +--------+-------------+
        |     10 | 2916.666667 |
        |     20 | 2175.000000 |
        |     30 | 1566.666667 |
        +--------+-------------+
   # 第二步：降序选第一个。
        select deptno,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;
    +--------+-------------+
    | deptno | avgsal      |
    +--------+-------------+
    |     10 | 2916.666667 |
    |     20 | 2175.000000 |
    |     30 | 1566.666667 |
    +--------+-------------+
   # 第二步：找出以上结果中avgsal最大的值。
    select max(t.avgsal) from (select avg(sal) as avgsal from emp group by deptno) t;
    +---------------+
    | max(t.avgsal) |
    +---------------+
    |   2916.666667 |
    +---------------+
    #第三步：
    select 
        deptno,avg(sal) as avgsal 
    from 
        emp 
    group by 
        deptno
    having
        avgsal = (select max(t.avgsal) from (select avg(sal) as avgsal from emp group by deptno) t);
    
    +--------+-------------+
    | deptno | avgsal      |
    +--------+-------------+
    |     10 | 2916.666667 |
    +--------+-------------+

6、取得平均薪水最高的部门的部门名称

select 
    d.dname,avg(e.sal) as avgsal 
from 
    emp e
join
    dept d
on
    e.deptno = d.deptno
group by 
    d.dname
order by 
    avgsal desc 
limit 
    1;
+------------+-------------+
| dname      | avgsal      |
+------------+-------------+
| ACCOUNTING | 2916.666667 |
+------------+-------------+

7、求平均薪水的等级最低的部门的部门名称

平均薪水是800
平均薪水是900
那么他俩都是1级别。

#第一步：找出每个部门的平均薪水
select deptno,avg(sal) as avgsal from emp group by deptno;
+--------+-------------+
| deptno | avgsal      |
+--------+-------------+
|     10 | 2916.666667 |
|     20 | 2175.000000 |
|     30 | 1566.666667 |
+--------+-------------+
#第二步：找出每个部门的平均薪水的等级
以上t表和salgrade表连接，条件：t.avgsal between s.losal and s.hisal
select 
    t.*,s.grade
from
    (select d.dname,avg(sal) as avgsal from emp e join dept d on e.deptno = d.deptno group by d.dname) t
join
    salgrade s
on
    t.avgsal between s.losal and s.hisal;
+------------+-------------+-------+
| dname      | avgsal      | grade |
+------------+-------------+-------+
| SALES      | 1566.666667 |     3 |
| ACCOUNTING | 2916.666667 |     4 |
| RESEARCH   | 2175.000000 |     4 |
+------------+-------------+-------+
select 
    t.*,s.grade
from
    (select d.dname,avg(sal) as avgsal from emp e join dept d on e.deptno = d.deptno group by d.dname) t
join
    salgrade s
on
    t.avgsal between s.losal and s.hisal
where
    s.grade = (select grade from salgrade where (select avg(sal) as avgsal from emp group by deptno order by avgsal asc limit 1) between losal and hisal);
+-------+-------------+-------+
| dname | avgsal      | grade |
+-------+-------------+-------+
| SALES | 1566.666667 |     3 |
+-------+-------------+-------+

抛开之前的，最低等级你怎么着？
平均薪水最低的对应的等级一定是最低的.

   select avg(sal) as avgsal from emp group by deptno order by avgsal asc limit 1;
    +-------------+
    | avgsal      |
    +-------------+
    | 1566.666667 |
    +-------------+
    select grade from salgrade where (select avg(sal) as avgsal from emp group by deptno order by avgsal asc limit 1) between losal and hisal;
    +-------+
    | grade |
    +-------+
    |     3 |
    +-------+

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

比“普通员工的最高薪水”还要高的一定是领导！
没毛病！！！！

mysql> select distinct mgr from emp where mgr is not null;
+------+
| mgr  |
+------+
| 7902 |
| 7698 |
| 7839 |
| 7566 |
| 7788 |
| 7782 |
+------+
#员工编号没有在以上范围内的都是普通员工。
#第一步：找出普通员工的最高薪水！
not in在使用的时候，后面小括号中记得排除NULL。
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 |
+-------+---------+

9、取得薪水最高的前五名员工

select ename,sal from emp order by sal desc limit 5;
+-------+---------+
| ename | sal     |
+-------+---------+
| KING  | 5000.00 |
| SCOTT | 3000.00 |
| FORD  | 3000.00 |
| JONES | 2975.00 |
| BLAKE | 2850.00 |
+-------+---------+

10、取得薪水最高的第六到第十名员工

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 |
| MARTIN | 1250.00 |
+--------+---------+

11、取得最后入职的 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 |
    | FORD   | 1981-12-03 |
    | JAMES  | 1981-12-03 |
    +--------+------------+

12、取得每个薪水等级有多少员工
分组count

# 第一步：找出每个员工的薪水等级
select 
    e.ename,e.sal,s.grade 
from 
    emp e 
join 
    salgrade s 
on 
    e.sal between s.losal and s.hisal;
+--------+---------+-------+
| ename  | sal     | grade |
+--------+---------+-------+
| SMITH  |  800.00 |     1 |
| ALLEN  | 1600.00 |     3 |
| WARD   | 1250.00 |     2 |
| JONES  | 2975.00 |     4 |
| MARTIN | 1250.00 |     2 |
| BLAKE  | 2850.00 |     4 |
| CLARK  | 2450.00 |     4 |
| SCOTT  | 3000.00 |     4 |
| KING   | 5000.00 |     5 |
| TURNER | 1500.00 |     3 |
| ADAMS  | 1100.00 |     1 |
| JAMES  |  950.00 |     1 |
| FORD   | 3000.00 |     4 |
| MILLER | 1300.00 |     2 |
+--------+---------+-------+
#第二步：继续按照grade分组统计数量
select 
    s.grade ,count(*)
from 
    emp e 
join 
    salgrade s 
on 
    e.sal between s.losal and s.hisal
group by
    s.grade;
+-------+----------+
| grade | count(*) |
+-------+----------+
|     1 |        3 |
|     2 |        3 |
|     3 |        2 |
|     4 |        5 |
|     5 |        1 |
+-------+----------+

13、面试题：
有 3 个表 S(学生表)，C（课程表），SC（学生选课表）
S（SNO，SNAME）代表（学号，姓名）
C（CNO，CNAME，CTEACHER）代表（课号，课名，教师）
SC（SNO，CNO，SCGRADE）代表（学号，课号，成绩）
问题：
1，找出没选过“黎明”老师的所有学生姓名。
2，列出 2 门以上（含2 门）不及格学生姓名及平均成绩。
3，即学过 1 号课程又学过 2 号课所有学生的姓名。
14、列出所有员工及领导的姓名

select 
    a.ename '员工', b.ename '领导'
from
    emp a
left join
    emp b
on
    a.mgr = b.empno;
+--------+-------+
| 员工   | 领导    |
+--------+-------+
| SMITH  | FORD  |
| ALLEN  | BLAKE |
| WARD   | BLAKE |
| JONES  | KING  |
| MARTIN | BLAKE |
| BLAKE  | KING  |
| CLARK  | KING  |
| SCOTT  | JONES |
| KING   | NULL  |
| TURNER | BLAKE |
| ADAMS  | SCOTT |
| JAMES  | BLAKE |
| FORD   | JONES |
| MILLER | CLARK |
+--------+-------+

15、列出受雇日期早于其直接上级的所有员工的编号,姓名,部门名称
emp a 员工表
emp b 领导表

a.mgr = b.empno and a.hiredate < b.hiredate
select 
    a.ename '员工', a.hiredate, b.ename '领导', b.hiredate, d.dname
from
    emp a
join
    emp b
on
    a.mgr = b.empno
join
    dept d
on
    a.deptno = d.deptno
where
     a.hiredate < b.hiredate;
+-------+------------+-------+------------+------------+
| 员工     | hiredate   | 领导    | hiredate   | dname      |
+-------+------------+-------+------------+------------+
| CLARK | 1981-06-09 | KING  | 1981-11-17 | ACCOUNTING |
| SMITH | 1980-12-17 | FORD  | 1981-12-03 | RESEARCH   |
| JONES | 1981-04-02 | KING  | 1981-11-17 | RESEARCH   |
| ALLEN | 1981-02-20 | BLAKE | 1981-05-01 | SALES      |
| WARD  | 1981-02-22 | BLAKE | 1981-05-01 | SALES      |
| BLAKE | 1981-05-01 | KING  | 1981-11-17 | SALES      |
+-------+------------+-------+------------+------------+

16、列出部门名称和这些部门的员工信息, 同时列出那些没有员工的部门

select 
    e.*,d.dname
from
    emp e
right join
    dept d
on
    e.deptno = d.deptno;
+-------+--------+-----------+------+------------+---------+---------+--------+------------+
| EMPNO | ENAME  | JOB       | MGR  | HIREDATE   | SAL     | COMM    | DEPTNO | dname      |
+-------+--------+-----------+------+------------+---------+---------+--------+------------+
|  7782 | CLARK  | MANAGER   | 7839 | 1981-06-09 | 2450.00 |    NULL |     10 | ACCOUNTING |
|  7839 | KING   | PRESIDENT | NULL | 1981-11-17 | 5000.00 |    NULL |     10 | ACCOUNTING |
|  7934 | MILLER | CLERK     | 7782 | 1982-01-23 | 1300.00 |    NULL |     10 | ACCOUNTING |
|  7369 | SMITH  | CLERK     | 7902 | 1980-12-17 |  800.00 |    NULL |     20 | RESEARCH   |
|  7566 | JONES  | MANAGER   | 7839 | 1981-04-02 | 2975.00 |    NULL |     20 | RESEARCH   |
|  7788 | SCOTT  | ANALYST   | 7566 | 1987-04-19 | 3000.00 |    NULL |     20 | RESEARCH   |
|  7876 | ADAMS  | CLERK     | 7788 | 1987-05-23 | 1100.00 |    NULL |     20 | RESEARCH   |
|  7902 | FORD   | ANALYST   | 7566 | 1981-12-03 | 3000.00 |    NULL |     20 | RESEARCH   |
|  7499 | ALLEN  | SALESMAN  | 7698 | 1981-02-20 | 1600.00 |  300.00 |     30 | SALES      |
|  7521 | WARD   | SALESMAN  | 7698 | 1981-02-22 | 1250.00 |  500.00 |     30 | SALES      |
|  7654 | MARTIN | SALESMAN  | 7698 | 1981-09-28 | 1250.00 | 1400.00 |     30 | SALES      |
|  7698 | BLAKE  | MANAGER   | 7839 | 1981-05-01 | 2850.00 |    NULL |     30 | SALES      |
|  7844 | TURNER | SALESMAN  | 7698 | 1981-09-08 | 1500.00 |    0.00 |     30 | SALES      |
|  7900 | JAMES  | CLERK     | 7698 | 1981-12-03 |  950.00 |    NULL |     30 | SALES      |
|  NULL | NULL   | NULL      | NULL | NULL       |    NULL |    NULL |   NULL | OPERATIONS |
+-------+--------+-----------+------+------------+---------+---------+--------+------------+

17、列出至少有 5 个员工的所有部门
按照部门编号分组，计数，筛选出 >= 5

select 
    deptno
from
    emp
group by
    deptno
having
    count(*) >= 5;
+--------+
| deptno |
+--------+
|     20 |
|     30 |
+--------+

18、列出薪金比"SMITH" 多的所有员工信息

select ename,sal from emp where sal > (select sal from emp where ename = 'SMITH');
+--------+---------+
| ename  | sal     |
+--------+---------+
| ALLEN  | 1600.00 |
| WARD   | 1250.00 |
| JONES  | 2975.00 |
| MARTIN | 1250.00 |
| BLAKE  | 2850.00 |
| CLARK  | 2450.00 |
| SCOTT  | 3000.00 |
| KING   | 5000.00 |
| TURNER | 1500.00 |
| ADAMS  | 1100.00 |
| JAMES  |  950.00 |
| FORD   | 3000.00 |
| MILLER | 1300.00 |
+--------+---------+

19、列出所有"CLERK"( 办事员) 的姓名及其部门名称, 部门的人数

select ename,job from emp where job = 'CLERK';
+--------+-------+
| ename  | job   |
+--------+-------+
| SMITH  | CLERK |
| ADAMS  | CLERK |
| JAMES  | CLERK |
| MILLER | CLERK |
+--------+-------+
select 
    e.ename,e.job,d.dname
from 
    emp e
join
    dept d
on
    e.deptno = d.deptno
where 
    e.job = 'CLERK';
+--------+-------+------------+
| ename  | job   | dname      |
+--------+-------+------------+
| MILLER | CLERK | ACCOUNTING |
| SMITH  | CLERK | RESEARCH   |
| ADAMS  | CLERK | RESEARCH   |
| JAMES  | CLERK | SALES      |
+--------+-------+------------+
select 
    e.ename,e.job,d.dname,d.deptno
from 
    emp e
join
    dept d
on
    e.deptno = d.deptno
where 
    e.job = 'CLERK';
+--------+-------+------------+--------+
| ename  | job   | dname      | deptno |
+--------+-------+------------+--------+
| MILLER | CLERK | ACCOUNTING |     10 |
| SMITH  | CLERK | RESEARCH   |     20 |
| ADAMS  | CLERK | RESEARCH   |     20 |
| JAMES  | CLERK | SALES      |     30 |
+--------+-------+------------+--------+
//每个部门的人数？
select deptno, count(*) as deptcount from emp group by deptno;
+--------+-----------+
| deptno | deptcount |
+--------+-----------+
|     10 |         3 |
|     20 |         5 |
|     30 |         6 |
+--------+-----------+
select 
    t1.*,t2.deptcount
from
    (select 
        e.ename,e.job,d.dname,d.deptno
    from 
        emp e
    join
        dept d
    on
        e.deptno = d.deptno
    where 
        e.job = 'CLERK') t1
join
    (select deptno, count(*) as deptcount from emp group by deptno) t2
on
    t1.deptno = t2.deptno;
+--------+-------+------------+--------+-----------+
| ename  | job   | dname      | deptno | deptcount |
+--------+-------+------------+--------+-----------+
| MILLER | CLERK | ACCOUNTING |     10 |         3 |
| SMITH  | CLERK | RESEARCH   |     20 |         5 |
| ADAMS  | CLERK | RESEARCH   |     20 |         5 |
| JAMES  | CLERK | SALES      |     30 |         6 |
+--------+-------+------------+--------+-----------+

20、列出最低薪金大于 1500 的各种工作及从事此工作的全部雇员人数
按照工作岗位分组求最小值。

select job,count(*) from emp group by job having min(sal) > 1500;
+-----------+----------+
| job       | count(*) |
+-----------+----------+
| ANALYST   |        2 |
| MANAGER   |        3 |
| PRESIDENT |        1 |
+-----------+----------+

21、列出在部门"SALES"< 销售部> 工作的员工的姓名, 假定不知道销售部的部门编号.

select ename from emp where deptno = (select deptno from dept where dname = 'SALES');
+--------+
| ename  |
+--------+
| ALLEN  |
| WARD   |
| MARTIN |
| BLAKE  |
| TURNER |
| JAMES  |
+--------+[object Object]

22、列出薪金高于公司平均薪金的所有员工, 所在部门, 上级领导, 雇员的工资等级.

select 
    e.ename '员工',d.dname,l.ename '领导',s.grade
from
    emp e
join
    dept d
on
    e.deptno = d.deptno
left join
    emp l
on
    e.mgr = l.empno
join
    salgrade s
on
    e.sal between s.losal and s.hisal
where
    e.sal > (select avg(sal) from emp);
+-------+------------+-------+-------+
| 员工     | dname      | 领导    | grade |
+-------+------------+-------+-------+
| JONES | RESEARCH   | KING  |     4 |
| BLAKE | SALES      | KING  |     4 |
| CLARK | ACCOUNTING | KING  |     4 |
| SCOTT | RESEARCH   | JONES |     4 |
| KING  | ACCOUNTING | NULL  |     5 |
| FORD  | RESEARCH   | JONES |     4 |
+-------+------------+-------+-------+

23、列出与"SCOTT" 从事相同工作的所有员工及部门名称

select job from emp where ename = 'SCOTT';
+---------+
| job     |
+---------+
| ANALYST |
+---------+
select 
    e.ename,e.job,d.dname
from
    emp e
join
    dept d
on
    e.deptno = d.deptno
where
    e.job = (select job from emp where ename = 'SCOTT')
and
    e.ename <> 'SCOTT';
+-------+---------+----------+
| ename | job     | dname    |
+-------+---------+----------+
| FORD  | ANALYST | RESEARCH |
+-------+---------+----------+[object Object]

24、列出薪金等于部门 30 中员工的薪金的其他员工的姓名和薪金.

select distinct sal from emp where deptno = 30;
+---------+
| sal     |
+---------+
| 1600.00 |
| 1250.00 |
| 2850.00 |
| 1500.00 |
|  950.00 |
+---------+
select 
    ename,sal 
from 
    emp 
where 
    sal in(select distinct sal from emp where deptno = 30) 
and 
    deptno <> 30;
Empty set (0.00 sec)

25、列出薪金高于在部门 30 工作的所有员工的薪金的员工姓名和薪金. 部门名称

select max(sal) from emp where deptno = 30;
+----------+
| max(sal) |
+----------+
|  2850.00 |
+----------+
select
    e.ename,e.sal,d.dname
from
    emp e
join
    dept d
on
    e.deptno = d.deptno
where
    e.sal > (select max(sal) from emp where deptno = 30);
+-------+---------+------------+
| ename | sal     | dname      |
+-------+---------+------------+
| KING  | 5000.00 | ACCOUNTING |
| JONES | 2975.00 | RESEARCH   |
| SCOTT | 3000.00 | RESEARCH   |
| FORD  | 3000.00 | RESEARCH   |
+-------+---------+------------+

26、列出在每个部门工作的员工数量, 平均工资和平均服务期限

没有员工的部门，部门人数是0

select 
    d.deptno, count(e.ename) ecount,ifnull(avg(e.sal),0) as avgsal, ifnull(avg(timestampdiff(YEAR, hiredate, now())), 0) as avgservicetime
from
    emp e
right join
    dept d
on
    e.deptno = d.deptno
group by
    d.deptno;
+--------+--------+-------------+----------------+
| deptno | ecount | avgsal      | avgservicetime |
+--------+--------+-------------+----------------+
|     10 |      3 | 2916.666667 |        38.0000 |
|     20 |      5 | 2175.000000 |        35.8000 |
|     30 |      6 | 1566.666667 |        38.3333 |
|     40 |      0 |    0.000000 |         0.0000 |
+--------+--------+-------------+----------------+

在mysql当中怎么计算两个日期的“年差”，差了多少年？
   TimeStampDiff(间隔类型, 前一个日期, 后一个日期)

   timestampdiff(YEAR, hiredate, now())

   间隔类型：
       SECOND 秒，
       MINUTE 分钟，
       HOUR 小时，
       DAY 天，
       WEEK 星期
       MONTH 月，
       QUARTER 季度，
       YEAR 年27、列出所有员工的姓名、部门名称和工资。

select 
    e.ename,d.dname,e.sal
from
    emp e
join 
    dept d
on
    e.deptno = d.deptno;
+--------+------------+---------+
| ename  | dname      | sal     |
+--------+------------+---------+
| CLARK  | ACCOUNTING | 2450.00 |
| KING   | ACCOUNTING | 5000.00 |
| MILLER | ACCOUNTING | 1300.00 |
| SMITH  | RESEARCH   |  800.00 |
| JONES  | RESEARCH   | 2975.00 |
| SCOTT  | RESEARCH   | 3000.00 |
| ADAMS  | RESEARCH   | 1100.00 |
| FORD   | RESEARCH   | 3000.00 |
| ALLEN  | SALES      | 1600.00 |
| WARD   | SALES      | 1250.00 |
| MARTIN | SALES      | 1250.00 |
| BLAKE  | SALES      | 2850.00 |
| TURNER | SALES      | 1500.00 |
| JAMES  | SALES      |  950.00 |
+--------+------------+---------+

28、列出所有部门的详细信息和人数

select 
    d.deptno,d.dname,d.loc,count(e.ename)
from
    emp e
right join
    dept d
on
    e.deptno = d.deptno
group by
    d.deptno,d.dname,d.loc;
+--------+------------+----------+----------------+
| deptno | dname      | loc      | count(e.ename) |
+--------+------------+----------+----------------+
|     10 | ACCOUNTING | NEW YORK |              3 |
|     20 | RESEARCH   | DALLAS   |              5 |
|     30 | SALES      | CHICAGO  |              6 |
|     40 | OPERATIONS | BOSTON   |              0 |
+--------+------------+----------+----------------+

29、列出各种工作的最低工资及从事此工作的雇员姓名

select 
    job,min(sal) as minsal
from
    emp
group by
    job;
+-----------+----------+
| job       | minsal        |
+-----------+----------+
| ANALYST   |  3000.00 |
| CLERK     |   800.00 |
| MANAGER   |  2450.00 |
| PRESIDENT |  5000.00 |
| SALESMAN  |  1250.00 |
+-----------+----------+
emp e和以上t连接
select 
    e.ename,t.*
from
    emp e
join
    (select 
        job,min(sal) as minsal
    from
        emp
    group by
        job) t
on
    e.job = t.job and e.sal = t.minsal;
+--------+-----------+---------+
| ename  | job       | minsal  |
+--------+-----------+---------+
| SMITH  | CLERK     |  800.00 |
| WARD   | SALESMAN  | 1250.00 |
| MARTIN | SALESMAN  | 1250.00 |
| CLARK  | MANAGER   | 2450.00 |
| SCOTT  | ANALYST   | 3000.00 |
| KING   | PRESIDENT | 5000.00 |
| FORD   | ANALYST   | 3000.00 |
+--------+-----------+---------+

30、列出各个部门的 MANAGER( 领导) 的最低薪金

select 
    deptno, min(sal)
from
    emp
where
    job = 'MANAGER'
group by
    deptno;
+--------+----------+
| deptno | min(sal) |
+--------+----------+
|     10 |  2450.00 |
|     20 |  2975.00 |
|     30 |  2850.00 |
+--------+----------+

31、列出所有员工的年工资, 按年薪从低到高排序

select 
    ename,(sal + ifnull(comm,0)) * 12 as yearsal
from
    emp
order by
    yearsal asc;
+--------+----------+
| ename  | yearsal  |
+--------+----------+
| SMITH  |  9600.00 |
| JAMES  | 11400.00 |
| ADAMS  | 13200.00 |
| MILLER | 15600.00 |
| TURNER | 18000.00 |
| WARD   | 21000.00 |
| ALLEN  | 22800.00 |
| CLARK  | 29400.00 |
| MARTIN | 31800.00 |
| BLAKE  | 34200.00 |
| JONES  | 35700.00 |
| FORD   | 36000.00 |
| SCOTT  | 36000.00 |
| KING   | 60000.00 |
+--------+----------+

32、求出员工领导的薪水超过3000的员工名称与领导

select 
    a.ename '员工',b.ename '领导'
from
    emp a
join
    emp b
on
    a.mgr = b.empno
where
    b.sal > 3000;
+-------+------+
| 员工  | 领导   |
+-------+------+
| JONES | KING |
| BLAKE | KING |
| CLARK | KING |
+-------+------+

33、求出部门名称中, 带'S'字符的部门员工的工资合计、部门人数

select 
    d.deptno,d.dname,d.loc,count(e.ename),ifnull(sum(e.sal),0) as sumsal
from
    emp e
right join
    dept d
on
    e.deptno = d.deptno
where
    d.dname like '%S%'
group by
    d.deptno,d.dname,d.loc;
+--------+------------+---------+----------------+----------+
| deptno | dname      | loc     | count(e.ename) | sumsal   |
+--------+------------+---------+----------------+----------+
|     20 | RESEARCH   | DALLAS  |              5 | 10875.00 |
|     30 | SALES      | CHICAGO |              6 |  9400.00 |
|     40 | OPERATIONS | BOSTON  |              0 |     0.00 |
+--------+------------+---------+----------------+----------+

34、给任职日期超过 30 年的员工加薪 10%.

update emp set sal = sal * 1.1 where timestampdiff(YEAR, hiredate, now()) > 30;

【大数据】Mysql 作业题（仅适合初学者！）