首先需要创建并初始化员工表、部门表、薪资等级表数据：
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;
SELECT *
FROM emp;
SELECT *
FROM dept;
SELECT *
FROM salgrade;
员工表：
部门表：
薪资等级表：
1. 取得每个部门最高薪水的人员名称
先取得部门最高薪水：
select deptno,max(sal) from emp group by deptno;
最终查询语句及结果：
select e.ename,t.* from (select deptno,max(sal) 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. 哪些人的薪水在部门平均薪水之上
先取得部门平均薪水：
select deptno,avg(sal) avgsal from emp group by deptno;
最终查询语句及结果：
select e1.ename,e1.sal,e1.deptno from emp e1 join
(select deptno,avg(sal) avgsal from emp group by deptno) e2 on e1. deptno = e2.deptno
where e1.sal > e2.avgsal order by deptno;
+-------+---------+--------+
| ename | sal | deptno |
+-------+---------+--------+
| KING | 5000.00 | 10 |
| JONES | 2975.00 | 20 |
| SCOTT | 3000.00 | 20 |
| FORD | 3000.00 | 20 |
| ALLEN | 1600.00 | 30 |
| BLAKE | 2850.00 | 30 |
+-------+---------+--------+
3. 取得部门中（所有人）平均薪水等级
先取得所有人的薪水等级：
select e.*,s.grade from emp e join salgrade s on e.sal between s.losal and hisal;
最终查询语句及结果：
select t.deptno,avg(t.grade) avgsalgrade from
(select e.*,s.grade from emp e join salgrade s on e.sal between s.losal and hisal) t
group by t.deptno;
+--------+-------------+
| DEPTNO | avgsalgrade |
+--------+-------------+
| 10 | 3.6667 |
| 20 | 2.8000 |
| 30 | 2.5000 |
+--------+-------------+
4. 不准用组函数（max），取得最高薪水（给出两种解决方案）
第一种解决方案：
根据降序后取第一条记录：
select ename,sal from emp order by sal desc limit 1;
第二种解决方案：
自连接：其中一个值不小于任何一个值，distinct去重后使用not in 过滤查询结果后即为最大值：
select ename,sal from emp where sal not in
(select distinct a.sal from emp a join emp b on a.sal < b.sal);
+-------+---------+
| ename | sal |
+-------+---------+
| KING | 5000.00 |
+-------+---------+
5. 取得平均薪水最高的部门的部门编号
第一种方案：
先取得部门的平均薪水后降序排序取第一条数据：
select deptno,avg(sal) avgsal from emp group by deptno order by avgsal desc limit 1;
+--------+-------------+
| deptno | avgsal |
+--------+-------------+
| 10 | 2916.666667 |
+--------+-------------+
第二种方案：
先取得每个部门的平均薪水：
select avg(sal) avgsal from emp group by deptno;
再将上面的查询结果当做临时表使用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 |
+--------+-------------+
6. 取得平均薪水最高的部门的部门名称
先取得每个部门的平均薪水：
select avg(sal) avgsal from emp group by deptno;
再将上面的查询结果当做临时表使用max分组函数取得最大值和部门编号：
select t.deptno, max(t.avgsal) maxavgsal from (select deptno,avg(sal) avgsal from emp group by deptno) t;
最后将该表与部门表连接后查询出部门名称:
select t.deptno,d.dname, max(t.avgsal) maxavgsal from
(select deptno,avg(sal) avgsal from emp group by deptno) t join dept d on t.deptno = d.deptno;
+--------+------------+-------------+
| deptno | dname | maxavgsal |
+--------+------------+-------------+
| 10 | ACCOUNTING | 2916.666667 |
+--------+------------+-------------+
7. 求平均薪水的等级最低的部门的部门名称
先求出每个部门平均薪水：
select deptno,avg(sal) avgsal from emp group by deptno;
再求出每个部门平均薪水的等级：
select t.deptno,s.grade from (select deptno,avg(sal) avgsal from emp group by deptno) t
join salgrade s on t.avgsal between s.losal and hisal;
最后将查询出来的数据当做一张临时表与部门表连表查询出部门名称：
select t1.deptno,d.dname,t1.grade from (select t.deptno,s.grade from
(select deptno,avg(sal) avgsal from emp group by deptno) t join salgrade s
on t.avgsal between s.losal and hisal) t1 join dept d on t1.deptno = d.deptno;
+--------+------------+-------+
| deptno | dname | grade |
+--------+------------+-------+
| 10 | ACCOUNTING | 4 |
| 20 | RESEARCH | 4 |
| 30 | SALES | 3 |
+--------+------------+-------+
8. 取得比普通员工（员工代码没有在mgr上出现的）的最高薪水还要高的经理人姓名
先取得所有普通员工的最高薪水（共8个）：
select max(e.sal) maxsal from emp e where ename not in
(select e.ename from emp e join emp e1 on e.empno = e1.mgr);
最终查询语句及结果：
select ename from emp where sal > (select max(e.sal) maxsal from emp e
where ename not in (select e.ename from emp e join emp e1 on e.empno = e1.mgr));
+-------+
| ename |
+-------+
| JONES |
| BLAKE |
| CLARK |
| SCOTT |
| KING |
| FORD |
+-------+
9. 取得薪水最高的前五名员工
select * from emp order by sal desc limit 5;
+-------+-------+-----------+------+------------+---------+------+--------+
| EMPNO | ENAME | JOB | MGR | HIREDATE | SAL | COMM | DEPTNO |
+-------+-------+-----------+------+------------+---------+------+--------+
| 7839 | KING | PRESIDENT | NULL | 1981-11-17 | 5000.00 | NULL | 10 |
| 7902 | FORD | ANALYST | 7566 | 1981-12-03 | 3000.00 | NULL | 20 |
| 7788 | SCOTT | ANALYST | 7566 | 1987-04-19 | 3000.00 | NULL | 20 |
| 7566 | JONES | MANAGER | 7839 | 1981-04-02 | 2975.00 | NULL | 20 |
| 7698 | BLAKE | MANAGER | 7839 | 1981-05-01 | 2850.00 | NULL | 30 |
+-------+-------+-----------+------+------------+---------+------+--------+
10. 取得薪水最高的第六到第十名员工
select * from emp order by sal desc limit 5,5;
+-------+--------+----------+------+------------+---------+--------+--------+
| EMPNO | ENAME | JOB | MGR | HIREDATE | SAL | COMM | DEPTNO |
+-------+--------+----------+------+------------+---------+--------+--------+
| 7782 | CLARK | MANAGER | 7839 | 1981-06-09 | 2450.00 | NULL | 10 |
| 7499 | ALLEN | SALESMAN | 7698 | 1981-02-20 | 1600.00 | 300.00 | 30 |
| 7844 | TURNER | SALESMAN | 7698 | 1981-09-08 | 1500.00 | 0.00 | 30 |
| 7934 | MILLER | CLERK | 7782 | 1982-01-23 | 1300.00 | NULL | 10 |
| 7521 | WARD | SALESMAN | 7698 | 1981-02-22 | 1250.00 | 500.00 | 30 |
+-------+--------+----------+------+------------+---------+--------+--------+
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 |
| 7900 | JAMES | CLERK | 7698 | 1981-12-03 | 950.00 | NULL | 30 |
| 7902 | FORD | ANALYST | 7566 | 1981-12-03 | 3000.00 | NULL | 20 |
+-------+--------+---------+------+------------+---------+------+--------+
12. 取得每个薪水等级有多少员工
select s.grade,count(1) count from emp e join salgrade s
on e.sal between s.losal and s.hisal group by grade;
+-------+-------+
| grade | count |
+-------+-------+
| 1 | 3 |
| 2 | 3 |
| 3 | 2 |
| 4 | 5 |
| 5 | 1 |
+-------+-------+
13. 面试题
有3个表S(学生表)，C（课程表），SC（学生选课表）
S（SNO，SNAME）代表（学号，姓名）
C（CNO，CNAME，CTEACHER）代表（课号，课名，教师）
SC（SNO，CNO，SCGRADE）代表（学号，课号，成绩）
首先需要创建并初始化学生表、课程表、学生选课表数据：
DROP TABLE IF EXISTS S;
create table s(
sno int(10) primary key auto_increment,
sname varchar(32)
);
DROP TABLE IF EXISTS C;
create table c(
cno int(10) primary key auto_increment,
cname varchar(32),
cteacher varchar(32)
);
// sno+cno是复合主键，主键叧有一个
// sno也是外键，cno也是外键，外键有两个
DROP TABLE IF EXISTS SC;
create table sc(
sno int(10),
cno int(10),
scgrade double(3,1),
constraint sc_sno_cno_pk primary key(sno,cno),
constraint sc_sno_fk foreign key(sno) references s(sno),
constraint sc_cno_fk foreign key(cno) references c(cno)
);
INSERT INTO S ( SNO, SNAME ) VALUES ( '1', '学生1');
INSERT INTO S ( SNO, SNAME ) VALUES ( '2', '学生2');
INSERT INTO S ( SNO, SNAME ) VALUES ( '3', '学生3');
INSERT INTO S ( SNO, SNAME ) VALUES ( '4', '学生4');
commit;
INSERT INTO C ( CNO, CNAME, CTEACHER ) VALUES ( '1', 'Java', '王老师');
INSERT INTO C ( CNO, CNAME, CTEACHER ) VALUES ( '2', 'C++', '张老师');
INSERT INTO C ( CNO, CNAME, CTEACHER ) VALUES ( '3', 'C#', '李老师');
INSERT INTO C ( CNO, CNAME, CTEACHER ) VALUES ( '4', 'MySQL', '赵老师');
INSERT INTO C ( CNO, CNAME, CTEACHER ) VALUES ( '5', 'Oracle', '黎明');
commit;
INSERT INTO SC ( SNO, CNO, SCGRADE ) VALUES ( '1', '1', '50');
INSERT INTO SC ( SNO, CNO, SCGRADE ) VALUES ( '1', '2', '50');
INSERT INTO SC ( SNO, CNO, SCGRADE ) VALUES ( '1', '3', '50');
INSERT INTO SC ( SNO, CNO, SCGRADE ) VALUES ( '2', '2', '80');
INSERT INTO SC ( SNO, CNO, SCGRADE ) VALUES ( '2', '3', '70');
INSERT INTO SC ( SNO, CNO, SCGRADE ) VALUES ( '2', '4', '59');
INSERT INTO SC ( SNO, CNO, SCGRADE ) VALUES ( '3', '1', '60');
INSERT INTO SC ( SNO, CNO, SCGRADE ) VALUES ( '3', '2', '61');
INSERT INTO SC ( SNO, CNO, SCGRADE ) VALUES ( '3', '3', '99');
INSERT INTO SC ( SNO, CNO, SCGRADE ) VALUES ( '3', '4', '100');
INSERT INTO SC ( SNO, CNO, SCGRADE ) VALUES ( '3', '5', '52');
INSERT INTO SC ( SNO, CNO, SCGRADE ) VALUES ( '4', '3', '82');
INSERT INTO SC ( SNO, CNO, SCGRADE ) VALUES ( '4', '4', '99');
INSERT INTO SC ( SNO, CNO, SCGRADE ) VALUES ( '4', '5', '40');
commit;
SELECT * FROM S;
SELECT * FROM C;
SELECT * FROM SC;
S 学生表：
C 课程表：
SC 学生选课表：
13-1. 找出没选过“黎明”老师的所有学生姓名？
先找出黎明老师的课程号：
select cno from c where cteacher = '黎明';
再通过课程号找出选修了该课程的学号：
select sno from sc where cno = (select cno from c where cteacher = '黎明');
最后过滤掉选择了黎明老师课程的学生：
select sname from s where sno not in
(select sno from sc where cno = (select cno from c where cteacher = '黎明'));
+---------+
| sname |
+---------+
| 学生1 |
| 学生2 |
+---------+
13-2. 列出2门以上（含2门）不及格学生姓名及平均成绩
先通过学号分组取得2门以上不及格学生的学号及平均成绩：
select sno,avg(scgrade) avggrade,count(1) count from sc where scgrade < 60 group by sno having count >=2;
再通过学号连表查询出学生姓名及平均成绩：
select s.sname,t.avggrade from s s join
(select sno,avg(scgrade) avggrade,count(1) count from sc where scgrade < 60 group by sno having count >=2) t
on s.sno = t.sno;
+---------+----------+
| sname | avggrade |
+---------+----------+
| 学生1 | 50 |
+---------+----------+
13-3. 既学过1号课程又学过2号课所有学生的姓名
先取得学过课程1和2的学号：
select sno from sc where cno = 1 and 2;
最终查询语句及结果：
select s.sname from s s join (select sno from sc where cno = 1 and 2) t on s.sno = t.sno;
+---------+
| sname |
+---------+
| 学生1 |
| 学生3 |
+---------+
14. 列出所有员工及领导的名字
select e.ename,e1.ename leadername from emp e left join emp e1 on e.mgr = e1.empno;
+--------+------------+
| ename | leadername |
+--------+------------+
| 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 e.empno, e.ename,e.deptno,e.hiredate,e1.ename leadername,e1.hiredate from emp e
left join emp e1 on e.mgr = e1.empno where e.hiredate < e1.hiredate;
+-------+-------+--------+------------+------------+------------+
| empno | ename | deptno | hiredate | leadername | hiredate |
+-------+-------+--------+------------+------------+------------+
| 7369 | SMITH | 20 | 1980-12-17 | FORD | 1981-12-03 |
| 7499 | ALLEN | 30 | 1981-02-20 | BLAKE | 1981-05-01 |
| 7521 | WARD | 30 | 1981-02-22 | BLAKE | 1981-05-01 |
| 7566 | JONES | 20 | 1981-04-02 | KING | 1981-11-17 |
| 7698 | BLAKE | 30 | 1981-05-01 | KING | 1981-11-17 |
| 7782 | CLARK | 10 | 1981-06-09 | KING | 1981-11-17 |
+-------+-------+--------+------------+------------+------------+
最终查询语句及结果：
select t.empno,t.ename,d.dname from (select e.empno, e.ename,e.deptno,e1.ename leadername from emp e
left join emp e1 on e.mgr = e1.empno where e.hiredate < e1.hiredate) t join dept d on t.deptno = d.deptno;
+-------+-------+------------+
| empno | ename | dname |
+-------+-------+------------+
| 7369 | SMITH | RESEARCH |
| 7499 | ALLEN | SALES |
| 7521 | WARD | SALES |
| 7566 | JONES | RESEARCH |
| 7698 | BLAKE | SALES |
| 7782 | CLARK | ACCOUNTING |
+-------+-------+------------+
16. 列出部门名称和这些部门的员工信息，同时列出那些没有员工的部门
select d.deptno,d.dname,e.* from emp e right join dept d on e.deptno = d.deptno order by d.deptno;
+--------+------------+-------+--------+-----------+------+------------+---------+---------+--------+
| deptno | dname | EMPNO | ENAME | JOB | MGR | HIREDATE | SAL | COMM | DEPTNO |
+--------+------------+-------+--------+-----------+------+------------+---------+---------+--------+
| 10 | ACCOUNTING | 7934 | MILLER | CLERK | 7782 | 1982-01-23 | 1300.00 | NULL | 10 |
| 10 | ACCOUNTING | 7782 | CLARK | MANAGER | 7839 | 1981-06-09 | 2450.00 | NULL | 10 |
| 10 | ACCOUNTING | 7839 | KING | PRESIDENT | NULL | 1981-11-17 | 5000.00 | NULL | 10 |
| 20 | RESEARCH | 7876 | ADAMS | CLERK | 7788 | 1987-05-23 | 1100.00 | NULL | 20 |
| 20 | RESEARCH | 7566 | JONES | MANAGER | 7839 | 1981-04-02 | 2975.00 | NULL | 20 |
| 20 | RESEARCH | 7902 | FORD | ANALYST | 7566 | 1981-12-03 | 3000.00 | NULL | 20 |
| 20 | RESEARCH | 7788 | SCOTT | ANALYST | 7566 | 1987-04-19 | 3000.00 | NULL | 20 |
| 20 | RESEARCH | 7369 | SMITH | CLERK | 7902 | 1980-12-17 | 800.00 | NULL | 20 |
| 30 | SALES | 7499 | ALLEN | SALESMAN | 7698 | 1981-02-20 | 1600.00 | 300.00 | 30 |
| 30 | SALES | 7698 | BLAKE | MANAGER | 7839 | 1981-05-01 | 2850.00 | NULL | 30 |
| 30 | SALES | 7844 | TURNER | SALESMAN | 7698 | 1981-09-08 | 1500.00 | 0.00 | 30 |
| 30 | SALES | 7521 | WARD | SALESMAN | 7698 | 1981-02-22 | 1250.00 | 500.00 | 30 |
| 30 | SALES | 7900 | JAMES | CLERK | 7698 | 1981-12-03 | 950.00 | NULL | 30 |
| 30 | SALES | 7654 | MARTIN | SALESMAN | 7698 | 1981-09-28 | 1250.00 | 1400.00 | 30 |
| 40 | OPERATIONS | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL |
+--------+------------+-------+--------+-----------+------+------------+---------+---------+--------+
17. 列出至少有5个员工的所有部门
select deptno, count(1) counter from emp group by deptno having counter >= 5;
+--------+---------+
| deptno | counter |
+--------+---------+
| 20 | 5 |
| 30 | 6 |
+--------+---------+
18. 列出薪水比“SMITH”多的所有员工信息
先查出‘SMTH’的薪水：
select sal from emp where ename = '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,d.dname from emp e join dept d on e.deptno = d.deptno where job = 'CLERK';
再找出每个部门的人数：
select count(*) number from emp group by deptno;
最后以上两张表联合查询：
select d1.ename,d1.dname,d2.number from
(select e.deptno,e.ename,d.dname from emp e join dept d on e.deptno = d.deptno where job = 'CLERK') d1
join (select deptno,count(*) number from emp group by deptno) d2 on d1.deptno = d2.deptno;
+--------+------------+--------+
| ename | dname | number |
+--------+------------+--------+
| SMITH | RESEARCH | 5 |
| ADAMS | RESEARCH | 5 |
| JAMES | SALES | 6 |
| MILLER | ACCOUNTING | 3 |
+--------+------------+--------+
20. 列出最低薪水大于1500的各种工作及从事此工作的全部雇员人数
select job,count(*) peoNumber from emp group by job having min(sal) > 1500;
+-----------+-----------+
| job | peoNumber |
+-----------+-----------+
| ANALYST | 2 |
| MANAGER | 3 |
| PRESIDENT | 1 |
+-----------+-----------+
21. 列出在部门“SALES”<销售部>工作的员工的姓名，假定不知道销售部门的部门编号
先找出“SALES”<销售部>的部门编号：
select deptno from dept where dname = 'SALES';
最后通过部门编号直接列出“SALES”部门工作的员工姓名：
select ename from emp where deptno = (select deptno from dept where dname = 'SALES');
+--------+
| ename |
+--------+
| ALLEN |
| WARD |
| MARTIN |
| BLAKE |
| TURNER |
| JAMES |
+--------+
22. 列出薪金高于公司平均薪金的所有员工，所在部门、上级领导、雇员的工资等级
先找出薪金高于公司平均薪金的所有员工：
select ename from emp where sal > (select avg(sal) avgsal from emp);
再找出薪金高于公司平均薪金的所有员工，所在部门：
select e.ename,d.dname from emp e join dept d on e.deptno = d.deptno
where sal > (select avg(sal) avgsal from emp);
然后找出薪金高于公司平均薪金的所有员工，所在部门、上级领导：
select e1.ename,d.dname,e2.ename from emp e1 join dept d on e1.deptno = d.deptno
left join emp e2 on e1.mgr = e2.empno where e1.sal > (select avg(sal) avgsal from emp);
最后列出薪金高于公司平均薪金的所有员工，所在部门、上级领导、工资等级：
select e1.ename,d.dname,e2.ename,s.grade from emp e1 join dept d on e1.deptno = d.deptno
left join emp e2 on e1.mgr = e2.empno join salgrade s on e1.sal between s.losal and s.hisal
where e1.sal > (select avg(sal) avgsal from emp);
+-------+------------+-------+-------+
| ename | dname | ename | grade |
+-------+------------+-------+-------+
| JONES | RESEARCH | KING | 4 |
| BLAKE | SALES | KING | 4 |
| CLARK | ACCOUNTING | KING | 4 |
| SCOTT | RESEARCH | JONES | 4 |
| KING | ACCOUNTING | NULL | 5 |
| FORD | RESEARCH | JONES | 4 |
+-------+------------+-------+-------+
23. 列出与“SCOTT”从事相同工作的所有员工及部门名称
先找出与“SCOTT”从事相同工作的所有员工：
select ename from emp where job = (select job from emp where ename = 'SCOTT');
再联合部门表找出与“SCOTT”从事相同工作的所有员工及部门名称：
select e.ename,d.dname from emp e join dept d on e.deptno = d.deptno
where job = (select job from emp where ename = 'SCOTT') and ename <> 'SCOTT';
+-------+----------+
| ename | dname |
+-------+----------+
| FORD | RESEARCH |
+-------+----------+
24. 列出薪金等于部门30中员工的薪金的其它员工的姓名和薪金
先找出30部门中员工的薪金：
select sal from emp where deptno = 30;
最终查询语句及结果：
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工作的所有员工的薪金的员工姓名和薪金、部门名称
先找出在30部门工作的所有员工中最高薪金：
select max(sal) maxsal from emp where deptno = 30;
再找出薪金高于以上薪金的员工姓名和薪金的员工：
select ename,sal from emp where sal > (select max(sal) maxsal from emp where deptno = 30) and deptno <> 30;
最后找出薪金高于在部门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) maxsal from emp where deptno = 30) and e.deptno <> 30;
+-------+---------+------------+
| ename | sal | dname |
+-------+---------+------------+
| JONES | 2975.00 | RESEARCH |
| SCOTT | 3000.00 | RESEARCH |
| KING | 5000.00 | ACCOUNTING |
| FORD | 3000.00 | RESEARCH |
+-------+---------+------------+
26. 列出在每个部门工作的员工数量、平均工资和平均服务期限（*）
// 其中涉及的函数包括：ifnull(字段,0) datediff(now(),日期) right join
select d.deptno,ifnull(count(e.ename),0) peonumber,ifnull(avg(e.sal),0) avgsal,
ifnull(avg(datediff(now(),e.hiredate)/365),0) servertime from emp e right join dept d
on e.deptno = d.deptno group by e.deptno order by deptno;
+--------+-----------+-------------+-------------+
| deptno | peonumber | avgsal | servertime |
+--------+-----------+-------------+-------------+
| 10 | 3 | 2916.666667 | 39.21186667 |
| 20 | 5 | 2175.000000 | 37.23890000 |
| 30 | 6 | 1566.666667 | 39.51051667 |
| 40 | 0 | 0.000000 | 0.00000000 |
+--------+-----------+-------------+-------------+
27. 列出所有员工的姓名、部门名称和工资
select e.ename,d.dname,e.sal from emp e 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 |
+--------+------------+---------+
28. 列出所有部门的详细信息和人数
select d.*,count(e.ename) peoNumber from dept d left join emp e
on d.deptno = e.deptno group by e.deptno order by d.deptno;
+--------+------------+----------+-----------+
| DEPTNO | DNAME | LOC | peoNumber |
+--------+------------+----------+-----------+
| 10 | ACCOUNTING | NEW YORK | 3 |
| 20 | RESEARCH | DALLAS | 5 |
| 30 | SALES | CHICAGO | 6 |
| 40 | OPERATIONS | BOSTON | 0 |
+--------+------------+----------+-----------+
29. 列出各种工作的最低工资及从事此工作的雇员姓名
先找出各种工作的最低工资：
select job,min(sal) minSal from emp group by job;
最终查询语句及结果：
select ename,job,sal from emp where sal in (select min(sal) minSal from emp group by job);
+--------+-----------+---------+
| ename | job | sal |
+--------+-----------+---------+
| 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 |
+--------+-----------+---------+
30. 列出各个部门MANAGER的最低薪金
select deptno,min(sal) minSal from emp where job = 'MANAGER' group by deptno;
+--------+---------+
| deptno | minSal |
+--------+---------+
| 10 | 2450.00 |
| 20 | 2975.00 |
| 30 | 2850.00 |
+--------+---------+
31. 列出所有员工的年工资，按年薪从低到高掋序
select ename,ifnull(sal,0) * 12 annualSal from emp order by annualSal;
+--------+-----------+
| ename | annualSal |
+--------+-----------+
| SMITH | 9600.00 |
| JAMES | 11400.00 |
| ADAMS | 13200.00 |
| WARD | 15000.00 |
| MARTIN | 15000.00 |
| MILLER | 15600.00 |
| TURNER | 18000.00 |
| ALLEN | 19200.00 |
| CLARK | 29400.00 |
| BLAKE | 34200.00 |
| JONES | 35700.00 |
| SCOTT | 36000.00 |
| FORD | 36000.00 |
| KING | 60000.00 |
+--------+-----------+
32. 求出员工领导的薪水超过3000的员工名称和领导名称
select e1.ename,e2.ename,e2.sal from emp e1 join emp e2 on e1.mgr = e2.empno where e2.sal > 3000;
+-------+-------+---------+
| ename | ename | sal |
+-------+-------+---------+
| JONES | KING | 5000.00 |
| BLAKE | KING | 5000.00 |
| CLARK | KING | 5000.00 |
+-------+-------+---------+
33. 求部门名称中带“S”字符的部门员工的工资合计、部门人数
select d.dname,ifnull(sum(e.sal),0) sumSal,count(e.ename) peoNumber from emp e right join dept d
on e.deptno = d.deptno where d.dname like '%S%' group by d.deptno;
+------------+----------+-----------+
| dname | sumSal | peoNumber |
+------------+----------+-----------+
| RESEARCH | 10875.00 | 5 |
| SALES | 9400.00 | 6 |
| OPERATIONS | 0.00 | 0 |
+------------+----------+-----------+
34. 给任职日期超过30年的员工加薪10%
找出任职日期超过30年的员工然后加薪10%：
update emp set sal = sal * 1.1 where datediff(now(),hiredate)/365 > 30;
试题答案均已经过验证，但试题由于本人所做，所以解决方案可能并非最优，如果有更好的调优方案可评论交流，谢谢(?▽?)！