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

第一步取得每个部门最高薪水

select deptno,max(sal) as maxsal from emp group by deptno;

mysql> select deptno,max(sal) as maxsal from emp group by deptno;
±-------±--------+
| deptno | maxsal |
±-------±--------+
| 10 | 5000.00 |
| 20 | 3000.00 |
| 30 | 2850.00 |
±-------±--------+

第二步 用表上表做临时表 t ，t和emp表连接，条件t.deptno = e.deptno and t.maxsal = e.sal

select 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 |
±------±-------±--------+
| KING | 10 | 5000.00 |
| SCOTT | 20 | 3000.00 |
| FORD | 20 | 3000.00 |
| BLAKE | 30 | 2850.00 |
±------±-------±--------+

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

第一步先算出部门的平均薪水

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

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

第二步

select t.*,e.ename,e.sal from (select deptno,avg(sal) as avg_sal from emp group by deptno) t join emp e on e.deptno = t .deptno and sal>avg_sal;

±-------±------------±------±--------+
| deptno | avg_sal | ename | sal |
±-------±------------±------±--------+
| 30 | 1566.666667 | ALLEN | 1600.00 |
| 20 | 2175.000000 | JONES | 2975.00 |
| 30 | 1566.666667 | BLAKE | 2850.00 |
| 20 | 2175.000000 | SCOTT | 3000.00 |
| 10 | 2916.666667 | KING | 5000.00 |
| 20 | 2175.000000 | FORD | 3000.00 |
±-------±------------±------±--------+

3、取得部门中（所有人的）平均的薪水等级，如下：

第一步 获取每个人的薪水等级

select e.ename,e.sal,e.deptno,s.grade from emp e join salgrade s on e.sal between s.losal and s.hisal ;
±-------±--------±-------±------+
| ename | sal | deptno | grade |
±-------±--------±-------±------+
| CLARK | 2450.00 | 10 | 4 |
| KING | 5000.00 | 10 | 5 |
| MILLER | 1300.00 | 10 | 2 |
| SCOTT | 3000.00 | 20 | 4 |
| FORD | 3000.00 | 20 | 4 |
| JONES | 2975.00 | 20 | 4 |
| SMITH | 800.00 | 20 | 1 |
| ADAMS | 1100.00 | 20 | 1 |
| JAMES | 950.00 | 30 | 1 |
| WARD | 1250.00 | 30 | 2 |
| MARTIN | 1250.00 | 30 | 2 |
| TURNER | 1500.00 | 30 | 3 |
| BLAKE | 2850.00 | 30 | 4 |
| ALLEN | 1600.00 | 30 | 3 |
±-------±--------±-------±------+

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;

±-------±-------------+
| deptno | avg(s.grade) |
±-------±-------------+
| 10 | 3.6667 |
| 20 | 2.8000 |
| 30 | 2.5000 |
±-------±-------------+

4、不准用组函数（Max），取得最高薪水

select ename,sal from emp order by sal desc limit 1;

±------±--------+
| ename | sal |
±------±--------+
| KING | 5000.00 |
±------±--------+

5、取得平均薪水最高的部门的部门编号

第一部 计算每个部门的平均薪水并排序

select deptno,avg(sal) as avg_sal from emp group by deptno order by avg_sal desc limit 1;

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

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

select d.dname,avg(e.sal) as avg_sal from emp e join dept d on e.deptno = d.deptno group by d.dname order by avg_sal desc limit 1;

±-----------±------------+
| dname | avg_sal |
±-----------±------------+
| ACCOUNTING | 2916.666667 |
±-----------±------------+

7、求平均薪水的等级最低的部门的部门名称

1.找出每个部门的平均薪水

select d.dname,avg(e.sal) as avgsal from emp e join dept d on e.deptno =d.deptno group by e.deptno;

±-----------±------------+
| dname | avgsal |
±-----------±------------+
| ACCOUNTING | 2916.666667 |
| RESEARCH | 2175.000000 |
| SALES | 1566.666667 |
±-----------±------------+

2.找出每个部门的平均薪水的等级

