mysql的34道练习题

文章	评论	标签
151	391	65

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和emp表链接,条件: e.sal=t.maxsal and e.deptno=t.deptno;

mysql> select
    -> e.ename,t.*
    -> from
    -> emp e
    -> join
    -> (select deptno,max(sal) as maxsal from emp group by deptno) t
    -> on
    -> e.sal=t.maxsal and e.deptno=t.deptno;
+-------+--------+---------+
| ename | deptno | maxsal  |
+-------+--------+---------+
| BLAKE |     30 | 2850.00 |
| SCOTT |     20 | 3000.00 |
| KING  |     10 | 5000.00 |
| FORD  |     20 | 3000.00 |
+-------+--------+---------+

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

第一步:找出每个部门的平均薪水

mysql> select deptno,avg(sal) from emp group by deptno;
+--------+-------------+
| deptno | avg(sal)    |
+--------+-------------+
|     10 | 2916.666667 |
|     20 | 2175.000000 |
|     30 | 1566.666667 |
+--------+-------------+

第二步:将以上查询结果当作t表,t和emp表连接
条件：部门编号相同，并且emp的sal大于t表的avgsal

mysql> select e.ename,e.sal,t.*
    -> 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;
+-------+---------+--------+-------------+
| 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.取得部门(所有人的)平均的薪水等级

平均的薪水等级:先计算每个薪水的等级，然后找出薪水等级的平均值
平均薪水的等级:先计算平均薪水，然后找出每个平均薪水的等级值
第一步:找出每个人的薪水等级
emp e和salgrade s表连接
连接条件：e.sal between s.losal and s.hisal;

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

第二步:基于以上的结果继续按照deptno分组

mysql> select e.ename,e.sal,s.grade,e.deptno
    -> from
    -> emp e
    -> join
    -> salgrade s
    -> on
    -> e.sal between s.losal and s.hisal
    -> group by
    -> e.deptno;
+-------+---------+-------+--------+
| ename | sal     | grade | deptno |
+-------+---------+-------+--------+
| CLARK | 2450.00 |     4 |     10 |
| SMITH |  800.00 |     1 |     20 |
| ALLEN | 1600.00 |     3 |     30 |
+-------+---------+-------+--------+

4.不准使用组函数(max)，取得最高薪水

第一种：降序，limit 1

mysql> select ename,sal from emp order by sal desc limit 1;
+-------+---------+
| ename | sal     |
+-------+---------+
| KING  | 5000.00 |
+-------+---------+

第二种：select max(sal) from emp;

mysql> select max(sal) from emp;
+----------+
| max(sal) |
+----------+
|  5000.00 |
+----------+

第三种:表的自连接，distinct去重

mysql> select distinct a.sal
    -> from
    -> emp a
    -> join
    -> emp b
    -> on
    -> a.sal<b.sal;
    除了5000之外都可以查询出来
+---------+
| sal     |
+---------+
|  800.00 |
| 1250.00 |
| 1500.00 |
| 1100.00 |
|  950.00 |
| 1300.00 |
| 1600.00 |
| 2850.00 |
| 2450.00 |
| 2975.00 |
| 3000.00 |
+---------+

select sal from emp where sal not in(把上面的放进来);

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

第一步：找出每个部门的平均薪水

mysql> select deptno,avg(sal) as avgsal from emp group by deptno;
+--------+-------------+
| deptno | avgsal      |
+--------+-------------+
|     10 | 2916.666667 |
|     20 | 2175.000000 |
|     30 | 1566.666667 |
+--------+-------------+

第二步:降序选第一个

mysql> select deptno,avg(sal) as avgsal from emp group by deptno order by deptno limit 1;
+--------+-------------+
| deptno | avgsal      |
+--------+-------------+
|     10 | 2916.666667 |
+--------+-------------+

第二种：max

mysql> select avg(sal) as avgsal from emp group by deptno limit 1 ;
+-------------+
| avgsal      |
+-------------+
| 2916.666667 |
+-------------+

mysql> select max(t.avgsal) from (select avg(sal) as avgsal from emp group by deptno) t;
+---------------+
| max(t.avgsal) |
+---------------+
|   2916.666667 |
+---------------+

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

mysql> 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.求平均薪水的等级最低的部门的部门名称

第一步:找出每个部门的平均薪水的等级

mysql> select deptno,avg(sal) as avgsal from emp group by deptno;
+--------+-------------+
| deptno | avgsal      |
+--------+-------------+
|     10 | 2916.666667 |
|     20 | 2175.000000 |
|     30 | 1566.666667 |
+--------+-------------+

第二步:找出每个部门的平均薪水的等级

mysql> 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.deptno) 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 |
+-------+-------------+-------+

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

mysql> select distinct mgr from emp where mgr is not null;
+------+
| mgr  |
+------+
| 7902 |
| 7698 |
| 7839 |
| 7566 |
| 7788 |
| 7782 |
+------+

员工编号没有在以上范围内的，都是普通员工。
第一步：找出普通员工的最高薪水；
not in在使用的时候，后面小括号记得要排除null

mysql> select max(sal) from emp where empno not in(select distinct mgr from emp where mgr is not null);
+----------+
| max(sal) |
+----------+
|  1600.00 |
+----------+

第二步:找出高于1600

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     |
+-------+---------+
| JONES | 2975.00 |
| BLAKE | 2850.00 |
| CLARK | 2450.00 |
| SCOTT | 3000.00 |
| KING  | 5000.00 |
| FORD  | 3000.00 |
+-------+---------+

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

mysql> 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.取得薪水最高的第六名到第十名员工

mysql> 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名员工

mysql> 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

第一步:找出每个员工的薪水等级

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

第二步:分组计数

mysql> select s.grade,count(*)
    -> from
    -> emp e
    -> join
    -> salgrade s
    -> on
    -> e.sal between s.losal and s.hisal
    -> group by
    -> grade;
+-------+----------+
| grade | count(*) |
+-------+----------+
|     1 |        3 |
|     2 |        3 |
|     3 |        2 |
|     4 |        5 |
|     5 |        1 |
+-------+----------+

13.没有题源

14.列出所有员工的以及领导的姓名

mysql> select
    -> a.ename,e.ename
    -> from
    -> emp a
    -> left join
    -> emp e
    -> on
    -> a.mgr=e.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 |
+--------+-------+

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

emp a员工表
emp b领导表
a.hiredate<b.hiredate and a.mgr=b.empno

mysql> select
    -> a.ename,a.hiredate,b.ename,b.hiredate
    -> from
    -> emp a
    -> join
    -> emp b
    -> on
    -> a.mgr=b.empno
    -> join
    -> dept d
    -> on
    -> a.deptno=d.deptno
    -> where
    -> a.hiredate<b.hiredate;
+-------+------------+-------+------------+
| ename | hiredate   | ename | hiredate   |
+-------+------------+-------+------------+
| SMITH | 1980-12-17 | FORD  | 1981-12-03 |
| ALLEN | 1981-02-20 | BLAKE | 1981-05-01 |
| WARD  | 1981-02-22 | BLAKE | 1981-05-01 |
| JONES | 1981-04-02 | KING  | 1981-11-17 |
| BLAKE | 1981-05-01 | KING  | 1981-11-17 |
| CLARK | 1981-06-09 | KING  | 1981-11-17 |
+-------+------------+-------+------------+

mysql的34道练习题

南香香