MYSQY 学习笔记之 练习题

① 先将员工表依据部门编号进行分组并找出每个分组中最高薪水的人员信息

②将最高薪水表与原表进行连接

#员工信息表
mysql> select * from emp;
+-------+--------+-----------+------+------------+---------+---------+--------+
| 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 |
|  7566 | JONES  | MANAGER   | 7839 | 1981-04-02 | 2975.00 |    NULL |     20 |
|  7654 | MARTIN | SALESMAN  | 7698 | 1981-09-28 | 1250.00 | 1400.00 |     30 |
|  7698 | BLAKE  | MANAGER   | 7839 | 1981-05-01 | 2850.00 |    NULL |     30 |
|  7782 | CLARK  | MANAGER   | 7839 | 1981-06-09 | 1000.00 |    NULL |     10 |
|  7788 | SCOTT  | ANALYST   | 7566 | 1987-04-19 | 3000.00 |    NULL |     20 |
|  7839 | KING   | PRESIDENT | NULL | 1981-11-17 | 1000.00 |    NULL |     10 |
|  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 |
|  7902 | FORD   | ANALYST   | 7566 | 1981-12-03 | 3000.00 |    NULL |     20 |
|  7934 | MILLER | CLERK     | 7782 | 1982-01-23 | 1000.00 |    NULL |     10 |
+-------+--------+-----------+------+------------+---------+---------+--------+
14 rows in set (0.00 sec)
#按照部门进行分组并查处每个组中最高薪水
mysql> select  deptno ,max(sal) maxsalary  from emp group by deptno;
+--------+-----------+
| deptno | maxsalary |
+--------+-----------+
|     20 |   3000.00 |
|     30 |   2850.00 |
|     10 |   1000.00 |
+--------+-----------+
3 rows in set (0.00 sec)
#表连接
mysql> 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  |
+--------+--------+---------+
| SCOTT  |     20 | 3000.00 |
| FORD   |     20 | 3000.00 |
| BLAKE  |     30 | 2850.00 |
| CLARK  |     10 | 1000.00 |
| KING   |     10 | 1000.00 |
| MILLER |     10 | 1000.00 |
+--------+--------+---------+
6 rows in set (0.00 sec)

试题二：哪些人的薪水在部门的平均薪资之上？

分析：

①按照部门进行分组并找出每个部门的平均薪资（作为t表）
③表连接 
   条件如下：I. 两张表的deptno相同 
           II. 员工表的员工薪水 大于 t表中的平均薪水           

#按照部门进行分组后然后再求出每个部门的平均薪资
mysql>  select deptno, avg(sal) avgsal from emp group by deptno;
+--------+-------------+
| deptno | avgsal      |
+--------+-------------+
|     20 | 2175.000000 |
|     30 | 1566.666667 |
|     10 | 1000.000000 |
+--------+-------------+
3 rows in set (0.00 sec)

