MYSQL——27道练习题

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

第一步：取得每个部门最高薪水(按照部门编号分组找出每一个部门最大值)

select 
    deptno,max(sal) 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.sal)

select 
    e.ename,t.* 
from 
    emp e 
join 
    (select deptno,max(sal) maxsal from emp group by deptno) t 
on 
    t.maxsal=e.sal and t.deptno=e.deptno;

+——-+——–+———+
| ename | deptno | maxsal |
+——-+——–+———+
| BLAKE | 30 | 2850.00 |
| SCOTT | 20 | 3000.00 |
| KING | 10 | 5000.00 |
| FORD | 20 | 3000.00 |
+——-+——–+———+

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

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

select 
    deptno,avg(sal) 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 
    e.ename,t.deptno,e.sal
from 
    emp e 
join
    (select deptno,avg(sal) avgsal from emp group by deptno) t
on
    e.sal>t.avgsal and e.deptno=t.deptno;

+——-+——–+———+
| ename | deptno | sal |
+——-+——–+———+
| ALLEN | 30 | 1600.00 |
| JONES | 20 | 2975.00 |
| BLAKE | 30 | 2850.00 |
| SCOTT | 20 | 3000.00 |
| KING | 10 | 5000.00 |
| FORD | 20 | 3000.00 |
+——-+——–+———+

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

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

第一步：找出每个人的薪水等级

select
    e.ename,e.sal,e.deptno,s.grade
from
    emp e
join
    salgrade s
on
    e.sal between s.losal and s.hisal
order by
    deptno;

+——–+———+——–+——-+
| ename | sal | deptno | grade |
+——–+———+——–+——-+
| CLARK | 2450.00 | 10 | 4 |
| KING | 5000.00 | 10 | 5 |
| MILLER | 1300.00 | 10 | 2 |
| ADAMS | 1100.00 | 20 | 1 |
| SCOTT | 3000.00 | 20 | 4 |
| FORD | 3000.00 | 20 | 4 |
| JONES | 2975.00 | 20 | 4 |
| SMITH | 800.00 | 20 | 1 |
| ALLEN | 1600.00 | 30 | 3 |
| JAMES | 950.00 | 30 | 1 |
| WARD | 1250.00 | 30 | 2 |
| MARTIN | 1250.00 | 30 | 2 |
| TURNER | 1500.00 | 30 | 3 |
| BLAKE | 2850.00 | 30 | 4 |
+——–+———+——–+——-+

基于以上的结果继续按照deptno分组，求grade的平均值

select
    e.deptno,avg(s.grade) avggrade
from
    emp e
join
    salgrade s
on
    e.sal between s.losal and s.hisal
group by
    e.deptno;

+——–+———-+
| deptno | avggrade |
+——–+———-+
| 10 | 3.6667 |
| 20 | 2.8000 |
| 30 | 2.5000 |
+——–+———-+

4、不准用组函数（MAX），取得最高薪水

第一种，降序，limit 1

select ename,sal from emp order by sal desc limit 1;

+——-+———+
| ename | sal |
+——-+———+
| KING | 5000.00 |
+——-+———+

第二种，表的自连接

select sal from emp where sal not in(select distinct a.sal from (select sal from emp) a join (select sal from emp) b on a.sal<b.sal);
+———+
| sal |
+———+
| 5000.00 |
+———+
select distinct a.sal from (select sal from emp) a join (select sal from emp) b on a.sal<b.sal;
a表
+———+
| 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表
+———+
| 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、取得平均薪水最高的部门的部门编号

第一种方案 //max
第一步：取得每个部门的平均薪水

select deptno,avg(sal) avgsal from emp group by deptno;

+——–+————-+
| deptno | avgsal |
+——–+————-+
| 10 | 2916.666667 |
| 20 | 2175.000000 |
| 30 | 1566.666667 |
+——–+————-+
第二步把上表当做临时表，然后用max进行求最大值的部门编号
select t.deptno,max(t.avgsal) maxavgsal from (select deptno,avg(sal) avgsal from emp group by deptno) t;
+——–+————-+
| deptno | maxavgsal |
+——–+————-+
| 10 | 2916.666667 |
+——–+————-+

第二个方案 //降序取第一个
第一步：取得每个部门的平均薪水
第二步：将上表当做临时表，通过avgsal进行降序排序，然后limit 1取第一行

select deptno,avg(sal) avgsal from emp group by deptno order by avgsal desc limit 1;

