SQL经典训练【1】

逃离我推掉我的手 2023-02-15 12:56 9阅读 0赞

>  [https://www.wkcto.com/course/121][https_www.wkcto.com_course_121]

> SQL经典训练【2】：[https://yuanyu.blog.csdn.net/article/details/106585513][https_yuanyu.blog.csdn.net_article_details_106585513]

--------------------

# **目录** #

> 1 取得薪水最高的前五名员工
> 
> 2 取得薪水最高的第六到第十名员工
> 
> 3 取得最后(也就是最大的日期)入职的5名员工
> 
> 4 取得每个薪水等级有多少员工
> 
> 5 取得每个部门最高薪水的人员名称
> 
> 6 哪些人的薪水在部门平均薪水之上
> 
> 7 取得部门中（所有人的）平均薪水等级
> 
> 8 不准用组函数（MAX），取得最高薪水（给出两种解决方案）
> 
> 9 取得平均薪水最高的部门的部门编号（至少两个方案）
> 
> 10 取得平均薪水最高的部门的部门名称
> 
> 11 求平均薪水的等级最低(高)的部门的部门名称
> 
> 12 取得比普通员工（员工代码没有在mgr上出现的）的最高薪水还要高的经理人姓名
> 
> 13 列出所有员工及领导的名字
> 
> 14 列出受雇日期早于其直接上级的所有员工编号、姓名、部门名称
> 
> 15 列出部门名称和这些部门的员工信息，同时列出那些没有员工的部门
> 
> 16 列出至少有5个员工的所有部门
> 
> 18 列出薪水比“smith”多的所有员工信息
> 
> 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%

--------------------

# 环境准备  #

DROP DATABASE IF EXISTS sql_practice;
    CREATE DATABASE sql_practice;
    USE sql_practice;

CREATE TABLE emp(
        empno    INT(4) NOT NULL COMMENT '员工编号',
        ename    VARCHAR(10) COMMENT '员工姓名',
        job      VARCHAR(9) COMMENT '职位',
        mgr      INT(4) COMMENT '经理',
        hiredate DATE DEFAULT NULL COMMENT '雇佣日期',
        sal      DOUBLE(7, 2) COMMENT '薪水',
        comm     DOUBLE(7, 2) COMMENT '津贴',
        deptno   INT(2) COMMENT '部门编号',
        PRIMARY KEY (empno)
    ) COMMENT '员工表';
    CREATE TABLE dept(
        deptno INT(2) NOT NULL COMMENT '部门编号',
        dname  VARCHAR(14) COMMENT '部门名字',
        loc    VARCHAR(13) COMMENT '位置',
        PRIMARY KEY (deptno)
    ) COMMENT '部门表';
    CREATE TABLE salgrade(
        grade INT COMMENT '等级',
        losal INT COMMENT '最高薪水',
        hisal INT COMMENT '最低薪水'
    ) COMMENT '薪水级别表';

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQwNzk0OTcz_size_16_color_FFFFFF_t_70][]

DESC emp ;

INSERT INTO emp (empno, ename, job, mgr, hiredate, sal, comm, deptno)
    VALUES (7369, 'smith', 'clerk', 7902, '1980-12-17', 800, NULL, 20),
           (7499, 'allen', 'salesman', 7698, '1981-02-20', 1600, 300, 30),
           (7521, 'ward', 'salesman', 7698, '1981-02-22', 1250, 500, 30),
           (7566, 'jones', 'manager', 7839, '1981-04-02', 2975, NULL, 20),
           (7654, 'martin', 'salesman', 7698, '1981-09-28', 1250, 1400, 30),
           (7698, 'blake', 'manager', 7839, '1981-05-01', 2850, NULL, 30),
           (7782, 'clark', 'manager', 7839, '1981-06-09', 2450, NULL, 10),
           (7788, 'scott', 'analyst', 7566, '1987-04-19', 3000, NULL, 20),
           (7839, 'king', 'president', NULL, '1981-11-17', 5000, NULL, 10),
           (7844, 'turner', 'salesman', 7698, '1981-09-08', 1500, 0, 30),
           (7876, 'adams', 'clerk', 7788, '1987-05-23', 1100, NULL, 20),
           (7900, 'james', 'clerk', 7698, '1981-12-03', 950, NULL, 30),
           (7902, 'ford', 'analyst', 7566, '1981-12-03', 3000, NULL, 20),
           (7934, 'miller', 'clerk', 7782, '1982-01-23', 1300, NULL, 10);
    INSERT INTO dept (deptno, dname, loc)
    VALUES (10, 'accounting', 'new york'),
           (20, 'research', 'dallas'),
           (30, 'sales', 'chicago'),
           (40, 'operations', 'boston');
    INSERT INTO salgrade (grade, losal, hisal)
    VALUES (1, 700, 1200),
           (2, 1201, 1400),
           (3, 1401, 2000),
           (4, 2001, 3000),
           (5, 3001, 9999);

TRUNCATE TABLE `dept`;
    TRUNCATE TABLE `emp`;
    TRUNCATE TABLE `salgrade`;

--------------------

# 1 取得薪水最高的前五名员工 #

-- (1)查询员工表的信息，按照工资降序排列
    SELECT * FROM emp ORDER BY sal DESC;
    -- (2)使用limit取得前5条记录
    SELECT * FROM emp ORDER BY sal DESC LIMIT 0,5; -- 从0开始，取5条记录
    SELECT * FROM emp ORDER BY sal DESC LIMIT 5;   -- 默认的就是从0开始的

![20200606163401915.png][]

--------------------

# 2 取得薪水最高的第六到第十名员工 #

-- (1)查询员工表的信息，按照工资降序排列
    SELECT * FROM emp ORDER BY sal DESC;
    -- (2)使用limit取得6~10位的数据
    SELECT * FROM emp ORDER BY sal DESC LIMIT 5,5; -- 从5开始，连续的5条记录

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQwNzk0OTcz_size_16_color_FFFFFF_t_70 1][]