#表连接
mysql> select t.*,e.ename,e.sal from emp e join  (select deptno, avg(sal) 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 |
|     20 | 2175.000000 | FORD  | 3000.00 |
+--------+-------------+-------+---------+
5 rows in set (0.00 sec)

试题三：取得部门中（所有人的）平均的薪水等级？？

分析：

区分： 平均的薪水等级VS 平均薪水的等级

平均的薪水等级：先求每个员工的薪水等级，然后再求平均值

平均薪水的等级：先求薪水的平均值，然后再求薪水等级

①先显示出每个部门中所有员工的基本信息及薪水等级
②薪水等级求平均值

#先显示出每个部门中所有员工的基本信息及薪水等级
#方式一：
mysql> select e.deptno,e.ename,e.sal ,d.grade from salgrade d,emp e where e.sal between d.losal and d.hisal order by e.deptno;
+--------+--------+---------+-------+
| deptno | ename  | sal     | grade |
+--------+--------+---------+-------+
|     10 | CLARK  | 1000.00 |     1 |
|     10 | KING   | 1000.00 |     1 |
|     10 | MILLER | 1000.00 |     1 |
|     20 | SMITH  |  800.00 |     1 |
|     20 | ADAMS  | 1100.00 |     1 |
|     20 | JONES  | 2975.00 |     4 |
|     20 | SCOTT  | 3000.00 |     4 |
|     20 | FORD   | 3000.00 |     4 |
|     30 | JAMES  |  950.00 |     1 |
|     30 | WARD   | 1250.00 |     2 |
|     30 | MARTIN | 1250.00 |     2 |
|     30 | TURNER | 1500.00 |     3 |
|     30 | ALLEN  | 1600.00 |     3 |
|     30 | BLAKE  | 2850.00 |     4 |
+--------+--------+---------+-------+
14 rows in set (0.00 sec)
#方式二：通过join...on..的方式实现表的连接
mysql> select e.deptno,e.ename,e.sal ,d.grade from salgrade d join emp e on e.sal between d.losal and d.hisal order by e.deptno;
+--------+--------+---------+-------+
| deptno | ename  | sal     | grade |
+--------+--------+---------+-------+
|     10 | CLARK  | 1000.00 |     1 |
|     10 | KING   | 1000.00 |     1 |
|     10 | MILLER | 1000.00 |     1 |
|     20 | SMITH  |  800.00 |     1 |
|     20 | ADAMS  | 1100.00 |     1 |
|     20 | JONES  | 2975.00 |     4 |
|     20 | SCOTT  | 3000.00 |     4 |
|     20 | FORD   | 3000.00 |     4 |
|     30 | JAMES  |  950.00 |     1 |
|     30 | WARD   | 1250.00 |     2 |
|     30 | MARTIN | 1250.00 |     2 |
|     30 | TURNER | 1500.00 |     3 |
|     30 | ALLEN  | 1600.00 |     3 |
|     30 | BLAKE  | 2850.00 |     4 |
+--------+--------+---------+-------+
14 rows in set (0.00 sec)

#薪水等级求平均值
mysql> select e.deptno,e.ename,e.sal ,avg(d.grade) from salgrade d,emp e where e.sal between d.losal and d.hisal group by e.deptno;
+--------+-------+---------+--------------+
| deptno | ename | sal     | avg(d.grade) |
+--------+-------+---------+--------------+
|     20 | SMITH |  800.00 |       2.8000 |
|     30 | JAMES |  950.00 |       2.5000 |
|     10 | CLARK | 1000.00 |       1.0000 |
+--------+-------+---------+--------------+
3 rows in set (0.00 sec)

试题四：不准用组函数（Max），取得最高薪水（给出两种方案）

分析：

方案一：sal降序，limit1
   ①将表依据薪水的高低进行排序
   ②然后通过limit进行取出最高薪水
方案二：表的自连接
   ①通过自连接生成一薪水表（原表的最高薪水不在其中）
   ②通过not in 的方式查出最高薪水  

#方案一：
#将表按照薪水高低降序排序
mysql> select ename,sal from emp order by sal desc ;
+--------+---------+
| ename  | sal     |
+--------+---------+
| SCOTT  | 3000.00 |
| FORD   | 3000.00 |
| JONES  | 2975.00 |
| BLAKE  | 2850.00 |
| ALLEN  | 1600.00 |
| TURNER | 1500.00 |
| WARD   | 1250.00 |
| MARTIN | 1250.00 |
| ADAMS  | 1100.00 |
| CLARK  | 1000.00 |
| KING   | 1000.00 |
| MILLER | 1000.00 |
| JAMES  |  950.00 |
| SMITH  |  800.00 |
+--------+---------+
14 rows in set (0.00 sec)
#通过limit来取出排序表中的第一个记录
mysql> select ename,sal from emp order by sal desc limit 1;
+-------+---------+
| ename | sal     |
+-------+---------+
| FORD  | 3000.00 |
+-------+---------+
1 row in set (0.00 sec)

#方式二：自连接
mysql> select sal from emp;
+---------+
| sal     |
+---------+
|  800.00 |
|  950.00 |
| 1000.00 |
| 1000.00 |
| 1000.00 |
| 1100.00 |
| 1250.00 |
| 1250.00 |
| 1500.00 |
| 1600.00 |
| 2850.00 |
| 2975.00 |
| 3000.00 |
| 3000.00 |
+---------+
14 rows in set (0.00 sec)
#通过自连接得到的结果中只有 3000（最高薪）不满足（distinct是用来去重的）
mysql> select distinct a.sal from emp a join emp b on a.sal < b.sal;
+---------+
| sal     |
+---------+
|  800.00 |
|  950.00 |
| 1000.00 |
| 1100.00 |
| 1250.00 |
| 1500.00 |
| 1600.00 |
| 2850.00 |
| 2975.00 |
+---------+
9 rows in set (0.00 sec)

mysql> select sal from emp where sal not in (select distinct a.sal from emp a join emp b on a.sal < b.sal);
+---------+
| sal     |
+---------+
| 3000.00 |
| 3000.00 |
+---------+
2 rows in set (0.00 sec)

#去重
mysql> select  distinct sal from emp where sal not in (select distinct a.sal from emp a join emp b on a.sal < b.sal);
+---------+
| sal     |
+---------+
| 3000.00 |
+---------+
1 row in set (0.00 sec)

试题五：取得平均薪水最高的部门的部门编号（至少给出两种方案）

分析：

方案一：limit
  ①将员工表根据部门进行分组
  ②根据函数（avg）求出每个组中的平均薪水
  ③根据薪水进行排序
  ④然后通过limit选取薪水最高的部门
方案二：max
  ①将员工表根据部门进行分组并求出平均值
  ②通过max函数求出最大的

#方案一
#根据部门进行分组、求出平均值、排序
mysql> select deptno,avg(sal) avgsal from emp group by deptno order by avg(sal) desc;
+--------+-------------+
| deptno | avgsal      |
+--------+-------------+
|     20 | 2175.000000 |
|     30 | 1566.666667 |
|     10 | 1000.000000 |
+--------+-------------+
3 rows in set (0.00 sec)

 #通过limit选取第一个记录
mysql> select deptno,avg(sal) avgsal from emp group by deptno order by avg(sal) desc limit 1;
+--------+-------------+
| deptno | avgsal      |
+--------+-------------+
|     20 | 2175.000000 |
+--------+-------------+
1 row in set (0.00 sec)

mysql> select deptno from emp group by deptno order by avg(sal) desc limit 1;
+--------+
| deptno |
+--------+
|     20 |
+--------+
1 row in set (0.00 sec)

方案二：max

#第一步：求出每个部门的平均薪水
mysql> select deptno,avg(sal) avgsal from emp group by deptno order by avg(sal) desc;
+--------+-------------+
| deptno | avgsal      |
+--------+-------------+
|     20 | 2175.000000 |
|     30 | 1566.666667 |
|     10 | 1000.000000 |
+--------+-------------+
3 rows in set (0.00 sec)

#通过max函数找到平均薪水最高的部门
mysql> select t.deptno,max(t.avgsal) maxavgsal from (select deptno,avg(sal) avgsal from emp group by deptno order by avg(sal) desc) t;
+--------+-------------+
| deptno | maxavgsal   |
+--------+-------------+
|     20 | 2175.000000 |
+--------+-------------+
1 row in set (0.00 sec)

试题六：取得平均薪水最高的部门名称

分析：

①先取得平均薪水最高的·部门编号
②连表查询得到部门编号对应的部门名称

mysql> select e.deptno,avg(e.sal) ,d.dname from emp e,dept d where e.deptno = d.deptno group by e.deptno order by avg(e.sal) desc;
+--------+-------------+------------+
| deptno | avg(e.sal)  | dname      |
+--------+-------------+------------+
|     20 | 2175.000000 | RESEARCH   |
|     30 | 1566.666667 | SALES      |
|     10 | 1000.000000 | ACCOUNTING |
+--------+-------------+------------+
3 rows in set (0.00 sec)

mysql> select e.deptno,avg(e.sal) ,d.dname from emp e,dept d where e.deptno = d.deptno group by e.deptno order by avg(e.sal) desc limit 1;
+--------+-------------+----------+
| deptno | avg(e.sal)  | dname    |
+--------+-------------+----------+
|     20 | 2175.000000 | RESEARCH |
+--------+-------------+----------+
1 row in set (0.00 sec)
#剔除无用信息
mysql> select d.dname from emp e,dept d where e.deptno = d.deptno group by e.deptno order by avg(e.sal) desc limit 1;
+----------+
| dname    |
+----------+
| RESEARCH |
+----------+
1 row in set (0.00 sec)

试题七：取得平均薪水等级最低的部门名称

#找出每个部门的平均薪水
mysql> select e.deptno,avg(e.sal) ,d.dname from emp e,dept d where e.deptno = d.deptno group by e.deptno order by avg(e.sal) desc;
+--------+-------------+------------+
| deptno | avg(e.sal)  | dname      |
+--------+-------------+------------+
|     20 | 2175.000000 | RESEARCH   |
|     30 | 1566.666667 | SALES      |
|     10 | 1000.000000 | ACCOUNTING |
+--------+-------------+------------+
3 rows in set (0.00 sec)

#找出每个部门的平均薪水等级

mysql> select t.*,e.grade from salgrade e join ( select e.deptno,avg(e.sal) avgsal ,d.dname from emp e,dept d where e.deptno = d.deptno group by e.deptno order by avg(e.sal) desc) t on t.avgsal between e.losal and e.hisal;
+--------+-------------+------------+-------+
| deptno | avgsal      | dname      | grade |
+--------+-------------+------------+-------+
|     20 | 2175.000000 | RESEARCH   |     4 |
|     30 | 1566.666667 | SALES      |     3 |
|     10 | 1000.000000 | ACCOUNTING |     1 |
+--------+-------------+------------+-------+
3 rows in set (0.00 sec)

#找出最低薪水
mysql> select avg(sal) as avgsal from emp group by deptno order by avgsal asc limit 1;
+-------------+
| avgsal      |
+-------------+
| 1000.000000 |
+-------------+
1 row in set (0.00 sec)

#找出平均薪水中等级最低的
mysql> 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 |
+-------+
|     1 |
+-------+
1 row in set (0.00 sec)

mysql> select t.*,s.grade from salgrade s join ( select e.deptno,avg(e.sal) avgsal ,d.dname from emp e,dept d where e.deptno = d.deptno group by e.deptno order by avg(e.sal) desc) t 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);
+--------+-------------+------------+-------+
| deptno | avgsal      | dname      | grade |
+--------+-------------+------------+-------+
|     10 | 1000.000000 | ACCOUNTING |     1 |
+--------+-------------+------------+-------+
1 row in set (0.00 sec)