select t.*,s.grade from (select d.dname,avg(e.sal) as avgsal from emp e join dept d on e.deptno =d.deptno group by e.deptno) t join salgrade s on t.avgsal between s.losal and s.hisal order by t.avgsal limit 1 ;

±------±------------±------+
| dname | avgsal | grade |
±------±------------±------+
| SALES | 1566.666667 | 3 |
±------±------------±------+

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

员工编号没有在mgr的

select distinct mgr from emp where mgr is not null;

±-----+
| mgr |
±-----+
| 7902 |
| 7698 |
| 7839 |
| 7566 |
| 7788 |
| 7782 |
±-----+

找出普通员工的最高薪水

select max(sal) from emp where empno not in (select distinct mgr from emp where mgr is not null);

±---------+
| max(sal) |
±---------+
| 1600.00 |
±---------+

找出高于（普通员工的最高薪水）

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

±------±--------+
| ename | sal |
±------±--------+
| JONES | 2975.00 |
| BLAKE | 2850.00 |
| CLARK | 2450.00 |
| SCOTT | 3000.00 |
| KING | 5000.00 |
| FORD | 3000.00 |
±------±--------+

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

select ename,sal from emp order by sal desc limit 5;

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

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

select ename,sal from emp order by sal desc limit 5 ,5;

mysql> 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 |
±-------±--------+
5 rows in set (0.00 sec)

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

select ename,hiredate from emp order by hiredate desc limit 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 e.ename,s.grade from emp e join salgrade s on e.sal between s.losal and s.hisal;

±-------±------+
| ename | grade |
±-------±------+
| SMITH | 1 |
| ALLEN | 3 |
| WARD | 2 |
| JONES | 4 |
| MARTIN | 2 |
| BLAKE | 4 |
| CLARK | 4 |
| SCOTT | 4 |
| KING | 5 |
| TURNER | 3 |
| ADAMS | 1 |
| JAMES | 1 |
| FORD | 4 |
| MILLER | 2 |
±-------±------+

在上面的基础按照 grade 分组 用函数count计数

select s.grade,count(*) from emp e join salgrade s on e.sal between s.losal and s.hisal group by s.grade;

±------±---------+
| grade | count(*) |
±------±---------+
| 1 | 3 |
| 2 | 3 |
| 3 | 2 |
| 4 | 5 |
| 5 | 1 |
±------±---------+

13、面试题 —>85

有3个表S(学生表)，C（课程表），SC（学生选课表）

S（SNO，SNAME）代表（学号，姓名）

C（CNO，CNAME，CTEACHER）代表（课号，课名，教师）

SC（SNO，CNO，SCGRADE）代表（学号，课号，成绩）

问题：

1，找出没选过“黎明”老师的所有学生姓名。

2，列出2门以上（含2门）不及格学生姓名及平均成绩。

3，即学过1号课程又学过2号课所有学生的姓名。

请用标准SQL语言写出答案，方言也行（请说明是使用什么方言）。

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’, ‘语文’, ‘张’);

INSERT INTO C ( CNO, CNAME, CTEACHER ) VALUES ( ‘2’, ‘政治’, ‘王’);

INSERT INTO C ( CNO, CNAME, CTEACHER ) VALUES ( ‘3’, ‘英语’, ‘李’);

INSERT INTO C ( CNO, CNAME, CTEACHER ) VALUES ( ‘4’, ‘数学’, ‘赵’);

INSERT INTO C ( CNO, CNAME, CTEACHER ) VALUES ( ‘5’, ‘物理’, ‘黎明’);

commit;

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

commit;

问题1.找出没选过“黎明”老师的所有学生姓名。

问题2:列出2门以上（含2门）不及格学生姓名及平均成绩。

问题3:即学过1号课程又学过2号课所有学生的姓名。

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

select a.ename ‘员工’ ,b.ename ‘领导’ from emp a left join emp b on a.mgr = b.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 |
±-------±------+

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

emp a 员工 emp b 领导 a hiredate < b hiredate

select a.ename ‘员工’,a.hiredate,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;