+——–+————-+
| deptno | avgsal |
+——–+————-+
| 10 | 2916.666667 |
+——–+————-+

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

第一步，取得平均薪水最高的部门

select avg(sal) 平均薪水, deptno from emp group by deptno order by 平均薪水 desc limit 1;

+————-+——–+
| 平均薪水 | deptno |
+————-+——–+
| 2916.666667 | 10 |
+————-+——–+

第二步，取得最高的部门名称

select d.dname,t.平均薪水 from dept d join (select avg(sal) 平均薪水, deptno from emp group by deptno order by 平均薪水 desc limit 1) t on d.deptno=t.deptno;

+————+————-+
| dname | 平均薪水 |
+————+————-+
| ACCOUNTING | 2916.666667 |
+————+————-+

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

第一步，求平均薪水的等级最低的部门

select t.*,s.grade from salgrade s join (select avg(sal) avgsal, deptno from emp group by deptno) t on t.avgsal between losal and hisal order by grade asc limit 1;

+————-+——–+——-+
| avgsal | deptno | grade |
+————-+——–+——-+
| 1566.666667 | 30 | 3 |
+————-+——–+——-+

第二步，找出部门名称
select d.dname, p.grade from dept d join(select t.*,s.grade from salgrade s join (select avg(sal) avgsal, deptno from emp group by deptno) t on t.avgsal between losal and hisal order by grade asc limit 1) p on d.deptno = p.deptno;
+——-+——-+
| dname | grade |
+——-+——-+
| SALES | 3 |
+——-+——-+

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

领导
mysql> select distinct mgr from emp;
+——+
| mgr |
+——+
| 7902 |
| 7698 |
| 7839 |
| 7566 |
| NULL |
| 7788 |
| 7782 |
+——+

第一步，求出普通员工的最高薪水

select max(sal) from emp where empno not in(select distinct mgr from emp where mgr is not NULL);

+———-+
| max(sal) |
+———-+
| 1600.00 |
+———-+

第二步，求出比普通员工最高薪水还高的领导
比普通员工薪水最高薪水还高的就是领导
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 0,5;

+——-+———+
| ename | sal |
+——-+———+
| KING | 5000.00 |
| FORD | 3000.00 |
| SCOTT | 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 |
| WARD | 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 |
| JAMES | 1981-12-03 |
| FORD | 1981-12-03 |
+——–+————+

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

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

select e.ename, s.grade from emp e join salgrade s on e.sal between losal and hisal;

+——–+——-+
| ename | grade |
+——–+——-+
| SMITH | 1 |
| ALLEN | 3 |
| WARD | 2 |
| JONES | 4 |
| MARTIN | 2 |
| BLAKE | 4 |
| CLARK | 4 |
| SCOTT | 4 |
| KING | 5 |
| TURNER | 3 |
| ADAMS | 1 |
| JAMES | 1 |
| FORD | 4 |
| MILLER | 2 |
+——–+——-+

第二步：继续按照grade分组统计数量

select count(*), s.grade from emp e join salgrade s on e.sal between losal and hisal group by s.grade;

+———-+——-+
| count(*) | grade |
+———-+——-+
| 3 | 1 |
| 3 | 2 |
| 2 | 3 |
| 5 | 4 |
| 1 | 5 |
+———-+——-+

13、面试题

有3给表 S（学生表）,C（课程表），SC（学生选课表）S（SNO，SNAME）代号（学号，姓名）
C（CNO，CNAME，CTEACHER）代表（课号，课名，教师）
SC（SNO，CNO，SCGRADE）代表（学号，课号，成绩）
问题：
1、找出没选过“黎明”老师的所有学生姓名。
2、列出2门以上（含两门）不及格学生姓名及平均成绩。
3、即学过1号课程又学过2号课所有学生的姓名。
请用标准SQL语言写出答案，方言也行（请说明是使用说明方言）。

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、列出受雇日期早于其直接上级的所有员工的编号，姓名，部门名称

第一步，求出员工与其上级员工

select a.ename,b.ename from emp a join emp b on a.mgr = b.empno;

+——–+——-+
| ename | ename |
+——–+——-+
| SMITH | FORD |
| ALLEN | BLAKE |
| WARD | BLAKE |
| JONES | KING |
| MARTIN | BLAKE |
| BLAKE | KING |
| CLARK | KING |
| SCOTT | JONES |
| TURNER | BLAKE |
| ADAMS | SCOTT |
| JAMES | BLAKE |
| FORD | JONES |
| MILLER | CLARK |
+——–+——-+