试题八：取得比普通员工（员工代码没有在mgr字段上出现的）最高薪水还要高的领导人姓名

分析：

① 找到普通员工的最高薪水
②找出高于最高薪水的领导

#查找所有领导的编号
mysql> select  distinct mgr from emp where mgr is not null;
+------+
| mgr  |
+------+
| 7902 |
| 7698 |
| 7839 |
| 7566 |
| 7788 |
| 7782 |
+------+
6 rows in set (0.00 sec)
#从非领导编号中查找员工中的最高工资
mysql> select max(sal) from emp where empno not in (select  distinct mgr from emp where mgr is not null);
+----------+
| max(sal) |
+----------+
|  1600.00 |
+----------+
1 row in set (0.00 sec)

#再根据最员工的最高工资找到对应的领导人名字
mysql> 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     |
+-------+---------+
| BLAKE | 2850.00 |
| JONES | 2975.00 |
| SCOTT | 3000.00 |
| FORD  | 3000.00 |
+-------+---------+
4 rows in set (0.00 sec)

试题九：取得薪水最高的前五名员工

分析

①将表进行排序（升序）
②通过limit来获取前五个员工的信息

#说明：第一个员工对应的标号是0
mysql> select ename ,sal from emp order by sal desc limit 0,5;
+-------+---------+
| ename | sal     |
+-------+---------+
| SCOTT | 3000.00 |
| FORD  | 3000.00 |
| JONES | 2975.00 |
| BLAKE | 2850.00 |
| ALLEN | 1600.00 |
+-------+---------+
5 rows in set (0.00 sec)

试题十：取得薪水最高的第六到第十名员工

分析：

① 将表按照薪水进行降序排列
② 通过limit获取特定名次的员工

#从第六名（下表为5）到第十名（共5个人）
mysql> select ename ,sal from emp order by sal desc limit 5,5;
+--------+---------+
| ename  | sal     |
+--------+---------+
| TURNER | 1500.00 |
| WARD   | 1250.00 |
| MARTIN | 1250.00 |
| ADAMS  | 1100.00 |
| CLARK  | 1000.00 |
+--------+---------+
5 rows in set (0.00 sec)

试题十一：取得最后入职的5名员工

分析：如期也是可以降序或升序排列的

