一、取得每个部门最高薪水的人员名称
第一步：取得每个部门的最高薪水
select e.deptno, max(e.sal) as 'maxSal' from emp e group by e.deptno;
+--------+---------+
| deptno | maxSal |
+--------+---------+
| 20 | 3000.00 |
| 30 | 2850.00 |
| 10 | 5000.00 |
+--------+---------+
3 rows in set (0.00 sec)
第二步：将以上结果当做临时表t，t表和emp e表进行连接，条件是：t.deptno = e.deptno and t.maxSal = e.sal
select
e.ename, e.sal, d.dname, d.deptno
from
emp e
inner join
(select e.deptno, max(e.sal) as 'maxSal' from emp e group by e.deptno) t
on
(e.sal = t.maxSal) and (e.deptno = t.deptno)
inner join
dept d
on
e.deptno = d.deptno
order by
e.sal asc;
解法二：
select
e.ename, e.sal, d.dname, d.deptno
from
emp e
inner join
dept d
on
e.deptno = d.deptno
where
e.sal in (select max(e.sal) as 'maxSal' from emp e group by e.deptno)
order by
e.sal asc;
+-------+---------+------------+--------+
| ename | sal | dname | deptno |
+-------+---------+------------+--------+
| BLAKE | 2850.00 | SALES | 30 |
| SCOTT | 3000.00 | RESEARCH | 20 |
| FORD | 3000.00 | RESEARCH | 20 |
| KING | 5000.00 | ACCOUNTING | 10 |
+-------+---------+------------+--------+
4 rows in set (0.00 sec)
二、那些人的薪水在部门平均薪水之上
第一步：找出每个部门的平均薪水
select e.deptno, avg(e.sal) as 'avgSal' from emp e group by e.deptno;
+--------+-------------+
| deptno | avgSal |
+--------+-------------+
| 20 | 2175.000000 |
| 30 | 1566.666667 |
| 10 | 2916.666667 |
+--------+-------------+
第二步：将以上查询的结果当做t表，t和emp表连接
条件：部门编号相同，并且emp的sal大于t表的avgSal
select
e.ename as '员工名', e.sal as '员工薪资', d.dname as '部门名称', d.deptno as '部门编号', t.avgSal as '部门平均薪资'
from
emp e
inner join
(select e.deptno, avg(e.sal) as 'avgSal' from emp e group by e.deptno) as t
on
e.deptno = t.deptno
inner join
dept d
on
e.deptno = d.deptno
where
e.sal > t.avgSal
order by
d.dname asc;
+--------+----------+------------+----------+--------------+
| 员工名 | 员工薪资 | 部门名称 | 部门编号 | 部门平均薪资 |
+--------+----------+------------+----------+--------------+
| KING | 5000.00 | ACCOUNTING | 10 | 2916.666667 |
| JONES | 2975.00 | RESEARCH | 20 | 2175.000000 |
| SCOTT | 3000.00 | RESEARCH | 20 | 2175.000000 |
| FORD | 3000.00 | RESEARCH | 20 | 2175.000000 |
| ALLEN | 1600.00 | SALES | 30 | 1566.666667 |
| BLAKE | 2850.00 | SALES | 30 | 1566.666667 |
+--------+----------+------------+----------+--------------+
6 rows in set (0.00 sec)三、取得部门中（所有人的）平均的薪水等级
select
d.deptno as '部门编号', max(d.dname) as '部门名称', avg(s.grade) as '平均的薪资等级'
from
emp e
inner join
salgrade s
on
e.sal between s.losal and s.hisal
inner join
dept d
on
e.deptno = d.deptno
group by
e.deptno
order by
d.deptno asc;
+----------+------------+----------------+
| 部门编号 | 部门名称 | 平均的薪资等级 |
+----------+------------+----------------+
| 10 | ACCOUNTING | 3.6667 |
| 20 | RESEARCH | 2.8000 |
| 30 | SALES | 2.5000 |
+----------+------------+----------------+
3 rows in set (0.01 sec)四、不准用组函数（max），取得最高薪水（给出两种解决方案）
第一种方法：
select
e.ename, e.sal
from
emp e
order by
e.sal desc
limit
1;
+-------+---------+
| ename | sal |
+-------+---------+
| KING | 5000.00 |
+-------+---------+
1 row in set (0.00 sec)
第二种方法：表的自连接
第一步：只会筛选出除最大值以外的所有数据
select distinct a.sal from emp a inner join emp b on a.sal < b.sal;
+---------+
| sal |
+---------+
| 800.00 |
| 950.00 |
| 1100.00 |
| 1250.00 |
| 1300.00 |
| 1500.00 |
| 1600.00 |
| 2450.00 |
| 2850.00 |
| 2975.00 |
| 3000.00 |
+---------+
11 rows in set (0.00 sec)
第二步：采取子查询语法
select
e.ename, e.sal
from
emp e
where
e.sal not in (select distinct a.sal from emp a inner join emp b on a.sal < b.sal);
+-------+---------+
| ename | sal |
+-------+---------+
| KING | 5000.00 |
+-------+---------+
1 row in set (0.00 sec)
五、取得平均薪水最高的部门的部门编号及部门名称
第一种方案：使用limit取第一个
第一步：找出每个部门的平均薪水
select
e.deptno, avg(e.sal) as 'avgSal'
from
emp e
group by
e.deptno;
+--------+-------------+
| deptno | avgSal |
+--------+-------------+
| 20 | 2175.000000 |
| 30 | 1566.666667 |
| 10 | 2916.666667 |
+--------+-------------+
3 rows in set (0.00 sec)
第二步：降序选第一个
select
d.deptno as '部门编号', max(d.dname) as '部门名称', avg(e.sal) as '平均薪资'
from
emp e
inner join
dept d
on
e.deptno = d.deptno
group by
e.deptno
order by
平均薪资 desc
limit
1;
+----------+------------+-------------+
| 部门编号 | 部门名称 | 平均薪资 |
+----------+------------+-------------+
| 10 | ACCOUNTING | 2916.666667 |
+----------+------------+-------------+
1 row in set (0.00 sec)
第二种方案：使用max取最大值
第一步：找出每个部门中平均薪水的最大值
select max(t.avgSal) from (select avg(e.sal) as 'avgSal' from emp e group by e.deptno) as t;
+---------------+
| max(t.avgSal) |
+---------------+
| 2916.666667 |
+---------------+
select
e.deptno as '部门编号', max(d.dname) as '部门名称', avg(e.sal) as '平均薪资'
from
emp e
inner join
dept d
on
e.deptno = d.deptno
group by
e.deptno
having
avg(e.sal) = (select max(t.avgSal) from (select avg(e.sal) as 'avgSal' from emp e group by e.deptno) as t);
+----------+------------+-------------+
| 部门编号 | 部门名称 | 平均薪资 |
+----------+------------+-------------+
| 10 | ACCOUNTING | 2916.666667 |
+----------+------------+-------------+六、求平均薪水的等级最低的部门的部门名称
第一步：找出每个部门的平均薪水
select e.deptno, avg(e.sal) as 'avgSal' from emp e group by e.deptno;
+--------+-------------+
| deptno | avgSal |
+--------+-------------+
| 20 | 2175.000000 |
| 30 | 1566.666667 |
| 10 | 2916.666667 |
+--------+-------------+
3 rows in set (0.00 sec)
第二步：找出每个部门的平均薪水的等级
以上t表和salgrade表连接，条件：t.avgSal between s.losal and s.hisal
select
d.deptno, d.dname, t.avgSal, s.grade
from
dept d
inner join
(select e.deptno, avg(e.sal) as 'avgSal' from emp e group by e.deptno) as t
on
d.deptno = t.deptno
inner join
salgrade s
on
t.avgSal between s.losal and s.hisal
order by
s.grade asc;
+--------+------------+-------------+-------+
| deptno | dname | avgSal | grade |
+--------+------------+-------------+-------+
| 30 | SALES | 1566.666667 | 3 |
| 20 | RESEARCH | 2175.000000 | 4 |
| 10 | ACCOUNTING | 2916.666667 | 4 |
+--------+------------+-------------+-------+
3 rows in set (0.00 sec)
平均薪水最低的对应的等级一定是最低的
找出最低的平均薪水值
select avg(e.sal) as 'avgSal' from emp e group by e.deptno order by avgSal asc limit 1;
+-------------+
| avgSal |
+-------------+
| 1566.666667 |
+-------------+
1 row in set (0.00 sec)
找出最低平均薪水值对应的薪资等级
select s.grade from salgrade s where (select avg(e.sal) as 'avgSal' from emp e group by e.deptno order by avgSal asc limit 1) between s.losal and s.hisal;
+-------+
| grade |
+-------+
| 3 |
+-------+
1 row in set (0.01 sec)
在加where条件，最终为：
select
d.deptno, d.dname, t.avgSal, s.grade
from
dept d
inner join
(select e.deptno, avg(e.sal) as 'avgSal' from emp e group by e.deptno) as t
on
d.deptno = t.deptno
inner join
salgrade s
on
t.avgSal between s.losal and s.hisal
where
s.grade = (select s.grade from salgrade s where (select avg(e.sal) as 'avgSal' from emp e group by e.deptno order by avgSal asc limit 1) between s.losal and s.hisal)
order by
d.deptno;
+--------+-------+-------------+-------+
| deptno | dname | avgSal | grade |
+--------+-------+-------------+-------+
| 30 | SALES | 1566.666667 | 3 |
+--------+-------+-------------+-------+
1 row in set (0.00 sec)
七、取得比普通员工（员工代码没有在mgr字段上出现的）的最高薪水还要高的领导人姓名
第一步：找出领导的员工编号
select distinct e.mgr from emp e;
+------+
| mgr |
+------+
| 7902 |
| 7698 |
| 7839 |
| 7566 |
| NULL |
| 7788 |
| 7782 |
+------+
7 rows in set (0.00 sec)
员工编号没有在以上范围内的都是普通员工
not in在使用的时候，后面小括号中记得排除NULL
select distinct e.mgr from emp e where e.mgr is not null;
+------+
| mgr |
+------+
| 7902 |
| 7698 |
| 7839 |
| 7566 |
| 7788 |
| 7782 |
+------+
6 rows in set (0.00 sec)
第二步：找出普通员工的最高薪水
select max(e.sal) from emp e where e.empno not in (select distinct e.mgr from emp e where e.mgr is not null);
+------------+
| max(e.sal) |
+------------+
| 1600.00 |
+------------+
1 row in set (0.00 sec)
第三步：找出薪水大于1600的领导
select
e.empno, e.ename, e.sal
from
emp e
where
e.sal > (select max(e.sal) from emp e where e.empno not in (select distinct e.mgr from emp e where e.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 |
+-------+-------+---------+
6 rows in set (0.00 sec)
select
e.empno, e.ename, e.sal
from
emp e
where
e.sal > (select max(e.sal) from emp e where e.empno not in (select distinct ifnull(e.mgr, 0) from emp e));
+-------+-------+---------+
| 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 |
+-------+-------+---------+
6 rows in set (0.00 sec)八、取得薪水最高的前五名员工
select
e.empno, e.ename, e.sal
from
emp e
order by
e.sal desc
limit
5;
+-------+-------+---------+
| empno | ename | sal |
+-------+-------+---------+
| 7839 | KING | 5000.00 |
| 7788 | SCOTT | 3000.00 |
| 7902 | FORD | 3000.00 |
| 7566 | JONES | 2975.00 |
| 7698 | BLAKE | 2850.00 |
+-------+-------+---------+
5 rows in set (0.00 sec)九、取得薪水最高的第六到第十名员工
select
e.empno, e.ename, e.sal
from
emp e
order by
e.sal desc
limit
5, 5;
+-------+--------+---------+
| empno | ename | sal |
+-------+--------+---------+
| 7782 | CLARK | 2450.00 |
| 7499 | ALLEN | 1600.00 |
| 7844 | TURNER | 1500.00 |
| 7934 | MILLER | 1300.00 |
| 7521 | WARD | 1250.00 |
+-------+--------+---------+
5 rows in set (0.00 sec)十、取得最后入职的5名员工
select
e.ename, e.hiredate
from
emp e
order by
e.hiredate desc
limit
0, 5;
+--------+------------+
| ename | hiredate |
+--------+------------+
| ADAMS | 1987-05-23 |
| SCOTT | 1987-04-19 |
| MILLER | 1982-01-23 |
| JAMES | 1981-12-03 |
| FORD | 1981-12-03 |
+--------+------------+
5 rows in set (0.00 sec)十一、取得每个薪水等级有多少个员工
第一步：找出每个员工的薪资等级
select
e.ename, e.sal, s.grade
from
emp e
inner 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
inner join
salgrade s
on
e.sal between s.losal and s.hisal
group by
s.grade
order by
s.grade asc;
+-------+----------+
| grade | count(*) |
+-------+----------+
| 1 | 3 |
| 2 | 3 |
| 3 | 2 |
| 4 | 5 |
| 5 | 1 |
+-------+----------+
5 rows in set (0.00 sec)十二、列出所有员工及领导的名字
select
e.ename as '员工名', t.ename as '领导名'
from
emp e
left outer join
emp t
on
e.mgr = t.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 |
+--------+--------+
14 rows in set (0.01 sec)十三、列出受雇日期早于其直接上级的所有员工的编号，姓名，部门名称
emp a 员工表
emp b 领导表
连接条件：a.mgr = b.empno and a.hiredate < b.hiredate
select
a.empno, a.ename '员工', d.dname, a.hiredate, b.ename '领导', b.hiredate
from
emp a
left outer join
emp b
on
a.mgr = b.empno
inner join
dept d
on
a.deptno = d.deptno
where
a.hiredate < b.hiredate
order by
a.ename asc;
+-------+-------+------------+------------+-------+------------+
| empno | 员工 | dname | hiredate | 领导 | hiredate |
+-------+-------+------------+------------+-------+------------+
| 7499 | ALLEN | SALES | 1981-02-20 | BLAKE | 1981-05-01 |
| 7698 | BLAKE | SALES | 1981-05-01 | KING | 1981-11-17 |
| 7782 | CLARK | ACCOUNTING | 1981-06-09 | KING | 1981-11-17 |
| 7566 | JONES | RESEARCH | 1981-04-02 | KING | 1981-11-17 |
| 7369 | SMITH | RESEARCH | 1980-12-17 | FORD | 1981-12-03 |
| 7521 | WARD | SALES | 1981-02-22 | BLAKE | 1981-05-01 |
+-------+-------+------------+------------+-------+------------+
6 rows in set (0.00 sec)select e.ename as 'staff', t.ename as 'leader' from emp e left outer join emp t on e.mgr = t.empno order by staff;
+--------+--------+
| staff | leader |
+--------+--------+
| ADAMS | SCOTT |
| ALLEN | BLAKE |
| BLAKE | KING |
| CLARK | KING |
| FORD | JONES |
| JAMES | BLAKE |
| JONES | KING |
| KING | NULL |
| MARTIN | BLAKE |
| MILLER | CLARK |
| SCOTT | JONES |
| SMITH | FORD |
| TURNER | BLAKE |
| WARD | BLAKE |
+--------+--------+
14 rows in set (0.00 sec)
select
distinct e.empno, t.staff, d.dname
from
(select e.ename as 'staff', t.ename as 'leader' from emp e left outer join emp t on e.mgr = t.empno) t
inner join
emp e
on
(select e.hiredate from emp e where e.ename = t.staff) < (select e.hiredate from emp e where e.ename = t.leader) and t.staff = e.ename
inner join
dept d
on
e.deptno = d.deptno
order by
t.staff asc;
+-------+-------+------------+
| empno | staff | dname |
+-------+-------+------------+
| 7499 | ALLEN | SALES |
| 7698 | BLAKE | SALES |
| 7782 | CLARK | ACCOUNTING |
| 7566 | JONES | RESEARCH |
| 7369 | SMITH | RESEARCH |
| 7521 | WARD | SALES |
+-------+-------+------------+
6 rows in set (0.00 sec)十四、列出部门名称和这些部门的员工信息，同时列出那些没有员工的部门
select
d.dname, e.ename
from
dept d
left outer join
emp e
on
d.deptno = e.deptno;
+------------+--------+
| dname | ename |
+------------+--------+
| ACCOUNTING | MILLER |
| ACCOUNTING | KING |
| ACCOUNTING | CLARK |
| RESEARCH | FORD |
| RESEARCH | ADAMS |
| RESEARCH | SCOTT |
| RESEARCH | JONES |
| RESEARCH | SMITH |
| SALES | JAMES |
| SALES | TURNER |
| SALES | BLAKE |
| SALES | MARTIN |
| SALES | WARD |
| SALES | ALLEN |
| OPERATIONS | NULL |
+------------+--------+
15 rows in set (0.00 sec)十五、列出至少有5个员工的所有部门
select
max(d.deptno) as '部门编号', d.dname as '部门名称', count(e.ename) as '部门人数'
from
dept d
left outer join
emp e
on
d.deptno = e.deptno
group by
d.dname
having
count(e.ename) >= 5
order by
部门人数 asc;
+----------+----------+----------+
| 部门编号 | 部门名称 | 部门人数 |
+----------+----------+----------+
| 20 | RESEARCH | 5 |
| 30 | SALES | 6 |
+----------+----------+----------+
2 rows in set (0.00 sec)
十六、列出薪资比“SMITH”多的所有员工信息
select
e.ename, e.sal
from
emp e
where
e.sal > (select e.sal from emp e where e.ename = 'SMITH');
+--------+---------+
| ename | sal |
+--------+---------+
| ALLEN | 1600.00 |
| WARD | 1250.00 |
| JONES | 2975.00 |
| MARTIN | 1250.00 |
| BLAKE | 2850.00 |
| CLARK | 2450.00 |
| SCOTT | 3000.00 |
| KING | 5000.00 |
| TURNER | 1500.00 |
| ADAMS | 1100.00 |
| JAMES | 950.00 |
| FORD | 3000.00 |
| MILLER | 1300.00 |
+--------+---------+
13 rows in set (0.00 sec)十七、列出所有“clerk”（办事员）的姓名及其部门名称，部门的人数
第一步：先求出每个部门的人数
select d.dname, count(e.ename) from dept d left outer join emp e on d.deptno = e.deptno group by d.dname;
+------------+----------------+
| dname | count(e.ename) |
+------------+----------------+
| ACCOUNTING | 3 |
| RESEARCH | 5 |
| SALES | 6 |
| OPERATIONS | 0 |
+------------+----------------+
4 rows in set (0.00 sec)
第二步：进行内连接
select
e.ename, e.job, d.dname, t.num
from
emp e
inner join
dept d
on
e.deptno = d.deptno
inner join
(select d.dname, count(e.ename) as 'num' from dept d left outer join emp e on d.deptno = e.deptno group by d.dname) as t
on
d.dname = t.dname
where
e.job = 'CLERK';
+--------+-------+------------+-----+
| ename | job | dname | num |
+--------+-------+------------+-----+
| SMITH | CLERK | RESEARCH | 5 |
| ADAMS | CLERK | RESEARCH | 5 |
| JAMES | CLERK | SALES | 6 |
| MILLER | CLERK | ACCOUNTING | 3 |
+--------+-------+------------+-----+
4 rows in set (0.01 sec)
十八、列出最低薪资大于1500的各种工作及从事此工作的全部员工人数
第一种方法：
select
e.job, count(e.ename)
from
emp e
group by
e.job
having
min(e.sal) > 1500;
+-----------+----------------+
| job | count(e.ename) |
+-----------+----------------+
| MANAGER | 3 |
| ANALYST | 2 |
| PRESIDENT | 1 |
+-----------+----------------+
3 rows in set (0.00 sec)第二种方法：
第一步：找出最低薪资大于1500的岗位
select
e.job, min(e.sal) as 'minSal'
from
emp e
group by
e.job
having
min(e.sal) > 1500;
+-----------+---------+
| job | minSal |
+-----------+---------+
| MANAGER | 2450.00 |
| ANALYST | 3000.00 |
| PRESIDENT | 5000.00 |
+-----------+---------+
3 rows in set (0.00 sec)
第二步：进行子查询
select
e.job, count(*)
from
emp e
where
e.job in
(select
e.job
from
emp e
group by
e.job
having
min(e.sal) > 1500)
group by
e.job;
+-----------+----------+
| job | count(*) |
+-----------+----------+
| MANAGER | 3 |
| ANALYST | 2 |
| PRESIDENT | 1 |
+-----------+----------+
3 rows in set (0.00 sec)十九、列出在部门“SALES”（销售部）工作的员工的姓名，假定不知道销售部的部门编号
第一种方法：
select
e.ename, d.dname
from
emp e
inner join
dept d
on
e.deptno = d.deptno
where
d.dname = 'SALES';
+--------+-------+
| ename | dname |
+--------+-------+
| ALLEN | SALES |
| WARD | SALES |
| MARTIN | SALES |
| BLAKE | SALES |
| TURNER | SALES |
| JAMES | SALES |
+--------+-------+
6 rows in set (0.00 sec)第二种方法：
select
e.ename
from
emp e
where
e.deptno = (select d.deptno from dept d where d.dname = 'SALES');
+--------+
| ename |
+--------+
| ALLEN |
| WARD |
| MARTIN |
| BLAKE |
| TURNER |
| JAMES |
+--------+
6 rows in set (0.01 sec)二十、列出薪资高于公司平均薪水的所有员工，所在部门，上级领导，雇员的工资等级
第一步：求公司平均薪资
select
avg(e.sal)
from
emp e;
+-------------+
| avg(e.sal) |
+-------------+
| 2073.214286 |
+-------------+
1 row in set (0.01 sec)
select
e.ename as '员工名', d.dname as '部门名称', t.ename as '领导名', e.sal as '薪资', s.grade as '薪资等级'
from
emp e
inner join
dept d
on
e.deptno = d.deptno
left outer join
emp t
on
e.mgr = t.empno
inner join
salgrade s
on
e.sal between s.losal and s.hisal
where
e.sal >
(select
avg(e.sal)
from
emp e);
+--------+------------+--------+---------+----------+
| 员工名 | 部门名称 | 领导名 | 薪资 | 薪资等级 |
+--------+------------+--------+---------+----------+
| FORD | RESEARCH | JONES | 3000.00 | 4 |
| SCOTT | RESEARCH | JONES | 3000.00 | 4 |
| CLARK | ACCOUNTING | KING | 2450.00 | 4 |
| BLAKE | SALES | KING | 2850.00 | 4 |
| JONES | RESEARCH | KING | 2975.00 | 4 |
| KING | ACCOUNTING | NULL | 5000.00 | 5 |
+--------+------------+--------+---------+----------+
6 rows in set (0.00 sec)二十一、列出与“SCOTT”从事相同工作的所有员工及部门名称
select
e.job
from
emp e
where
e.ename = 'SCOTT';
+---------+
| job |
+---------+
| ANALYST |
+---------+
1 row in set (0.00 sec)
select
e.ename, e.job, d.dname
from
emp e
inner join
dept d
on
e.deptno = d.deptno
where
e.job = (
select
e.job
from
emp e
where
e.ename = 'SCOTT')
and
e.ename != 'SCOTT';
+-------+---------+----------+
| ename | job | dname |
+-------+---------+----------+
| FORD | ANALYST | RESEARCH |
+-------+---------+----------+
1 row in set (0.00 sec)二十二、列出薪资等于部门30中员工的薪资的其他员工的姓名和薪资
第一种写法：
select
*
from
emp e
where
e.deptno = 30;
+-------+--------+----------+------+------------+---------+---------+--------+
| 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 |
| 7654 | MARTIN | SALESMAN | 7698 | 1981-09-28 | 1250.00 | 1400.00 | 30 |
| 7698 | BLAKE | MANAGER | 7839 | 1981-05-01 | 2850.00 | NULL | 30 |
| 7844 | TURNER | SALESMAN | 7698 | 1981-09-08 | 1500.00 | 0.00 | 30 |
| 7900 | JAMES | CLERK | 7698 | 1981-12-03 | 950.00 | NULL | 30 |
+-------+--------+----------+------+------------+---------+---------+--------+
6 rows in set (0.00 sec)
select
*
from
emp e
where
e.sal in (select e.sal from emp e where e.deptno = 30)
and
e.ename not in (select e.ename from emp e where e.deptno = 30);
Empty set (0.02 sec)第二种写法：
select
e.ename, e.sal
from
emp e
where
e.sal in (select distinct e.sal from emp e where e.deptno = 30)
and
e.deptno != 30;
Empty set (0.00 sec)二十三、列出薪资高于在部门30工作的所有员工的薪资的员工姓名和薪资，部门名称
第一步：找出在30部门工作的所有员工的最高薪资
select
max(e.sal)
from
emp e
where
e.deptno = 30;
+------------+
| max(e.sal) |
+------------+
| 2850.00 |
+------------+
1 row in set (0.00 sec)
第二步：找出高于该薪资的员工和部门名称
select
e.ename as '姓名', d.dname as '部门名称'
from
emp e
inner join
dept d
on
e.deptno = d.deptno
where
e.sal > (
select
max(e.sal)
from
emp e
where
e.deptno = 30
);
+-------+------------+
| 姓名 | 部门名称 |
+-------+------------+
| JONES | RESEARCH |
| SCOTT | RESEARCH |
| FORD | RESEARCH |
| KING | ACCOUNTING |
+-------+------------+
4 rows in set (0.00 sec)二十四、列出在每个部门工作的员工数量，平均工资和平均服务期限
select
d.deptno, max(d.dname), count(e.ename), avg(e.sal)
from
dept d
left outer join
emp e
on
d.deptno = e.deptno
group by
d.deptno;
+--------+--------------+----------------+-------------+
| deptno | max(d.dname) | count(e.ename) | avg(e.sal) |
+--------+--------------+----------------+-------------+
| 10 | ACCOUNTING | 3 | 2916.666667 |
| 20 | RESEARCH | 5 | 2175.000000 |
| 30 | SALES | 6 | 1566.666667 |
| 40 | OPERATIONS | 0 | NULL |
+--------+--------------+----------------+-------------+
4 rows in set (0.00 sec)
select
d.deptno as '部门编号', d.dname as '部门名称', d.loc as '部门地址', count(e.ename) as '部门人数', ifnull(avg(e.sal), 0) as '平均薪资'
from
dept d
left outer join
emp e
on
d.deptno = e.deptno
group by
d.deptno, d.dname, d.loc;
+----------+------------+----------+----------+-------------+
| 部门编号 | 部门名称 | 部门地址 | 部门人数 | 平均薪资 |
+----------+------------+----------+----------+-------------+
| 10 | ACCOUNTING | NEW YORK | 3 | 2916.666667 |
| 20 | RESEARCH | DALLAS | 5 | 2175.000000 |
| 30 | SALES | CHICAGO | 6 | 1566.666667 |
| 40 | OPERATIONS | BOSTON | 0 | 0.000000 |
+----------+------------+----------+----------+-------------+
4 rows in set (0.00 sec)
select
d.deptno as '部门编号', d.dname as '部门名称', count(e.ename) as '部门员工数', ifnull(avg(e.sal), 0) as '平均薪资', ifnull(avg(timestampdiff(year, e.hiredate, now())), 0) as '平均服务期限'
from
dept d
left outer join
emp e
on
d.deptno = e.deptno
group by
d.deptno, d.dname;
+----------+------------+------------+-------------+--------------+
| 部门编号 | 部门名称 | 部门员工数 | 平均薪资 | 平均服务期限 |
+----------+------------+------------+-------------+--------------+
| 10 | ACCOUNTING | 3 | 2916.666667 | 41.3333 |
| 20 | RESEARCH | 5 | 2175.000000 | 39.4000 |
| 30 | SALES | 6 | 1566.666667 | 41.8333 |
| 40 | OPERATIONS | 0 | 0.000000 | 0.0000 |
+----------+------------+------------+-------------+--------------+
4 rows in set (0.00 sec)二十五、列出所有员工的姓名，部门名称和工资
select
e.ename, d.dname, e.sal
from
emp e
inner join
dept d
on
e.deptno = d.deptno;
+--------+------------+---------+
| ename | dname | sal |
+--------+------------+---------+
| SMITH | RESEARCH | 800.00 |
| ALLEN | SALES | 1600.00 |
| WARD | SALES | 1250.00 |
| JONES | RESEARCH | 2975.00 |
| MARTIN | SALES | 1250.00 |
| BLAKE | SALES | 2850.00 |
| CLARK | ACCOUNTING | 2450.00 |
| SCOTT | RESEARCH | 3000.00 |
| KING | ACCOUNTING | 5000.00 |
| TURNER | SALES | 1500.00 |
| ADAMS | RESEARCH | 1100.00 |
| JAMES | SALES | 950.00 |
| FORD | RESEARCH | 3000.00 |
| MILLER | ACCOUNTING | 1300.00 |
+--------+------------+---------+
14 rows in set (0.00 sec)二十六、列出所有部门的详细信息和人数
select
d.deptno '部门编号', d.dname '部门名称', d.loc '部门地址', count(e.ename) '部门人数'
from
dept d
left outer join
emp e
on
d.deptno = e.deptno
group by
d.deptno, d.dname, d.loc;
+----------+------------+----------+----------+
| 部门编号 | 部门名称 | 部门地址 | 部门人数 |
+----------+------------+----------+----------+
| 10 | ACCOUNTING | NEW YORK | 3 |
| 20 | RESEARCH | DALLAS | 5 |
| 30 | SALES | CHICAGO | 6 |
| 40 | OPERATIONS | BOSTON | 0 |
+----------+------------+----------+----------+
4 rows in set (0.00 sec)二十七、列出各种工作的最低工资及从事此工作的员工姓名
select
e.job, min(e.sal)
from
emp e
group by
e.job;
+-----------+------------+
| job | min(e.sal) |
+-----------+------------+
| CLERK | 800.00 |
| SALESMAN | 1250.00 |
| MANAGER | 2450.00 |
| ANALYST | 3000.00 |
| PRESIDENT | 5000.00 |
+-----------+------------+
5 rows in set (0.01 sec)
select
e.ename, t.*
from
emp e
inner join
(select
e.job, min(e.sal) as 'minSal'
from
emp e
group by
e.job) as t
on
e.job = t.job
and
e.sal = t.minSal;
+--------+-----------+---------+
| ename | job | minSal |
+--------+-----------+---------+
| SMITH | CLERK | 800.00 |
| WARD | SALESMAN | 1250.00 |
| MARTIN | SALESMAN | 1250.00 |
| CLARK | MANAGER | 2450.00 |
| SCOTT | ANALYST | 3000.00 |
| KING | PRESIDENT | 5000.00 |
| FORD | ANALYST | 3000.00 |
+--------+-----------+---------+
7 rows in set (0.01 sec)二十八、列出各个部门的“MANAGER”（领导）的最低薪资
select
e.deptno, e.ename, e.job, min(e.sal)
from
emp e
where
e.job = 'MANAGER'
group by
e.deptno, e.ename, e.job;
+--------+-------+---------+------------+
| deptno | ename | job | min(e.sal) |
+--------+-------+---------+------------+
| 20 | JONES | MANAGER | 2975.00 |
| 30 | BLAKE | MANAGER | 2850.00 |
| 10 | CLARK | MANAGER | 2450.00 |
+--------+-------+---------+------------+
3 rows in set (0.00 sec)二十九、列出所有员工的年工资，按年薪从低到高排序
select
e.ename, (e.sal + ifnull(e.comm, 0)) * 12 as 'yearsal'
from
emp e
order by
yearsal asc;
+--------+----------+
| 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 |
+--------+----------+
14 rows in set (0.00 sec)三十、求出员工领导的薪水超过3000的员工名称及领导名称
select
a.ename '员工名', a.sal '员工薪资', b.ename '领导名', b.sal '领导薪资'
from
emp a
left outer join
emp b
on
a.mgr = b.empno
where
b.sal > 3000;
+--------+----------+--------+----------+
| 员工名 | 员工薪资 | 领导名 | 领导薪资 |
+--------+----------+--------+----------+
| JONES | 2975.00 | KING | 5000.00 |
| BLAKE | 2850.00 | KING | 5000.00 |
| CLARK | 2450.00 | KING | 5000.00 |
+--------+----------+--------+----------+
3 rows in set (0.03 sec)三十一、求出部门名称中带“S”字符的部门员工的工资合计，部门人数
select
d.deptno '部门编号', d.dname '部门名称', ifnull(sum(e.sal), 0) '工资合计', count(e.ename) '部门人数'
from
dept d
left outer join
emp e
on
d.deptno = e.deptno
where
d.dname like '%S%'
group by
d.deptno, d.dname;
+----------+------------+----------+----------+
| 部门编号 | 部门名称 | 工资合计 | 部门人数 |
+----------+------------+----------+----------+
| 20 | RESEARCH | 10875.00 | 5 |
| 30 | SALES | 9400.00 | 6 |
| 40 | OPERATIONS | 0.00 | 0 |
+----------+------------+----------+----------+
3 rows in set (0.00 sec)三十二、给任职日期超过30年的员工加薪10%
select
e.ename '姓名', e.sal '当前薪资', timestampdiff(year, e.hiredate, now()) '任职年限', e.sal * 1.1 '调整后薪资'
from
emp e
where
timestampdiff(year, e.hiredate, now()) > 30;
+--------+----------+----------+------------+
| 姓名 | 当前薪资 | 任职年限 | 调整后薪资 |
+--------+----------+----------+------------+
| SMITH | 800.00 | 42 | 880.00 |
| ALLEN | 1600.00 | 42 | 1760.00 |
| WARD | 1250.00 | 42 | 1375.00 |
| JONES | 2975.00 | 42 | 3272.50 |
| MARTIN | 1250.00 | 42 | 1375.00 |
| BLAKE | 2850.00 | 42 | 3135.00 |
| CLARK | 2450.00 | 42 | 2695.00 |
| SCOTT | 3000.00 | 36 | 3300.00 |
| KING | 5000.00 | 41 | 5500.00 |
| TURNER | 1500.00 | 42 | 1650.00 |
| ADAMS | 1100.00 | 36 | 1210.00 |
| JAMES | 950.00 | 41 | 1045.00 |
| FORD | 3000.00 | 41 | 3300.00 |
| MILLER | 1300.00 | 41 | 1430.00 |
+--------+----------+----------+------------+
14 rows in set (0.00 sec)