MySQL学习34道作业题

谁践踏了优雅 2023-07-17 12:56 2阅读 0赞

# MySQL学习34道作业题 #

### 文章目录 ###

*  MySQL学习34道作业题
 *   *  0 练习数据
     *  1 取得每个部门最高薪水的人员名称
     *  2 哪些人的薪水在部门平均薪水之上
     *  3 取得部门中所有人的平均的薪水等级
     *  4 不准使用组函数Max，给出最高薪水（给出两种解决方案）
     *  5 取得平均薪水最高的部门和部门编号
     *  6 取得平均薪水最高的部门名称
     *  7 求平均薪水的等级最高的部门的部门名称
     *  8 取得比普通员工（员工代码没在mgr字段出现的）最高薪水更高的领导人姓名
     *  9 取得薪水最高的前五名员工
     *  10 取得薪水最高的第六到第十名员工
     *  11 取得最后入职的5名员工
     *  12 取得每个薪水等级有多少员工
     *  13 面试题
     *  14 列出所有员工及领导的姓名
     *  15 列出受雇日期早于其上级的所有员工的编号、姓名、部门名称
     *  16 列出部门名称和这些部门的员工信息，同时列出没有员工的部门
     *  17 列出至少有5名员工的所有部门
     *  18 列出薪水比simith多的所有员工信息
     *  19 列出所有岗位为clerk的姓名及部门名称，部门人数
     *  20 列出最低薪水大于1500的各种工作及从事此工作的全部雇员人数
     *  21 列出在部门sales工作的员工的姓名，假定不知道销售部的部门编号
     *  22 列出薪水高于公司平均薪水的所有员工，所在部门，上级领导，薪水等级
     *  23 列出与scott从事相同工作的所有员工及部门名称
     *  24 列出薪水等于部门30中员工的薪水的其他员工的姓名和薪水
     *  25 列出薪水高于部门30的全部员工的员工姓名，薪水，部门名称
     *  26 列出在每个部门工作的员工数量，平均工资和平均服务期限
     *  27 列出所有员工的姓名、部门名称、工资
     *  28 列出所有部门的详细信息和人数
     *  29 列出各种工作的最低工资以及从事此工作的雇员姓名
     *  30 列出各个部门的manager的最低薪水
     *  31 列出员工的年工资，按年薪从低到高排序
     *  32 求出员工领导的薪水超过3000的员工姓名和领导姓名
     *  33 求出部门名称中，带有‘s’字符的部门员工的工资合计，部门人数
     *  34 给任职时间超过30年的员工加薪10%

## 0 练习数据 ##

sql脚本文件：

drop table if exists dept;
    drop table if exists salgrade;
    drop table if exists emp;

create table dept(  
deptno int(10) primary key,  
dname varchar(14),  
loc varchar(13)  
);

create table salgrade(  
grade int(11),  
losal int(11),  
hisal int(11)  
);

create table emp(  
empno int(4) primary key,  
ename varchar(10),  
job varchar(9),  
mgr int(4),  
hiredate date,  
sal double(7,2),  
comm double(7,2),  
deptno int(2)  
);

insert into dept(deptno,dname,loc) values(10,‘ACCOUNTING’,‘NEW YORK’);  
insert into dept(deptno,dname,loc) values(20,‘RESEARCHING’,‘DALLAS’);  
insert into dept(deptno,dname,loc) values(30,‘SALES’,‘CHICAGO’);  
insert into dept(deptno,dname,loc) values(40,‘OPERATIONS’,‘BOSTON’);

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

insert into emp(empno,ename,job,mgr,hiredate,sal,comm,deptno)  
values(7369,‘SIMITH’,‘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,null,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);

select  from dept;  
select  from salgrade;  
select \* from emp;

dept表格

salgrade表格

mysql> select * from salgrade;
    +-------+-------+-------+
    | grade | losal | hisal |
    +-------+-------+-------+
    |     1 |   700 |  1200 |
    |     2 |  1201 |  1400 |
    |     3 |  1401 |  2000 |
    |     4 |  2001 |  3000 |
    |     5 |  3001 |  5000 |
    +-------+-------+-------+

emp表格