① 将员工表按照员工入职的时间降序排列
② 通过limit获取的前五个员工就是最后入职的五个员工

#根据入职时间降序排列（由晚到早）
mysql> select ename,hiredate from emp order by hiredate desc;
+--------+------------+
| ename  | hiredate   |
+--------+------------+
| ADAMS  | 1987-05-23 |
| SCOTT  | 1987-04-19 |
| MILLER | 1982-01-23 |
| JAMES  | 1981-12-03 |
| FORD   | 1981-12-03 |
| KING   | 1981-11-17 |
| MARTIN | 1981-09-28 |
| TURNER | 1981-09-08 |
| CLARK  | 1981-06-09 |
| BLAKE  | 1981-05-01 |
| JONES  | 1981-04-02 |
| WARD   | 1981-02-22 |
| ALLEN  | 1981-02-20 |
| SMITH  | 1980-12-17 |
+--------+------------+
14 rows in set (0.00 sec)
#通过limit获取前五个
mysql> select ename,hiredate from emp order by hiredate desc limit 0,5;
+--------+------------+
| ename  | hiredate   |
+--------+------------+
| ADAMS  | 1987-05-23 |
| SCOTT  | 1987-04-19 |
| MILLER | 1982-01-23 |
| JAMES  | 1981-12-03 |
| FORD   | 1981-12-03 |
+--------+------------+
5 rows in set (0.00 sec)

试题十二：取得每个薪水等级有多少员工

分析：

①找出每个员工的薪水等级
②分组统计

mysql> 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 |
| JAMES  |  950.00 |     1 |
| CLARK  | 1000.00 |     1 |
| KING   | 1000.00 |     1 |
| MILLER | 1000.00 |     1 |
| ADAMS  | 1100.00 |     1 |
| WARD   | 1250.00 |     2 |
| MARTIN | 1250.00 |     2 |
| TURNER | 1500.00 |     3 |
| ALLEN  | 1600.00 |     3 |
| BLAKE  | 2850.00 |     4 |
| JONES  | 2975.00 |     4 |
| SCOTT  | 3000.00 |     4 |
| FORD   | 3000.00 |     4 |
+--------+---------+-------+
14 rows in set (0.00 sec)
#按照级别分组统计
mysql> 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 |        6 |
|     2 |        2 |
|     3 |        2 |
|     4 |        4 |
+-------+----------+
4 rows in set (0.00 sec)

试题十四：列出所有员工及领导姓名

分析：

①将一张表当做两张表来使用
②第一个表中取出员工名称 和 领导编号 ，第二个表中取出编号对应领导名称

mysql> select * from emp;
+-------+--------+-----------+------+------------+---------+---------+--------+
| 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 |
|  7566 | JONES  | MANAGER   | 7839 | 1981-04-02 | 2975.00 |    NULL |     20 |
|  7654 | MARTIN | SALESMAN  | 7698 | 1981-09-28 | 1250.00 | 1400.00 |     30 |
|  7698 | BLAKE  | MANAGER   | 7839 | 1981-05-01 | 2850.00 |    NULL |     30 |
|  7782 | CLARK  | MANAGER   | 7839 | 1981-06-09 | 1000.00 |    NULL |     10 |
|  7788 | SCOTT  | ANALYST   | 7566 | 1987-04-19 | 3000.00 |    NULL |     20 |
|  7839 | KING   | PRESIDENT | NULL | 1981-11-17 | 1000.00 |    NULL |     10 |
|  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 |
|  7902 | FORD   | ANALYST   | 7566 | 1981-12-03 | 3000.00 |    NULL |     20 |
|  7934 | MILLER | CLERK     | 7782 | 1982-01-23 | 1000.00 |    NULL |     10 |
+-------+--------+-----------+------+------------+---------+---------+--------+
14 rows in set (0.00 sec)

#使用左外连接
mysqlmysql> select  e.ename ,d. ename from emp e  left join emp d on e.mgr=d.empno;
+--------+-------+
| ename  | ename |
+--------+-------+
| 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 |
+--------+-------+
14 rows in set (0.00 sec)

试题十五：列出受雇日期早于直接上级的所有员工的编号、姓名、部门名称

分析：

①条件限制中添加 编号对应相同 并且 员工受雇时间早于直接上级受雇时间

mysql> 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      |
+-------+------------+-------+------------+------------+
| SMITH | 1980-12-17 | FORD  | 1981-12-03 | RESEARCH   |
| ALLEN | 1981-02-20 | BLAKE | 1981-05-01 | SALES      |
| WARD  | 1981-02-22 | BLAKE | 1981-05-01 | SALES      |
| JONES | 1981-04-02 | KING  | 1981-11-17 | RESEARCH   |
| BLAKE | 1981-05-01 | KING  | 1981-11-17 | SALES      |
| CLARK | 1981-06-09 | KING  | 1981-11-17 | ACCOUNTING |
+-------+------------+-------+------------+------------+
6 rows in set (0.00 sec)

试题十六：列出部门名称和这些部门的员工信息，同时列出那些没有员工的部门

分析：左外连接

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

试题十七：列出至少有5个员工的所有部门

分析：

①先查找出每个部门中的员工总人数
②然后再通过条件限制找到满足条件的部门

#查询出每个部门中的人数并按照部门人数从高到底进行排列
mysql> select deptno,count(deptno) number from emp group by deptno order by count(deptno) desc;
+--------+--------+
| deptno | number |
+--------+--------+
|     30 |      6 |
|     20 |      5 |
|     10 |      3 |
+--------+--------+
3 rows in set (0.00 sec)

mysql> select deptno from emp group by deptno having count(*) >=5;
+--------+
| deptno |
+--------+
|     20 |
|     30 |
+--------+
2 rows in set (0.00 sec)

