开发: C++知识库 Java知识库 JavaScript Python PHP知识库人工智能区块链大数据移动开发嵌入式开发工具数据结构与算法开发测试游戏开发网络协议系统运维
教程: HTML教程 CSS教程 JavaScript教程 Go语言教程 JQuery教程 VUE教程 VUE3教程 Bootstrap教程 SQL数据库教程 C语言教程 C++教程 Java教程 Python教程 Python3教程 C#教程
数码: 电脑笔记本显卡显示器固态硬盘硬盘耳机手机 iphone vivo oppo 小米华为单反装机图拉丁

-> 大数据 -> MySQL练习题（附带答案和结果） -> 正文阅读

[大数据]MySQL练习题（附带答案和结果）

所用到的表的SQL语句信息

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;

在这里插入图片描述

问题

1、取得每个部门最高薪水的人员名称
2、哪些人的薪水再部门平均薪资以上
3、取得部门中（所有人的）平均薪水等级
4、不使用组函数max，取得最高薪资（两种方案）
5、取得平均薪资最高的部门的部门编号（两种方案）
6、取得平均薪资最高的部门的部门名称
7、求平均薪资的等级最低的部门的部门名称
8、取得比普通员工(员工代码没有在 mgr 字段上出现的)的最高薪水还要高的领导人姓名
9、取得薪资最高的前5名员工
10、取薪资最高的第六到第十名员工
11、取得最后入职的五名员工
12、取得每个薪资等级有多少员工
13、
有 3 个表 S(学生表)，C（课程表），SC（学生选课表）
S（SNO，SNAME）代表（学号，姓名）
C（CNO，CNAME，CTEACHER）代表（课号，课名，教师）
SC（SNO，CNO，SCGRADE）代表（学号，课号，成绩）
问题：
1），找出没选过“黎明”老师的所有学生姓名。
2），列出 2 门以上（含 2 门）不及格学生姓名及平均成绩。
3），即学过 1 号课程又学过 2 号课所有学生的姓名。

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；

14、请列出所有员工及领导姓名
15、列出受雇日期早于其直接上级的所有员工的编号、姓名和部门名称
16、列出部门名称和这些部门的员工信息，同时列出那些没有员工的部门
17、列出至少有五个员工的部门
18、列出薪资比"SMITH"多的所有员工信息
19、列出所有"CLERKK"的姓名、部门名称以及部门人数
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%

问题加答案（附带查询结果）

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

// 先查询出每个部门的最高薪资
select deptno,max(sal) as maxsal from emp group by deptno;

// 将上述查询结果当作一张临时表与emp表进行连接查询
select
    e.ename,t.deptno,t.maxsal
from
    emp e
join 
    (select deptno,max(sal) as maxsal from emp group by deptno) t
on
    t.deptno=e.deptno and t.maxsal=e.sal;

在这里插入图片描述
2、哪些人的薪水在部门平均薪资以上

// 先查询出每个部门的平均薪资avgsal
select deptno,avg(sal) as avgsal from emp group by deptno; 
// 再查询sal>avgsal的员工信息
select 
    e.ename,sal,t.deptno,t.avgsal
from
    emp e
join 
    (select deptno,avg(sal) as avgsal from emp group by deptno) t
on 
    e.deptno=t.deptno and sal>avgsal;

在这里插入图片描述
3、取得部门中（所有人的）平均薪水的等级

// 先取得每个部门的平均薪资avgsal
select deptno,avg(sal) as avgsal from emp group by deptno;
// 再去查询avgsal的薪资等级
select 
    t.deptno,t.avgsal,s.grade
from 
    salgrade s
join
    (select deptno,avg(sal) as avgsal from emp group by deptno) t
on
    avgsal between s.losal and s.hisal;

在这里插入图片描述
4、不使用组函数max，取得最高薪资（两种方案）

// 第一种，按降序排列薪资，取第一条
select 
    sal as maxsal
from 
    emp
order by
   sal desc
limit
    1;

在这里插入图片描述

/// 第二种，自连接，查询出a表中sal小于b表中sal并去重
select distinct a.sal from emp a join emp b on a.sal < b.sal;

// 因为最大工资是不小于任何工资的，所以我们可以利用这一点来获取最大薪资
select 
    sal 
from 
    emp 
where 
    sal not in (select distinct a.sal from emp a join emp b on a.sal < b.sal);

在这里插入图片描述
5、取得平均薪资最高的部门的部门编号（两种方案）

第一种：

// 取得部门平均薪资
select deptno,avg(sal) as avgsal from emp group by deptno;
// 降序排列后使用分页查询取第一个
select 
    deptno,avg(sal) as avgsal 
from 
    emp 
group by 
    deptno 
order by 
    avgsal desc 