--------------------

# 3 取得最后(也就是最大的日期)入职的5名员工 #

-- 日期类型的数据也是有大小的，日期类型数据的大小可以由其字面值来决定，例如"1987"小于"2018" 
    -- (1)查询员工信息，按照入职日期降序排列
    SELECT * FROM emp ORDER BY hiredate DESC;
    -- (2)使用limit取得前5条记录
    SELECT * FROM emp ORDER BY hiredate DESC LIMIT 5;

![20200606164056809.png][]

--------------------

# 4 取得每个薪水等级有多少员工 #

-- (1)查询员工的薪水级别(把emp表和salgrade表进行连接)
    SELECT 
      e.sal, s.grade 
    FROM
      emp e 
      INNER JOIN salgrade s ON (e.sal BETWEEN s.losal AND s.hisal) ;
    -- (2)使用grade进行分组，查询每组的数据总数(count())
    SELECT 
      COUNT(*), s.grade 
    FROM
      emp e 
      INNER JOIN salgrade s ON (e.sal BETWEEN s.losal AND s.hisal) 
    GROUP BY s.grade ;

![20200606164224562.png][]

--------------------

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

-- (1)按照部门编号进行分组，查询最高薪水
    SELECT
      deptno, MAX(sal) maxsal
    FROM
      emp
    GROUP BY deptno ;
    -- (2)把上面的查询结果当做一个临时表t，和emp表进行连接查询,连接的条件(e.deptno = t.deptno and e.sal = t.maxsal)
    SELECT
      e.ename, e.empno, t.maxsal, t.deptno
    FROM
      emp e
      INNER JOIN
        (SELECT deptno, MAX(sal) maxsal FROM emp GROUP BY deptno) t
        ON (e.deptno = t.deptno AND t.maxsal = e.sal)
    ORDER BY t.deptno ;
    -- (3)校验查询结果
    SELECT
      *
    FROM
      emp
    ORDER BY deptno ASC, sal DESC ;

![20200606165915179.png][]

--------------------

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

-- (1)按照部门编号分组，查询平均薪水
    SELECT 
      deptno, AVG(sal) avgsal 
    FROM
      emp e 
    GROUP BY deptno ;
    -- (2)把上面的查询结果当做一个临时表t，和emp表进行连接查询，连接的条件(e.deptno = t.deptno and e.sal > t.maxsal)
    SELECT 
      e.empno, e.ename, e.sal, t.avgsal, t.deptno 
    FROM
      emp e 
      INNER JOIN 
        (SELECT deptno, AVG(sal) avgsal FROM emp e GROUP BY deptno) t 
        ON (e.deptno = t.deptno AND e.sal > t.avgsal) 
    ORDER BY t.deptno ;

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQwNzk0OTcz_size_16_color_FFFFFF_t_70 2][]

--------------------

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

> JOIN等效于INNER JOIN

-- (1)按照部门编号分组,查询平均工资
    SELECT 
      deptno, AVG(sal) avgsal 
    FROM
      emp 
    GROUP BY deptno ;
    -- (2)把上面的查询结果当做一个临时表t,和salgrade表进行连接,连接的条件(t.avgsal between s.losal and s.hisal)
    SELECT 
      t.deptno, t.avgsal, s.grade, s.losal, s.hisal 
    FROM
      salgrade s 
      JOIN 
        (SELECT deptno, AVG(sal) avgsal FROM emp GROUP BY deptno) t 
      ON (t.avgsal BETWEEN s.losal AND s.hisal) 
    ORDER BY t.deptno ;

![20200606170429883.png][]

--------------------

# 8 不准用组函数（MAX），取得最高薪水（给出两种解决方案） #

