SQL练习题02
SQL练习题02
一.本次练习用到的三个表
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 | 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 |
+-------+--------+-----------+------+------------+---------+---------+--------+
14 rows in set (0.01 sec)
dept表
+--------+------------+----------+
| DEPTNO | DNAME | LOC |
+--------+------------+----------+
| 10 | ACCOUNTING | NEW YORK |
| 20 | RESEARCH | DALLAS |
| 30 | SALES | CHICAGO |
| 40 | OPERATIONS | BOSTON |
+--------+------------+----------+
4 rows in set (0.01 sec)
salgrade表
+-------+-------+-------+
| GRADE | LOSAL | HISAL |
+-------+-------+-------+
| 1 | 700 | 1200 |
| 2 | 1201 | 1400 |
| 3 | 1401 | 2000 |
| 4 | 2001 | 3000 |
| 5 | 3001 | 9999 |
+-------+-------+-------+
5 rows in set (0.01 sec)
二.练习题
9.取得比普通员工(员工代码没有在mgr字段上出现的)的最高薪水还要高的领导人姓名
第一步：找出所有领导人的员工编号
select distinct mgr
from emp
where mgr is not null;
结果：
+------+
| mgr |
+------+
| 7902 |
| 7698 |
| 7839 |
| 7566 |
| 7788 |
| 7782 |
+------+
6 rows in set (0.00 sec)
第二步：找出普通员工的最高薪资
不在第一步查询结果中的都是普通员工的员工编号
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)
第三步：找出高于普通员工最高薪资的领导人
在员工表中，比普通员工最高薪资还高的不可能还是普通员工
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 |
+-------+---------+
6 rows in set (0.01 sec)
10.取得薪水最高的前5名员工
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 |
+-------+---------+
5 rows in set (0.00 sec)
11.取得薪水最高的第6名到第10名员工
select ename,sal
from emp
order by sal desc
limit 5,5;
#limit 第6条记录的下标，长度
结果：
+--------+---------+
| ename | sal |
+--------+---------+
| CLARK | 2450.00 |
| ALLEN | 1600.00 |
| TURNER | 1500.00 |
| MILLER | 1300.00 |
| MARTIN | 1250.00 |
+--------+---------+
5 rows in set (0.00 sec)
12.取得最后入职的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 |
+--------+------------+
5 rows in set (0.00 sec)
13.取得每个薪水等级有多少名员工
第一步：取得所有员工的薪水等级：
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 |
+--------+---------+-------+
14 rows in set (0.00 sec)
第二步：接着把第一步的结果按薪资等级分组，统计每个等级的员工数量
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 |
+-------+----------+
5 rows in set (0.00 sec)
14.列出所有员工及其对应领导的名字
使用自连接
select a.ename as 'woker',b.ename as 'leader'
from emp a
left join emp b#左外连接，防止king不显示
on a.mgr = b.empno;
结果：
+--------+--------+
| woker | leader |
+--------+--------+
| 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)
15.列出受雇日期早于其直接上级的所有员工的编号、姓名及部门名称
emp a
emp b
a.mgr = b.empno
a.hiredate < b.hiradate
select a.empno,a.ename as 'worker',a.hiredate,b.ename as 'leader',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;
结果：
+-------+--------+------------+--------+------------+------------+
| empno | worker | hiredate | leader | hiredate | dname |
+-------+--------+------------+--------+------------+------------+
| 7782 | CLARK | 1981-06-09 | KING | 1981-11-17 | ACCOUNTING |
| 7369 | SMITH | 1980-12-17 | FORD | 1981-12-03 | RESEARCH |
| 7566 | JONES | 1981-04-02 | KING | 1981-11-17 | RESEARCH |
| 7499 | ALLEN | 1981-02-20 | BLAKE | 1981-05-01 | SALES |
| 7521 | WARD | 1981-02-22 | BLAKE | 1981-05-01 | SALES |
| 7698 | BLAKE | 1981-05-01 | KING | 1981-11-17 | SALES |
+-------+--------+------------+--------+------------+------------+
6 rows in set (0.00 sec)
16.列出部门名称和这些部门的员工信息，同时列出那些没有员工的部门
select e.empno,e.ename,e.deptno,d.dname
from emp e
right join dept d
on e.deptno = d.deptno;
结果：
+-------+--------+--------+------------+
| empno | ename | deptno | dname |
+-------+--------+--------+------------+
| 7782 | CLARK | 10 | ACCOUNTING |
| 7839 | KING | 10 | ACCOUNTING |
| 7934 | MILLER | 10 | ACCOUNTING |
| 7369 | SMITH | 20 | RESEARCH |
| 7566 | JONES | 20 | RESEARCH |
| 7788 | SCOTT | 20 | RESEARCH |
| 7876 | ADAMS | 20 | RESEARCH |
| 7902 | FORD | 20 | RESEARCH |
| 7499 | ALLEN | 30 | SALES |
| 7521 | WARD | 30 | SALES |
| 7654 | MARTIN | 30 | SALES |
| 7698 | BLAKE | 30 | SALES |
| 7844 | TURNER | 30 | SALES |
| 7900 | JAMES | 30 | SALES |
| NULL | NULL | NULL | OPERATIONS |
+-------+--------+--------+------------+
15 rows in set (0.00 sec)
17.列出至少有5个员工的部门
select deptno,count(*) as count
from emp
group by deptno
having count >= 5;
结果：
+--------+-------+
| deptno | count |
+--------+-------+
| 20 | 5 |
| 30 | 6 |
+--------+-------+
2 rows in set (0.00 sec)
18.列出工资比员工"SMITH"多的所有员工
第一步：找出员工"SMITH"的工资
select sal from emp where ename = 'SMITH';
结果：
+--------+
| sal |
+--------+
| 800.00 |
+--------+
1 row in set (0.00 sec)
第二步：找出比"SMITH"工资高的员工
select ename
from emp
where sal > (select sal from emp where ename = 'SMITH');
结果：
+--------+
| ename |
+--------+
| ALLEN |
| WARD |
| JONES |
| MARTIN |
| BLAKE |
| CLARK |
| SCOTT |
| KING |
| TURNER |
| ADAMS |
| JAMES |
| FORD |
| MILLER |
+--------+
13 rows in set (0.00 sec)
19.列出所有职位为"CLERK"的员工姓名及其部门名称，以及其部门的总人数
第一步：找出所有职位为"CLERK"的员工
select ename,deptno,job
from emp
where job = 'CLERK';
结果：
+--------+--------+-------+
| ename | deptno | job |
+--------+--------+-------+
| SMITH | 20 | CLERK |
| ADAMS | 20 | CLERK |
| JAMES | 30 | CLERK |
| MILLER | 10 | CLERK |
+--------+--------+-------+
4 rows in set (0.00 sec)
第二步：为第一步的结果添加部门名称
select t.*,d.dname
from (select ename,deptno,job from emp where job = 'CLERK') as t
join dept d
on t.deptno = d.deptno;
结果：
+--------+--------+-------+------------+
| ename | deptno | job | dname |
+--------+--------+-------+------------+
| MILLER | 10 | CLERK | ACCOUNTING |
| SMITH | 20 | CLERK | RESEARCH |
| ADAMS | 20 | CLERK | RESEARCH |
| JAMES | 30 | CLERK | SALES |
+--------+--------+-------+------------+
4 rows in set (0.00 sec)
第三步：查询各个部门的总人数
select deptno,count(*) as count
from emp
group by deptno;
结果：
+--------+-------+
| deptno | count |
+--------+-------+
| 10 | 3 |
| 20 | 5 |
| 30 | 6 |
+--------+-------+
3 rows in set (0.00 sec)
第四步：为第二步结果添加各个部门的总人数
select t.*,d.dname,c.count
from (select ename,deptno,job from emp where job = 'CLERK') as t
join dept d
on t.deptno = d.deptno
join (select deptno,count(*) as count from emp group by deptno) as c
on t.deptno = c.deptno;
结果：
+--------+--------+-------+------------+-------+
| ename | deptno | job | dname | count |
+--------+--------+-------+------------+-------+
| SMITH | 20 | CLERK | RESEARCH | 5 |
| ADAMS | 20 | CLERK | RESEARCH | 5 |
| JAMES | 30 | CLERK | SALES | 6 |
| MILLER | 10 | CLERK | ACCOUNTING | 3 |
+--------+--------+-------+------------+-------+
4 rows in set (0.01 sec)
20.列出最低工资大于1500的工作以及从事此工作的全部雇员人数
select job,count(*)
from emp
group by job
having min(sal) > 1500;
结果：
+-----------+----------+
| job | count(*) |
+-----------+----------+
| ANALYST | 2 |
| MANAGER | 3 |
| PRESIDENT | 1 |
+-----------+----------+
3 rows in set (0.00 sec)
21.列出在部门"SALES"中工作的员工姓名(假设不知到该部门的部门编号)
第一步：获取该部门的部门编号
select deptno
from dept
where dname = 'SALES';
结果：
+--------+
| deptno |
+--------+
| 30 |
+--------+
1 row in set (0.00 sec)
第二步：获取在部门编号为30的员工姓名
select ename
from emp
where deptno = (select deptno from dept where dname = 'SALES');
结果：
+--------+
| ename |
+--------+
| ALLEN |
| WARD |
| MARTIN |
| BLAKE |
| TURNER |
| JAMES |
+--------+
6 rows in set (0.00 sec)
22.列出薪水高于公司平均薪水的所有员工，要求显示员工所在部门、上级领导以及员工的工资等级
第一步：查询公司平均薪水
select avg(sal)
from emp;
结果：
+-------------+
| avg(sal) |
+-------------+
| 2073.214286 |
+-------------+
1 row in set (0.02 sec)
第二步：列举薪水高于公司平均薪水的所有员工
select ename
from emp
where sal > (select avg(sal) from emp);
结果：
+-------+
| ename |
+-------+
| JONES |
| BLAKE |
| CLARK |
| SCOTT |
| KING |
| FORD |
+-------+
6 rows in set (0.00 sec)
第三步：查询薪水高于公司平均薪水的所有员工所在部门、上级领导以及员工的工资等级
select e.ename as 'worker',l.ename as 'leader',d.dname,e.sal,s.grade
from emp e
left join emp l
on e.mgr = l.empno
join dept d
on e.deptno = d.deptno
join salgrade s
on e.sal between s.losal and s.hisal
where e.sal > (select avg(sal) from emp);
结果：
+--------+--------+------------+---------+-------+
| worker | leader | dname | sal | grade |
+--------+--------+------------+---------+-------+
| JONES | KING | RESEARCH | 2975.00 | 4 |
| BLAKE | KING | SALES | 2850.00 | 4 |
| CLARK | KING | ACCOUNTING | 2450.00 | 4 |
| SCOTT | JONES | RESEARCH | 3000.00 | 4 |
| KING | NULL | ACCOUNTING | 5000.00 | 5 |
| FORD | JONES | RESEARCH | 3000.00 | 4 |
+--------+--------+------------+---------+-------+
6 rows in set (0.00 sec)
23.列出与员工"SCOTT"从事相同工作的所有员工及部门名称
第一步：找出员工”SCOTT“所从事的工作
select job
c
where ename = 'SCOTT';
结果：
+---------+
| job |
+---------+
| ANALYST |
+---------+
1 row in set (0.01 sec)
第二步：查询除”SCOTT“外所有从事”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 |
+-------+---------+----------+
1 row in set (0.00 sec)
24.列出比30部门中所有员工的工资都高的员工，要求显示员工姓名、薪资和部门名称
第一步：找出30部门员工中工资最高的
select max(sal)
from emp
where deptno = 30;
结果：
+----------+
| max(sal) |
+----------+
| 2850.00 |
+----------+
1 row in set (0.02 sec)
第二步：列出工资比2850高的员工
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 |
+-------+---------+------------+
25.列出在每个部门工作的员工数量，平均工资和平均服务期限
第一步：列出每个部门的员工数量
需要列出所有部门，故使用外连接
select d.dname,count(e.ename) as wokers,ifnull(avg(e.sal),0) as avgsal
from emp e
right join dept d
on e.deptno = d.deptno
group by d.dname;
结果：
+------------+--------+-------------+
| dname | wokers | avgsal |
+------------+--------+-------------+
| ACCOUNTING | 3 | 2916.666667 |
| OPERATIONS | 0 | 0.000000 |
| RESEARCH | 5 | 2175.000000 |
| SALES | 6 | 1566.666667 |
+------------+--------+-------------+
第二步：显示平均服务期限
select d.dname,count(e.ename) as wokers,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.dname;
结果：
+------------+--------+-------------+----------------+
| dname | wokers | avgsal | avgservicetime |
+------------+--------+-------------+----------------+
| ACCOUNTING | 3 | 2916.666667 | 39.3333 |
| OPERATIONS | 0 | 0.000000 | 0.0000 |
| RESEARCH | 5 | 2175.000000 | 37.4000 |
| SALES | 6 | 1566.666667 | 39.5000 |
+------------+--------+-------------+----------------+
4 rows in set (0.00 sec)
在mysql中怎么计算两个日期的”年差“，差了多少年？
#TimeStampDiff(间隔日期类型，前一个日期，后一个日期)
timestampdiff(YEAR,hiradate,now())
间隔日期类型：
- 秒：second
- 分：minute
- 时：hour
- 天：day
- 星期：week
- 月：month
- 季度：quarter
- 年：year
26.列出所有部门的详细信息和人数
select d.deptno,d.dname,d.loc,count(*)
from emp e
right join dept d
on e.deptno = d.deptno
group by d.deptno,d.dname,d.loc;
结果：
+--------+------------+----------+----------+
| deptno | dname | loc | count(*) |
+--------+------------+----------+----------+
| 10 | ACCOUNTING | NEW YORK | 3 |
| 20 | RESEARCH | DALLAS | 5 |
| 30 | SALES | CHICAGO | 6 |
| 40 | OPERATIONS | BOSTON | 1 |
+--------+------------+----------+----------+
4 rows in set (0.00 sec)
27.列出各种工作的最低工资以及从事此工作的雇员姓名
第一步：找出各种工作的最低工资
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 |
+-----------+---------+
5 rows in set (0.00 sec)
第二步：找出从事各种工作中薪资最低的员工姓名
select e.ename,t.*
from emp e
join (select job,min(sal) as minsal from emp group by job) as t
on e.job = t.job and e.sal = t.minsal;
28.列出各个部门的中工作为”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 |
+--------+----------+
3 rows in set (0.00 sec)
29.列出所有员工的年薪，按年薪正序排列
select ename,(sal + ifnull(comm,0))*12 as 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 |
| FORD | 36000.00 |
| SCOTT | 36000.00 |
| KING | 60000.00 |
+--------+----------+
14 rows in set (0.01 sec)
30.查询所有员工中领导薪水超过3000的，显示其名称及其领导名称
select a.ename as 'worker',b.ename as 'leader'
from emp a
left join emp b
on a.mgr = b.empno
where b.sal > 3000;
结果：
+--------+--------+
| worker | leader |
+--------+--------+
| JONES | KING |
| BLAKE | KING |
| CLARK | KING |
+--------+--------+
3 rows in set (0.00 sec)
31.查询部门名称中，带”S“字符的部门，要求显示该部门员工的工资合计、部门人数
select d.dname,count(e.ename) as wokers,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.dname;
结果：
+------------+--------+----------+
| dname | wokers | sumsal |
+------------+--------+----------+
| OPERATIONS | 0 | 0.00 |
| RESEARCH | 5 | 10875.00 |
| SALES | 6 | 9400.00 |
+------------+--------+----------+
3 rows in set (0.00 sec)
32.给任职日期超过30年的员工加薪10%
update emp
set sal = sal * 0.9
where timestampdiff(YEAR,hiredate,now()) > 30;
结果：
+-------+--------+-----------+------+------------+---------+---------+--------+
| EMPNO | ENAME | JOB | MGR | HIREDATE | SAL | COMM | DEPTNO |
+-------+--------+-----------+------+------------+---------+---------+--------+
| 7369 | SMITH | CLERK | 7902 | 1980-12-17 | 880.00 | NULL | 20 |
| 7499 | ALLEN | SALESMAN | 7698 | 1981-02-20 | 1760.00 | 300.00 | 30 |
| 7521 | WARD | SALESMAN | 7698 | 1981-02-22 | 1375.00 | 500.00 | 30 |
| 7566 | JONES | MANAGER | 7839 | 1981-04-02 | 3272.50 | NULL | 20 |
| 7654 | MARTIN | SALESMAN | 7698 | 1981-09-28 | 1375.00 | 1400.00 | 30 |
| 7698 | BLAKE | MANAGER | 7839 | 1981-05-01 | 3135.00 | NULL | 30 |
| 7782 | CLARK | MANAGER | 7839 | 1981-06-09 | 2695.00 | NULL | 10 |
| 7788 | SCOTT | ANALYST | 7566 | 1987-04-19 | 3300.00 | NULL | 20 |
| 7839 | KING | PRESIDENT | NULL | 1981-11-17 | 5500.00 | NULL | 10 |
| 7844 | TURNER | SALESMAN | 7698 | 1981-09-08 | 1650.00 | 0.00 | 30 |
| 7876 | ADAMS | CLERK | 7788 | 1987-05-23 | 1210.00 | NULL | 20 |
| 7900 | JAMES | CLERK | 7698 | 1981-12-03 | 1045.00 | NULL | 30 |
| 7902 | FORD | ANALYST | 7566 | 1981-12-03 | 3300.00 | NULL | 20 |
| 7934 | MILLER | CLERK | 7782 | 1982-01-23 | 1430.00 | NULL | 10 |
+-------+--------+-----------+------+------------+---------+---------+--------+
14 rows in set (0.00 sec)