mysql> select * from emp;
    +-------+--------+-----------+------+------------+---------+---------+--------+
    | empno | ename  | job       | mgr  | hiredate   | sal     | comm    | deptno |
    +-------+--------+-----------+------+------------+---------+---------+--------+
    |  7369 | SIMITH | CLERK     | 7902 | 1980-12-17 |  800.00 |    NULL |     20 |
    |  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 |    NULL |     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 |
    +-------+--------+-----------+------+------------+---------+---------+--------+

**说明：本练习基于MySQL学习视频的资料，[B站视频链接][B]，部分答案与视频相同，部分是我自己写的，有更好的解决方法欢迎留言。**

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

先取得每个部门的最高薪水，然后找出和此薪水和部门编号相同的人。

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

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

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

先求出每个部门的平均薪水

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

然后找出部门中大于其平均薪水的人

mysql> select a.deptno,a.ename,a.sal,b.avgsal from emp a join (select deptno,avg(sal) as avgsal from emp group by deptno) b on a.sal>b.avgsal and a.deptno=b.deptno;
    +--------+-------+---------+-------------+
    | deptno | ename | sal     | avgsal      |
    +--------+-------+---------+-------------+
    |     30 | ALLEN | 1600.00 | 1566.666667 |
    |     20 | JONES | 2975.00 | 2175.000000 |
    |     30 | BLAKE | 2850.00 | 1566.666667 |
    |     20 | SCOTT | 3000.00 | 2175.000000 |
    |     10 | KING  | 5000.00 | 2916.666667 |
    |     20 | FORD  | 3000.00 | 2175.000000 |
    +--------+-------+---------+-------------+

## 3 取得部门中所有人的平均的薪水等级 ##

先计算每个人的薪水等级

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

然后根据部门分组，由于使用了分组函数，所以只能显示参加分组的字段和分组函数

mysql> select a.deptno,avg(grade) as AvgSalGrade from emp a join salgrade b on a.sal between b.losal and b.hisal group by a.deptno;
    +--------+-------------+
    | deptno | AvgSalGrade |
    +--------+-------------+
    |     20 |      2.8000 |
    |     30 |      2.5000 |
    |     10 |      3.6667 |
    +--------+-------------+

## 4 不准使用组函数Max，给出最高薪水（给出两种解决方案） ##

方案一，按照薪水降序排列，取第一个

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

方案二，使用自连接。

mysql> select ename,sal from emp where sal not in 
    (select a.sal from emp a join emp b on a.sal<b.sal);#最大值不小于表中任何一个工资，所以不会出现在此临时表中
    +-------+---------+
    | ename | sal     |
    +-------+---------+
    | KING  | 5000.00 |
    +-------+---------+

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

方案一：先取得每个部门的平均薪水

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

再取得最高的平均薪水

mysql> select avg(sal) as avgsal from emp group by deptno order by avgsal desc limit 1; 
    +-------------+
    | avgsal      |
    +-------------+
    | 2916.666667 |
    +-------------+
    
     
      
     
      
       1
       2
       3
       4
       5
       6

然后取得和最高平均薪水相同的部门（因为可能有多个部门并列最高）

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

方案二：与方案一类似，用max取得最高平均薪水

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

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

类似上题，将部门表与其连接

mysql> select b.dname,avg(a.sal) as avgsal from emp a join dept b on a.deptno=b.deptno group by b.dname having avgsal=(select max(t.avgsal) from (select avg(sal) as avgsal from emp group by deptno) t);
    +------------+-------------+
    | dname      | avgsal      |
    +------------+-------------+
    | ACCOUNTING | 2916.666667 |
    +------------+-------------+

## 7 求平均薪水的等级最高的部门的部门名称 ##

先求各部门平均薪水的等级

select a.deptno,a.avgsal,b.grade from (select deptno,avg(sal) as avgsal from emp group by deptno) a join salgrade b on avgsal between losal and hisal;
    +--------+-------------+-------+
    | deptno | avgsal      | grade |
    +--------+-------------+-------+
    |     20 | 2175.000000 |     4 |
    |     30 | 1566.666667 |     3 |
    |     10 | 2916.666667 |     4 |
    +--------+-------------+-------+

取得最高的等级