> **组函数**又叫做聚集函数（aggregation function），它在一个行的集合（一组行）上进行操作，对每个组给一个结果；每个组函数接受一个参数，参数expr通常是列或表达式；默认情况下，组函数忽略列值为null的行，不把它们拿来参与计算

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQwNzk0OTcz_size_16_color_FFFFFF_t_70 3][]

-- 方式1:按照sal降序排列,使用limit取得第一条结果
    SELECT sal FROM emp ORDER BY sal DESC LIMIT 1 ;
    SELECT sal FROM emp ORDER BY sal DESC LIMIT 0, 1 ;

-- 方式2
    -- (1)使用连接,查询比最高工资低的工资
    SELECT DISTINCT 
      a.sal 
    FROM
      emp a 
      INNER JOIN emp b ON (a.sal < b.sal) ;
    -- (2)把上面的查询结果当做条件,
    SELECT 
      sal 
    FROM
      emp 
    WHERE sal NOT IN (SELECT DISTINCT a.sal FROM emp a INNER JOIN emp b ON (a.sal < b.sal)) ;

-- (3)使用max()函数 （可以是可以但是不符合题意）
    SELECT 
      MAX(sal) maxsal 
    FROM
      emp ;

![20200606171109537.png][]

--------------------

# 9 取得平均薪水最高的部门的部门编号（至少两个方案） #

-- 方式1
    -- 按照部分编号分组,查询平均工资,然后按照平均薪水降序排列,使用limit取得第一条结果
    SELECT 
      deptno, AVG(sal) avgsal 
    FROM
      emp 
    GROUP BY deptno 
    ORDER BY avgsal DESC 
    LIMIT 1 ;

-- 方式2
    -- (1)按照部分编号分组,查询平均工资
    SELECT 
      deptno, AVG(sal) avgsal 
    FROM
      emp 
    GROUP BY deptno ;
    -- (2)把上面的查询结果当做一个临时表t,查询最高的平均工资
    SELECT 
      MAX(t.avgsal) 
    FROM
      (SELECT deptno,AVG(sal) avgsal FROM emp GROUP BY deptno) t ;
    -- (3)用第二步的查询结果当做条件,过滤第一步的查询结果
    SELECT 
      deptno, AVG(sal) avgsal 
    FROM
      emp 
    GROUP BY deptno 
    HAVING avgsal = (SELECT MAX(t.avgsal) FROM (SELECT deptno, AVG(sal) avgsal FROM emp GROUP BY deptno) t) ;

-- 方式3
    -- (1) 按照部分分组，对每个部门的平均工资进行降序排序，获取第一条记录
    SELECT 
      AVG(sal) avgsal 
    FROM
      emp 
    GROUP BY deptno 
    ORDER BY avgsal DESC 
    LIMIT 1 ;
    -- (2)把第二步的查询结果当做查询条件，过滤第一步的查询结果
    SELECT 
      deptno, AVG(sal) avgsal 
    FROM
      emp 
    GROUP BY deptno 
    HAVING avgsal = (SELECT AVG(sal) avgsal FROM emp GROUP BY deptno ORDER BY avgsal DESC LIMIT 1) ;

![20200606171551765.png][]

--------------------

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

-- 方式1
    -- (1)取得平均薪水最高的部门的部门编号
    SELECT 
      deptno, AVG(sal) avgsal 
    FROM
      emp 
    GROUP BY deptno 
    ORDER BY avgsal DESC 
    LIMIT 1 ;
    -- (2)把上面的查询结果当做一个临时表t，和dept 表进行连接
    SELECT 
      t.deptno, d.dname, t.avgsal 
    FROM
      dept d 
      INNER JOIN 
        (SELECT deptno, AVG(sal) avgsal FROM emp GROUP BY deptno ORDER BY avgsal DESC LIMIT 1) t 
      ON (d.deptno = t.deptno) ;

-- 方式2 
    -- 把dept和emp进行连接查询，然后按照部门名称进行分组，并且将查询结果按照平均工资降序排列，使用limit取得第一条结果
    SELECT 
      d.dname, AVG(e.sal) avgsal 
    FROM
      emp e 
      INNER JOIN dept d ON (e.deptno = d.deptno) 
    GROUP BY d.dname 
    ORDER BY avgsal DESC 
    LIMIT 1 ;

![20200606172643974.png][]

--------------------

# 11 求平均薪水的等级最低(高)的部门的部门名称 #

