34道经典SQL试题(MySQL版)

34道经典SQL试题。记录下在学习MySQL过程中针对这34道题的理解。

0、测试数据准备

-- 创建部门表
DROP TABLE IF EXISTS `dept`;
CREATE TABLE if not exists `dept` (
    `deptno` INT (2) NOT NULL COMMENT '部门编号',
    `dname` VARCHAR (14) DEFAULT NULL COMMENT '部门名称',
    `loc` VARCHAR (13) DEFAULT NULL COMMENT '位置',
    PRIMARY KEY (`deptno`)
) ENGINE=INNODB DEFAULT CHARSET=utf8;
-- 插入数据
INSERT INTO `dept` VALUES (10, 'ACCOUNTING', 'NEW YORK');
INSERT INTO `dept` VALUES (20, 'RESEARCH', 'DALLAS');
INSERT INTO `dept` VALUES (30, 'SALES', 'CHICAGO');
INSERT INTO `dept` VALUES (40, 'OPERATIONS', 'BOSTON');


-- 创建员工表
DROP TABLE IF EXISTS `emp`;
CREATE TABLE if not exists `emp` (
    `empno` INT(4) NOT NULL COMMENT '员工编号',
    `ename` VARCHAR(10) DEFAULT NULL COMMENT '员工姓名',
    `job` VARCHAR(9) DEFAULT NULL COMMENT '工作岗位',
    `mgr` INT(4) DEFAULT NULL COMMENT '上级经理',
    `hiredate` DATE DEFAULT NULL,
    `sal` DOUBLE(7,2) DEFAULT NULL,
    `comm` DOUBLE(7,2) DEFAULT NULL,
    `deptno` INT(2) DEFAULT NULL,
    PRIMARY KEY (`empno`),
    KEY `deptno` (`deptno`),
    KEY `sal` (`sal`),
    CONSTRAINT `emp_ibfk_1` FOREIGN KEY (`deptno`) REFERENCES `dept` (`deptno`)
) ENGINE=INNODB DEFAULT CHARSET=utf8 COMMENT='员工表';
-- 插入数据
INSERT INTO `emp` VALUES(7369,'SIMITH','CLERK',7902,'1980-12-17',800,NULL,20);
INSERT INTO `emp` VALUES(7499,'ALLEN','SALESMAN',7698,'1981-02-20',1600,300,30);
INSERT INTO `emp` VALUES(7521,'WARD','SALESMAN',7698,'1981-02-22',1250,500,30);
INSERT INTO `emp` VALUES(7566,'JONES','MANAGER',7839,'1981-04-02',2975,NULL,20);
INSERT INTO `emp` VALUES(7654,'MARTIN','SALESMAN',7698,'1981-09-28',1250,1400,30);
INSERT INTO `emp` VALUES(7698,'BLAKE','MANAGER',7839,'1981-05-01',2850,NULL,30);
INSERT INTO `emp` VALUES(7782,'CLARK','MANAGER',7839,'1981-06-09',2450,NULL,10);
INSERT INTO `emp` VALUES(7788,'SCOTT','ANALYST',7566,'1987-04-19',3000,NULL,20);
INSERT INTO `emp` VALUES(7839,'KING','PRESIDENT',NULL,'1981-11-17',5000,NULL,10);
INSERT INTO `emp` VALUES(7844,'TURNER','SALESMAN',7698,'1981-09-08',1500,NULL,30);
INSERT INTO `emp` VALUES(7876,'ADAMS','CLERK',7788,'1987-05-23',1100,NULL,20);
INSERT INTO `emp` VALUES(7900,'JAMES','CLERK',7698,'1981-12-03',950,NULL,30);
INSERT INTO `emp` VALUES(7902,'FORD','ANALYST',7566,'1981-12-03',3000,NULL,20);
INSERT INTO `emp` VALUES(7934,'MILLER','CLERK',7782,'1982-01-23',1300,NULL,10);


-- 创建薪水等级表
DROP TABLE IF EXISTS `salgrade`;
CREATE TABLE if not exists `salgrade` (
    `grade` INT(11) DEFAULT NULL,
    `losal` INT(11) DEFAULT NULL,
    `hisal` INT(11) DEFAULT NULL
)ENGINE=INNODB DEFAULT CHARSET=utf8;

