SQL经典训练【1】

最新推荐文章于 2023-04-03 10:50:01 发布

爪哇

最新推荐文章于 2023-04-03 10:50:01 发布

阅读量450

点赞数

分类专栏： MySQL 面试

记录平时的问题，完全是给自己看的，垃圾博客，浪费各位大佬时间...

本文链接：https://blog.csdn.net/qq_40794973/article/details/106574112

版权

MySQL 同时被 2 个专栏收录

8 篇文章 0 订阅

订阅专栏

面试

5 篇文章 0 订阅

订阅专栏

https://www.wkcto.com/course/121

SQL经典训练【2】：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 '薪水级别表';

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开始的

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条记录

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

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

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 ;

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 ;

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 ;

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 ;

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

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

-- 方式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 ;

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

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 ;

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

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

NOT IN 的坑 : 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) ;

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

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 ;

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

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

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 ;

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

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

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 ;

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

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

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

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