limit 1;

在这里插入图片描述

/*第二种：
使用max分组函数
取得部门平均薪资*/
select deptno,avg(sal) as avgsal from emp group by deptno;

// 使用max分组函数取得最大值
select max(t.avgsal) from (select deptno,avg(sal) as avgsal from emp group by deptno) t;

// 最后查询出想要的结果
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);

在这里插入图片描述
6、取得平均薪资最高的部门的部门名称

// 取得部门平均薪资
select deptno,avg(sal) as avgsal from emp group by deptno;

// 降序排列后使用分页查询取第一个即为最高薪资的部门，获取其部门编号和平均薪资
select
    deptno,avg(sal) as avgsal
from
    emp
group by
    deptno
order by
    avgsal desc
limit 1;
// 连接查询，与salgrade表进行内连接，查询出对应的部门名
select
    e.deptno,avg(e.sal) as avgsal,d.dname
from
    emp e
join 
    dept d
on
    e.deptno=d.deptno
group by
    e.deptno
order by
    avgsal desc
limit 1;

在这里插入图片描述
7、求平均薪资的等级最低的部门的部门名称

注意：平均薪资等级最低的部门可能不止一个

// 第一步：找出每个部门的平均薪资
select deptno,avg(sal) as avgsal from emp group by deptno;

// 第二步：找出每个部门平均薪资的等级并创建视图view_avgsal_grade    
create 
    view view_avgsal_grade    
as
    select
        t.*,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;
// 第三步：查出最低薪资等级并创建视图view_min_grade
create 
    view view_min_grade 
as
    select 
        min(grade)
    as 
        mingrade 
    from 
        view_avgsal_grade;
// 第四步：查询出平均薪资等级等于最低薪资等级的信息并创建视图view_min_grade_deptno
create
    view view_min_grade_deptno
as
    select
        vag.deptno,vag.grade
    from
        view_avgsal_grade vag
    join
        view_min_grade vmg
    on
        vag.grade=vmg.mingrade;

// 第五步：视图view_min_grade_deptno与表dept进行连接，查询出部门名
select
    vmgd.*,d.dname
from
    view_min_grade_deptno vmgd
join
    dept d
on 
    vmgd.deptno=d.deptno;

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

// 查询出在mgr字段出现过的员工代码并去重,则员工编号不在(not in)其范围中的员工为普通员工(使用not in时，需要排除小括号中的null)
select distinct mgr from emp is not null;
// 找出普通员工的最高薪资,并创建视图view_max_sal
create 
    view view_max_sal 
as
    select 
        max(sal) as maxsal
    from 
        emp 
    where 
        empno not in (select distinct mgr from emp where mgr is not null);    
// 查出薪资大于普通员工的最高薪资的人的信息即为所求
select
    e.ename,e.sal
from
    emp e
join
    view_max_sal v
on
    e.sal>v.maxsal;

在这里插入图片描述
9、取得薪资最高的前5名员工

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

在这里插入图片描述
10、取薪资最高的第六到第十名员工

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

在这里插入图片描述
11、取得最后入职的五名员工

// 按入职时间分页降序排序，取前五条
select 
    ename,hiredate
from
    emp
order by
    hiredate desc
limit 0,5;

在这里插入图片描述
12、取得每个薪资等级有多少员工

// 找出每个员工的薪资等级
select
    e.ename,s.grade
from
    emp e
join
    salgrade s
on
 e.sal between s.losal and s.hisal;

// 按工资等级分组
select
    s.grade,count(s.grade)
from    
    emp e
join
    salgrade s
on
    e.sal between s.losal and s.hisal
group by
    grade;

在这里插入图片描述
13、（面试题）
有 3 个表 S(学生表)，C（课程表），SC（学生选课表）
S（SNO，SNAME）代表（学号，姓名）
C（CNO，CNAME，CTEACHER）代表（课号，课名，教师）
SC（SNO，CNO，SCGRADE）代表（学号，课号，成绩）

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），找出没选过“黎明”老师的所有学生姓名。

// 先查询出每个学生所选的课程
select  sc.sno,c.cno,c.cname,c.cteacher from sc join c on sc.cno=c.cno;

// 找出选过"黎明"老师的课的学生的编号
select s.sno from s join (select  sc.sno,c.cno,c.cname,c.cteacher from sc join c on sc.cno=c.cno) t on s.sno=t.sno where t.cteacher='黎明';

在这里插入图片描述

// 得到选择了"黎明"老师课的学生的编号之后，使用not in查询出没有选择的学生的姓名
select 
    s.sname 
from 
    s 
where 
    sno not in (select s.sno from s join (select  sc.sno,c.cno,c.cname,c.cteacher from sc join c on sc.cno=c.cno) t on s.sno=t.sno where t.cteacher='黎明');