INSERT INTO `salgrade` VALUES (1,700,1200);
INSERT INTO `salgrade` VALUES (2,1201,1400);
INSERT INTO `salgrade` VALUES (3,1401,2000);
INSERT INTO `salgrade` VALUES (4,2001,3000);
INSERT INTO `salgrade` VALUES (5,3001,5000);

-- 检查插入的数据
SELECT * FROM dept;
SELECT * FROM emp;
SELECT * FROM salgrade;

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

-- 第一步：查出每个部门的最高薪水
SELECT `deptno`, MAX(sal) AS `maxsal`
FROM emp
GROUP BY `deptno`;

-- 第二步：用第一步的查询结果做一个临时表 t
SELECT e.`ename`, t.*
FROM emp e
    JOIN (
		SELECT `deptno`, MAX(sal) AS `maxSal`
		FROM emp
		GROUP BY `deptno`
	) t
	ON e.`deptno` = t.`deptno` and e.`sal` = t.`maxSal`;

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

-- 第一步：找出部门的平均薪水(按部门编号分组求平均薪水)
SELECT `deptno`, AVG(sal) AS `avgSal`
FROM emp
GROUP BY `deptno`;

-- 第二步：将上面的查询结果当作临时表t，与emp表进行连接
SELECT e.`ename`, e.`sal`, e.`deptno`, t.`avgsal`
FROM emp AS e
    JOIN (
        SELECT `deptno`, AVG(sal) AS `avgsal`
        FROM emp AS e
        GROUP BY `deptno`
    ) AS t
    ON e.`deptno` = t.`deptno` and e.`sal` > t.`avgsal`;

3.1、取得部门中(所有人)平均薪水的等级

-- 第一步：取得部门中的平均薪水
select deptno, avg(sal) as avgSal
from emp
group by deptno;

-- 第二步：将上面的查询结果作为临时表t，和salgrade表关联
select t.*, s.grade
from salgrade s
    join (
        select deptno, avg(sal) as avgSal
        from emp
        group by deptno
    ) t
    on t.avgSal between s.losal and s.hisal;

3.2、取得部门中(所有人)薪水的平均等级

-- 第一步：每个员工的薪水等级
select e.ename, e.sal, e.deptno, s.grade
from emp e
    join salgrade s on e.sal between s.losal and s.hisal;

-- 第二步：将上面的结果表以部门编号分组，求平均薪水等级
select e.deptno, avg(s.grade) as avgGrade
from emp e
    join salgrade s on e.sal between s.losal and s.hisal
group by e.deptno;

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

-- 方案一：按照薪水倒序排序，取第一个
select sal
from emp
order by sal desc
limit 1;


-- 方案二：使用自连接
-- 最大值不小于表中任何一个工资，所以不会出现在此临时表中
SELECT sal
FROM emp
WHERE sal NOT IN (
    SELECT e1.sal
    FROM emp e1
        JOIN emp e2 ON e1.sal < e2.sal
);

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

-- 方案一：平均薪水降序排取第一个
-- 第一步：先求出每个部门的平均薪水
select deptno, avg(sal) as avgSal
from emp
group by deptno;

-- 第二步：取得平均薪水的最大值
select avg(sal) as avgSal
from emp
group by deptno
order by avgSal desc
limit 1;

-- 第三步：将第一步和第二步结合
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
);


-- 方案二：使用MAX函数
select deptno, avg(sal) as avgSal
from emp
group by deptno
having avg(sal) = (
    select max(t.avgsal)
    from (
        select avg(sal) avgsal
        from emp 
        group by deptno
    ) t
);


-- 方案二：
-- 与方案一类似，用max取得最高平均薪水
-- 注意：求最高的平均薪水时，不能使用order by 和 limit 1 取最大值
-- 因为如果有100个部门，可能存在多个部门的平均值都是一样的，所以不能使用limit
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 d.dname
from dept d
    join (
        select deptno, avg(sal) avgsal
        from emp
        group by deptno
        having avgsal = (
            select max(t.avgsal)
            from (
                select deptno, avg(sal) as avgsal
                from emp
                group by deptno
            ) t
        )
    ) t2
    on d.deptno = t2.deptno;

7.1、求平均薪水的等级最高的部门的部门名称

-- 第一步：求每个部门的平均薪水的等级
select t.deptno, t.dname, t.avgsal, s.grade
from (
    select d.*, avg(sal) avgsal
    from emp e
        join dept d on e.deptno = d.deptno
    group by d.deptno
) t
    join salgrade s on t.avgsal between s.losal and s.hisal;