试题十八：列出薪金比“SMITH”多的员工信息

分析：

①先查询对应员工的薪水信息
②根据第①步中查出的薪水信息来筛选员工信息

#分开查询
#先查询对应名称员工的薪水
mysql> select  ename,sal  from emp where ename ='SMITH';
+-------+--------+
| ename | sal    |
+-------+--------+
| SMITH | 800.00 |
+-------+--------+
1 row in set (0.00 sec)

#根据薪水来进行筛选出薪资大于800的员工信息
mysql> select empno,ename,job,mgr,hiredate,sal ,comm,deptno from emp where sal>800;
+-------+--------+-----------+------+------------+---------+---------+--------+
| empno | ename  | job       | mgr  | hiredate   | sal     | comm    | deptno |
+-------+--------+-----------+------+------------+---------+---------+--------+
|  7499 | ALLEN  | SALESMAN  | 7698 | 1981-02-20 | 1600.00 |  300.00 |     30 |
|  7521 | WARD   | SALESMAN  | 7698 | 1981-02-22 | 1250.00 |  500.00 |     30 |
|  7566 | JONES  | MANAGER   | 7839 | 1981-04-02 | 2975.00 |    NULL |     20 |
|  7654 | MARTIN | SALESMAN  | 7698 | 1981-09-28 | 1250.00 | 1400.00 |     30 |
|  7698 | BLAKE  | MANAGER   | 7839 | 1981-05-01 | 2850.00 |    NULL |     30 |
|  7782 | CLARK  | MANAGER   | 7839 | 1981-06-09 | 1000.00 |    NULL |     10 |
|  7788 | SCOTT  | ANALYST   | 7566 | 1987-04-19 | 3000.00 |    NULL |     20 |
|  7839 | KING   | PRESIDENT | NULL | 1981-11-17 | 1000.00 |    NULL |     10 |
|  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 |
|  7902 | FORD   | ANALYST   | 7566 | 1981-12-03 | 3000.00 |    NULL |     20 |
|  7934 | MILLER | CLERK     | 7782 | 1982-01-23 | 1000.00 |    NULL |     10 |
+-------+--------+-----------+------+------------+---------+---------+--------+
13 rows in set (0.00 sec)

#嵌套查询
mysql> select empno,ename,job,mgr,hiredate,sal comm,deptno from emp where sal>(select sal from emp where ename ='SMITH');
+-------+--------+-----------+------+------------+---------+--------+
| empno | ename  | job       | mgr  | hiredate   | comm    | deptno |
+-------+--------+-----------+------+------------+---------+--------+
|  7499 | ALLEN  | SALESMAN  | 7698 | 1981-02-20 | 1600.00 |     30 |
|  7521 | WARD   | SALESMAN  | 7698 | 1981-02-22 | 1250.00 |     30 |
|  7566 | JONES  | MANAGER   | 7839 | 1981-04-02 | 2975.00 |     20 |
|  7654 | MARTIN | SALESMAN  | 7698 | 1981-09-28 | 1250.00 |     30 |
|  7698 | BLAKE  | MANAGER   | 7839 | 1981-05-01 | 2850.00 |     30 |
|  7782 | CLARK  | MANAGER   | 7839 | 1981-06-09 | 1000.00 |     10 |
|  7788 | SCOTT  | ANALYST   | 7566 | 1987-04-19 | 3000.00 |     20 |
|  7839 | KING   | PRESIDENT | NULL | 1981-11-17 | 1000.00 |     10 |
|  7844 | TURNER | SALESMAN  | 7698 | 1981-09-08 | 1500.00 |     30 |
|  7876 | ADAMS  | CLERK     | 7788 | 1987-05-23 | 1100.00 |     20 |
|  7900 | JAMES  | CLERK     | 7698 | 1981-12-03 |  950.00 |     30 |
|  7902 | FORD   | ANALYST   | 7566 | 1981-12-03 | 3000.00 |     20 |
|  7934 | MILLER | CLERK     | 7782 | 1982-01-23 | 1000.00 |     10 |
+-------+--------+-----------+------+------------+---------+--------+
13 rows in set (0.00 sec)

试题十九：列出所有“CLERKsele（办事员）”的姓名及其部门名称和人数

分析：两张表之间的连接查询

①从第一张表中查出对应职位的员工姓名
②根据第一个表中的部门编号对应第二个表中的部门编号从而查找部门名称

#查找对应的工作对应的员工姓名及部门名称
mysql> select e.ename,d.dname,d.deptno from emp e,dept d where e.job='CLERK' and (e.deptno = d.deptno);
+--------+------------+--------+
| ename  | dname      | deptno |
+--------+------------+--------+
| SMITH  | RESEARCH   |     20 |
| JAMES  | SALES      |     30 |
| MILLER | ACCOUNTING |     10 |
| ADAMS  | RESEARCH   |     20 |
+--------+------------+--------+
4 rows in set (0.00 sec)

#每个部门的人数
mysql> select deptno,count(*) as deptcount from emp group by deptno;
+--------+-----------+
| deptno | deptcount |
+--------+-----------+
|     20 |         5 |
|     30 |         6 |
|     10 |         3 |
+--------+-----------+
3 rows in set (0.00 sec)

mysql> select t1.*,t2.deptcount from (select e.ename,d.dname,d.deptno from emp e,dept d where e.job='CLERK' and (e.deptno = d.deptno)) t1 join (select deptno,count(*) as deptcount from emp group by deptno)t2 on t1.deptno=t2.deptno;
+--------+------------+--------+-----------+
| ename  | dname      | deptno | deptcount |
+--------+------------+--------+-----------+
| SMITH  | RESEARCH   |     20 |         5 |
| JAMES  | SALES      |     30 |         6 |
| MILLER | ACCOUNTING |     10 |         3 |
| ADAMS  | RESEARCH   |     20 |         5 |
+--------+------------+--------+-----------+
4 rows in set (0.00 sec)