第二步，对比a,b两个表hiredate的大小，找出受雇日期早于其领导的人员

select a.ename from emp a join emp b on a.mgr = b.empno where a.hiredate<b.hiredate;

+——-+
| ename |
+——-+
| SMITH |
| ALLEN |
| WARD |
| JONES |
| BLAKE |
| CLARK |
+——-+

第三步，利用自连接dept进行输出dname

select a.ename,a.empno,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;

+——-+——-+————+
| ename | empno | dname |
+——-+——-+————+
| SMITH | 7369 | RESEARCH |
| ALLEN | 7499 | SALES |
| WARD | 7521 | SALES |
| JONES | 7566 | RESEARCH |
| BLAKE | 7698 | SALES |
| CLARK | 7782 | ACCOUNTING |
+——-+——-+————+

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

select d.dname, e.* from emp e right join dept d on e.deptno = d.deptno;

+————+——-+——–+———–+——+————+———+———+——–+
| dname | EMPNO | ENAME | JOB | MGR | HIREDATE | SAL | COMM | DEPTNO |
+————+——-+——–+———–+——+————+———+———+——–+
| RESEARCH | 7369 | SMITH | CLERK | 7902 | 1980-12-17 | 800.00 | NULL | 20 |
| SALES | 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 |
| RESEARCH | 7566 | JONES | MANAGER | 7839 | 1981-04-02 | 2975.00 | NULL | 20 |
| 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 |
| ACCOUNTING | 7782 | CLARK | MANAGER | 7839 | 1981-06-09 | 2450.00 | NULL | 10 |
| RESEARCH | 7788 | SCOTT | ANALYST | 7566 | 1987-04-19 | 3000.00 | NULL | 20 |
| ACCOUNTING | 7839 | KING | PRESIDENT | NULL | 1981-11-17 | 5000.00 | NULL | 10 |
| SALES | 7844 | TURNER | SALESMAN | 7698 | 1981-09-08 | 1500.00 | 0.00 | 30 |
| RESEARCH | 7876 | ADAMS | CLERK | 7788 | 1987-05-23 | 1100.00 | NULL | 20 |
| SALES | 7900 | JAMES | CLERK | 7698 | 1981-12-03 | 950.00 | NULL | 30 |
| RESEARCH | 7902 | FORD | ANALYST | 7566 | 1981-12-03 | 3000.00 | NULL | 20 |
| ACCOUNTING | 7934 | MILLER | CLERK | 7782 | 1982-01-23 | 1300.00 | NULL | 10 |
| OPERATIONS | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL |
+————+——-+——–+———–+——+————+———+———+——–+

17、列出至少有5给员工的所有部门

mysql> select count(*),d.dname from emp e join dept d on e.deptno = d.deptno group by d.dname having count(*)>=5;

+———-+———-+
| count(*) | dname |
+———-+———-+
| 5 | RESEARCH |
| 6 | SALES |
+———-+———-+

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

select * from emp where sal>(select sal from emp where ename='smith');

+——-+——–+———–+——+————+———+———+——–+
| 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 | 2450.00 | NULL | 10 |
| 7788 | SCOTT | ANALYST | 7566 | 1987-04-19 | 3000.00 | NULL | 20 |
| 7839 | KING | PRESIDENT | NULL | 1981-11-17 | 5000.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 | 1300.00 | NULL | 10 |
+——-+——–+———–+——+————+———+———+——–+

19、列出所有“CLERK”（办事员）的姓名及其部门名称，部门的人数。

第一步，列出所有“CLERK”的人员

select e.ename,e.job,d.dname from emp e join dept d on d.deptno = e.deptno where e.job='CLERK';

+——–+——-+————+
| ename | job | dname |
+——–+——-+————+
| SMITH | CLERK | RESEARCH |
| ADAMS | CLERK | RESEARCH |
| JAMES | CLERK | SALES |
| MILLER | CLERK | ACCOUNTING |
+——–+——-+————+

第二步，找出每个部门的部门人数作为t2表，上表作为t1表进行内连接

select count(*), d.dname from emp e join dept d on e.deptno = d.deptno group by d.dname;

+———-+————+
| count(*) | dname |
+———-+————+
| 3 | ACCOUNTING |
| 5 | RESEARCH |
| 6 | SALES |
+———-+————+

第三步，自连接t1和t2表