-- 第二步：获得最高等级
select max(s.grade)
from (
    select avg(sal) as avgSal
    from emp
    group by deptno
) t
    join salgrade s on t.avgSal between s.losal and s.hisal;

-- 第三步：将第一步和第二步联合
select t.dname, t.avgsal, s.grade
from (
    select d.*, avg(sal) avgsal
    from emp e
        join dept d on e.deptno = d.deptno
    group by d.deptno
) t
    join salgrade s on t.avgsal between s.losal and s.hisal
where s.grade = (
    select max(s.grade)
    from (
        select avg(sal) as avgSal
        from emp
        group by deptno
    ) t
        join salgrade s on t.avgSal between s.losal and s.hisal
);

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

-- 第一步：求每个部门的平均薪水的等级
select t.deptno, t.dname, t.avgsal, s.grade
from (
    select d.*, avg(sal) avgsal
    from emp e
        join dept d on e.deptno = d.deptno
    group by d.deptno
) t
    join salgrade s on t.avgsal between s.losal and s.hisal;

-- 第二步：获得最低等级
select min(s.grade)
from (
    select avg(sal) as avgSal
    from emp
    group by deptno
) t
    join salgrade s on t.avgSal between s.losal and s.hisal;

-- 第三步：将第一步和第二步联合
select t.dname, t.avgsal, s.grade
from (
    select d.*, avg(sal) avgsal
    from emp e
        join dept d on e.deptno = d.deptno
    group by d.deptno
) t
    join salgrade s on t.avgsal between s.losal and s.hisal
where s.grade = (
    select min(s.grade)
    from (
        select avg(sal) as avgSal
        from emp
        group by deptno
    ) t
        join salgrade s on t.avgSal between s.losal and s.hisal
);

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

-- 第一步：取得普通员工
select *
from emp
where empno not in (
    select distinct mgr
    from emp
    where mgr is not null
);

-- 第二步：找出普通员工中最高薪水
SELECT max(sal)
FROM emp
WHERE empno NOT IN (
    SELECT DISTINCT mgr
    FROM emp
    WHERE mgr IS NOT NULL
);

-- 第三步：找出薪水大于1600的领导人
select distinct e1.empno, e1.ename
from emp e1
    join emp e2 on e1.empno = e2.mgr
where e1.sal > (
    SELECT max(sal)
    FROM emp
    WHERE empno NOT IN (
        SELECT DISTINCT mgr
        FROM emp
        WHERE mgr IS NOT NULL
    )
);

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

SELECT *
FROM emp  
ORDER BY sal DESC
LIMIT 0,5; 

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

SELECT *
FROM emp  
ORDER BY sal DESC
LIMIT 5,5;  

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

SELECT *
FROM emp
ORDER BY hiredate DESC
LIMIT 5;

12、取得每个薪水等级有多少员工

-- 第一步：找出每个员工的薪水的等级
select e.empno, e.ename, e.sal, s.grade
from emp e
    join salgrade s on e.sal between s.losal and s.hisal;

-- 第二步：在以上结果的基础上，按照grade进行分组，count计数
select s.grade, count(*)
from emp e
    join salgrade s on e.sal between s.losal and s.hisal
group by s.grade;

13、面试题

-- 三张表：
-- 学生表S：学号SNO，姓名SNAME
-- 课程表C：课号CNO，课程名CNAME，课程老师CTEACHER
-- 选课表SC：学号SNO，课号CNO，分数SCGRADE
DROP TABLE IF EXISTS s;
CREATE TABLE if not exists s(
    sno INT(2) PRIMARY KEY AUTO_INCREMENT,
    sname VARCHAR(16)
);

DROP TABLE IF EXISTS c;
CREATE TABLE if not exists c(
    cno INT(2) PRIMARY KEY AUTO_INCREMENT,
    cname VARCHAR(32),
    cteacher VARCHAR(16)
);


DROP TABLE IF EXISTS sc;
CREATE TABLE if not exists sc(
    sno INT(2),
    cno INT(2),
    scgrade INT(3),
    PRIMARY KEY(sno, cno)
);


-- 插入数据
truncate table s;
INSERT INTO s(sname)VALUES('张三');
INSERT INTO s(sname)VALUES('李四');
INSERT INTO s(sname)VALUES('王五');
INSERT INTO s(sname)VALUES('赵六');