-- (1).查询平均薪水及其部门名称,也就是把emp表和dept表进行连接查询,然后按照部门名称进行分组
    SELECT 
      d.dname, AVG(e.sal) avgsal 
    FROM
      emp e 
      INNER JOIN dept d ON (e.deptno = d.deptno) 
    GROUP BY d.dname ;
    -- (2)把上面的查询结果当做一个临时表t,和salgrade 表进行连接,查询平均工资的级别
    SELECT 
      t.dname, t.avgsal, s.grade 
    FROM
      (SELECT d.dname, AVG(e.sal) avgsal FROM emp e INNER JOIN dept d ON (e.deptno = d.deptno) GROUP BY d.dname) t 
      INNER JOIN salgrade s ON (t.avgsal BETWEEN s.losal AND s.hisal) ;
    -- (3)把上面的查询结果当做一个临时表t,查询最大的工资级别
    SELECT 
      MAX(t.grade) maxgrade 
    FROM
      (SELECT t.dname, t.avgsal, s.grade FROM (SELECT d.dname, AVG(e.sal) avgsal FROM emp e INNER JOIN dept d ON (e.deptno = d.deptno) GROUP BY d.dname) t INNER JOIN salgrade s ON (t.avgsal BETWEEN s.losal AND s.hisal)) t ;
    -- (4)把上面的查询结果当做添加,过滤第二步的查询结果
    SELECT 
      t.dname, t.avgsal, s.grade 
    FROM
      (SELECT d.dname, AVG(e.sal) avgsal FROM emp e INNER JOIN dept d ON (e.deptno = d.deptno) GROUP BY d.dname) t INNER JOIN salgrade s ON (t.avgsal BETWEEN s.losal AND s.hisal) 
    WHERE s.grade = (SELECT MAX(t.grade) maxgrade FROM (SELECT t.dname, t.avgsal, s.grade FROM (SELECT d.dname, AVG(e.sal) avgsal FROM emp e INNER JOIN dept d ON (e.deptno = d.deptno) GROUP BY d.dname) t INNER JOIN salgrade s ON (t.avgsal BETWEEN s.losal AND s.hisal)) t) ;

![20200606173655536.png][]

--------------------

# 12 取得比普通员工（员工代码没有在mgr上出现的）的最高薪水还要高的经理人姓名 #

> **NOT IN** 的坑 **: **[https://www.jianshu.com/p/ead81f3ed054][https_www.jianshu.com_p_ead81f3ed054]

-- MySQL NOT IN 的坑——注意null
    SELECT 
      * 
    FROM
      emp 
    WHERE empno NOT IN (NULL) ;

-- (1)查询出所有的经理的编号，也就是出现在mgr中的员工编号
    SELECT DISTINCT mgr FROM emp ;
    -- (2)查询员工的编号，也就是EMPNO没有出现在经理编号
    /*
    SELECT *
    FROM emp
    WHERE empno NOT IN ( SELECT DISTINCT mgr FROM emp );
    上面的语句查询不到结果，这是因为 empno  NOT IN (7788,NULL) 等效于 empno <> 7788 OR empno <> NULL
    IN会自动的忽略空值，所以需要我们手动的排除空值，改正上面的语句
    */
    SELECT 
      * 
    FROM
      emp 
    WHERE empno NOT IN 
      (SELECT DISTINCT mgr FROM emp WHERE mgr IS NOT NULL) ;
    -- (3)查询所有员工的最高工资，也就是把上面的查询结果单做一个临时表t
    SELECT 
      MAX(t.sal) maxsal 
    FROM
      (SELECT * FROM emp WHERE empno NOT IN (SELECT DISTINCT mgr FROM emp WHERE mgr IS NOT NULL)) t ;
    -- (4)把上面的查询结果当做条件
    SELECT 
      * 
    FROM
      emp 
    WHERE sal > (SELECT MAX(t.sal) maxsal FROM (SELECT * FROM emp WHERE empno NOT IN (SELECT DISTINCT mgr FROM emp WHERE mgr IS NOT NULL)) t) ;

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQwNzk0OTcz_size_16_color_FFFFFF_t_70 4][]

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQwNzk0OTcz_size_16_color_FFFFFF_t_70 5][]

--------------------

# 13 列出所有员工及领导的名字 #

/*
    -- 使用自连接进行查询
    SELECT e.empno '员工编号', e.ename '员工姓名', m.empno '经理编号', m.ename '经理姓名' 
    FROM
      emp e 
      INNER JOIN emp m ON (e.mgr = m.empno) ;
    上面的语句是错误的，因为没有查询出7839这个人的经理，因为内连接只能查询满足一一对应关系的数据我们要查询出不满足对应关系的7839，应该使用外连接改正上面的语句
    */
    SELECT 
      e.empno '员工编号', e.ename '员工姓名', m.empno '经理编号', m.ename '经理姓名' 
    FROM
      emp e 
      LEFT JOIN emp m ON (e.mgr = m.empno) ;

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQwNzk0OTcz_size_16_color_FFFFFF_t_70 6][]

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQwNzk0OTcz_size_16_color_FFFFFF_t_70 7][]

--------------------

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