±------±-----------±------±-----------±-----------+
| 员工 | hiredate | 领导 | hiredate | dname |
±------±-----------±------±-----------±-----------+
| CLARK | 1981-06-09 | KING | 1981-11-17 | ACCOUNTING |
| SMITH | 1980-12-17 | FORD | 1981-12-03 | RESEARCH |
| JONES | 1981-04-02 | KING | 1981-11-17 | RESEARCH |
| ALLEN | 1981-02-20 | BLAKE | 1981-05-01 | SALES |
| WARD | 1981-02-22 | BLAKE | 1981-05-01 | SALES |
| BLAKE | 1981-05-01 | KING | 1981-11-17 | SALES |
±------±-----------±------±-----------±-----------+

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

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

±------±-------±----------±-----±-----------±--------±--------±-------±-------±-----------±---------+
| EMPNO | ENAME | JOB | MGR | HIREDATE | SAL | COMM | DEPTNO | DEPTNO | DNAME | LOC |
±------±-------±----------±-----±-----------±--------±--------±-------±-------±-----------±---------+
| 7782 | CLARK | MANAGER | 7839 | 1981-06-09 | 2450.00 | NULL | 10 | 10 | ACCOUNTING | NEW YORK |
| 7839 | KING | PRESIDENT | NULL | 1981-11-17 | 5000.00 | NULL | 10 | 10 | ACCOUNTING | NEW YORK |
| 7934 | MILLER | CLERK | 7782 | 1982-01-23 | 1300.00 | NULL | 10 | 10 | ACCOUNTING | NEW YORK |
| 7369 | SMITH | CLERK | 7902 | 1980-12-17 | 800.00 | NULL | 20 | 20 | RESEARCH | DALLAS |
| 7566 | JONES | MANAGER | 7839 | 1981-04-02 | 2975.00 | NULL | 20 | 20 | RESEARCH | DALLAS |
| 7788 | SCOTT | ANALYST | 7566 | 1987-04-19 | 3000.00 | NULL | 20 | 20 | RESEARCH | DALLAS |
| 7876 | ADAMS | CLERK | 7788 | 1987-05-23 | 1100.00 | NULL | 20 | 20 | RESEARCH | DALLAS |
| 7902 | FORD | ANALYST | 7566 | 1981-12-03 | 3000.00 | NULL | 20 | 20 | RESEARCH | DALLAS |
| 7499 | ALLEN | SALESMAN | 7698 | 1981-02-20 | 1600.00 | 300.00 | 30 | 30 | SALES | CHICAGO |
| 7521 | WARD | SALESMAN | 7698 | 1981-02-22 | 1250.00 | 500.00 | 30 | 30 | SALES | CHICAGO |
| 7654 | MARTIN | SALESMAN | 7698 | 1981-09-28 | 1250.00 | 1400.00 | 30 | 30 | SALES | CHICAGO |
| 7698 | BLAKE | MANAGER | 7839 | 1981-05-01 | 2850.00 | NULL | 30 | 30 | SALES | CHICAGO |
| 7844 | TURNER | SALESMAN | 7698 | 1981-09-08 | 1500.00 | 0.00 | 30 | 30 | SALES | CHICAGO |
| 7900 | JAMES | CLERK | 7698 | 1981-12-03 | 950.00 | NULL | 30 | 30 | SALES | CHICAGO |
| NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | 40 | OPERATIONS | BOSTON |
±------±-------±----------±-----±-----------±--------±--------±-------±-------±-----------±---------+

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-RH7c42KT-1627395524132)(C:\Users\汝悦\AppData\Roaming\Typora\typora-user-images\image-20210727090935563.png)]

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

按照部门编号分组 计数 筛选出大于等于5的的部门

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

±-------+
| deptno |
±-------+
| 20 |
| 30 |
±-------+

18、列出薪金比"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"(办事员)的姓名及其部门名称,部门的人数

select ename,job from emp where job = ‘clerk’;

±-------±------+
| ename | job |
±-------±------+
| SMITH | CLERK |
| ADAMS | CLERK |
| JAMES | CLERK |
| MILLER | CLERK |
±-------±------+