truncate table c;
INSERT INTO c(cname,cteacher)VALUES('Linux','张老师');
INSERT INTO c(cname,cteacher)VALUES('MySQL','李老师');
INSERT INTO c(cname,cteacher)VALUES('Git','王老师');
INSERT INTO c(cname,cteacher)VALUES('Java','赵老师');
INSERT INTO c(cname,cteacher)VALUES('Redis','黎明');

truncate table sc;
INSERT INTO sc(sno,cno,scgrade)VALUES(1,1,50);
INSERT INTO sc(sno,cno,scgrade)VALUES(1,2,50);
INSERT INTO sc(sno,cno,scgrade)VALUES(1,3,50);
INSERT INTO sc(sno,cno,scgrade)VALUES(2,2,80);
INSERT INTO sc(sno,cno,scgrade)VALUES(2,3,70);
INSERT INTO sc(sno,cno,scgrade)VALUES(2,4,59);
INSERT INTO sc(sno,cno,scgrade)VALUES(3,1,60);
INSERT INTO sc(sno,cno,scgrade)VALUES(3,2,61);
INSERT INTO sc(sno,cno,scgrade)VALUES(3,3,99);
INSERT INTO sc(sno,cno,scgrade)VALUES(3,4,100);
INSERT INTO sc(sno,cno,scgrade)VALUES(3,5,52);
INSERT INTO sc(sno,cno,scgrade)VALUES(4,3,82);
INSERT INTO sc(sno,cno,scgrade)VALUES(4,4,99);
INSERT INTO sc(sno,cno,scgrade)VALUES(4,5,46);

-- 查看数据
SELECT * FROM s;
SELECT * FROM c;
SELECT * FROM sc;


-- 1、找出没选过“黎明”老师的所有学生姓名
-- 第一步：找出黎明老师所授课的编号
SELECT cno 
FROM c 
WHERE cteacher="黎明";

-- 第二步：通过学生选课表查询cno=上面结果的sno，这些sno是选黎明老师课程的学号
SELECT sno
FROM sc
WHERE cno = (
    SELECT cno 
    FROM c 
    WHERE cteacher="黎明"
);

-- 第三步：在学生表中查询sno not in 上面结果的数据
SELECT sname
FROM s
WHERE sno NOT IN (
    SELECT sno
    FROM sc
    WHERE cno = (
        SELECT cno
        FROM c
        WHERE cteacher="黎明"
    )
);


-- 2、列出2门以上(含2门)不及格学生姓名及平均成绩
-- 第一步：找出分数小于60并且按sno分组，计数大于2的学号
SELECT sno, avg(sc.scgrade) as avgGrade
FROM sc
WHERE scgrade < 60
GROUP BY sno
HAVING COUNT(*) >= 2;

-- 第二步：与学生表s进行连接
select s.sname, t.avgGrade
from s
    join (
        SELECT sno, avg(sc.scgrade) as avgGrade
        FROM sc
        WHERE scgrade < 60
        GROUP BY sno
        HAVING COUNT(*) >= 2
    ) t
    on s.sno = t.sno;


-- 3、学过1号课程和2号课程的所有学生的姓名
-- 第一步：找出学过1号课程的学生
select sno
from sc
where cno = 1;

-- 第二步：找出学过2号课程的学生
select sno
from sc
where cno = 2;

-- 第三步：将第一步和第二部进行联合
select sno
from sc
where cno = 1 and sno in (
    select sno
    from sc 
    where cno = 2
);

-- 第四步：将上面结果和s表进行联合
select sc.sno, s.sname
from sc
    join s on sc.sno = s.sno
where sc.cno = 1 and sc.sno in (
    select sno
    from sc
    where cno = 2
);

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

-- 因为要列出所有员工，且最高的领导的领导为 NULL，因此需要使用左连接
select e1.ename AS empName, e2.`ename` as mgrName
from emp as e1
    left join emp as e2
    on e1.mgr = e2.`empno`;

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

-- 第一次join连接找出所有员工的编号及其领导的编号和姓名
select e1.empno , e1.ename empName, e2.empno mgrEmpno, e2.ename mgrEname
from emp as e1
    join emp as e2 on e1.mgr = e2.empno;