-- (1)查询员工其经理
    SELECT
      e.empno "员工编号", e.ename "员工姓名", m.empno "经理编号", m.ename "经理姓名"
    FROM
      emp e
      LEFT JOIN emp m ON (e.mgr = m.empno) ;
    -- (2)使用受雇日期早于其直接上级的日期作为条件,对上面的结果进行过滤
    SELECT
      e.empno "员工编号", e.ename "员工姓名", e.deptno "部门编号", m.empno "经理编号", m.ename "经理姓名"
    FROM
      emp e
      LEFT JOIN emp m ON (e.mgr = m.empno)
    WHERE e.hiredate < m.hiredate ;
    -- (3)把上面的查询结果和dept表进行连接
    SELECT
      t.empno, t.ename, t.deptno, d.dname
    FROM
      dept d
      INNER JOIN
        (SELECT e.empno, e.ename, e.deptno FROM emp e LEFT JOIN emp m ON (e.mgr = m.empno) WHERE e.hiredate < m.hiredate) t
            ON (d.deptno = t.deptno)
    ORDER BY t.deptno ;

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQwNzk0OTcz_size_16_color_FFFFFF_t_70 8][]

--------------------

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

--  使用外连接进行查询
    SELECT 
      e.empno, e.ename, d.deptno, d.dname 
    FROM
      emp e 
      RIGHT OUTER JOIN dept d ON (e.deptno = d.deptno) ;

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQwNzk0OTcz_size_16_color_FFFFFF_t_70 9][]

--------------------

# 16 列出至少有5个员工的所有部门 #

-- (1)把dept和emp进行连接查询
    SELECT 
      e.empno, d.deptno, d.dname, d.loc 
    FROM
      emp e 
      INNER JOIN dept d ON (e.deptno = d.deptno) ;
    -- (2)对上的查询结果进行分组(按照deptno ,dname ,loc进行分组)，并且查询每组的记录总数
    SELECT 
      COUNT(e.empno) total, d.deptno, d.dname, d.loc 
    FROM
      emp e 
      INNER JOIN dept d ON (e.deptno = d.deptno) 
    GROUP BY d.deptno, d.dname, d.loc ;
    -- (3)使用havging对分组后的数据进行过滤
    SELECT 
      COUNT(e.empno) total, d.deptno, d.dname, d.loc 
    FROM
      emp e 
      INNER JOIN dept d ON (e.deptno = d.deptno) 
    GROUP BY d.deptno, d.dname, d.loc 
    HAVING total >= 5 ;

![20200615091728762.png][]

--------------------

# 18 列出薪水比“smith”多的所有员工信息 #

-- (1)查询 "smith" 的工资
    SELECT sal FROM emp WHERE ename = "smith" ;
    -- (2)用上面的查询结果当做查询条件,也就是使用一个出现在where后面的子查询
    SELECT 
      * 
    FROM
      emp 
    WHERE sal > 
      (SELECT sal FROM emp WHERE ename = "smith") ;

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQwNzk0OTcz_size_16_color_FFFFFF_t_70 10][]

--------------------

# 19 列出所有 “clerk”（办事员）的姓名及其部门名称，部门人数 #

-- (1)查询所有办事员的姓名及其部门名称,用一个等值连接(emp,dept)
    SELECT 
      e.ename, e.job, d.deptno, d.dname 
    FROM
      emp e 
      INNER JOIN dept d ON (e.deptno = d.deptno) 
    WHERE e.job = "clerk" ;
    -- (2)按照部门编号分组,查询每个部门的员工总数
    SELECT 
      deptno, COUNT(*) totalemp 
    FROM
      emp 
    GROUP BY deptno ;
    -- (3)把上面的两个查询结果当做临时表t1,t2 ,做一个连接查询
    SELECT 
      t1.ename, t1.job, t1.deptno, t1.dname, t2.totalemp 
    FROM
      (SELECT e.ename, e.job, d.deptno, d.dname FROM emp e INNER JOIN dept d ON (e.deptno = d.deptno) WHERE e.job = "clerk") t1 
      INNER JOIN 
      (SELECT deptno, COUNT(*) totalemp FROM emp GROUP BY deptno) t2 
        ON (t1.deptno = t2.deptno) ;

![20200615092340512.png][]

--------------------

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

-- (1)列出最低薪水大于1500的各种工作，按照职务分组查询最低工资，并且过滤大于1500的记录
    SELECT 
      job, MIN(sal) minsal 
    FROM
      emp 
    GROUP BY job 
    HAVING minsal > 1500 ;
    -- (2)在上面查询结果的基础上出现员工总数
    SELECT 
      job, MIN(sal) minsal, COUNT(*) total 
    FROM
      emp 
    GROUP BY job 
    HAVING minsal > 1500 ;
    -- (3)校验数据
    SELECT 
      * 
    FROM
      emp 
    WHERE sal > 1500 
    ORDER BY job ;

![2020061509252045.png][]

--------------------

# 21 列出在部门“sales”（销售部）工作的员工的姓名 #

> 假定不知道销售部门的部门编号