select e.ename,e.job,d.dname,e.deptno from emp e join dept d on e.deptno = d.deptno where job = ‘clerk’;

±-------±------±-----------±-------+
| ename | job | dname | deptno |
±-------±------±-----------±-------+
| MILLER | CLERK | ACCOUNTING | 10 |
| SMITH | CLERK | RESEARCH | 20 |
| ADAMS | CLERK | RESEARCH | 20 |
| JAMES | CLERK | SALES | 30 |
±-------±------±-----------±-------+

select deptno,count(*) as deptcount from emp group by deptno;

±-------±----------+
| deptno | deptcount |
±-------±----------+
| 10 | 3 |
| 20 | 5 |
| 30 | 6 |
±-------±----------+

select t1.,t2.deptcount from (select e.ename,e.job,d.dname,e.deptno from emp e join dept d on e.deptno = d.deptno where job = ‘clerk’) t1 join (select deptno,count() as deptcount from emp group by deptno) t2 on t1.deptno = t2.deptno;

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

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

select job,count(*) from emp group by job having min(sal)>1500;

±----------±---------+
| job | count(*) |
±----------±---------+
| ANALYST | 2 |
| MANAGER | 3 |
| PRESIDENT | 1 |
±----------±---------+

21、列出在部门"SALES"<销售部>工作的员工的姓名,假定不知道销售部的部门编号

select deptno from dept where dname = ‘sales’;

±-------+
| deptno |
±-------+
| 30 |
±-------+

select ename from emp where deptno = (select deptno from dept where dname = ‘sales’);

±-------+
| ename |
±-------+
| ALLEN |
| WARD |
| MARTIN |
| BLAKE |
| TURNER |
| JAMES |
±-------+

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

mysql> select avg(sal) from emp;
±------------+
| avg(sal) |
±------------+
| 2073.214286 |
±------------+

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

±------±-----------±------±------+
| 员工 | dname | 领导 | 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"从事相同工作的所有员工及部门名称

select job from emp where ename = ‘scott’;

±--------+
| job |
±--------+
| ANALYST |
±--------+

select e.ename,d.dname,e.job

from emp e

join dept d

on e.deptno = d.deptno

where job = (select job from emp where ename = ‘scott’)

and e.ename <> ‘scott’;

±------±---------±--------+
| ename | dname | job |
±------±---------±--------+
| FORD | RESEARCH | ANALYST |
±------±---------±--------+

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

select sal from emp where deptno = 30;

±--------+
| sal |
±--------+
| 1600.00 |
| 1250.00 |
| 1250.00 |
| 2850.00 |
| 1500.00 |
| 950.00 |
±--------+

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

±---------+
| max(sal) |
±---------+
| 2850.00 |
±---------+

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、列出在每个部门工作的员工数量,平均工资和平均服务期限

select avg(sal) ,count(*),avg(timestampdiff(year,hiredate,now())) from emp group by deptno;

±------------±---------±----------------------------------------+
| avg(sal) | count(*) | avg(timestampdiff(year,hiredate,now())) |
±------------±---------±----------------------------------------+
| 2916.666667 | 3 | 39.3333 |
| 2175.000000 | 5 | 37.4000 |
| 1566.666667 | 6 | 39.5000 |
±------------±---------±----------------------------------------+

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 |
±-------±-----------±--------+

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

select deptno,count(*)as deptcount from emp group by deptno;

±-------±----------+
| deptno | deptcount |
±-------±----------+
| 10 | 3 |
| 20 | 5 |
| 30 | 6 |
±-------±----------+

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

±-------±-----------±---------±----------+
| DEPTNO | DNAME | LOC | deptcount |
±-------±-----------±---------±----------+
| 10 | ACCOUNTING | NEW YORK | 3 |
| 20 | RESEARCH | DALLAS | 5 |
| 30 | SALES | CHICAGO | 6 |
| 40 | OPERATIONS | BOSTON | NULL |
±-------±-----------±---------±----------+

select d.deptno,d.dname,d.loc,count(e.ename)

from emp e right join dept d

on e.deptno = d.deptno

group by d.deptno,d.dname,d.loc;

