1. 取得每个部门最高薪水的人员名称
-
取得每个部门最高薪水，按照部门编号进行分组。
select deptno, max(sal) from emp group by deptno; +--------+----------+ | deptno | max(sal) | +--------+----------+ | 10 | 5000.00 | | 20 | 3000.00 | | 30 | 2850.00 | +--------+----------+ 3 rows in set (0.00 sec) -
将第一步得到的表，作为临时表和emp表进行左连接，查出部门每个部门最高薪资的这几个员工。
select e.ename, t.* from (select deptno, max(sal) as msal from emp group by deptno) t left join emp e on t.deptno = e.deptno and t.msal = e.sal; +-------+--------+---------+ | ename | deptno | msal | +-------+--------+---------+ | KING | 10 | 5000.00 | | SCOTT | 20 | 3000.00 | | FORD | 20 | 3000.00 | | BLAKE | 30 | 2850.00 | +-------+--------+---------+ 4 rows in set (0.00 sec)
2. 哪些人的薪水在部门的平均薪水之上
-
先求出每个部门的平均薪水
select deptno, avg(sal) from emp group by deptno; +--------+-------------+ | deptno | avg(sal) | +--------+-------------+ | 10 | 2916.666667 | | 20 | 2175.000000 | | 30 | 1566.666667 | +--------+-------------+ 3 rows in set (0.00 sec) -
再找出每个部门那些高于本部门平均薪水的人
select e.deptno, e.ename, e.sal, t.avgsal from (select deptno, avg(sal) as avgsal from emp group by deptno) t join emp e on e.deptno = t.deptno and e.sal > t.avgsal order by e.deptno, e.sal; +--------+-------+---------+-------------+ | deptno | ename | sal | avgsal | +--------+-------+---------+-------------+ | 10 | KING | 5000.00 | 2916.666667 | | 20 | JONES | 2975.00 | 2175.000000 | | 20 | FORD | 3000.00 | 2175.000000 | | 20 | SCOTT | 3000.00 | 2175.000000 | | 30 | ALLEN | 1600.00 | 1566.666667 | | 30 | BLAKE | 2850.00 | 1566.666667 | +--------+-------+---------+-------------+ 6 rows in set (0.00 sec)
3. 取得部门中（所有人的）平均的薪水等级
-
先求出每个人的薪资等级
-
按照部门分组，对该组中所有人的薪资等级求取平均值
select e.deptno, avg(grade) from emp e join salgrade s on e.sal between s.losal and s.hisal group by e.deptno order by e.deptno; +--------+------------+ | deptno | avg(grade) | +--------+------------+ | 10 | 3.6667 | | 20 | 2.8000 | | 30 | 2.5000 | +--------+------------+ 3 rows in set (0.00 sec)
4. 不准用组函数（Max），取得最高薪水
-
按照工资的降序排序找出工资最低的那个人，然后将表分页输出(limit)。
mysql> select ename, sal from emp order by sal desc limit 0, 1; +-------+---------+ | ename | sal | +-------+---------+ | KING | 5000.00 | +-------+---------+ 1 row in set (0.00 sec)
5. 取得平均薪水最高的部门的部门编号
-
先按照部门编号进行分组，求出每个部门的平均薪水
-
按照平均薪水进行倒序排序，输出表中第一行数据。
select deptno, avg(sal) as avgsal from emp group by deptno order by avgsal desc limit 0, 1; +--------+-------------+ | deptno | avgsal | +--------+-------------+ | 10 | 2916.666667 | +--------+-------------+ 1 row in set (0.00 sec)
6. 取得平均薪水最高的部门的部门名称
-
在第5题的基础上，将第五题得到的表与部门表进行连接，查出部门名称。
select t.deptno, d.dname from (select deptno, avg(sal) as avgsal from emp group by deptno order by avgsal desc limit 0, 1) t join dept d on t.deptno = d.deptno; +--------+------------+ | deptno | dname | +--------+------------+ | 10 | ACCOUNTING | +--------+------------+ 1 row in set (0.00 sec)
7. 求平均薪水的等级最低的部门的部门名称
-
按照部门分组，求各个部门的平均薪水
-
与工资等级表连接，求出各个组的部门等级
-
接着与部门表连接，找出部门名称，最后排序，使用limit显示第一个。 -
上面第3步是错误的，因为存在这种情况：多个部门的薪水等级均为最低等级。
-
接着第2步向下进行->所以先求出最低等级是多少，然后查询最低等级的有哪些部门。
------------------按照部门编号，进行分组，求出各个部门平均薪水的等级 select t.deptno, t.avgsal, s.grade, d.dname from (select deptno, avg(sal) as avgsal from emp group by deptno) t join salgrade s on t.avgsal between s.losal and s.hisal join dept d on t.deptno = d.deptno order by s.grade; -------------------------求出最低等级 select s.grade from (select deptno, avg(sal) as avgsal from emp group by deptno) t join salgrade s on t.avgsal between s.losal and s.hisal join dept d on t.deptno = d.deptno order by s.grade limit 1; --------------------求出平均薪水的等级 等于最低等级的部门 select t.avgsal, s.grade, d.dname from (select deptno, avg(sal) as avgsal from emp group by deptno) t join salgrade s on t.avgsal between s.losal and s.hisal join dept d on t.deptno = d.deptno where s.grade = ( select s.grade from (select deptno, avg(sal) as avgsal from emp group by deptno) t join salgrade s on t.avgsal between s.losal and s.hisal join dept d on t.deptno = d.deptno order by s.grade limit 1 ); +-------------+-------+-------+ | avgsal | grade | dname | +-------------+-------+-------+ | 1566.666667 | 3 | SALES | +-------------+-------+-------+
8. 取得比普通员工(员工代码没有在mgr字段上出现的)的最高薪水还要高的领导人姓名
（最开始没有思考出不需要中间过程、直接完成的sql语句。现在在第2步解决这个了问题，运用外连接,匹配不上的赋值为null。）
-
找出所有的领导编号
select distinct mgr from emp where mgr is not null; +------+ | mgr | +------+ | 7902 | | 7698 | | 7839 | | 7566 | | 7788 | | 7782 | +------+ 6 rows in set (0.00 sec) -
将员工表与领导编号表左连接，员工编号与领导编号匹配相同的则是领导，匹配不上的则是员工，由于是外连接，匹配不上时，会赋值为null，通过判断是否为null来过滤出员工。找出普通员工的最高薪资。
select e.sal from emp e left join (select distinct mgr from emp where mgr is not null) l on e.empno = l.mgr where l.mgr is null order by e.sal desc limit 0, 1; -
找出比普通最高薪资高的领导的名称。领导编号表与员工表连接，求出领导的姓名，where字句对领导工资与员工最高工资进行判断
select e.ename, e.sal from (select distinct mgr from emp where mgr is not null) l join emp e on l.mgr = e.empno where e.sal > ( select e.sal from emp e left join (select distinct mgr from emp where mgr is not null) l on e.empno = l.mgr where l.mgr is null order by e.sal desc limit 0, 1 ); +-------+---------+ | ename | sal | +-------+---------+ | FORD | 3000.00 | | BLAKE | 2850.00 | | KING | 5000.00 | | JONES | 2975.00 | | SCOTT | 3000.00 | | CLARK | 2450.00 | +-------+---------+ 6 rows in set (0.00 sec)
9. 取得薪水最高的前五名员工
-
使用降序和分页函数
select ename, sal from emp order by sal desc limit 0, 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)
10. 取得薪水最高的第六到第十名员工
-
使用降序和分页函数
select ename, sal from emp order by sal desc limit 5, 5; +--------+---------+ | ename | sal | +--------+---------+ | CLARK | 2450.00 | | ALLEN | 1600.00 | | TURNER | 1500.00 | | MILLER | 1300.00 | | MARTIN | 1250.00 | +--------+---------+
11. 取得最后入职的5名员工
-
对入职日期字段进行排序
mysql> select ename,hiredate from emp order by hiredate desc limit 0, 5; +--------+------------+ | ename | hiredate | +--------+------------+ | ADAMS | 1987-05-23 | | SCOTT | 1987-04-19 | | MILLER | 1982-01-23 | | FORD | 1981-12-03 | | JAMES | 1981-12-03 | +--------+------------+
12. 取得每个薪水等级有多少员工
-
按薪水等级进行分组，分完组之后统计该组的数据数量
select s.grade, count(*) from emp e join salgrade s on e.sal between s.losal and s.hisal group by s.grade order by s.grade; +-------+----------+ | grade | count(*) | +-------+----------+ | 1 | 3 | | 2 | 3 | | 3 | 2 | | 4 | 5 | | 5 | 1 | +-------+----------+ 5 rows in set (0.00 sec)
13. 面试题
首先创建表3个表，向表中插入数据。
drop table if exists sc;
create table sc(
sno varchar(200),
cno varchar(200),
scgrade varchar(200)
);
create table s(
sno varchar(200),
sname varchar(200)
);
create table c(
cno varchar(200),
cname varchar(200),
cteacher varchar(200)
);
insert into c (cno, cname, cteacher) values ('1', 'yuwen', 'zhang');
insert into c (cno, cname, cteacher) values ('2', 'zhengzhi', 'wang');
insert into c (cno, cname, cteacher) values ('3', 'yingyu', 'li');
insert into c (cno, cname, cteacher) values ('4', 'shuxue', 'zhao');
insert into c (cno, cname, cteacher) values ('5', 'wuli', 'liming');
INSERT INTO S ( SNO, SNAME ) VALUES ( '1', 'student 1');
INSERT INTO S ( SNO, SNAME ) VALUES ( '2', 'student 2');
INSERT INTO S ( SNO, SNAME ) VALUES ( '3', 'student 3');
INSERT INTO S ( SNO, SNAME ) VALUES ( '4', 'student 4');
INSERT INTO SC ( SNO, CNO, SCGRADE ) VALUES ( '1', '1', '40');
INSERT INTO SC ( SNO, CNO, SCGRADE ) VALUES ( '1', '2', '30');
INSERT INTO SC ( SNO, CNO, SCGRADE ) VALUES ( '1', '3', '20');
INSERT INTO SC ( SNO, CNO, SCGRADE ) VALUES ( '1', '4', '80');
INSERT INTO SC ( SNO, CNO, SCGRADE ) VALUES ( '1', '5', '60');
INSERT INTO SC ( SNO, CNO, SCGRADE ) VALUES ( '2', '1', '60');
INSERT INTO SC ( SNO, CNO, SCGRADE ) VALUES ( '2', '2', '60');
INSERT INTO SC ( SNO, CNO, SCGRADE ) VALUES ( '2', '3', '60');
INSERT INTO SC ( SNO, CNO, SCGRADE ) VALUES ( '2', '4', '60');
INSERT INTO SC ( SNO, CNO, SCGRADE ) VALUES ( '2', '5', '40');
INSERT INTO SC ( SNO, CNO, SCGRADE ) VALUES ( '3', '1', '60');
INSERT INTO SC ( SNO, CNO, SCGRADE ) VALUES ( '3', '3', '80');
mysql> select * from s;
+------+-----------+
| sno | sname |
+------+-----------+
| 1 | student 1 |
| 2 | student 2 |
| 3 | student 3 |
| 4 | student 4 |
+------+-----------+
4 rows in set (0.00 sec)
mysql> select * from c;
+------+----------+----------+
| cno | cname | cteacher |
+------+----------+----------+
| 1 | yuwen | zhang |
| 2 | zhengzhi | wang |
| 3 | yingyu | li |
| 4 | shuxue | zhao |
| 5 | wuli | liming |
+------+----------+----------+
5 rows in set (0.00 sec)
mysql> select * from sc;
+------+------+---------+
| sno | cno | scgrade |
+------+------+---------+
| 1 | 1 | 40 |
| 1 | 2 | 30 |
| 1 | 3 | 20 |
| 1 | 4 | 80 |
| 1 | 5 | 60 |
| 2 | 1 | 60 |
| 2 | 2 | 60 |
| 2 | 3 | 60 |
| 2 | 4 | 60 |
| 2 | 5 | 40 |
| 3 | 1 | 60 |
| 3 | 3 | 80 |
+------+------+---------+
12 rows in set (0.00 sec)-
问题1：找出没选过“黎明”老师的所有学生姓名。
-
将学生选课表与课程表进行连接，筛选出选“黎明老师课的学生”，作为一个临时表。与学生表进行外连接。筛选出匹配后为null的学生，即是没选黎明老师课的学生。
----------选过黎明老师课的学生 select sc.*, c.* from sc join c on sc.cno = c.cno where sc.cno = '5'; --------------将学生表与该表连接 select s.sno, s.sname from s left join ( select sc.sno, c.cteacher from sc join c on sc.cno = c.cno where sc.cno = '5' ) t on s.sno = t.sno where t.sno is null; +------+-----------+ | sno | sname | +------+-----------+ | 3 | student 3 | | 4 | student 4 | +------+-----------+ 2 rows in set (0.00 sec)
-
-
列出2门以上（含2门）不及格学生姓名及平均成绩
-
利用学生选课表，先用where条件将学生选课表里高于60的成绩过滤掉，只剩下不及格的成绩。
-
然后按照学号进行分组，计算出每个学生不及格成绩科目的数量。
-
利用having对分组后的数据进行过滤，选出不及格科目数量>=2的学生。
-
按学号分组，求出每个学生的平均成绩，作为临时表tscore。
-
与学生表进行连接，找出该学生的名字，接着与临时表tscore进行连接，找出该学生的平均成绩。
select s.sname, tscore.avgScgrade from s join ( select sno, count(*) from sc where scgrade < 60 group by sno having count(*) >= 2 ) t on s.sno = t.sno join ( select sno, count(*), avg(scgrade) as avgScgrade from sc group by sno ) tscore on s.sno = tscore.sno; +-----------+------------+ | sname | avgScgrade | +-----------+------------+ | student 1 | 46 | +-----------+------------+
-
-
既学过1号课程又学过2号课程的学生的姓名。
-
通过sc表，选张一张临时表t1，选出选过1号课程的学生
-
通过sc表，选张一张临时表t2，选出选过2号课程的学生
-
将两张表进行连接，通过学号，选出既在t1表也在t2表中的学生，接着与学生表进行连接，查出学生姓名。
select t1.sno, s.sname from (select sno, cno from sc where cno = '1') t1 join (select sno, cno from sc where cno = '2') t2 on t1.sno = t2.sno join s where t1.sno = s.sno; +------+-----------+ | sno | sname | +------+-----------+ | 1 | student 1 | | 2 | student 2 | +------+-----------+ 2 rows in set (0.00 sec)
-
14. 列出所有员工及领导的姓名
-
员工表进行自连接，找出各个员工对应的领导的姓名
-
最高领导的领导姓名为null，
如果想对这个null值进行修改，需要将上述得到的表保存，然后使用update。
（做题的时候一直陷在误区里，不知道怎么改查询的结果。现在知道了，应该是在select字句中使用ifnull()函数进行对空值的修改）
select
e1.ename, ifnull(e2.ename, '没有上级') as leadername
from
emp e1
left join
emp e2
on
e1.mgr = e2.empno;
+--------+------------+
| ename | leadername |
+--------+------------+
| SMITH | FORD |
| ALLEN | BLAKE |
| WARD | BLAKE |
| JONES | KING |
| MARTIN | BLAKE |
| BLAKE | KING |
| CLARK | KING |
| SCOTT | JONES |
| KING | 没有上级 |
| TURNER | BLAKE |
| ADAMS | SCOTT |
| JAMES | BLAKE |
| FORD | JONES |
| MILLER | CLARK |
+--------+------------+
14 rows in set, 1 warning (0.00 sec) 15. 列出受雇日期早于其直接上级的所有员工的编号,姓名,部门名称
-
员工表与员工表进行连接，得到的数据表每一行信息为员工及其对应的领导，通过where选出员工入职日期早于对应的领导入职日期的员工。
-
接着与部门表进行连接，查出这些员工的部门名称。
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 order by e1.empno; +-------+-------+------------+ | empno | ename | dname | +-------+-------+------------+ | 7369 | SMITH | RESEARCH | | 7499 | ALLEN | SALES | | 7521 | WARD | SALES | | 7566 | JONES | RESEARCH | | 7698 | BLAKE | SALES | | 7782 | CLARK | ACCOUNTING | +-------+-------+------------+ 6 rows in set (0.00 sec)
16. 列出部门名称和这些部门的员工信息,同时列出那些没有员工的部门.
-
将员工表与部门表进行外连接，部门表作为主表，这样部门下没有员工的部门也会显示出来
select d.dname, e.* from dept d left join emp e on d.deptno = e.deptno; +--------+------------+-------+--------+-----------+------+------------+---------+---------+--------+ | deptno | dname | EMPNO | ENAME | JOB | MGR | HIREDATE | SAL | COMM | DEPTNO | +--------+------------+-------+--------+-----------+------+------------+---------+---------+--------+ | 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 | | 10 | ACCOUNTING | 7934 | MILLER | CLERK | 7782 | 1982-01-23 | 1300.00 | NULL | 10 | | 20 | RESEARCH | 7369 | SMITH | CLERK | 7902 | 1980-12-17 | 800.00 | NULL | 20 | | 20 | RESEARCH | 7566 | JONES | MANAGER | 7839 | 1981-04-02 | 2975.00 | NULL | 20 | | 20 | RESEARCH | 7788 | SCOTT | ANALYST | 7566 | 1987-04-19 | 3000.00 | NULL | 20 | | 20 | RESEARCH | 7876 | ADAMS | CLERK | 7788 | 1987-05-23 | 1100.00 | NULL | 20 | | 20 | RESEARCH | 7902 | FORD | ANALYST | 7566 | 1981-12-03 | 3000.00 | NULL | 20 | | 30 | SALES | 7499 | ALLEN | SALESMAN | 7698 | 1981-02-20 | 1600.00 | 300.00 | 30 | | 30 | SALES | 7521 | WARD | SALESMAN | 7698 | 1981-02-22 | 1250.00 | 500.00 | 30 | | 30 | SALES | 7654 | MARTIN | SALESMAN | 7698 | 1981-09-28 | 1250.00 | 1400.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 | 7900 | JAMES | CLERK | 7698 | 1981-12-03 | 950.00 | NULL | 30 | | 40 | OPERATIONS | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | +--------+------------+-------+--------+-----------+------+------------+---------+---------+--------+ 15 rows in set (0.00 sec)
17. 列出至少有5个员工的所有部门
-
在上一题目的基础上，进行按照部门名称进行分组，对每个部门的员工进行计数，计数的时候选用对员工编号字段进行计数，因为分组函数会自动忽略null。
select d.dname, count(empno) from dept d left join emp e on d.deptno = e.deptno group by d.dname having count(empno) >= 5; +----------+--------------+ | dname | count(empno) | +----------+--------------+ | RESEARCH | 5 | | SALES | 6 | +----------+--------------+ 2 rows in set (0.00 sec)
18. 列出薪金比"SMITH"多的所有员工信息.
-
一个简单的where字句嵌套
select ename, sal from emp where sal > (select sal from emp where ename = 'SMITH');
19. 列出所有"CLERK"(办事员)的姓名及其部门名称,部门的人数.
-
通过将员工表与部门表进行连接，然后按照部门编号分组，获得每个部门的人数，作为临时表
-
利用where字句筛选出工作名称为'CLERK'的员工，然后和部门表进行连接检索到部门名称，然后和部门人数表连接检索到该员工所属部门的员工人数。
select e.ename, d.dname, t.num from emp e join dept d on e.deptno = d.deptno join ( select d.deptno, count(e.ename) as num from dept d join emp e on d.deptno = e.deptno group by d.deptno ) t on e.deptno = t.deptno where e.job = 'CLERK'; +--------+------------+-----+ | ename | dname | num | +--------+------------+-----+ | SMITH | RESEARCH | 5 | | ADAMS | RESEARCH | 5 | | JAMES | SALES | 6 | | MILLER | ACCOUNTING | 3 | +--------+------------+-----+ 4 rows in set (0.00 sec)
20. 列出最低薪金大于1500的各种工作及从事此工作的全部雇员人数.
-
首先根据工作字段分组，找出各种工作的员工数量，作为临时表t
-
将员工表与临时表t连接，找出各个员工所从事的工种的人员数量，然后按照工作字段进行分组，对分组后的数据进行筛选，利用分组函数min()，选出最低薪资>1500的各种工作。
select e.job, t.num from emp e join ( select job, count(*) as num from emp group by job ) t on e.job = t.job group by job having min(sal) > 1500; +-----------+-----+ | job | num | +-----------+-----+ | ANALYST | 2 | | MANAGER | 3 | | PRESIDENT | 1 | +-----------+-----+ 3 rows in set (0.00 sec)
21. 列出在部门"SALES"<销售部>工作的员工的姓名,假定不知道销售部的部门编号.
-
利用where字句的嵌套使用，查出'SALES'部门的编号，然后对员工表进行对比
select deptno from dept where dname = 'SALES'; 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. 列出薪金高于公司平均薪金的所有员工,所在部门,上级领导,雇员的工资等级.
-
求出该公司的平均工资
-
接下来就是4张表的连接。与部门表连接，检索出所在部门名称；接着与自身表连接，找出领导名字,注意这里的连接是外连接。接着与工资等级表连接，计算出各个员工的工资等级。
select e.ename, d.dname, ifnull(e2.ename, '无') as leadername, s.grade from emp e join dept d on e.deptno = d.deptno left join emp e2 on e.mgr = e2.empno join salgrade s on e.sal between s.losal and s.hisal where e.sal > (select avg(sal) from emp); +-------+------------+------------+-------+ | ename | dname | leadername | grade | +-------+------------+------------+-------+ | JONES | RESEARCH | KING | 4 | | BLAKE | SALES | KING | 4 | | CLARK | ACCOUNTING | KING | 4 | | SCOTT | RESEARCH | JONES | 4 | | KING | ACCOUNTING | 无 | 5 | | FORD | RESEARCH | JONES | 4 | +-------+------------+------------+-------+ 6 rows in set, 1 warning (0.00 sec)
23. 列出与"SCOTT"从事相同工作的所有员工及部门名称.
-
一个表连接和一个where子句嵌套
select e.ename, d.dname from emp e join dept d on e.deptno = d.deptno where e.ename != 'SCOTT' and e.job = (select job from emp where ename = 'SCOTT'); +-------+----------+ | ename | dname | +-------+----------+ | FORD | RESEARCH | +-------+----------+ 1 row in set (0.00 sec)
24. 列出薪金等于部门30中员工的薪金的其他员工的姓名和薪金.
-
题目的意思是薪资等于30部门员工的工资但是不属于30部门的员工。求这些员工额的名字和薪资
select e.ename, e.sal, t.sal from emp e join (select distinct e.sal from emp e where e.deptno = 30) t on e.deptno != 30 and e.sal = t.sal; Empty set (0.00 sec)
25. 列出薪金高于在部门30工作的所有员工的薪金的员工姓名和薪金.部门名称.
-
求出30部门的最高薪资，作为where字句判断条件。再同部门表做一个连接。
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 e where e.deptno = 30) order by e.sal; +-------+---------+------------+ | ename | sal | dname | +-------+---------+------------+ | JONES | 2975.00 | RESEARCH | | FORD | 3000.00 | RESEARCH | | SCOTT | 3000.00 | RESEARCH | | KING | 5000.00 | ACCOUNTING | +-------+---------+------------+ 4 rows in set (0.00 sec)
26. 列出在每个部门工作的员工数量,平均工资和平均服务期限.
-
这里的重点在于对平均服务期限的求解，进行日期的计算。（听讲题时注意听这段要）
select d.dname, count(*), avg(e.sal), avg( (to_days(now()) - to_days(hiredate))/365) as avgserveryear from emp e join dept d on e.deptno = d.deptno group by d.dname; +------------+----------+-------------+---------------+ | dname | count(*) | avg(e.sal) | avgserveryear | +------------+----------+-------------+---------------+ | ACCOUNTING | 3 | 2916.666667 | 38.74336667 | | RESEARCH | 5 | 2175.000000 | 36.77040000 | | SALES | 6 | 1566.666667 | 39.04201667 | +------------+----------+-------------+---------------+ 3 rows in set (0.01 sec)
27. 列出所有员工的姓名、部门名称和工资
-
一个简单的连接查询
select e.ename, d.dname, e.sal from emp e join dept d on e.deptno = d.deptno; +--------+------------+---------+ | ename | dname | sal | +--------+------------+---------+ | CLARK | ACCOUNTING | 2450.00 | | KING | ACCOUNTING | 5000.00 | | MILLER | ACCOUNTING | 1300.00 | | SMITH | RESEARCH | 800.00 | | JONES | RESEARCH | 2975.00 | | SCOTT | RESEARCH | 3000.00 | | ADAMS | RESEARCH | 1100.00 | | FORD | RESEARCH | 3000.00 | | ALLEN | SALES | 1600.00 | | WARD | SALES | 1250.00 | | MARTIN | SALES | 1250.00 | | BLAKE | SALES | 2850.00 | | TURNER | SALES | 1500.00 | | JAMES | SALES | 950.00 | +--------+------------+---------+ 14 rows in set (0.00 sec)
28. 列出所有部门的详细信息和人数
-
先求出每个部门的人数
-
再与部门进行连接
select d.*, t.num from dept d join ( select d.deptno, count(e.ename) as num from dept d left join emp e on d.deptno = e.deptno group by d.deptno ) t on d.deptno = t.deptno; +--------+------------+----------+-----+ | DEPTNO | DNAME | LOC | num | +--------+------------+----------+-----+ | 10 | ACCOUNTING | NEW YORK | 3 | | 20 | RESEARCH | DALLAS | 5 | | 30 | SALES | CHICAGO | 6 | | 40 | OPERATIONS | BOSTON | 0 | +--------+------------+----------+-----+ 4 rows in set (0.00 sec)
29. 列出各种工作的最低工资及从事此工作的雇员姓名
-
先求出每种工作的最低薪资，作为临时表，再与员工表进行连接
select e.job, e.ename, t.minsal from emp e join (select e.job, min(e.sal) as minsal from emp e group by job) t on e.job = t.job and e.sal = t.minsal order by e.job; +-----------+--------+---------+ | job | ename | minsal | +-----------+--------+---------+ | ANALYST | FORD | 3000.00 | | ANALYST | SCOTT | 3000.00 | | CLERK | SMITH | 800.00 | | MANAGER | CLARK | 2450.00 | | PRESIDENT | KING | 5000.00 | | SALESMAN | MARTIN | 1250.00 | | SALESMAN | WARD | 1250.00 | +-----------+--------+---------+
30. 列出各个部门的MANAGER(领导)的最低薪金
-
找出job字段为'MANAGER'的员工，按照部门编号进行分组，找出每个部门‘MANGER’的最低薪资
select deptno, min(sal) as minsal from emp where job = 'MANAGER' group by deptno; +--------+---------+ | deptno | minsal | +--------+---------+ | 10 | 2450.00 | | 20 | 2975.00 | | 30 | 2850.00 | +--------+---------+ 3 rows in set (0.00 sec)
31. 列出所有员工的年工资,按年薪从低到高排序
-
年薪等于每个月的工资、津贴之和乘以12。
select ename, ((ifnull(comm, 0) + sal) * 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.00 sec)
32. 求出员工领导的薪水超过3000的员工名称与领导名称
-
题意是，找出员工的直接领导的工资大于3000的员工名称与领导名称
select e1.ename, e2.ename as leadername from emp e1 join emp e2 on e1.mgr = e2.empno where e2.sal > 3000; +-------+------------+ | ename | leadername | +-------+------------+ | JONES | KING | | BLAKE | KING | | CLARK | KING | +-------+------------+ 3 rows in set (0.00 sec)
33. 求出部门名称中,带'S'字符的部门员工的工资合计、部门人数.
-
首先找出部门名称中带有'S'字符的部门，利用通配字符
-
再与员工表进行连接检索
select t.dname, ifnull(sum(e.sal), ' ') as sumsal, count(e.ename) from emp e right join (select deptno, dname from dept where dname like '%S%') t on e.deptno = t.deptno group by t.deptno; +------------+----------+----------------+ | dname | sumsal | count(e.ename) | +------------+----------+----------------+ | RESEARCH | 10875.00 | 5 | | SALES | 9400.00 | 6 | | OPERATIONS | | 0 | +------------+----------+----------------+ 3 rows in set (0.00 sec)
34. 给任职日期超过30年的员工加薪10%.
-
首先查出年薪超过30年的员工
select ename from emp where ((to_days(now()) - to_days(hiredate)) / 365) > 30;