试题二十：列出底薪金大于1500的各种工作及从事此工作的全部雇员人数

分析：

① 根据工作分组
②然后统计每个分组中工资大于1500员工总人数

#查询工资大于1500的员工信息
mysql> select job from emp group by job having min(sal)>1500;
+---------+
| job     |
+---------+
| ANALYST |
+---------+
1 row in set (0.00 sec)

#根据工作进行分组后并统计每个工作中的员工总数
mysql> select job ,count(*) from emp group by job having min(sal)>1500;
+---------+----------+
| job     | count(*) |
+---------+----------+
| ANALYST |        2 |
+---------+----------+
1 row in set (0.00 sec)

试题二十一：列出在部门“SALES”<销售部>工作的员工的姓名，假定不知道销售部门的编号

分析：

从部门表中选出销售部门编号和员工表中的编号对应取出员工姓名

mysql> select e.ename from emp e,dept d where d.dname='SALES'and (e.deptno=d.deptno);
+--------+
| ename  |
+--------+
| ALLEN  |
| WARD   |
| MARTIN |
| BLAKE  |
| TURNER |
| JAMES  |
+--------+
6 rows in set (0.00 sec)

试题二十二：列出薪金高于公司平均薪资的所有员工，所在部门、上级领导、雇员的工资等级

mysql> 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 |
+-------+----------+-------+-------+
| FORD  | RESEARCH | JONES |     4 |
| SCOTT | RESEARCH | JONES |     4 |
| JONES | RESEARCH | KING  |     4 |
| BLAKE | SALES    | KING  |     4 |
+-------+----------+-------+-------+
4 rows in set (0.00 sec)

试题二十三：列出与“SCOTT”从事相同工作的所有员工及其部门名称

分析：

① 先找到对应员工对应的职业
②然后根据限制条件找（职业和两张表对应的部门编号）到合适的员工信息

#第一步：从员工信息表总查找该员工对应的职业名称
mysql> select job from emp where (ename ='SCOTT');
+---------+
| job     |
+---------+
| ANALYST |
+---------+
1 row in set (0.00 sec)

#根据职业名称和对应的员工表和部门表编号匹配找到合适的信息
mysql> select e.ename,e.job,d.dname from emp e,dept d where d.deptno= e.deptno and ( job='ANALYST');
+-------+---------+----------+
| ename | job     | dname    |
+-------+---------+----------+
| SCOTT | ANALYST | RESEARCH |
| FORD  | ANALYST | RESEARCH |
+-------+---------+----------+
2 rows in set (0.00 sec)

#将‘SCOTT’排除在外
mysql> 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 |
+-------+---------+----------+
1 row in set (0.00 sec)

试题二十四：列出薪金等于部门 30 中员工的薪资的其他员工的姓名和薪资

#查找部门编号为30的薪资
mysql> select distinct sal from emp where deptno =30;
+---------+
| sal     |
+---------+
|  950.00 |
| 1250.00 |
| 1500.00 |
| 1600.00 |
| 2850.00 |
+---------+
5 rows in set (0.00 sec)
#查询其他员工
mysql> select ename,sal from emp where sal in(select distinct sal from emp where deptno =30) and deptno <>30;
Empty set (0.00 sec)

试题二十五：列出薪资高于所在部门 30 工作的所有员工的薪资的员工姓名和薪资、部门名称

#找出最高薪资
mysql> select max(sal)from emp;
+----------+
| max(sal) |
+----------+
|  3000.00 |
+----------+
1 row in set (0.00 sec)

mysql> 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    |
+-------+---------+----------+
| JONES | 2975.00 | RESEARCH |
| SCOTT | 3000.00 | RESEARCH |
| FORD  | 3000.00 | RESEARCH |
+-------+---------+----------+
3 rows in set (0.00 sec)

试题二十六：列出在每个部门工作的员工数量，平均薪资和平均服务期限

分析：

平均服务期限=系统当前年份-入职年份

如何计算呢？？

TimeStampDiff(间隔类型，前一个日期，后一个日期)
间隔类型：
  （1）SECOND:秒  （2）MINUTE：分钟 （3）HOUR：小时
  （4）WEEK：星期  （5）MONTH：月   （6）QUARTER:季度
  （7）YEAR:年

mysql> select d.*,count(e.ename) ecount ,avg(e.sal) as avgsal,avg(timestampdiff(YEAR,hiredate,now()))as avgsevertime from emp e right join dept d on e.deptno = d.deptno group by d.deptno,d.dname,d.loc;
+--------+------------+----------+--------+-------------+--------------+
| DEPTNO | DNAME      | LOC      | ecount | avgsal      | avgsevertime |
+--------+------------+----------+--------+-------------+--------------+
|     10 | ACCOUNTING | NEW YORK |      3 | 1000.000000 |      40.3333 |
|     20 | RESEARCH   | DALLAS   |      5 | 2175.000000 |      38.4000 |
|     30 | SALES      | CHICAGO  |      6 | 1566.666667 |      40.8333 |
|     40 | OPERATIONS | BOSTON   |      0 |        NULL |         NULL |
+--------+------------+----------+--------+-------------+--------------+
4 rows in set (0.00 sec)

试题二十七：列出所有员工的姓名、部门名称和工资

