MySQL学习2：练习题

SQL脚本代码

DROP TABLE IF EXISTS EMP;
DROP TABLE IF EXISTS DEPT;
DROP TABLE IF EXISTS SALGRADE;

CREATE TABLE DEPT
       (DEPTNO int(2) not null ,
    DNAME VARCHAR(14) ,
    LOC VARCHAR(13),
    primary key (DEPTNO)
    );
CREATE TABLE EMP
       (EMPNO int(4)  not null ,
    ENAME VARCHAR(10),
    JOB VARCHAR(9),
    MGR INT(4),
    HIREDATE DATE  DEFAULT NULL,
    SAL DOUBLE(7,2),
    COMM DOUBLE(7,2),
    primary key (EMPNO),
    DEPTNO INT(2) 
    )
    ;

CREATE TABLE SALGRADE
      ( GRADE INT,
    LOSAL INT,
    HISAL INT );




INSERT INTO DEPT ( DEPTNO, DNAME, LOC ) VALUES ( 
10, 'ACCOUNTING', 'NEW YORK'); 
INSERT INTO DEPT ( DEPTNO, DNAME, LOC ) VALUES ( 
20, 'RESEARCH', 'DALLAS'); 
INSERT INTO DEPT ( DEPTNO, DNAME, LOC ) VALUES ( 
30, 'SALES', 'CHICAGO'); 
INSERT INTO DEPT ( DEPTNO, DNAME, LOC ) VALUES ( 
40, 'OPERATIONS', 'BOSTON'); 
commit;
 
INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,
DEPTNO ) VALUES ( 
7369, 'SMITH', 'CLERK', 7902,  '1980-12-17'
, 800, NULL, 20); 
INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,
DEPTNO ) VALUES ( 
7499, 'ALLEN', 'SALESMAN', 7698,  '1981-02-20'
, 1600, 300, 30); 
INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,
DEPTNO ) VALUES ( 
7521, 'WARD', 'SALESMAN', 7698,  '1981-02-22'
, 1250, 500, 30); 
INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,
DEPTNO ) VALUES ( 
7566, 'JONES', 'MANAGER', 7839,  '1981-04-02'
, 2975, NULL, 20); 
INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,
DEPTNO ) VALUES ( 
7654, 'MARTIN', 'SALESMAN', 7698,  '1981-09-28'
, 1250, 1400, 30); 
INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,
DEPTNO ) VALUES ( 
7698, 'BLAKE', 'MANAGER', 7839,  '1981-05-01'
, 2850, NULL, 30); 
INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,
DEPTNO ) VALUES ( 
7782, 'CLARK', 'MANAGER', 7839,  '1981-06-09'
, 2450, NULL, 10); 
INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,
DEPTNO ) VALUES ( 
7788, 'SCOTT', 'ANALYST', 7566,  '1987-04-19'
, 3000, NULL, 20); 
INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,
DEPTNO ) VALUES ( 
7839, 'KING', 'PRESIDENT', NULL,  '1981-11-17'
, 5000, NULL, 10); 
INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,
DEPTNO ) VALUES ( 
7844, 'TURNER', 'SALESMAN', 7698,  '1981-09-08'
, 1500, 0, 30); 
INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,
DEPTNO ) VALUES ( 
7876, 'ADAMS', 'CLERK', 7788,  '1987-05-23'
, 1100, NULL, 20); 
INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,
DEPTNO ) VALUES ( 
7900, 'JAMES', 'CLERK', 7698,  '1981-12-03'
, 950, NULL, 30); 
INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,
DEPTNO ) VALUES ( 
7902, 'FORD', 'ANALYST', 7566,  '1981-12-03'
, 3000, NULL, 20); 
INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,
DEPTNO ) VALUES ( 
7934, 'MILLER', 'CLERK', 7782,  '1982-01-23'
, 1300, NULL, 10); 
commit;
 
INSERT INTO SALGRADE ( GRADE, LOSAL, HISAL ) VALUES ( 
1, 700, 1200); 
INSERT INTO SALGRADE ( GRADE, LOSAL, HISAL ) VALUES ( 
2, 1201, 1400); 
INSERT INTO SALGRADE ( GRADE, LOSAL, HISAL ) VALUES ( 
3, 1401, 2000); 
INSERT INTO SALGRADE ( GRADE, LOSAL, HISAL ) VALUES ( 
4, 2001, 3000); 
INSERT INTO SALGRADE ( GRADE, LOSAL, HISAL ) VALUES ( 
5, 3001, 9999); 
commit;
 

emp表.png

1、取得每个部门最高薪水的人员名称
第一步：取得每个部门最高薪水（按照部门编号分组，找出每一组最大值）
select deptno,max(sal) as maxsal from emp group by deptno;

第二步：将以上查询结果当作临时表t，t表和emp e表进行连接，条件是：t.deptno = e.deptno and t.maxsal = e.sal
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;

2、哪些人的薪水在部门的平均薪水之上
第一步：找出每个部门的平均薪水
select deptno,avg(sal) as avgsal from emp group by deptno;

第二步：将以上查询结果当作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.depton = t.deptno and e.sal > t.avgsal;

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;
第二步：基于以上的结果继续按照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;