在这里插入图片描述
2，列出 2 门以上（含 2 门）不及格学生姓名及平均成绩。

// 查询出各学生的不及格的门数
select s.sno,count(sc.scgrade) as countfail from s join sc on s.sno=sc.sno where sc.scgrade<60 group by s.sno;

// 找出不及格门数大于两门的学生的姓名，编号,并创建视图view_sno_sname 
create view view_sno_sname as
select s.sno,s.sname from s join (select s.sno,count(sc.scgrade) as countfail from s join sc on s.sno=sc.sno where sc.scgrade<60 group by s.sno) t on s.sno=t.sno where t.countfail >= 2;

// 找到对应条件的学生编号后连接sc表进行查询
select 
    vs.sname,avg(sc.scgrade) as avgsagrade
from
    sc
join
    view_sno_sname vs
on
    vs.sno=sc.sno
group by
    vs.sname;

在这里插入图片描述
3，既学过 1 号课程又学过 2 号课所有学生的姓名。

// 查询出各个学生所选的课程
select sno,cno from sc;

// 找出学了1或2的课程的学生
select s.sno,t.cno from s join (select sno,cno from sc) t on s.sno=t.sno where t.cno=1 or t.cno=2;

// 按学号分组，找出课程1和2都学的学生
select 
    s.sname
from 
    s 
join 
    (select sno,cno from sc) t       
on 
    s.sno=t.sno 
where 
    t.cno=1 or t.cno=2                
group by 
    s.sname
having 
    count(t.cno)=2;

在这里插入图片描述
14、请列出所有员工及领导姓名

// 左外连接
select
    a.ename '员工' , ifnull(b.ename,'没有领导') '领导'
from
    emp a
left join
    emp b
on
    a.mgr=b.empno;

在这里插入图片描述

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

// 内连接，找出有领导的员工
select
    a.ename
from
    emp a
join
    emp b
on
    a.mgr=b.empno;

// 条件查询，找出受雇日期早于其领导的员工
select
    a.ename,a.hiredate,b.ename,b.hiredate,a.empno,d.dname
from
    emp a
join
    emp b
on
    a.mgr=b.empno
join
    dept d
on 
    a.deptno=d.deptno
where
    b.hiredate>a.hiredate;

在这里插入图片描述
16、列出部门名称和这些部门的员工信息，同时列出那些没有员工的部门

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

在这里插入图片描述

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

// 查询出员工数大于等于5 的部门的编号及人数
select
    deptno,count(*)
from
    emp
group by
    deptno
having
    count(*)>=5;

// 连接查询
select
    e.deptno,count(*),d.dname
from
    emp e
join
    dept d
on
    e.deptno=d.deptno
group by
    deptno
having
    count(*)>=5;

在这里插入图片描述
18、列出薪资比"SMITH"多的所有员工信息

// 先查出SMITH的工资
select sal from emp where ename='SMITH';
// 查出工资高于SMITH
select * from emp where sal>(select sal from emp where ename='SMITH');

在这里插入图片描述
19、列出所有JOB为"CLERK"的姓名、部门名称以及部门人数

// 查询job为"CLERK"的员工的姓名及部门编号并创建视图view_clerk
create view view_clerk as select ename,deptno from emp where job='clerk';

// 连接表，查出部门名并创建视图view_ename_deptno
create view 
    view_ename_deptno 
as
    select
        v.ename,v.deptno,d.dname
    from
        view_clerk v
    join
        dept d
    on
        v.deptno=d.deptno;
// 查找出每个部门的人数并创建视图view_deptcount
create view 
    view_deptcount 
as
    select 
        deptno,count(*) as deptcount 
    from 
        emp 
    group by 
        deptno;

// view_ename_deptno和view_deptcount连接查询
select
    ved.*,vd.deptcount
from 
    view_ename_deptno ved
join
    view_deptcount vd
on
    ved.deptno=vd.deptno

在这里插入图片描述
20、列出最低薪资大于1500的各种工作以及从事该工作的全部员工的信息

// 找出最低工资大于1500的工作
select job from emp group by job having min(sal)>1500;

// 查找出从事上述工作的人
select 
    e.*
from 
    emp e
join
    (select job from emp group by job having min(sal)>1500) t
on
    e.job=t.job;

在这里插入图片描述
21、列出在部门"SALES"工作的员工的姓名，假设不知道部门编号

select
    ename
from 
    emp
where
    deptno=(select deptno from dept where dname='SALES');

在这里插入图片描述
22、列出薪资高于公司平均薪资的所有员工姓名、所在部门、上级领导、工资等级

// 查出公司平均工资
select avg(sal) as avgsal from emp;