-- 第二次join连接找出对应的部门名称
select e1.empno, e1.ename, d.dname
from emp as e1
    join emp as e2 on e1.mgr = e2.empno
    join dept as d on e1.`deptno` = d.`deptno`;

-- 最终使用where过滤出受雇日期早于其上级的员工
select e1.empno , e1.ename, d.dname
from emp as e1
    join emp as e2 on e1.mgr = e2.empno
    join dept as d on e1.`deptno` = d.`deptno`
WHERE e1.`hiredate` < e2.hiredate;

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

-- 没有员工的部门也列出，因此需要使用左连接或右连接
SELECT d.dname, e.*
FROM dept d
    LEFT JOIN emp AS e ON d.deptno = e.deptno;

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

SELECT d.dname, d.`deptno`, COUNT(*) AS empCount
FROM dept d
    JOIN emp e ON d.`deptno` = e.`deptno`
GROUP BY d.dname, d.`deptno`
HAVING empCount >= 5;

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

-- 先查到simith的薪水
SELECT sal
FROM emp
WHERE ename = 'simith';
-- 再找出薪水比simith多的所有员工
SELECT *
FROM emp e
WHERE sal > (
    SELECT sal
    FROM emp
    WHERE ename = 'simith'
);

19、列出所有岗位为clerk的姓名及部门名称，部门人数

-- 先求每个部门的编号和人数
SELECT deptno, COUNT(*) emp_count
FROM emp
GROUP BY deptno;
-- 第一次join连接查出部门名称，第二次join连接查出部门人数
-- 最后过滤出岗位为clerk的员工姓名
SELECT e.ename, e.job, a.dname, b.emp_count
FROM emp e
    JOIN dept a ON e.`deptno` = a.deptno
    JOIN (
        SELECT deptno, COUNT(*) AS emp_count
        FROM emp
        GROUP BY deptno
    ) b
    ON e.`deptno` = b.deptno
WHERE job = 'clerk';

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

-- 对job分组，从而求出每组中的最低薪水和人数
SELECT job, MIN(sal) AS minSal, COUNT(*) AS empNum
FROM emp
GROUP BY job
HAVING minSal > 1500;

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

-- 先查出销售部的部门编号
SELECT deptno, dname
FROM dept
WHERE dname = "sales";
-- 再列出在部门编号等于上一步查询到的部门编号的部门工作的员工姓名
SELECT e.ename
FROM emp e
WHERE e.`deptno` IN (
    SELECT deptno
    FROM dept
    WHERE dname = "sales"
);

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

-- 先求出公司平均薪水
SELECT AVG(sal) AS avgsal
FROM emp;
-- 使用三次join连接分别查询出每一个员工所在的部门、上级领导人姓名、薪水等级
SELECT e.empno, e.ename, d.deptno, dname, e2.empno AS mgrNmpno, e2.ename mgrName, s.grade
FROM emp e
    LEFT JOIN dept d ON e.deptno = d.deptno
    LEFT JOIN emp e2 ON e.mgr = e2.empno
    LEFT JOIN salgrade s ON e.sal BETWEEN s.losal AND s.hisal;
-- 最后过滤出薪水高于公司平均薪水的
SELECT e.empno, e.ename, dname, e2.ename mgrName, s.grade
FROM emp e
    LEFT JOIN dept d ON e.deptno = d.deptno
    LEFT JOIN emp e2 ON e.mgr = e2.empno
    LEFT JOIN salgrade s ON e.sal BETWEEN s.losal AND s.hisal
WHERE e.sal > (
    SELECT AVG(sal)
    FROM emp
);

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

-- 先找出scott所从事的工作
SELECT job
FROM emp e
WHERE ename = 'scott';
-- 找出从事以上工作的所有员工及部门名称
SELECT e.empno, e.ename, d.dname
FROM emp e
    LEFT JOIN dept d ON e.deptno = d.deptno
WHERE e.job = (
    SELECT job
    FROM emp e
    WHERE ename = 'scott'    
) and e.ename <> "SCOTT";

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

-- 第一步：先查出部门30中所有员工的薪水并去重
SELECT distinct sal
FROM emp
WHERE deptno = 20;
-- 然后找出其他员工的姓名和薪水
-- 输出为空
SELECT ename, sal
FROM emp
WHERE sal IN (
    SELECT distinct sal
    FROM emp
    WHERE deptno = 20
) AND deptno <> 20;

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

