MySQL（作业题）

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

第一步：取得每个部门最高薪水
select deptno,max(sal) as maxsal from emp group by deptno;
+--------+----------+
| deptno | max(sal) |
+--------+----------+
| 10 | 5000.00 |
| 20 | 3000.00 |
| 30 | 2850.00 |
+--------+----------+

第二步：将以上结果当作临时表t，t表和emp e表进行连接，条件是t.maxsal = e.sal 并且 t.deptno = e.deptno

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

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

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

2、以部门和薪资为条件表连接
select
e.ename,e.sal,t.*
from
emp e
join
(select deptno,avg(sal) as avgsal from emp group by deptno) t
on
e.sal > t.avgsal
and
e.deptno = t.deptno;

+-------+---------+--------+-------------+
| 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、取得部门中（所有人的）平均的薪资等级

1、取得所有人的薪资等级
select
e.deptno,e.ename,s.grade
from
emp e
join
salgrade s
on
e.sal
between
s.losal
and
s.hisal
order by
deptno;

+--------+--------+-------+
| deptno | ename | grade |
+--------+--------+-------+
| 10 | CLARK | 4 |
| 10 | MILLER | 2 |
| 10 | KING | 5 |

| 20 | SMITH | 1 |
| 20 | FORD | 4 |
| 20 | SCOTT | 4 |
| 20 | JONES | 4 |
| 20 | ADAMS | 1 |

| 30 | ALLEN | 3 |
| 30 | WARD | 2 |
| 30 | TURNER | 3 |
| 30 | MARTIN | 2 |
| 30 | BLAKE | 4 |
| 30 | JAMES | 1 |
+--------+--------+-------+

2、按照部门划分，取薪资等级平均值
select
e.deptno,avg(s.grade) as 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），取得最高薪水（给出两种解决方案）

第一种：降序 limit1

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

第二种：自连接

（1）选出除了最高薪资以外的所有薪资

select distinct e1.sal
from emp e1
join emp e2
on e1.sal < e2.sal;
+---------+
| sal |
+---------+
| 800.00 |
| 1250.00 |
| 1500.00 |
| 1100.00 |
| 950.00 |
| 1300.00 |
| 1600.00 |
| 2850.00 |
| 2450.00 |
| 2975.00 |
| 3000.00 |
+---------+
（2）选出最高薪资

select sal from emp where sal not in(select distinct e1.sal
from emp e1
join emp e2
on e1.sal < e2.sal);
+---------+
| sal |
+---------+
| 5000.00 |
+---------+

5、取得平均薪资最高的部门编号（给出两种解决方案）

第一种方案：降序取第一个
1、取得各部门平均薪资

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

2、排序取得最高编号
select
deptno,avg(sal) as avgsal
from
emp
group by
deptno
order by
avgsal desc
limit 1;
+--------+---------------+
| deptno | max(t.avgsal) |
+--------+---------------+
| 10 | 2916.666667 |
+--------+---------------+

第二种方案：max

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

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

1、取得每个部门的平均薪资

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

+--------+-------------+
| deptno | avg(sal) |
+--------+-------------+
| 10 | 2916.666667 |
| 20 | 2175.000000 |
| 30 | 1566.666667 |
+--------+-------------+

2、找出平均薪资最高的部门

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

3、表内连接找出部门名称
select d.dname,t.avgsal
from dept d
join (select deptno,avg(sal) as avgsal from emp group by deptno order by avgsal desc limit 1) t
on d.deptno = t.deptno;
+------------+-------------+
| dname | avgsal |
+------------+-------------+
| ACCOUNTING | 2916.666667 |
+------------+-------------+

7、求平均薪水的等级最低的部门名称（比较复杂）

1、找出每个部门的平均薪水
select d.dname,avg(e.sal)
from dept d
join emp e
on d.deptno = e.deptno
group by d.dname;

+------------+-------------+
| dname | avg(e.sal) |
+------------+-------------+
| ACCOUNTING | 2916.666667 |
| RESEARCH | 2175.000000 |
| SALES | 1566.666667 |
+------------+-------------+

2、内连接找出每个部门平均薪水对应的等级
select t.*,s.grade
from salgrade s
join (select d.dname,avg(e.sal) as avgsal from dept d join emp e on d.deptno = e.deptno group by d.dname) t
on t.avgsal between s.losal and s.hisal;

+------------+-------------+-------+
| dname | avgsal | grade |
+------------+-------------+-------+
| SALES | 1566.666667 | 3 |
| ACCOUNTING | 2916.666667 | 4 |
| RESEARCH | 2175.000000 | 4 |
+------------+-------------+-------+

3、找出平均薪资等级最低的部门
（1）取出平均薪资等级最低的等级
select grade from salgrade where (select avg(sal) avgsal from emp group by deptno order by avgsal asc limit 1) between losal and hisal;
+-------+
| grade |
+-------+
| 3 |
+-------+