// 查出工资高于平均工资的员工的ename，deptno，mgr，empno,sal并创建视图view_t
create view view_t as
    select
        e.ename,e.deptno,e.mgr,e.empno,e.sal
    from
        emp e
    join
        (select avg(sal) as avgsal from emp) t
    on
        e.sal>t.avgsal;
// 连接emp表查上级领导，连接dept查部门名,连接salgrade表查工资等级
select
    t.ename,d.dname,e.ename,t.sal,s.grade
from
    view_t t
left join
    emp e
on
    t.mgr=e.empno
join
    dept d
on
    d.deptno=t.deptno
join
    salgrade s
on
    t.sal between s.losal and s.hisal;

在这里插入图片描述
23、列出与"SCOTT"从事相同工作的所有员工及部门名称

// 查询出SCOTT的工作
select job from emp where ename='scott';

// 查询
select
    e.ename,e.deptno,d.dname
from
    emp e
join
    (select job from emp where ename='scott') t
on
    e.job=t.job
join
    dept d
on
    d.deptno=e.deptno
where
    e.ename != 'scott';

在这里插入图片描述
24、列出薪资等于部门编号为30中员工的薪资的其他员工的姓名和薪资

select
    ename,sal
from
    emp
where
    sal in(select distinct sal from emp where deptno=30)
and
    deptno != 30;

在这里插入图片描述
25、列出薪资高于在部门编号为30工作的所有员工的薪资的员工姓名、薪资和部门名称

查询出部门编号为30的员工的最高薪资
select max(sal) maxsal from emp where deptno=30;

// 连接查询
select 
    e.ename,e.sal,d.dname
from
    emp e
join
    (select max(sal) maxsal from emp where deptno=30) t
on
    e.sal>t.maxsal
join
    dept d
on
    e.deptno=d.deptno;

在这里插入图片描述
26、列出在每个部门工作的员工数量，平均工资和平均服务期限

// 查询每个部门的员工数量
select d.deptno,count(e.ename) from emp e  right join dept d on e.deptno=d.deptno group by deptno;

// 查询每个部门的平均薪资
select d.deptno,ifnull(avg(sal),0) as avgsal from emp e right join dept d on e.deptno=d.deptno group by deptno;

// 查询每个部门的平均服务期限（timeStampDiff函数，计算两个时间的时间差：第一个参数是返回的类型，第二个参数是开始时间，第三个参数是结束时间）
select d.deptno,ifnull(avg(timeStampDiff(year,hiredate,now())),0) as avgtime from emp e right join dept d on e.deptno=d.deptno group by deptno;

在这里插入图片描述
27、列出所有员工的姓名、部门名称和工资

select
    e.ename,e.sal,d.dname
from
    emp e
join
    dept d
on
    e.deptno=d.deptno;

在这里插入图片描述
28、列出所有部门的详细信息和人数

select d.*,count(e.ename) from dept d left join emp e on d.deptno=e.deptno group by d.deptno;

在这里插入图片描述
29、列出不同工作的最低工资以及该工资的员工的信息

// 先查出每个岗位最低工资
select min(sal) as minsal,ename from emp group by job;

// 连接查询，找出job和sal都对应相等的员工得到其详细信息
select 
    e.* 
from 
    emp e 
join 
    (select min(sal) as minsal,job from emp group by job) t 
on 
    e.sal=t.minsal and e.job=t.job;

在这里插入图片描述
30、列出各个部门"MANAGER"的最低薪资

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

在这里插入图片描述
31、列出所有员工的年工资，按照年薪升序排列

select
    ename,(sal + ifnull(comm,0)) * 12 as yearsal
from 
    emp
order by
    yearsal asc;

在这里插入图片描述
32、列出员工领导的薪资超过3000的员工姓名和领导姓名

select
    a.ename '员工姓名',b.ename '领导姓名'
from 
    emp a
join     
    emp b
on
    a.mgr=b.empno
where
    b.sal>3000;

在这里插入图片描述
33、列出部门名称中带"S"字符的部门员工的工资合计和部门人数

// 查询出部门名称带S的部门
select deptno,dname from dept where dname like '%S%';

// 连接查询
select
   t.deptno,t.dname,ifnull(sum(e.sal),0) as sumsal,count(e.ename) countnum
from 
    emp e
right join
    (select deptno,dname from dept where dname like '%S%') t
on
    e.deptno=t.deptno
group by
    t.deptno;

在这里插入图片描述
34、给任职日期超过40年的员工加薪10%

// 查询任期超过40年的员工的信息
select ename,empno,hiredate,sal from emp where timeStampDiff(year,hiredate,now())>40;

// 加薪10%
update
    emp e
set
    e.sal=e.sal*1.1
where
    timeStampDiff(year,hiredate,now())>40;