-- (1)从部门表中查询"sales"的部门编号
    SELECT deptno FROM dept WHERE dname = "sales" ;
    -- (2)用上面的查询结果当做查询条件,也就是使用出现在where中的子查询
    SELECT 
      deptno, ename 
    FROM
      emp 
    WHERE deptno = (SELECT deptno FROM dept WHERE dname = "sales") ;

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQwNzk0OTcz_size_16_color_FFFFFF_t_70 11][]

--------------------

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

-- (1)列出薪金高于公司平均薪金的所有员工
    SELECT 
      e.ename, e.sal 
    FROM
      emp e 
    WHERE e.sal > (SELECT AVG(sal) FROM emp) ;
    -- (2)在上面的查询基础上，查询上级领导(使用自连接) ，员工的工资级别(和salgrade表进行连接)
    SELECT 
      e.ename "员工姓名", e.sal "员工工资", s.grade "工资级别", e.deptno "部门编号", m.ename "经理姓名" 
    FROM
      emp e 
      INNER JOIN emp m ON (e.mgr = m.empno) 
      INNER JOIN salgrade s ON (e.sal BETWEEN s.losal AND s.hisal) 
    WHERE e.sal > (SELECT AVG(sal) FROM emp) ;
    -- (3)把king也查询出来，也就是做自连接时候使用外连接方式
    SELECT 
      e.ename "员工姓名", e.sal "员工工资", s.grade "工资级别", e.deptno "部门编号", m.ename "经理姓名" 
    FROM
      emp e 
      LEFT JOIN emp m ON (e.mgr = m.empno) 
      INNER JOIN salgrade s ON (e.sal BETWEEN s.losal AND s.hisal) 
    WHERE e.sal > (SELECT AVG(sal) FROM emp) ;

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQwNzk0OTcz_size_16_color_FFFFFF_t_70 12][]

--------------------

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

-- (1)查询出和scott从事相同职务的员工，使用是一个出现在where后面的子查询
    SELECT 
      ename, job 
    FROM
      emp 
    WHERE job = (SELECT job FROM emp WHERE ename = "scott") ;
    -- (2)在上面查询的基础上，连接dept表
    SELECT 
      e.ename, e.job, d.deptno, d.dname 
    FROM
      emp e 
      INNER JOIN dept d ON (e.deptno = d.deptno) 
    WHERE e.job = (SELECT job FROM emp WHERE ename = "scott") ;

![20200615094717530.png][]

--------------------

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

-- (1)查询出30部门的员工工资
    SELECT 
      sal 
    FROM
      emp 
    WHERE deptno = 30 ;
    -- (2)把上面的查询结果当做查询条件，也就是使用出现在where后面子查询
    SELECT 
      sal, ename, deptno 
    FROM
      emp 
    WHERE sal IN (SELECT sal FROM emp WHERE deptno = 30) AND (deptno <> 30) ;

--------------------

# 25 列出薪金高于在部门30工作的所有员工的薪金的员工姓名和薪金、部门名称 #

-- 方式1
    -- (1)查询30部门的最高工资
    SELECT 
      MAX(sal) maxsal 
    FROM
      emp 
    WHERE deptno = 30 ;
    -- (2)用上面的查询结果当做查询条件,并且和dept进行连接查询查询出部门名称
    SELECT 
      e.ename, e.sal, e.deptno, d.dname 
    FROM
      emp e 
      INNER JOIN dept d ON (e.deptno = d.deptno) 
    WHERE e.sal > (SELECT MAX(sal) maxsal FROM emp WHERE deptno = 30) ;

-- 方式2
    -- 如果子查询返回多行多列,需要使用多行比较操作符,用 > all,表示大于子查询的最大值 
    SELECT 
      e.ename, e.sal, e.deptno, d.dname 
    FROM
      emp e 
      INNER JOIN dept d ON (e.deptno = d.deptno) 
    WHERE sal > ALL (SELECT sal FROM emp WHERE deptno = 30) ;
    -- 如果子查询返回多行多列,需要使用多行比较操作符,用 < all,表示小于子查询的最小值 
    SELECT 
      e.ename, e.sal, e.deptno, d.dname 
    FROM
      emp e 
      INNER JOIN dept d ON (e.deptno = d.deptno) 
    WHERE sal < ALL (SELECT sal FROM emp WHERE deptno = 30)

--------------------

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