（2）取出平均薪资等级为最低级别的部门名称
select t.*,s.grade
from salgrade s
join (select d.dname,avg(e.sal) as avgsal from dept d join emp e on d.deptno = e.deptno group by d.dname) t
on t.avgsal between s.losal and s.hisal
where s.grade = (select grade from salgrade where (select avg(sal) 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上出现的）的最高薪水还要高的领导人姓名

1、取得普通员工
select empno,ename,sal from emp where empno not in (select distinct mgr from emp where mgr is not null);
+-------+--------+---------+
| empno | ename | sal |
+-------+--------+---------+
| 7369 | SMITH | 800.00 |
| 7499 | ALLEN | 1600.00 |
| 7521 | WARD | 1250.00 |
| 7654 | MARTIN | 1250.00 |
| 7844 | TURNER | 1500.00 |
| 7876 | ADAMS | 1100.00 |
| 7900 | JAMES | 950.00 |
| 7934 | MILLER | 1300.00 |
+-------+--------+---------+

2、取普通员工的最高薪水
select max(sal) as maxsal from emp where empno not in (select distinct mgr from emp where mgr is not null);
+---------+
| maxsal |
+---------+
| 1600.00 |
+---------+

3、取薪资比普通员工的最高薪资高的员工
（比普通员工的最高薪资高的一定是领导，因为普通员工的薪资不会比所有普通员工的最高薪资更高）
select empno,ename,sal
from emp
where sal >(select max(sal) as maxsal from emp where empno not in (select distinct mgr from emp where mgr is not null));
+-------+-------+---------+
| empno | ename | sal |
+-------+-------+---------+
| 7566 | JONES | 2975.00 |
| 7698 | BLAKE | 2850.00 |
| 7782 | CLARK | 2450.00 |
| 7788 | SCOTT | 3000.00 |
| 7839 | KING | 5000.00 |
| 7902 | FORD | 3000.00 |
+-------+-------+---------+

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

select sal,ename
from emp
order by sal desc
limit 5;
+---------+-------+
| sal | ename |
+---------+-------+
| 5000.00 | KING |
| 3000.00 | SCOTT |
| 3000.00 | FORD |
| 2975.00 | JONES |
| 2850.00 | BLAKE |
+---------+-------+

10、取得薪水最高的第六到第十名员工

select sal,ename
from emp
order by sal desc
limit 5,5;
+---------+--------+
| sal | ename |
+---------+--------+
| 2450.00 | CLARK |
| 1600.00 | ALLEN |
| 1500.00 | TURNER |
| 1300.00 | MILLER |
| 1250.00 | MARTIN |
+---------+--------+

11、取得最后入职的5名员工

select *
from emp
order by HIREDATE desc
limit 5;

+-------+--------+---------+------+------------+---------+------+--------+
| EMPNO | ENAME | JOB | MGR | HIREDATE | SAL | COMM | DEPTNO |
+-------+--------+---------+------+------------+---------+------+--------+
| 7876 | ADAMS | CLERK | 7788 | 1987-05-23 | 1100.00 | NULL | 20 |
| 7788 | SCOTT | ANALYST | 7566 | 1987-04-19 | 3000.00 | NULL | 20 |
| 7934 | MILLER | CLERK | 7782 | 1982-01-23 | 1300.00 | NULL | 10 |
| 7902 | FORD | ANALYST | 7566 | 1981-12-03 | 3000.00 | NULL | 20 |
| 7900 | JAMES | CLERK | 7698 | 1981-12-03 | 950.00 | NULL | 30 |
+-------+--------+---------+------+------------+---------+------+--------+

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

select count(*),s.grade
from emp e
join salgrade s
on e.sal between s.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门以上（含2门）不及格学生姓名及平均成绩。
3、即学过1号课程又学过2号课所有学生的姓名。

1、select s.sname
from S s
join SC sc
on s.sno = c.sno
where sc.cno not in (select CNO
from C
where CTEACHER = '黎明');

2、3、懒得写。。。

14、列出所有员工以及领导的名字

select e1.ename as '员工',e2.ename as '领导'
from emp e1
left join emp e2
on e1.mgr = e2.empno;

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

select e1.empno,e1.ename,d.dname
from emp e1
join emp e2
on e1.mgr = e2.empno
join dept d
on e1.deptno = d.deptno
where e1.HIREDATE < e2.HIREDATE;

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

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

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

mysql> select deptno
-> from emp
-> group by deptno
-> having count(*) > 5;
+--------+
| deptno |
+--------+
| 30 |
+--------+

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

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

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

1、查出所有CLERK的姓名以及其部门名称
select e.ename,e.job,d.dname,d.deptno
from emp e
join dept d
on e.deptno = d.deptno
where job = 'CLERK';

2、查出每个部门的人数部门的人数
select deptno,count(*) as count from emp group by deptno;
+--------+----------+
| deptno | count |
+--------+----------+
| 10 | 3 |
| 20 | 5 |
| 30 | 6 |
+--------+----------+

3、表连接
select e.ename,e.job,d.dname,d.deptno,t.count
from emp e
join dept d
on e.deptno = d.deptno
join (select deptno,count(*) as count from emp group by deptno) t
on d.deptno = t.deptno
where job = 'CLERK';

+--------+-------+------------+--------+-------+
| ename | job | dname | deptno | count |
+--------+-------+------------+--------+-------+
| SMITH | CLERK | RESEARCH | 20 | 5 |
| ADAMS | CLERK | RESEARCH | 20 | 5 |
| JAMES | CLERK | SALES | 30 | 6 |
| MILLER | CLERK | ACCOUNTING | 10 | 3 |
+--------+-------+------------+--------+-------+

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

select min(sal),job,count()
from emp
group by job
having min(sal) > 1500;
+----------+-----------+----------+
| min(sal) | job | count() |
+----------+-----------+----------+
| 3000.00 | ANALYST | 2 |
| 2450.00 | MANAGER | 3 |
| 5000.00 | PRESIDENT | 1 |
+----------+-----------+----------+

21、列出在部门“SALES”销售部工作的员工的姓名，假定不知道销售部的部门编号

select ename,deptno
from emp
where deptno = (select deptno
from dept
where dname = 'SALES');
+--------+--------+
| ename | deptno |
+--------+--------+
| ALLEN | 30 |
| WARD | 30 |
| MARTIN | 30 |
| BLAKE | 30 |
| TURNER | 30 |
| JAMES | 30 |
+--------+--------+

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

select e.ename,d.dname,e1.ename as '上级领导',s.grade
from emp e
join dept d
on e.deptno = d.deptno
left join emp e1
on e.mgr = e1.empno
join salgrade s
on e.sal between s.losal and s.hisal
where e.sal > (select avg(sal) from emp);

注意：查询所有员工，代表着即使没有上级领导，也要把员工查出来。

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

select e.ename,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的员工的薪资的其它员工的姓名和薪资

mysql> select ename,sal
-> from emp
-> where sal in (select sal
-> from emp
-> where deptno = 30)
-> and deptno <> 30;
Empty set (0.00 sec)

25、列出薪资高于在部门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);

+-------+---------+------------+
| ename | sal | dname |
+-------+---------+------------+
| KING | 5000.00 | ACCOUNTING |
| JONES | 2975.00 | RESEARCH |
| SCOTT | 3000.00 | RESEARCH |
| FORD | 3000.00 | RESEARCH |
+-------+---------+------------+

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

（1）在mysql中计算两个时间的年差，有固定的函数：TimeStampDiff()

TimeStampDiff()的使用的语法是,TimeStampDiff(间隔类型，前一个日期，后一个日期)；
其中，间隔类型有FRAC_SECOND 表示间隔是毫秒，SECOND 秒，MINUTE 分钟，HOUR 小时，DAY 天，WEEK 星期
MONTH 月，QUARTER 季度，YEAR 年；

（2）获取系统当前时间：now()

select count(e.ename),d.deptno,ifnull(avg(e.sal),0),ifnull(avg(timestampdiff(YEAR,HIREDATE,now())),0)
from emp e
right join dept d
on e.deptno = d.deptno
group by d.deptno;

+----------------+--------+----------------------+---------------------------------------------------+
| count(e.ename) | deptno | ifnull(avg(e.sal),0) | ifnull(avg(timestampdiff(YEAR,HIREDATE,now())),0) |
+----------------+--------+----------------------+---------------------------------------------------+
| 3 | 10 | 2916.666667 | 38.3333 |
| 5 | 20 | 2175.000000 | 36.4000 |
| 6 | 30 | 1566.666667 | 38.5000 |
| 0 | 40 | 0.000000 | 0.0000 |
+----------------+--------+----------------------+---------------------------------------------------+

(虽然聚合函数会忽略null，但是如果全部为null，那么它就是null，因此使用了ifnull)

27、列出所有员工的姓名、部门名称和工资

select e.ename,d.dname,e.sal
from emp e
join dept d
on e.deptno = d.deptno;

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

select d.,ifnull(t.count,0)
from dept d
left join (select count() as count ,deptno from emp group by deptno) t
on d.deptno = t.deptno;

+--------+------------+----------+-------------------+
| DEPTNO | DNAME | LOC | ifnull(t.count,0) |
+--------+------------+----------+-------------------+
| 10 | ACCOUNTING | NEW YORK | 3 |
| 20 | RESEARCH | DALLAS | 5 |
| 30 | SALES | CHICAGO | 6 |
| 40 | OPERATIONS | BOSTON | 0 |
+--------+------------+----------+-------------------+

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

select e.ename,e.job,t.minsal
from emp e
join (select min(sal) as minsal,job
from emp
group by job) t
where e.job = t.job;

30、列出各个部门的MANAGER的最低薪资

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

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

select ename,(sal*12 + ifnull(comm,0)) as '年薪'
from emp
order by '年薪';

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

select e1.ename,e2.ename as '领导'
from emp e1
join emp e2
on e1.mgr = e2.empno
where e2.sal > 3000;

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

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

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

update emp
set sal = sal + 0.1 * sal
where timestampdiff(YEAR,HIREDATE,now()) > 30;