mysql> select b.grade from (select deptno,avg(sal) as avgsal from emp group by deptno) a join salgrade b on avgsal between losal and hisal order by b.grade desc limit 1;
    +-------+
    | grade |
    +-------+
    |     4 |
    +-------+

显示平均薪水等级等于最高等级的部门名称

mysql> select d.dname,c.avgsal,c.grade
    from
    	(select a.deptno,a.avgsal,b.grade
         	from
         		(select deptno,avg(sal) as avgsal from emp group by deptno) a
         	join
         		salgrade b
         	on
         		avgsal between losal and hisal) c
    join
    	dept d
    on
    	c.deptno=d.deptno and c.grade=
    	#最高等级为4
    		(select b.grade from (select deptno,avg(sal) as avgsal from emp group by deptno) a join salgrade b on avgsal between losal and hisal order by b.grade desc limit 1);
    +-------------+-------------+-------+
    | dname       | avgsal      | grade |
    +-------------+-------------+-------+
    | ACCOUNTING  | 2916.666667 |     4 |
    | RESEARCHING | 2175.000000 |     4 |
    +-------------+-------------+-------+

## 8 取得比普通员工（员工代码没在mgr字段出现的）最高薪水更高的领导人姓名 ##

先取出普通员工的最高薪水，注意mgr字段里有null，不能直接使用not in 语句

mysql> select max(sal) as cmsal from emp where empno not in (select distinct mgr from emp where mgr is not null);
    +---------+
    | cmsal   |
    +---------+
    | 1600.00 |
    +---------+

取得所有领导人的姓名和薪水

mysql> select ename,sal from emp where empno in (select distinct mgr from emp);
    +-------+---------+
    | ename | sal     |
    +-------+---------+
    | FORD  | 3000.00 |
    | BLAKE | 2850.00 |
    | KING  | 5000.00 |
    | JONES | 2975.00 |
    | SCOTT | 3000.00 |
    | CLARK | 2450.00 |
    +-------+---------+

然后取得结果