-- (1)所有所有的部门信息及其员工信息
    SELECT
      e.*, d.*
    FROM
      emp e
      RIGHT JOIN dept d ON (e.deptno = d.deptno) ;
    -- (2)在上面查询的基础上用部门编号分组，查询员工总数
    --  注意，count(*)会查询包含null的数据，所以用count(ename)
    SELECT
      d.deptno, COUNT(ename) totalemp
    FROM
      emp e
      RIGHT JOIN dept d ON (e.deptno = d.deptno)
    GROUP BY d.deptno ;
    -- (3)在上面的查询结果的基础上，查询平均工资
    SELECT
      d.deptno,COUNT(ename) totalemp, IFNULL(AVG(sal), 0) avgsal
    FROM
      emp e
      RIGHT JOIN dept d ON (e.deptno = d.deptno)
    GROUP BY d.deptno ;
    -- (4)在上面的查询结果的基础上，查询平均服务年限
    -- mysql中有一个to_days()函数,把一个日期转换为天数(距离0年的天数)
    SELECT
      AVG((TO_DAYS(NOW()) - TO_DAYS(hiredate)) / 365)
    FROM
      emp ;
    --
    SELECT
      d.deptno, COUNT(ename) totalemp, IFNULL(AVG(sal), 0) avgsal, IFNULL(AVG((TO_DAYS(NOW()) - TO_DAYS(hiredate)) / 365),0) servertime
    FROM
      emp e
      RIGHT JOIN dept d ON (e.deptno = d.deptno)
    GROUP BY d.deptno ;

--------------------

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

-- 连接emp，dept表，做等值连接
    -- sql1999语句
    SELECT 
      e.ename, e.sal, d.dname 
    FROM
      emp e 
      INNER JOIN dept d ON (e.deptno = d.deptno) ;
    -- sql1992语句
    SELECT 
      e.ename, e.sal, d.dname 
    FROM
      emp e, dept d 
    WHERE e.deptno = d.deptno ;

--------------------

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

-- 用外连接,连接emp表和dept表，然后按照部门编号分组，查询员工总数
    SELECT 
      d.deptno, d.dname, d.loc, COUNT(ename) totalemp 
    FROM
      emp e 
      RIGHT JOIN dept d ON (e.deptno = d.deptno) 
    GROUP BY d.deptno, d.dname, d.loc ;

--------------------

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

-- (1)按照职务分组,查询最低工资
    SELECT 
      job, MIN(sal) minsal 
    FROM
      emp 
    GROUP BY job ;
    -- (2)把上面的查询结果当做临时表t，和emp表进行连接查询
    SELECT 
      e.ename, t.job, t.minsal 
    FROM
      emp e 
      INNER JOIN (SELECT job, MIN(sal) minsal FROM emp GROUP BY job) t 
      ON (e.job = t.job AND e.sal = t.minsal) ;

--------------------

# 30 列出各个部门manager的最低薪金 #

-- (1)查询职务为"manager"的员工信息
    SELECT 
      * 
    FROM
      emp 
    WHERE job = "manager" ;
    -- (2)在上面查询的基础上，按照部门编号分组，查询最低工资
    SELECT 
      deptno, MIN(sal) minsal 
    FROM
      emp 
    WHERE job = "manager" 
    GROUP BY deptno ;

--------------------

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

-- 年薪 = (月薪+津贴) * 12
    SELECT 
      empno, ename, sal, comm, (sal + IFNULL(comm, 0)) * 12 AS "年薪" 
    FROM
      emp 
    ORDER BY 年薪 ;
    --
    SELECT 
      empno, ename, sal, comm, (sal + IFNULL(comm, 0)) * 12 AS "年薪" 
    FROM
      emp 
    ORDER BY 年薪 ASC ;
    -- 
    SELECT 
      empno, ename, sal, comm, (sal + IFNULL(comm, 0)) * 12 AS "年薪" 
    FROM
      emp 
    ORDER BY 年薪 DESC ;

--------------------

# 32 求出员工领导的薪水超过3000的员工名称和领导名称 #

-- (1)使用自连接查询员工信息及其领导信息
    SELECT 
      e.ename "员工姓名", e.sal "员工工资", m.ename "经理姓名", m.sal "经理工资" 
    FROM
      emp e 
      INNER JOIN emp m ON (e.mgr = m.empno) ;
    -- (2)在上面查询的基础上,加上查询条件，领导的工资大于3000
    -- 在加入其它的的查询条件的时候，可以使用where 
    SELECT 
      e.ename "员工姓名", e.sal "员工工资", m.ename "经理姓名", m.sal "经理工资" 
    FROM
      emp e 
      INNER JOIN emp m ON (e.mgr = m.empno) 
    WHERE m.sal > 3000 ;
    -- 在加入其它的的查询条件的时候，可以使用where，也可以使用and
    SELECT 
      e.ename "员工姓名", e.sal "员工工资", m.ename "经理姓名", m.sal "经理工资" 
    FROM
      emp e 
      INNER JOIN emp m ON (e.mgr = m.empno) AND (m.sal > 3000) ;

--------------------

# 33 求部门名称中带“s”字符的部门员工的工资合计、部门人数 #