select t1.ename,t1.dname,t2.count from (select e.ename,e.job,d.dname from emp e join dept d on d.deptno = e.deptno where e.job='CLERK') t1 join (select count(*) count, d.dname from emp e join dept d on e.deptno = d.deptno group by d.dname) t2 on t1.dname = t2.dname;

+——–+————+——-+
| ename | dname | count |
+——–+————+——-+
| SMITH | RESEARCH | 5 |
| ADAMS | RESEARCH | 5 |
| JAMES | SALES | 6 |
| MILLER | ACCOUNTING | 3 |
+——–+————+——-+

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 deptno from dept where dname='SALES';

+——–+
| deptno |
+——–+
| 30 |
+——–+
select ename from emp where deptno =(select deptno from dept where dname=’SALES’);
+——–+
| ename |
+——–+
| ALLEN |
| WARD |
| MARTIN |
| BLAKE |
| TURNER |
| JAMES |
+——–+

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

第一步，列出薪金高于公司平均薪资的所有员工

select e.* from emp e where sal>(select avg(sal) avgsal from emp);

+——-+——-+———–+——+————+———+——+——–+
| EMPNO | ENAME | JOB | MGR | HIREDATE | SAL | COMM | DEPTNO |
+——-+——-+———–+——+————+———+——+——–+
| 7566 | JONES | MANAGER | 7839 | 1981-04-02 | 2975.00 | NULL | 20 |
| 7698 | BLAKE | MANAGER | 7839 | 1981-05-01 | 2850.00 | NULL | 30 |
| 7782 | CLARK | MANAGER | 7839 | 1981-06-09 | 2450.00 | NULL | 10 |
| 7788 | SCOTT | ANALYST | 7566 | 1987-04-19 | 3000.00 | NULL | 20 |
| 7839 | KING | PRESIDENT | NULL | 1981-11-17 | 5000.00 | NULL | 10 |
| 7902 | FORD | ANALYST | 7566 | 1981-12-03 | 3000.00 | NULL | 20 |
+——-+——-+———–+——+————+———+——+——–+

第二步，列出所在部门，上级领导，薪资等级

select d.dname 部门,t2.ename 上级领导, t1.ename 员工姓名,s.grade from (select e.* from emp e where sal>(select avg(sal) avgsal from emp)) t1 left join dept d on t1.deptno = d.deptno left join (select e.* from emp e where sal>(select avg(sal) avgsal from emp)) t2 on t1.mgr=t2.empno left join salgrade s on t1.sal between losal and hisal;

+————+———-+———-+——-+
| 部门 | 上级领导 | 员工姓名 | grade |
+————+———-+———-+——-+
| ACCOUNTING | KING | CLARK | 4 |
| RESEARCH | KING | JONES | 4 |
| RESEARCH | JONES | SCOTT | 4 |
| RESEARCH | JONES | FORD | 4 |
| SALES | KING | BLAKE | 4 |
| ACCOUNTING | NULL | KING | 5 |
+————+———-+———-+——-+

23、列出各个部门的MANAGER（领导）最低薪金

select min(sal),deptno from emp where job='MANAGER' group by deptno;

+———-+——–+
| min(sal) | deptno |
+———-+——–+
| 2450.00 | 10 |
| 2975.00 | 20 |
| 2850.00 | 30 |
+———-+——–+

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

select ename, (sal+ifnull(comm,0))*12 yearsal from emp order by yearsal;

+——–+———-+
| 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 |
| SCOTT | 36000.00 |
| FORD | 36000.00 |
| KING | 60000.00 |
+——–+———-+

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

select a.ename, b.ename from emp a join emp b on a.mgr = b.empno where b.sal>3000;

+——-+——-+
| ename | ename |
+——-+——-+
| JONES | KING |
| BLAKE | KING |
| CLARK | KING |
+——-+——-+

26、求出部门名称中，带‘S’字符的部门员工的工资合计、部门人数

select d.dname, ifnull(sum(e.sal),' '),count(e.ename) from emp e right join (select * from dept where dname like '%s%') d on e.deptno = d.deptno group by d.dname;

+————+————————+—————-+
| DNAME | ifnull(sum(e.sal),’ ‘) | count(e.ename) |
+————+————————+—————-+
| OPERATIONS | | 0 |
| RESEARCH | 10875.00 | 5 |
| SALES | 9400.00 | 6 |
+————+————————+—————-+

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

update emp set sal = sal * 1.1 where timestampdiff(year,hiredate,now());

Query OK, 14 rows affected (0.02 sec)
Rows matched: 14 Changed: 14 Warnings: 0