mysql> select e.ename '员工姓名',d.deptno '部门名称',e.sal '工资' from emp e,dept d where d.deptno=e.deptno;
+----------+----------+---------+
| 员工姓名 | 部门名称 | 工资    |
+----------+----------+---------+
| SMITH    |       20 |  800.00 |
| ALLEN    |       30 | 1600.00 |
| WARD     |       30 | 1250.00 |
| JONES    |       20 | 2975.00 |
| MARTIN   |       30 | 1250.00 |
| BLAKE    |       30 | 2850.00 |
| CLARK    |       10 | 1000.00 |
| SCOTT    |       20 | 3000.00 |
| KING     |       10 | 1000.00 |
| TURNER   |       30 | 1500.00 |
| ADAMS    |       20 | 1100.00 |
| JAMES    |       30 |  950.00 |
| FORD     |       20 | 3000.00 |
| MILLER   |       10 | 1000.00 |
+----------+----------+---------+
14 rows in set (0.00 sec)

试题二十八：列出所有部门的详细信息和人数

#根据部门编号简单的将两张表连接起来，显示员工对应的基本信息
mysql> select e.ename,sal,e.hiredate,e.mgr,e.comm,d.dname from emp e,dept d where d.deptno=e.deptno order by e.deptno;
+--------+---------+------------+------+---------+------------+
| ename  | sal     | hiredate   | mgr  | comm    | dname      |
+--------+---------+------------+------+---------+------------+
| CLARK  | 1000.00 | 1981-06-09 | 7839 |    NULL | ACCOUNTING |
| KING   | 1000.00 | 1981-11-17 | NULL |    NULL | ACCOUNTING |
| MILLER | 1000.00 | 1982-01-23 | 7782 |    NULL | ACCOUNTING |
| SMITH  |  800.00 | 1980-12-17 | 7902 |    NULL | RESEARCH   |
| JONES  | 2975.00 | 1981-04-02 | 7839 |    NULL | RESEARCH   |
| SCOTT  | 3000.00 | 1987-04-19 | 7566 |    NULL | RESEARCH   |
| ADAMS  | 1100.00 | 1987-05-23 | 7788 |    NULL | RESEARCH   |
| FORD   | 3000.00 | 1981-12-03 | 7566 |    NULL | RESEARCH   |
| ALLEN  | 1600.00 | 1981-02-20 | 7698 |  300.00 | SALES      |
| WARD   | 1250.00 | 1981-02-22 | 7698 |  500.00 | SALES      |
| MARTIN | 1250.00 | 1981-09-28 | 7698 | 1400.00 | SALES      |
| BLAKE  | 2850.00 | 1981-05-01 | 7839 |    NULL | SALES      |
| TURNER | 1500.00 | 1981-09-08 | 7698 |    0.00 | SALES      |
| JAMES  |  950.00 | 1981-12-03 | 7698 |    NULL | SALES      |
+--------+---------+------------+------+---------+------------+
14 rows in set (0.00 sec)
#根据部门进行分组并统计部门中的员工人数
mysql> 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 |
+--------+------------+----------+----------------+
4 rows in set (0.00 sec)

试题二十九：列出各种工作的最低工资及从事此工作的雇员姓名

分析：

  根据工资进行分组，然后从分组中找出工资最少的员工信息

#查询员工的姓名、工作、薪资等信息
mysql> select e.ename name,e.job,e.sal from emp e;
+--------+-----------+---------+
| name   | job       | sal     |
+--------+-----------+---------+
| SMITH  | CLERK     |  800.00 |
| ALLEN  | SALESMAN  | 1600.00 |
| WARD   | SALESMAN  | 1250.00 |
| JONES  | MANAGER   | 2975.00 |
| MARTIN | SALESMAN  | 1250.00 |
| BLAKE  | MANAGER   | 2850.00 |
| CLARK  | MANAGER   | 1000.00 |
| SCOTT  | ANALYST   | 3000.00 |
| KING   | PRESIDENT | 1000.00 |
| TURNER | SALESMAN  | 1500.00 |
| ADAMS  | CLERK     | 1100.00 |
| JAMES  | CLERK     |  950.00 |
| FORD   | ANALYST   | 3000.00 |
| MILLER | CLERK     | 1000.00 |
+--------+-----------+---------+
14 rows in set (0.00 sec)

#根据员工的工作分组并查询信息
mysql> select e.ename name,e.job,min(e.sal) lowestsalary from emp e group by job;
+-------+-----------+--------------+
| name  | job       | lowestsalary |
+-------+-----------+--------------+
| SCOTT | ANALYST   |      3000.00 |
| SMITH | CLERK     |       800.00 |
| CLARK | MANAGER   |      1000.00 |
| KING  | PRESIDENT |      1000.00 |
| WARD  | SALESMAN  |      1250.00 |
+-------+-----------+--------------+
5 rows in set (0.00 sec)

试题三十：列各个部门的 MANAGER（领导）的最低薪金

mysql> select deptno,min(sal) from emp where job ='MANAGER'group by deptno;
+--------+----------+
| deptno | min(sal) |
+--------+----------+
|     10 |  1000.00 |
|     30 |  2850.00 |
|     20 |  2975.00 |
+--------+----------+
3 rows in set (0.00 sec)

试题三十一：列出所有员工的年工薪，按照年薪从低到高排序

mysql> select ename '员工姓名',sal*12 '年薪' from emp order by (sal*12) asc;
+----------+----------+
| 员工姓名 | 年薪     |
+----------+----------+
| SMITH    |  9600.00 |
| JAMES    | 11400.00 |
| CLARK    | 12000.00 |
| KING     | 12000.00 |
| MILLER   | 12000.00 |
| ADAMS    | 13200.00 |
| WARD     | 15000.00 |
| MARTIN   | 15000.00 |
| TURNER   | 18000.00 |
| ALLEN    | 19200.00 |
| BLAKE    | 34200.00 |
| JONES    | 35700.00 |
| SCOTT    | 36000.00 |
| FORD     | 36000.00 |
+----------+----------+
14 rows in set (0.00 sec)