-- (1)查询出部门名称，工资合计，部门人数
    SELECT 
      d.dname, IFNULL(SUM(sal), 0) sumsal, COUNT(ename) totalemp 
    FROM
      emp e 
      RIGHT JOIN dept d ON (e.deptno = d.deptno) 
    GROUP BY d.dname ;
    --  在上面的查询基础是上，加上条件——部门名称中带“s”字符
    SELECT 
      d.dname, IFNULL(SUM(sal), 0) sumsal, COUNT(ename) totalemp 
    FROM
      emp e 
      RIGHT JOIN dept d ON (e.deptno = d.deptno) 
    WHERE d.dname LIKE "%s%" 
    GROUP BY d.dname ;

--------------------

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

-- 创建一个新表来测试
    DROP TABLE IF EXISTS emp_bak ;
    CREATE TABLE emp_bak AS
    SELECT
      *
    FROM
      emp ;
    --
    SELECT * FROM emp_bak ;
    --
    UPDATE
      emp_bak
    SET
      sal = sal * 1.1
    WHERE ((TO_DAYS(NOW()) - TO_DAYS(hiredate)) / 365 > 30);

[https_www.wkcto.com_course_121]: https://www.wkcto.com/course/121
[https_yuanyu.blog.csdn.net_article_details_106585513]: https://yuanyu.blog.csdn.net/article/details/106585513
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQwNzk0OTcz_size_16_color_FFFFFF_t_70]: https://img-blog.csdnimg.cn/20201007133425799.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQwNzk0OTcz,size_16,color_FFFFFF,t_70
[20200606163401915.png]: https://img-blog.csdnimg.cn/20200606163401915.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQwNzk0OTcz_size_16_color_FFFFFF_t_70 1]: https://img-blog.csdnimg.cn/20200606163904858.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQwNzk0OTcz,size_16,color_FFFFFF,t_70
[20200606164056809.png]: https://img-blog.csdnimg.cn/20200606164056809.png
[20200606164224562.png]: https://img-blog.csdnimg.cn/20200606164224562.png
[20200606165915179.png]: https://img-blog.csdnimg.cn/20200606165915179.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQwNzk0OTcz_size_16_color_FFFFFF_t_70 2]: https://img-blog.csdnimg.cn/20200606165956727.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQwNzk0OTcz,size_16,color_FFFFFF,t_70
[20200606170429883.png]: https://img-blog.csdnimg.cn/20200606170429883.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQwNzk0OTcz_size_16_color_FFFFFF_t_70 3]: https://img-blog.csdnimg.cn/20200605195532419.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQwNzk0OTcz,size_16,color_FFFFFF,t_70
[20200606171109537.png]: https://img-blog.csdnimg.cn/20200606171109537.png
[20200606171551765.png]: https://img-blog.csdnimg.cn/20200606171551765.png
[20200606172643974.png]: https://img-blog.csdnimg.cn/20200606172643974.png
[20200606173655536.png]: https://img-blog.csdnimg.cn/20200606173655536.png
[https_www.jianshu.com_p_ead81f3ed054]: https://www.jianshu.com/p/ead81f3ed054
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQwNzk0OTcz_size_16_color_FFFFFF_t_70 4]: https://img-blog.csdnimg.cn/20200606180828127.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQwNzk0OTcz,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQwNzk0OTcz_size_16_color_FFFFFF_t_70 5]: https://img-blog.csdnimg.cn/20200606175253981.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQwNzk0OTcz,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQwNzk0OTcz_size_16_color_FFFFFF_t_70 6]: https://img-blog.csdnimg.cn/20200606134647666.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQwNzk0OTcz,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQwNzk0OTcz_size_16_color_FFFFFF_t_70 7]: https://img-blog.csdnimg.cn/20200606181607632.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQwNzk0OTcz,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQwNzk0OTcz_size_16_color_FFFFFF_t_70 8]: https://img-blog.csdnimg.cn/20200615091249867.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQwNzk0OTcz,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQwNzk0OTcz_size_16_color_FFFFFF_t_70 9]: https://img-blog.csdnimg.cn/20200615091541499.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQwNzk0OTcz,size_16,color_FFFFFF,t_70
[20200615091728762.png]: https://img-blog.csdnimg.cn/20200615091728762.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQwNzk0OTcz_size_16_color_FFFFFF_t_70 10]: https://img-blog.csdnimg.cn/20200615092019262.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQwNzk0OTcz,size_16,color_FFFFFF,t_70
[20200615092340512.png]: https://img-blog.csdnimg.cn/20200615092340512.png
[2020061509252045.png]: https://img-blog.csdnimg.cn/2020061509252045.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQwNzk0OTcz_size_16_color_FFFFFF_t_70 11]: https://img-blog.csdnimg.cn/2020061509284747.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQwNzk0OTcz,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQwNzk0OTcz_size_16_color_FFFFFF_t_70 12]: https://img-blog.csdnimg.cn/20200615093105175.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQwNzk0OTcz,size_16,color_FFFFFF,t_70
[20200615094717530.png]: https://img-blog.csdnimg.cn/20200615094717530.png