4、不准用组函数（Max）取得最高薪水
第一种方案：sal降序，取limit 1
select ename,sal from emp order by sal desc limit 1;

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

第三种方案：表的自连接
第一步：自连接
select
distinct a.sal
from
emp a
join
emp b
on
a.sal < b.sal;

第二步：select sal from emp where sal not in(select distinct a.sal from emp a join emp b on a.sal < b.sal);

5、取得平均薪水的最高的部门的部门编号
第一种方案：降序取第一个。
第一步：找出每个部门的平均薪水
select deptno,avg(sal) as avgsal from emp group by deptno;
第二步：降序输出取第一个
select deptno,avg(sal) as avgsal from emp group by deptno order by avgsal desc limit 1;

第二种方案：max
第一步：找出每个部门的平均薪水。
select avg(sal) as avgsal from emp group by deptno;

第二步：找出以上结果中avgsal最大的值。
select max(t.avgsal) from (select avg(sal) as avgsal from emp group by deptno) t;

第三步：
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);

6、取得平均薪水最高的部门的部门名称
select
e.ename,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;

7、求平均薪水的等级最低的部门的部门名称
第一步：找出每个部门的平均薪水
select deptno,avg(sal) as avgsal from emp group by deptno;

第二步：找出每个部门的平均薪水的等级
以上结果作为临时表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
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);

抛开之前的，最低等级怎么找？
平均薪水最低的对应的等级一定是最低的。
select avg(sal) as avgsal from emp group by deptno order by avgsal asc limit 1;

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;

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

select distinct mgr from emp where mgr is not null;
员工编号没有在以上查询结果范围内的都是普通员工。

第一步：找出普通员工的最高薪水
注意：not in 在使用的时候，后面小括号中记得排除NULL。
select max(sal) from emp where empno not in(select distinct mgr from emp where mgr is not null);

第二步：找出高于以上结果的
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));

9、取得薪水最高的前五名员工
select ename,sal from emp order by sal desc limit 5;

10、取得薪水最高的第六到第十名员工
select ename,sal from emp order by sal desc limit 5,5;

11、取得最后入职的5名员工
日期也可以降序，升序。
select ename,hiredate from emp order by hiredate desc limit 5;

12、取得每个薪水等级有多少员工
分组count
第一步：找出每个员工的薪水等级
select e.ename,e.sal,s.grade from emp e join salgrade s on e.sal between s.losal and hisal;

第二步：继续按照grade分组统计数量
select
s.grade,count(*)
from
emp e
join
salgrade s
on
e.sal between s.losal and hisal
group by
s.grade;

14、列出所有员工及领导的姓名
select
a.ename '员工',b.ename '领导'
from
emp a
left join
emp b
on
a.mgr = b.empno;

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

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

select
a.ename '员工',a.hirdate,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;

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

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

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

select
deptno
from
emp
group by
deptno
having
count(*) >= 5;

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

select sal from emp where ename = 'SMITH';

select ename,sal from emp where sal > (select sal from emp where ename = 'SMITH');

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

//所有办事员名字
select ename,job from emp where job = 'CLERK';

//部门名称
select
e.ename,e.job ,d.dname,d.deptno
from
emp e
join
dept d
on
e.deptno = d.deptno
where
e.job = 'CLERK';

//每个部门的人数
select deptno,count(*) as deptcount from emp group by deptno;

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;

20、列出最低薪金大于1500的各种工作及从事此工作的全部雇员人数

按照工作岗位分组求最小值。
select job,count(*) from emp group by job having min(sal) > 1500;

21、列出在部门“SALES”<销售部>工作的员工的姓名，假定不知道销售部的部门编号
select deptno from dept where dname = 'SALES';
select ename from emp where deptno = (select deptno from dept where dname = 'SALES');

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);

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

select job from emp where ename = 'SCOTT';
select
e.name,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';

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

select distinct sal from emp where deptno = 30;
select
ename,sal
from
emp
where
sal in(select distinct sal from emp where deptno = 30)
and
deptno <> 30;

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

select max(sal) from emp where deptno = 30;
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);

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

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

select
d.*,count(e.ename) ecount,ifnull(avg(e.sal),0) as avgsal,ifnull(avg(timestampdiff(YEAR,hiredate,now()))) as avgservicetime
from
emp e
join
dept d
on
e.deptno = d.deptno
group by
d.deptno,d.dname,d.loc;

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

timestampdiff(YEAR,hiredate,now())

间隔类型：
SECOND 秒
MINUTE 分钟
HOUR 小时
DAY 天
WEEK 星期
MONTH 月
QUARTER 季度
YEAR 年

27、列出所有员工的姓名、部门名称和工资。
select
e,ename,d.dnameme.sal
from
emp e
join
dept d
on
e,deptno = d.deptno;

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

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

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

select
job,min(sal) as minsal
from
emp
group by
job;

拿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;

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

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

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

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

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

select
a.ename '员工',b.ename '领导'
from
emp a
join
emp b
on
a.mgr = b.empno
where
b.sal > 3000;

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

select
d.deptno,d.dname,d.loc,count(e.ename),sum(e.sal)
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

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

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