mysql> select ename,sal from emp where empno in (select distinct mgr from emp) and sal>(select max(sal) as cmsal 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 取得薪水最高的前五名员工 ##

mysql> 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 取得薪水最高的第六到第十名员工 ##

mysql> 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 |   NULL |     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名员工 ##

mysql> 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 取得每个薪水等级有多少员工 ##

mysql> select s.grade,count(t.ename) as count from emp t join salgrade s on t.sal between s.losal and s.hisal group by s.grade;
    +-------+-------+
    | grade | count |
    +-------+-------+
    |     1 |     3 |
    |     3 |     2 |
    |     2 |     3 |
    |     4 |     5 |
    |     5 |     1 |
    +-------+-------+

## 13 面试题 ##

三张表：

*  学生表S：学号SNO，姓名SNAME
 *  课程表C：课号CNO，课程名CNAME，课程老师CTEACHER
 *  选课表SC：学号SNO，课号CNO，分数SCGRADE

1.显示没选”黎明“老师课的学生

#”黎明“老师的课号
    select cno from c where cteacher='黎明';
    #没选其课学生的学号
    select distinct sno from sc where sno not in (select cno from c where cteacher='黎明');
    #没选课学生的姓名
    select sname from s where sno in (select distinct sno from sc where sno not in (select cno from c where cteacher='黎明'));

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

#2门及以上不及格学生的学号
    select sno from sc where scgrade<60 group by sno having count(*)>=2;
    #不及格学生姓名
    select sname,sno from s where sno in (select sno from sc where scgrade<60 group by sno having count(*)>=2);
    #学生的平均成绩
    select sno,avg(scgrade) avggrade from sc group by sno;
    #联合姓名和平均成绩
    select a.sno,a.sname,b.avggrade from (select sname,sno from s where sno in (select sno from sc where scgrade<60 group by sno having count(*)>=2)) a join (select sno,avg(scgrade) avggrade from sc group by sno) b on a.sno=b.sno;

3.学过1号课程和2号课程的所有学生的姓名

#选过1号课程和2号课程的学生学号
    select distinct sno from sc where cno=1 or cno=2;
    #从s表中取出其姓名
    select sno,sname from s where sno in (select distinct sno from sc where cno=1 or cno=2);
    
     
      
     
      
       1
       2
       3
       4

最后补充根据视频数据编写的sql文件

create table c(
    	cno int(2) primary key auto_increment,
    	cname varchar(32),
    	cteacher varchar(16)
    	);

create table s(  
sno int(2) primary key auto\_increment,  
sname varchar(16)  
);

create table sc(  
sno int(2),  
cno int(2),  
scgrade int(3),  
primary key(sno,cno)  
);

insert into c(cname,cteacher)values(‘语文’,‘张老师’);  
insert into c(cname,cteacher)values(‘政治’,‘王老师’);  
insert into c(cname,cteacher)values(‘英语’,‘李老师’);  
insert into c(cname,cteacher)values(‘数学’,‘赵老师’);  
insert into c(cname,cteacher)values(‘物理’,‘黎明’);

insert into s(sname)values(‘学生1’);  
insert into s(sname)values(‘学生2’);  
insert into s(sname)values(‘学生3’);  
insert into s(sname)values(‘学生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,2,80);

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

最高的领导的领导为null，因此需要使用左连接

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

mysql> select e.empno,e.ename,e.hiredate,l.ename,l.hiredate from emp e join emp l on e.mgr=l.empno and e.hiredate<l.hiredate;
    +-------+--------+------------+-------+------------+
    | empno | ename  | hiredate   | ename | hiredate   |
    +-------+--------+------------+-------+------------+
    |  7369 | SIMITH | 1980-12-17 | FORD  | 1981-12-03 |
    |  7499 | ALLEN  | 1981-02-20 | BLAKE | 1981-05-01 |
    |  7521 | WARD   | 1981-02-22 | BLAKE | 1981-05-01 |
    |  7566 | JONES  | 1981-04-02 | KING  | 1981-11-17 |
    |  7698 | BLAKE  | 1981-05-01 | KING  | 1981-11-17 |
    |  7782 | CLARK  | 1981-06-09 | KING  | 1981-11-17 |
    +-------+--------+------------+-------+------------+

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

有部门没有员工，所以需要使用左连接或右连接

mysql> select d.dname,e.* from dept d left join emp e on d.deptno=e.deptno;
    +-------------+-------+--------+-----------+------+------------+---------+---------+--------+
    | dname       | empno | ename  | job       | mgr  | hiredate   | sal     | comm    | deptno |
    +-------------+-------+--------+-----------+------+------------+---------+---------+--------+
    | RESEARCHING |  7369 | SIMITH | CLERK     | 7902 | 1980-12-17 |  800.00 |    NULL |     20 |
    | SALES       |  7499 | ALLEN  | SALESMAN  | 7698 | 1981-02-20 | 1600.00 |  300.00 |     30 |
    | SALES       |  7521 | WARD   | SALESMAN  | 7698 | 1981-02-22 | 1250.00 |  500.00 |     30 |
    | RESEARCHING |  7566 | JONES  | MANAGER   | 7839 | 1981-04-02 | 2975.00 |    NULL |     20 |
    | SALES       |  7654 | MARTIN | SALESMAN  | 7698 | 1981-09-28 | 1250.00 | 1400.00 |     30 |
    | SALES       |  7698 | BLAKE  | MANAGER   | 7839 | 1981-05-01 | 2850.00 |    NULL |     30 |
    | ACCOUNTING  |  7782 | CLARK  | MANAGER   | 7839 | 1981-06-09 | 2450.00 |    NULL |     10 |
    | RESEARCHING |  7788 | SCOTT  | ANALYST   | 7566 | 1987-04-19 | 3000.00 |    NULL |     20 |
    | ACCOUNTING  |  7839 | KING   | PRESIDENT | NULL | 1981-11-17 | 5000.00 |    NULL |     10 |
    | SALES       |  7844 | TURNER | SALESMAN  | 7698 | 1981-09-08 | 1500.00 |    NULL |     30 |
    | RESEARCHING |  7876 | ADAMS  | CLERK     | 7788 | 1987-05-23 | 1100.00 |    NULL |     20 |
    | SALES       |  7900 | JAMES  | CLERK     | 7698 | 1981-12-03 |  950.00 |    NULL |     30 |
    | RESEARCHING |  7902 | FORD   | ANALYST   | 7566 | 1981-12-03 | 3000.00 |    NULL |     20 |
    | ACCOUNTING  |  7934 | MILLER | CLERK     | 7782 | 1982-01-23 | 1300.00 |    NULL |     10 |
    | OPERATIONS  |  NULL | NULL   | NULL      | NULL | NULL       |    NULL |    NULL |   NULL |
    +-------------+-------+--------+-----------+------+------------+---------+---------+--------+

## 17 列出至少有5名员工的所有部门 ##

mysql> select d.dname,count(e.empno) as number from dept d join emp e on d.deptno=e.deptno group by d.deptno having number>=5;
    +-------------+--------+
    | dname       | number |
    +-------------+--------+
    | RESEARCHING |      5 |
    | SALES       |      6 |
    +-------------+--------+

## 18 列出薪水比simith多的所有员工信息 ##

mysql> select * from emp where sal>(select sal from emp where ename='simith');
    +-------+--------+-----------+------+------------+---------+---------+--------+
    | 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 |    NULL |     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的姓名及部门名称，部门人数 ##

先取出每个部门的人数

mysql> select d.deptno,d.dname,count(e.empno) as numbers from dept d join emp e on d.deptno=e.deptno group by e.deptno;
    +--------+-------------+---------+
    | deptno | dname       | numbers |
    +--------+-------------+---------+
    |     20 | RESEARCHING |       5 |
    |     30 | SALES       |       6 |
    |     10 | ACCOUNTING  |       3 |
    +--------+-------------+---------+

然后将上表作为临时表与emp表连接

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

先取出最低薪水大于1500的工作

mysql> select job,min(sal) as minsal from emp group by job having minsal>1500;
    +-----------+---------+
    | job       | minsal  |
    +-----------+---------+
    | MANAGER   | 2450.00 |
    | ANALYST   | 3000.00 |
    | PRESIDENT | 5000.00 |
    +-----------+---------+

找出工作为这些职业的职员

mysql> select a.*,b.minsal from emp a join (select job,min(sal) as minsal from emp group by job having minsal>1500) b where a.job=b.job;
    +-------+-------+-----------+------+------------+---------+------+--------+---------+
    | empno | ename | job       | mgr  | hiredate   | sal     | comm | deptno | minsal  |
    +-------+-------+-----------+------+------------+---------+------+--------+---------+
    |  7566 | JONES | MANAGER   | 7839 | 1981-04-02 | 2975.00 | NULL |     20 | 2450.00 |
    |  7698 | BLAKE | MANAGER   | 7839 | 1981-05-01 | 2850.00 | NULL |     30 | 2450.00 |
    |  7782 | CLARK | MANAGER   | 7839 | 1981-06-09 | 2450.00 | NULL |     10 | 2450.00 |
    |  7788 | SCOTT | ANALYST   | 7566 | 1987-04-19 | 3000.00 | NULL |     20 | 3000.00 |
    |  7839 | KING  | PRESIDENT | NULL | 1981-11-17 | 5000.00 | NULL |     10 | 5000.00 |
    |  7902 | FORD  | ANALYST   | 7566 | 1981-12-03 | 3000.00 | NULL |     20 | 3000.00 |
    +-------+-------+-----------+------+------------+---------+------+--------+---------+

最后计数

mysql> select t.job,count(t.empno),t.minsal from (select a.*,b.minsal from emp a join (select job,min(sal) as minsal from emp group by job having minsal>1500) b where a.job=b.job) t group by job;
    +-----------+----------------+---------+
    | job       | count(t.empno) | minsal  |
    +-----------+----------------+---------+
    | MANAGER   |              3 | 2450.00 |
    | ANALYST   |              2 | 3000.00 |
    | PRESIDENT |              1 | 5000.00 |
    +-----------+----------------+---------+

## 21 列出在部门sales工作的员工的姓名，假定不知道销售部的部门编号 ##

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

需要使用多表连接，将三张表连接在一起。

先取出公司平均薪水

mysql> select avg(sal) as avgsal from emp;
    +-------------+
    | avgsal      |
    +-------------+
    | 2073.214286 |
    +-------------+
    
     
      
     
      
       1
       2
       3
       4
       5
       6

然后多表连接

mysql> select e.ename,e.sal,d.dname,s.grade
    			from
    				emp e
    			join
    				dept d
    			on
    				e.deptno=d.deptno
    			join
    				salgrade s
    			on
    				e.sal between s.losal and s.hisal
    			having
    				sal>(select avg(sal) as avgsal from emp);
    +-------+---------+-------------+-------+
    | ename | sal     | dname       | grade |
    +-------+---------+-------------+-------+
    | JONES | 2975.00 | RESEARCHING |     4 |
    | BLAKE | 2850.00 | SALES       |     4 |
    | CLARK | 2450.00 | ACCOUNTING  |     4 |
    | SCOTT | 3000.00 | RESEARCHING |     4 |
    | KING  | 5000.00 | ACCOUNTING  |     5 |
    | FORD  | 3000.00 | RESEARCHING |     4 |
    +-------+---------+-------------+-------+

## 23 列出与scott从事相同工作的所有员工及部门名称 ##

先找出scott的岗位

mysql> select job from emp where ename='scott';
    +---------+
    | job     |
    +---------+
    | ANALYST |
    +---------+
    
     
      
     
      
       1
       2
       3
       4
       5
       6

然后找出从事此工作的员工并与部门表连接显示部门名，注意排除scott本人

mysql> select e.ename,d.dname from emp e join dept d where e.deptno=d.deptno and e.job=(select job from emp where ename='scott') and e.ename<>'scott';
    +-------+-------------+
    | ename | dname       |
    +-------+-------------+
    | FORD  | RESEARCHING |
    +-------+-------------+
    
     
      
     
      
       1
       2
       3
       4
       5
       6

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

取出部门30的各个员工的薪水

mysql> select sal from emp where deptno=30;
    +---------+
    | sal     |
    +---------+
    | 1600.00 |
    | 1250.00 |
    | 1250.00 |
    | 2850.00 |
    | 1500.00 |
    |  950.00 |
    +---------+

然后找出其他部门中是否有员工工资与上表中数据相同

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

结果为空

## 25 列出薪水高于部门30的全部员工的员工姓名，薪水，部门名称 ##

先取出部门30的最高薪水

mysql> select sal from emp where deptno=30 order by sal desc limit 1;
    +---------+
    | sal     |
    +---------+
    | 2850.00 |
    +---------+
    
     
      
     
      
       1
       2
       3
       4
       5
       6

然后连接员工表和部门表

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

先取出每个部门的员工数量

mysql> select d.deptno,count(e.ename)as number from dept d left join emp e on d.deptno=e.deptno group by d.deptno;
    +--------+--------+
    | deptno | number |
    +--------+--------+
    |     20 |      5 |
    |     30 |      6 |
    |     10 |      3 |
    |     40 |      0 |
    +--------+--------+

平均薪水

mysql> select d.deptno,count(e.ename)as number,ifnull(avg(e.sal),0) as avgsal from dept d left join emp e on d.deptno=e.deptno group by d.deptno;
    +--------+--------+-------------+
    | deptno | number | avgsal      |
    +--------+--------+-------------+
    |     20 |      5 | 2175.000000 |
    |     30 |      6 | 1566.666667 |
    |     10 |      3 | 2916.666667 |
    |     40 |      0 |    0.000000 |
    +--------+--------+-------------+

使用to\_days()将时间转换为公元元年到某时间点的天数

mysql> select d.deptno,
    		count(e.ename)as number,ifnull(avg(e.sal),0) as avgsal,
    		ifnull(avg((to_days(now())-to_days(e.hiredate))/365),0) as avgtime
    	from
    		dept d
    	left join
    		emp e
    	on
    		d.deptno=e.deptno
    	group by
    		d.deptno;
    +--------+--------+-------------+-------------+
    | deptno | number | avgsal      | avgtime     |
    +--------+--------+-------------+-------------+
    |     20 |      5 | 2175.000000 | 35.57588000 |
    |     30 |      6 | 1566.666667 | 37.84750000 |
    |     10 |      3 | 2916.666667 | 37.54886667 |
    |     40 |      0 |    0.000000 |  0.00000000 |
    +--------+--------+-------------+-------------+

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

[B]: https://www.bilibili.com/video/av14716238