#改进（如果薪水为null，就设置为0）
mysql> select ename,(sal+ifnull(comm,0))*12 as yearsal from emp order by yearsal asc;
+--------+----------+
| ename  | yearsal  |
+--------+----------+
| SMITH  |  9600.00 |
| JAMES  | 11400.00 |
| CLARK  | 12000.00 |
| KING   | 12000.00 |
| MILLER | 12000.00 |
| ADAMS  | 13200.00 |
| TURNER | 18000.00 |
| WARD   | 21000.00 |
| ALLEN  | 22800.00 |
| MARTIN | 31800.00 |
| BLAKE  | 34200.00 |
| JONES  | 35700.00 |
| SCOTT  | 36000.00 |
| FORD   | 36000.00 |
+--------+----------+
14 rows in set (0.00 sec)

试题三十二：求出员工领导的薪水超过 3000 的员工名称与领导名称

分析：

①先求出员工和领导的总工资（每个月）
②通过条件限制从而选出合适的员工和领导

mysql> select e.ename,d.ename,d.sal+e.sal from emp e,emp d where e.mgr=d.empno;
+--------+-------+-------------+
| ename  | ename | d.sal+e.sal |
+--------+-------+-------------+
| SMITH  | FORD  |     3800.00 |
| ALLEN  | BLAKE |     4450.00 |
| WARD   | BLAKE |     4100.00 |
| JONES  | KING  |     3975.00 |
| MARTIN | BLAKE |     4100.00 |
| BLAKE  | KING  |     3850.00 |
| CLARK  | KING  |     2000.00 |
| SCOTT  | JONES |     5975.00 |
| TURNER | BLAKE |     4350.00 |
| ADAMS  | SCOTT |     4100.00 |
| JAMES  | BLAKE |     3800.00 |
| FORD   | JONES |     5975.00 |
| MILLER | CLARK |     2000.00 |
+--------+-------+-------------+
13 rows in set (0.00 sec)

mysql> select e.ename '员工名称',d.ename'领导名称',d.sal+e.sal '总薪水' from emp e,emp d where e.mgr=d.empno and ((d.sal + e.sal)>3000);
+----------+----------+---------+
| 员工名称 | 领导名称 | 总薪水  |
+----------+----------+---------+
| SMITH    | FORD     | 3800.00 |
| ALLEN    | BLAKE    | 4450.00 |
| WARD     | BLAKE    | 4100.00 |
| JONES    | KING     | 3975.00 |
| MARTIN   | BLAKE    | 4100.00 |
| BLAKE    | KING     | 3850.00 |
| SCOTT    | JONES    | 5975.00 |
| TURNER   | BLAKE    | 4350.00 |
| ADAMS    | SCOTT    | 4100.00 |
| JAMES    | BLAKE    | 3800.00 |
| FORD     | JONES    | 5975.00 |
+----------+----------+---------+
11 rows in set (0.00 sec)

试题三十三：求出部门名称中带‘S’字符的部门员工的工资合计、部门人数

分析：

通过 %来查找带有特定字母的名字

mysql> select d.dname '部门',sum(e.sal) '工资合计'from emp e,dept d where e.deptno = d.deptno group by e.deptno ;
+------------+----------+
| 部门       | 工资合计 |
+------------+----------+
| RESEARCH   | 10875.00 |
| SALES      |  9400.00 |
| ACCOUNTING |  3000.00 |
+------------+----------+
3 rows in set (0.00 sec)

mysql> select d.dname '部门',sum(e.sal) '工资合计',count(e.ename) '部门人数'from emp e,dept d where d.dname like "%S%" and (e.deptno = d.deptno) group by e.deptno ;
+----------+----------+----------+
| 部门     | 工资合计 | 部门人数 |
+----------+----------+----------+
| RESEARCH | 10875.00 |        5 |
| SALES    |  9400.00 |        6 |
+----------+----------+----------+
2 rows in set (0.00 sec)
#方式二

mysql> select d.deptno,d.dname,d.loc,count(e.ename),ifnull(sum(e.sal),0) as sumsal from emp e 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 |
+--------+----------+---------+----------------+----------+
2 rows in set (0.01 sec)

试题三十四：给任职日期超过 30 年的员工加薪 10%

#显示员工表
mysql> select * from emp;
+-------+--------+-----------+------+------------+---------+---------+--------+
| 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 |
|  7566 | JONES  | MANAGER   | 7839 | 1981-04-02 | 2975.00 |    NULL |     20 |
|  7654 | MARTIN | SALESMAN  | 7698 | 1981-09-28 | 1250.00 | 1400.00 |     30 |
|  7698 | BLAKE  | MANAGER   | 7839 | 1981-05-01 | 2850.00 |    NULL |     30 |
|  7782 | CLARK  | MANAGER   | 7839 | 1981-06-09 | 1000.00 |    NULL |     10 |
|  7788 | SCOTT  | ANALYST   | 7566 | 1987-04-19 | 3000.00 |    NULL |     20 |
|  7839 | KING   | PRESIDENT | NULL | 1981-11-17 | 1000.00 |    NULL |     10 |
|  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 |
|  7902 | FORD   | ANALYST   | 7566 | 1981-12-03 | 3000.00 |    NULL |     20 |
|  7934 | MILLER | CLERK     | 7782 | 1982-01-23 | 1000.00 |    NULL |     10 |
+-------+--------+-----------+------+------------+---------+---------+--------+
14 rows in set (0.00 sec)