-- 第一步：先查出部门30中所有员工中的最高薪水
SELECT MAX(sal)
FROM emp
WHERE deptno = 30;
-- 第二步：然后找出其他员工的姓名和薪水和部门名称
SELECT e.ename, e.sal, d.dname
FROM emp e
    JOIN dept d ON e.deptno = d.deptno
WHERE e.sal > (
    SELECT MAX(sal)
    FROM emp
    WHERE deptno = 30
) AND e.deptno <> 30;

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

-- 要求列出每个部门，则部门中没有员工的也要列出，所以要使用外连接
-- 先列出每个部门的员工数量，平均薪水
SELECT d.deptno, COUNT(e.ename) AS empNum, AVG(e.sal) AS avgsal
FROM dept d
    LEFT JOIN emp e ON d.deptno = e.deptno
GROUP BY d.deptno;
-- 使用to_days()将时间转换为公元元年到某时间点的天数
SELECT 
    d.deptno, 
    COUNT(e.ename) empNum, 
    IFNULL(AVG(e.sal), 0) avgSal,
    IFNULL(AVG((TO_DAYS(NOW()) - TO_DAYS(e.hiredate))/365), 0) avgTime
FROM dept d
    LEFT JOIN emp e ON d.deptno = e.deptno
GROUP BY d.deptno;

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

SELECT e.ename, d.dname, e.sal
FROM emp e
    LEFT JOIN dept d ON e.deptno = d.deptno;

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

-- 要求列出所有部门，所以需要外连接
-- 先列出每个部门的人数
SELECT d.deptno, COUNT(e.ename)
FROM dept d
    LEFT JOIN emp e ON d.deptno = e.deptno
GROUP BY d.deptno;
-- 再求出部门详细信息
SELECT d.*, t.empNum
FROM dept d
    JOIN (
        SELECT d.deptno, COUNT(e.ename) AS empNum
        FROM dept d
        LEFT JOIN emp e ON d.deptno = e.deptno
        GROUP BY d.deptno
    ) t
    ON d.deptno = t.deptno;

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

-- 第一步：按工作岗位分组，使用min()函数求工资最小值
SELECT job, MIN(sal)
FROM emp
GROUP BY job;

-- 然后从员工表emp中找出岗位和工资与上表相同的员工
SELECT e.ename, t.job, t.minsal
FROM emp e
    JOIN (
        SELECT e.job, MIN(e.sal) AS minsal
        FROM emp e
        GROUP BY e.job       
    ) t
    ON e.job = t.job AND e.sal = t.minsal;

30、列出各个部门的manager的最低薪水

-- 先查询所有是manager身份的员工信息
SELECT *
FROM emp e
    JOIN emp e2 ON e.empno = e2.mgr;
-- 按部门分组后，取最低薪水
SELECT e.deptno, MIN(e.sal) AS minsal
FROM emp e
    JOIN emp e2 ON e.empno = e2.mgr
GROUP BY e.deptno;

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

SELECT ename, sal * 12 AS annual_sal
FROM emp
ORDER BY annual_sal;

32、求出员工领导的薪水超过3000的员工姓名和领导姓名

SELECT e1.ename, e2.ename, e2.sal
FROM emp e1
    JOIN emp e2 ON e1.mgr = e2.`empno`
WHERE e2.sal > 3000;

33、求出部门名称中，带有‘s’字符的部门员工的工资合计，部门人数

-- 先求出部门名称中带有's'字符的部门编号
-- 有符合条件的部门是没有员工的，所以需要使用外联，保证部门信息完整列出
SELECT
    d.deptno, 
    d.dname,
    IFNULL(SUM(e.sal), 0) AS sumsal,
    IFNULL(COUNT(e.ename), 0) AS totalemp
FROM dept d
    LEFT JOIN emp e ON d.deptno = e.deptno
WHERE d.dname LIKE '%s%'
GROUP BY d.deptno;

34、给任职时间超过38年的员工加薪10%

-- 第一步：创建emp_bak
CREATE TABLE emp_bak AS SELECT * FROM emp;

-- 第二步：使用(to_days(now())-to_days(hiredate))/35 >30
UPDATE emp_bak
SET sal = sal * 1.1
WHERE (TO_DAYS(NOW()) - TO_DAYS(hiredate))/365 > 30;
-- 查看
SELECT * FROM emp_bak;

日期：2020年5月15日