±-------±-----------±---------±---------------+
| deptno | dname | loc | count(e.ename) |
±-------±-----------±---------±---------------+
| 10 | ACCOUNTING | NEW YORK | 3 |
| 20 | RESEARCH | DALLAS | 5 |
| 30 | SALES | CHICAGO | 6 |
| 40 | OPERATIONS | BOSTON | 0 |
±-------±-----------±---------±---------------+

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

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

±----------±--------+
| job | minsal |
±----------±--------+
| ANALYST | 3000.00 |
| CLERK | 800.00 |
| MANAGER | 2450.00 |
| PRESIDENT | 5000.00 |
| SALESMAN | 1250.00 |
±----------±--------+

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 ;

±-------±----------±--------+
| 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 |
±-------±----------±--------+

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

select ename,deptno,min(sal) as minsal from emp where job = ‘manager’ group by deptno ;

±------±-------±--------+
| ename | deptno | minsal |
±------±-------±--------+
| CLARK | 10 | 2450.00 |
| JONES | 20 | 2975.00 |
| BLAKE | 30 | 2850.00 |
±------±-------±--------+

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

select ename,(sal + ifnull(comm,0) )*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 |
±-------±---------+

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

select e.ename ‘员工’,t.ename ‘领导’ from emp e join emp t on e.mgr = t.empno where t.sal>3000;

±------±-----+
| 员工 | 领导 |
±------±-----+
| JONES | KING |
| BLAKE | KING |
| CLARK | KING |
±------±-----+

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

select d.deptno,d.dname,d.loc,count(ename),ifnull (sum(e.sal),0) as sumsal

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;

±-------±-----------±--------±-------------±---------+
| deptno | dname | loc | count(ename) | sumsal |
±-------±-----------±--------±-------------±---------+
| 20 | RESEARCH | DALLAS | 5 | 10875.00 |
| 30 | SALES | CHICAGO | 6 | 9400.00 |
| 40 | OPERATIONS | BOSTON | 0 | 0.00 |
±-------±-----------±--------±-------------±---------+

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

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

mysql> select * from emp;
±------±-------±----------±-----±-----------±--------±--------±-------+
| EMPNO | ENAME | JOB | MGR | HIREDATE | SAL | COMM | DEPTNO |
±------±-------±----------±-----±-----------±--------±--------±-------+
| 7369 | SMITH | CLERK | 7902 | 1980-12-17 | 880.00 | NULL | 20 |
| 7499 | ALLEN | SALESMAN | 7698 | 1981-02-20 | 1760.00 | 300.00 | 30 |
| 7521 | WARD | SALESMAN | 7698 | 1981-02-22 | 1375.00 | 500.00 | 30 |
| 7566 | JONES | MANAGER | 7839 | 1981-04-02 | 3272.50 | NULL | 20 |
| 7654 | MARTIN | SALESMAN | 7698 | 1981-09-28 | 1375.00 | 1400.00 | 30 |
| 7698 | BLAKE | MANAGER | 7839 | 1981-05-01 | 3135.00 | NULL | 30 |
| 7782 | CLARK | MANAGER | 7839 | 1981-06-09 | 2695.00 | NULL | 10 |
| 7788 | SCOTT | ANALYST | 7566 | 1987-04-19 | 3300.00 | NULL | 20 |
| 7839 | KING | PRESIDENT | NULL | 1981-11-17 | 5500.00 | NULL | 10 |
| 7844 | TURNER | SALESMAN | 7698 | 1981-09-08 | 1650.00 | 0.00 | 30 |
| 7876 | ADAMS | CLERK | 7788 | 1987-05-23 | 1210.00 | NULL | 20 |
| 7900 | JAMES | CLERK | 7698 | 1981-12-03 | 1045.00 | NULL | 30 |
| 7902 | FORD | ANALYST | 7566 | 1981-12-03 | 3300.00 | NULL | 20 |
| 7934 | MILLER | CLERK | 7782 | 1982-01-23 | 1430.00 | NULL | 10 |
±------±-------±----------±-----±-----------±--------±--------±-------+