34道经典SQL试题

最新推荐文章于 2023-04-15 22:28:39 发布

狗辣子

最新推荐文章于 2023-04-15 22:28:39 发布

阅读量318

点赞数 1

分类专栏： Database

本文链接：https://blog.csdn.net/zhelong19830726/article/details/100541065

版权

Database 专栏收录该内容

9 篇文章 0 订阅

订阅专栏

创建部门表：

DROP TABLE IF EXISTS `dept`;
CREATE TABLE `dept` (
  `deptno` int(2) NOT NULL COMMENT '部门编号',
  `dname` varchar(14) DEFAULT NULL COMMENT '部门名称',
  `loc` varchar(13) DEFAULT NULL COMMENT '位置',
  PRIMARY KEY (`DEPTNO`)
) ENGINE=InnoDB;

插入语句：

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 `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`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='员工编号';

插入语句：

INSERT INTO `emp` VALUES ('7369', 'SMITH', 'CLERK', '7902', '1980-12-17', '800.00', null, '20');
INSERT INTO `emp` VALUES ('7499', 'ALLEN', 'SALESMAN', '7698', '1981-02-20', '1600.00', '300.00', '30');
INSERT INTO `emp` VALUES ('7521', 'WARD', 'SALESMAN', '7698', '1981-02-22', '1250.00', '500.00', '30');
INSERT INTO `emp` VALUES ('7566', 'JONES', 'MANAGER', '7839', '1981-04-02', '2975.00', null, '20');
INSERT INTO `emp` VALUES ('7654', 'MARTIN', 'SALESMAN', '7698', '1981-09-28', '1250.00', '1400.00', '30');
INSERT INTO `emp` VALUES ('7698', 'BLAKE', 'MANAGER', '7839', '1981-05-01', '2850.00', null, '30');
INSERT INTO `emp` VALUES ('7782', 'CLARK', 'MANAGER', '7839', '1981-06-09', '2450.00', null, '10');
INSERT INTO `emp` VALUES ('7788', 'SCOTT', 'ANALYST', '7566', '1987-04-19', '3000.00', null, '20');
INSERT INTO `emp` VALUES ('7839', 'KING', 'PRESIDENT', null, '1981-11-17', '5000.00', null, '10');
INSERT INTO `emp` VALUES ('7844', 'TURNER', 'SALESMAN', '7698', '1981-09-08', '1500.00', '0.00', '30');
INSERT INTO `emp` VALUES ('7876', 'ADAMS', 'CLERK', '7788', '1981-05-23', '1100.00', null, '20');
INSERT INTO `emp` VALUES ('7900', 'JAMES', 'CLERK', '7698', '1981-12-03', '950.00', null, '30');
INSERT INTO `emp` VALUES ('7902', 'FORD', 'ANALYST', '7566', '1981-12-03', '3000.00', null, '20');
INSERT INTO `emp` VALUES ('7934', 'MILLER', 'CLERK', '7782', '1982-01-23', '1300.00', null, '10');

创建薪水等级表：

DROP TABLE IF EXISTS `salgrade`;
CREATE TABLE `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', '9999');

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

第一步：求出每个部门的最高薪水

SELECT
    e.deptno, MAX(e.sal) AS maxsal
FROM
    emp e
GROUP BY
    e.deptno;

+--------+---------+
| deptno | maxsal  |
+--------+---------+
|     10 | 5000.00 |
|     20 | 3000.00 |
|     30 | 2850.00 |
+--------+---------+

将以上查询结果当成一个临时表t(deptno,maxsal)

SELECT 
    e.deptno, e.ename, t.maxsal, e.sal
FROM
    (SELECT
            e.deptno, MAX(e.sal) AS maxsal
        FROM
            emp e
        GROUP BY
            e.deptno)t
JOIN
    emp e
ON
    t.deptno = e.deptno
WHERE
    t.maxsal = e.sal
ORDER BY
    e.deptno;

+--------+-------+---------+---------+
| deptno | ename | maxsal  | sal     |
+--------+-------+---------+---------+
|     10 | KING  | 5000.00 | 5000.00 |
|     20 | SCOTT | 3000.00 | 3000.00 |
|     20 | FORD  | 3000.00 | 3000.00 |
|     30 | BLAKE | 2850.00 | 2850.00 |
+--------+-------+---------+---------+

分析下：
首先group by 首先经常和聚合函数max等配合使用，第二使用了group by 在select后面的查询字段只能是group by 后面指定的字段不能是其他字段
on是两个表联合查询连接起来生成一个临时表，where是在生成临时表的基础上，对生成的临时表进行条件帅选
t.deptno = e.deptno 表示两个表生成临时表的关系是 emp表中的部门编号必须等于 t表中的部门编号
where之后的条件是：emp表和t表已经生成了临时表，然后对临时表进行条件过滤

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

2.哪些人的薪水在部门平均薪水之上
第一步：求出每个部门的平均薪水

SELECT
    e.deptno,avg(e.sal) AS avgsal
FROM
    emp e
GROUP BY 
    e.deptno;

+--------+-------------+
| deptno | avgsal      |
+--------+-------------+
|     10 | 2916.666667 |
|     20 | 2175.000000 |
|     30 | 1566.666667 |
+--------+-------------+

将以上查询结果当成临时表t(deptno,avgsal)

SELECT
    t.deptno,e.ename
FROM
    (SELECT
        e.deptno,AVG(e.sal) AS avgsal
    FROM
        emp e
    GROUP BY 
        e.deptno) t
JOIN
    emp e
ON
    e.deptno = t.deptno
WHERE
    e.sal > t.avgsal;

+--------+-------+
| deptno | ename |
+--------+-------+
|     30 | ALLEN |
|     20 | JONES |
|     30 | BLAKE |
|     20 | SCOTT |
|     10 | KING  |
|     20 | FORD  |
+--------+-------+

【3】取得部门中（所有人的）平均薪水等级
第一种情况：emp表中按照部门进行分组，求出每个组的平均工资，看每个组的平均工资属于那个等级

第一步：求出部门的平均薪水

SELECT
    e.deptno, AVG(e.sal) AS avgsal
FROM
    emp e
GROUP BY
    e.deptno;

将以下查询结果当成临时表t(deptno,avgsal)

+--------+-------------+
| deptno | avgsal      |
+--------+-------------+
|     10 | 2916.666667 |
|     20 | 2175.000000 |
|     30 | 1566.666667 |
+--------+-------------+

SELECT * FROM salgrade;

+-------+-------+-------+
| GRADE | LOSAL | HISAL |
+-------+-------+-------+
|     1 |   700 |  1200 |
|     2 |  1201 |  1400 |
|     3 |  1401 |  2000 |
|     4 |  2001 |  3000 |
|     5 |  3001 |  9999 |
+-------+-------+-------+

SELECT
    t.deptno, t.avgsal, s.grade
FROM
    (SELECT
            e.deptno, AVG(e.sal) AS avgsal
     FROM
            emp e
     GROUP BY
            e.deptno) t
JOIN
    salgrade s
ON
    t.avgsal BETWEEN s.losal AND s.hisal;

+--------+-------------+-------+
| deptno | avgsal      | grade |
+--------+-------------+-------+
|     30 | 1566.666667 |     3 |
|     10 | 2916.666667 |     4 |
|     20 | 2175.000000 |     4 |
+--------+-------------+-------+

第二种情况：首先求出每个人的薪水属于那个等级，然后进行分组
3.2 取得部门中所有人的平均的薪水等级
第一步：求出每个人的薪水等级

SELECT
    e.deptno, e.ename, s.grade
FROM
    emp e
JOIN
    salgrade s
ON
    e.sal BETWEEN s.losal AND s.hisal
ORDER BY
    e.deptno;

+--------+--------+-------+
| deptno | ename  | grade |
+--------+--------+-------+
|     10 | CLARK  |     4 |
|     10 | MILLER |     2 |
|     10 | KING   |     5 |
|     20 | ADAMS  |     1 |
|     20 | SMITH  |     1 |
|     20 | FORD   |     4 |
|     20 | SCOTT  |     4 |
|     20 | JONES  |     4 |
|     30 | BLAKE  |     4 |
|     30 | JAMES  |     1 |
|     30 | ALLEN  |     3 |
|     30 | WARD   |     2 |
|     30 | TURNER |     3 |
|     30 | MARTIN |     2 |
+--------+--------+-------+

将以上查询结果当成临时表t(deptno,ename,grade)

SELECT
    t.deptno, AVG(t.grade) AS avgGrade
FROM
    (SELECT
        e.deptno, e.ename, s.grade
    FROM
        emp e
    JOIN
        salgrade s
    ON
        e.sal BETWEEN s.losal AND s.hisal) t
GROUP BY
    t.deptno;

+--------+----------+
| deptno | avgGrade |
+--------+----------+
|     10 |   3.6667 |
|     20 |   2.8000 |
|     30 |   2.5000 |
+--------+----------+

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

SELECT sal FROM emp ORDER BY sal DESC LIMIT 1;

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 
    e.deptno, avg(e.sal) AS avgsal 
FROM 
    emp e
GROUP BY
    e.deptno;

+--------+-------------+
| deptno | avgsal      |
+--------+-------------+
|     10 | 2916.666667 |
|     20 | 2175.000000 |
|     30 | 1566.666667 |
+--------+-------------+

第二步：将以上结果当成临时表t(deptno, avgsal)

SELECT 
    MAX(t.avgsal) AS maxAvgSal 
FROM
    (SELECT 
         e.deptno, avg(e.sal) AS avgsal 
     FROM 
         emp e
     GROUP BY
         e.deptno) t;

+-------------+
| maxAvgSal   |
+-------------+
| 2916.666667 |
+-------------+

SELECT 
    e.deptno, avg(e.sal) AS avgsal 
FROM 
    emp e
GROUP BY
    e.deptno
HAVING
    avgsal = (SELECT 
                  MAX(t.avgsal) AS maxAvgSal 
              FROM
                  (SELECT 
                       e.deptno, avg(e.sal) AS avgsal 
                   FROM 
                       emp e
                   GROUP BY
                       e.deptno) t);

+--------+-------------+
| deptno | avgsal      |
+--------+-------------+
|     10 | 2916.666667 |
+--------+-------------+

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

SELECT 
    e.deptno, d.dname, avg(e.sal) AS avgsal 
FROM 
    emp e
JOIN
    dept d
ON 
    e.deptno = d.deptno
GROUP BY
    e.deptno
HAVING
    avgsal = (SELECT 
                  MAX(t.avgsal) AS maxAvgSal 
              FROM
                  (SELECT 
                       e.deptno, avg(e.sal) AS avgsal 
                   FROM 
                       emp e
                   GROUP BY
                       e.deptno) t);

+--------+------------+-------------+
| deptno | dname      | avgsal      |
+--------+------------+-------------+
|     10 | ACCOUNTING | 2916.666667 |
+--------+------------+-------------+

【7】求平均薪水的等级最低的部门的部门名称

第一步：部门的平均薪水

SELECT
    e.deptno, avg(e.sal) AS avgsal
FROM 
    emp e
GROUP BY
    e.deptno;

+--------+-------------+
| deptno | avgsal      |
+--------+-------------+
|     10 | 2916.666667 |
|     20 | 2175.000000 |
|     30 | 1566.666667 |
+--------+-------------+

第二步：将以上结果当成临时表t(deptno, avgsal)与salgrade表进行表连接：t.avgsal between s.losal and s.hisal

SELECT
    t.deptno, t.dname, s.grade
FROM
    (SELECT
         e.deptno, d.dname, avg(e.sal) AS avgsal
     FROM 
         emp e
     JOIN
         dept d
     ON
         e.deptno = d.deptno
     GROUP BY
         e.deptno) t
JOIN
    salgrade s
ON
    t.avgsal BETWEEN s.losal AND s.hisal;

+--------+------------+-------+
| deptno | dname       | grade |
+--------+------------+-------+
|     10 | ACCOUNTING |     4 |
|     20 | RESEARCH   |     4 |
|     30 | SALES      |     3 |
+--------+------------+-------+

第三步：将以上查询结果当成一张临时表t

SELECT 
    MIN(tt.grade) AS minGrade 
FROM 
    (SELECT
          t.deptno,t.dname,s.grade
     FROM
         (SELECT
              e.deptno, d.dname, AVG(e.sal) AS avgsal
          FROM
              emp e
          JOIN
              dept d
          ON
              e.deptno = d.deptno
          GROUP BY
              e.deptno, d.dname) t
     JOIN
         salgrade s
     ON
         t.avgsal BETWEEN s.losal AND s.hisal) tt;

+----------+
| minGrade |
+----------+
|        3 |
+----------+

SELECT
    t.deptno, t.dname, s.grade
FROM
    (SELECT
         e.deptno, d.dname, AVG(e.sal) AS avgsal
     FROM
         emp e
     JOIN
         dept d
     ON
         e.deptno = d.deptno
     GROUP BY
         e.deptno,d.dname) t
JOIN
    salgrade s
ON
    t.avgsal BETWEEN s.losal AND s.hisal
WHERE
    s.grade = (SELECT 
                   MIN(t.grade) AS minGrade 
               FROM 
                   (SELECT
                        t.deptno,t.dname,s.grade
                    FROM 
                        (SELECT
                             e.deptno, d.dname, AVG(e.sal) AS avgsal
                         FROM
                            emp e
                         JOIN
                            dept d
                         ON
                            e.deptno = d.deptno
                         GROUP BY
                            e.deptno, d.dname) t
                JOIN
                    salgrade s
                ON
                    t.avgsal BETWEEN s.losal AND s.hisal) t);

+--------+-------+-------+
| deptno | dname | grade |
+--------+-------+-------+
|     30 | SALES |     3 |
+--------+-------+-------+

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

第一步：找出普通员工(员工代码没有出现在mgr上的)
1.1 先找出mgr有哪些人

SELECT DISTINCT mgr FROM emp;

+------+
| mgr  |
+------+
| 7902 |
| 7698 |
| 7839 |
| 7566 |
| NULL |
| 7788 |
| 7782 |
+------+

SELECT * FROM emp WHERE empno IN(SELECT DISTINCT mgr FROM emp);

+-------+-------+-----------+------+------------+---------+------+--------+
| empno | ename | job       | mgr  | hiredate   | sal     | comm | deptno |
+-------+-------+-----------+------+------------+---------+------+--------+
|  7902 | FORD  | ANALYST   | 7566 | 1981-12-03 | 3000.00 | NULL |     20 |
|  7698 | BLAKE | MANAGER   | 7839 | 1981-05-01 | 2850.00 | NULL |     30 |
|  7839 | KING  | PRESIDENT | NULL | 1981-11-17 | 5000.00 | NULL |     10 |
|  7566 | JONES | MANAGER   | 7839 | 1981-04-02 | 2975.00 | NULL |     20 |
|  7788 | SCOTT | ANALYST   | 7566 | 1987-04-19 | 3000.00 | NULL |     20 |
|  7782 | CLARK | MANAGER   | 7839 | 1981-06-09 | 2450.00 | NULL |     10 |
+-------+-------+-----------+------+------------+---------+------+--------+

SELECT MAX(sal) AS maxsal FROM emp WHERE empno NOT IN(SELECT DISTINCT mgr FROM emp WHERE mgr IS NOT NULL);

+---------+
| maxsal  |
+---------+
| 1600.00 |
+---------+

not in不会自动忽略空值
in会自动忽略空值

SELECT 
    ename 
FROM 
    emp 
WHERE 
    sal > (SELECT 
               MAX(sal) AS maxsal 
           FROM 
               emp 
           WHERE 
               empno NOT IN(SELECT 
                                DISTINCT mgr 
                            FROM 
                                emp
                            WHERE mgr IS NOT NULL));

+-------+
| ename |
+-------+
| JONES |
| BLAKE |
| CLARK |
| SCOTT |
| KING  |
| FORD  |
+-------+

这里有一个很关键的地方，not in 没有排除null值，如果存在null值和not in 做计算，得带的值就是null

mysql> SELECT * FROM emp WHERE empno NOT IN (SELECT DISTINCT mgr FROM emp);
Empty set (0.00 sec)

mysql>

SELECT DISTINCT mgr FROM emp 的结果存在null值
这里not in 没有去掉null值得到的结果就是空

SELECT 
    ename, sal
FROM
    emp
WHERE sal > (SELECT 
                 MAX(sal) AS maxsal 
             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】取得薪水最高的前五名员工

SELECT * FROM emp ORDER BY sal DESC LIMIT 0,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】取得薪水最高的第六到第十名员工

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 |   0.00 |     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名员工

SELECT * FROM emp ORDER BY hiredate DESC LIMIT 5;

+-------+--------+-----------+------+------------+---------+------+--------+
| empno | ename  | job       | mgr  | hiredate   | sal     | comm | deptno |
+-------+--------+-----------+------+------------+---------+------+--------+
|  7788 | SCOTT  | ANALYST   | 7566 | 1987-04-19 | 3000.00 | NULL |     20 |
|  7934 | MILLER | CLERK     | 7782 | 1982-01-23 | 1300.00 | NULL |     10 |
|  7902 | FORD   | ANALYST   | 7566 | 1981-12-03 | 3000.00 | NULL |     20 |
|  7900 | JAMES  | CLERK     | 7698 | 1981-12-03 |  950.00 | NULL |     30 |
|  7839 | KING   | PRESIDENT | NULL | 1981-11-17 | 5000.00 | NULL |     10 |
+-------+--------+-----------+------+------------+---------+------+--------+

【12】取得每个薪水等级有多少员工
第一步：查询出每个员工的薪水等级

SELECT 
    e.ename, s.grade 
FROM 
    emp e
JOIN
    salgrade s
ON
    e.sal BETWEEN s.losal AND s.hisal
ORDER BY
    s.grade;

+--------+-------+
| ename  | grade |
+--------+-------+
| SMITH  |     1 |
| JAMES  |     1 |
| ADAMS  |     1 |
| WARD   |     2 |
| MARTIN |     2 |
| MILLER |     2 |
| TURNER |     3 |
| ALLEN  |     3 |
| JONES  |     4 |
| BLAKE  |     4 |
| CLARK  |     4 |
| SCOTT  |     4 |
| FORD   |     4 |
| KING   |     5 |
+--------+-------+

将以上查询结果当成临时表t(ename, grade)

SELECT
    t.grade, COUNT(t.ename) AS totalEmp
FROM
    (SELECT 
         e.ename, s.grade 
     FROM 
         emp e
     JOIN
         salgrade s
     ON
         e.sal BETWEEN s.losal AND s.hisal) t
GROUP BY
    t.grade;

+-------+----------+
| grade | totalEmp |
+-------+----------+
|     1 |        3 |
|     2 |        3 |
|     3 |        2 |
|     4 |        5 |
|     5 |        1 |
+-------+----------+

【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 s(
    sno INT(4) PRIMARY KEY AUTO_INCREMENT,
    sname VARCHAR(32)
);

INSERT INTO s(sname) VALUES('zhangsan');
INSERT INTO s(sname) VALUES('lisi');
INSERT INTO s(sname) VALUES('wangwu');
INSERT INTO s(sname) VALUES('zhaoliu');

CREATE TABLE c(
    cno INT(4) PRIMARY KEY AUTO_INCREMENT,
    cname VARCHAR(32),
    cteacher VARCHAR(32)
)ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO c(cname, cteacher) VALUES('Java', '吴老师');
INSERT INTO c(cname, cteacher) VALUES('C++', '王老师');
INSERT INTO c(cname, cteacher) VALUES('C##', '张老师');
INSERT INTO c(cname, cteacher) VALUES('MySQL', '郭老师');
INSERT INTO c(cname, cteacher) VALUES('Oracle', '黎明');

首先分析下上面表的设计
一个学生可以选修多门课程
同一门课程可以被多个学生选择
学生和课程之间是多对多的关系
所以就要引入第三张中间表来解决学生和课程之间的关系

CREATE TABLE sc(
    sno INT(4),
    cno INT(4),
    scgrade DOUBLE(3, 1),
    PRIMARY KEY(sno, cno)
);

INSERT INTO sc(sno, cno, scgrade) VALUES(1, 1, 30);
INSERT INTO sc(sno, cno, scgrade) VALUES(1, 2, 50);
INSERT INTO sc(sno, cno, scgrade) VALUES(1, 3, 80);
INSERT INTO sc(sno, cno, scgrade) VALUES(1, 4, 90);
INSERT INTO sc(sno, cno, scgrade) VALUES(1, 5, 70);
INSERT INTO sc(sno, cno, scgrade) VALUES(2, 2, 80);
INSERT INTO sc(sno, cno, scgrade) VALUES(2, 3, 50);
INSERT INTO sc(sno, cno, scgrade) VALUES(2, 4, 70);
INSERT INTO sc(sno, cno, scgrade) VALUES(2, 5, 80);
INSERT INTO sc(sno, cno, scgrade) VALUES(3, 1, 60);
INSERT INTO sc(sno, cno, scgrade) VALUES(3, 2, 70);
INSERT INTO sc(sno, cno, scgrade) VALUES(3, 3, 80);
INSERT INTO sc(sno, cno, scgrade) VALUES(4, 3, 50);
INSERT INTO sc(sno, cno, scgrade) VALUES(4, 4, 80);

第三张表的sno的值必须来自学生表
cno必须来自课程表
然后建立一个sno 和cno的一个复合主键
接下来我们就可以做题了
/*
1、找出没选过“黎明”老师的所有学生姓名。
2、列出2门以上（含2门）不及格学生姓名及平均成绩。
3、即学过1号课程又学过2号课所有学生的姓名。
*/
第一题的第一种做法：

--先找出选过黎明老师的学生编号 -> 黎明老师的授课的编号

SELECT cno FROM c WHERE cteacher = '黎明';

SELECT sno FROM sc WHERE cno = (SELECT cno FROM c WHERE cteacher = '黎明');

SELECT * FROM s WHERE sno NOT IN(SELECT sno FROM sc WHERE cno = (SELECT cno FROM c WHERE cteacher = '黎明'));

第一题的第二种做法：

第一步：找到黎明老师所上课对应的课程对应的课程编号

SELECT cno FROM c WHERE cteacher = '黎明';

第二步：求出那些学生选修了黎明老师的课程

SELECT 
    sno 
FROM 
    sc
JOIN
    (SELECT cno FROM c WHERE cteacher = '黎明') t
ON
    sc.cno = t.cno;

第三步：求出那些学生没有选择黎明老师的课

SELECT 
    sno,sname
FROM
    s  
WHERE
    sno NOT IN (SELECT 
                    sno 
                FROM 
                    sc
                JOIN
                    (SELECT cno FROM c WHERE cteacher = '黎明') t
                ON
                    sc.cno = t.cno);

+-----+---------+
| sno | sname   |
+-----+---------+
|   3 | wangwu  |
|   4 | zhaoliu |
+-----+---------+

第二题：
2、列出2门以上（含2门）不及格学生姓名及平均成绩。
思路一：在sc表中首先按照学生编号进行分组，得到哪些学生的有两门以上的成绩低于60分

SELECT 
    sc.sno, COUNT(*) AS studentNum
FROM 
    sc 
WHERE 
    scgrade < 60
GROUP BY
    sc.sno
HAVING
    studentNum >= 2;

第二步：查询出该学生对应的编号

SELECT 
    a.sno, a.sname
FROM
    s AS a
JOIN
    (SELECT 
         sc.sno, COUNT(*) AS studentNum
     FROM 
         sc 
     WHERE 
         scgrade < 60
     GROUP BY
         sc.sno
     HAVING
         studentNum >= 2) AS b
ON
    a.sno = b.sno;

+-----+----------+
| sno | sname    |
+-----+----------+
|   1 | zhangsan |
+-----+----------+

接下来需要获得该学生的平均成绩，我们得到该学生的sno的值是1，我们需要在sc表中求出该学生的平均成绩，首先需要将上面的表和sc表关联起来形成一个临时表，然后对这个临时表按照sno进行group by

我们来看下下面的代码

SELECT 
    sc.sno, COUNT(*) AS studentNum
FROM 
    sc 
WHERE 
    scgrade < 60
GROUP BY
    sc.sno
HAVING
    studentNum >= 2;

第二步：查询出该学生对应的编号

SELECT 
    a.sno, a.sname
FROM
    s AS a
JOIN
    (SELECT 
         sc.sno, COUNT(*) AS studentNum
     FROM 
         sc 
     WHERE 
         scgrade < 60
     GROUP BY
         sc.sno
     HAVING
         studentNum >= 2
    ) AS b
ON
    a.sno = b.sno;

第三步得到该学生的平均成绩，把上面的表当成临时表m

SELECT 
    m.sno, m.sname, AVG(d.scgrade)
FROM
    sc AS d
JOIN
    (SELECT 
         a.sno, a.sname
     FROM
         s AS a
     JOIN
         (SELECT 
              sc.sno, COUNT(*) AS studentNum
          FROM 
              sc 
          WHERE 
              scgrade < 60
          GROUP BY
              sc.sno
          HAVING
              studentNum >= 2
         ) AS b
     ON
         a.sno = b.sno
    ) AS m
ON
    m.sno = d.sno   
GROUP BY
    d.sno;

+-----+----------+----------------+
| sno | sname    | AVG(d.scgrade) |
+-----+----------+----------------+
|   1 | zhangsan |       64.00000 |
+-----+----------+----------------+

第三题：既学过1号课程又学过2号课程所有学生的姓名

SELECT 
    s.sname
FROM 
    sc 
JOIN
    s
ON
    sc.sno = s.sno
WHERE 
    cno = 1 AND sc.sno IN (SELECT sno FROM sc WHERE cno = 2);

+----------+
| sname    |
+----------+
| zhangsan |
| wangwu   |
+----------+

【14】列出所有员工及领导的名字

SELECT 
    e.ename, b.ename AS leadername
FROM
    emp e
LEFT JOIN
    emp b
ON 
    e.mgr = b.empno;

+--------+------------+
| ename  | leadername |
+--------+------------+
| SMITH  | FORD       |
| ALLEN  | BLAKE      |
| WARD   | BLAKE      |
| JONES  | KING       |
| MARTIN | BLAKE      |
| BLAKE  | KING       |
| CLARK  | KING       |
| SCOTT  | JONES      |
| KING   | NULL       |
| TURNER | BLAKE      |
| ADAMS  | SCOTT      |
| JAMES  | BLAKE      |
| FORD   | JONES      |
| MILLER | CLARK      |
+--------+------------+

【15】列出受雇日期早于其直接上级的所有员工编号、姓名、部门名称
思路一：第一步将emp a看成员工表，将emp b 看成领导表，员工表的mgr字段应该等于领导表的主键字段

SELECT 
    e.empno, e.ename
FROM
    emp e
JOIN
    emp b
ON
    e.mgr = b.empno
WHERE
    e.hiredate < b.hiredate;

+-------+-------+
| empno | ename |
+-------+-------+
|  7369 | SMITH |
|  7499 | ALLEN |
|  7521 | WARD  |
|  7566 | JONES |
|  7698 | BLAKE |
|  7782 | CLARK |
|  7876 | ADAMS |
+-------+-------+

第二步：显示上面员工的部门名称，将emp a员工表和dept d进行关联

SELECT 
    d.dname, e.empno, e.ename
FROM
    emp e
JOIN
    emp b
ON
    e.mgr = b.empno
JOIN
    dept d
ON
    e.deptno = d.deptno
WHERE
    e.hiredate < b.hiredate;

+------------+-------+-------+
| dname      | empno | ename |
+------------+-------+-------+
| ACCOUNTING |  7782 | CLARK |
| RESEARCH   |  7369 | SMITH |
| RESEARCH   |  7566 | JONES |
| RESEARCH   |  7876 | ADAMS |
| SALES      |  7499 | ALLEN |
| SALES      |  7521 | WARD  |
| SALES      |  7698 | BLAKE |
+------------+-------+-------+

下面考查右连接

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

SELECT 
    d.dname, e.*
FROM
    emp e
RIGHT JOIN
    dept d
ON
    e.deptno = d.deptno;

+------------+-------+--------+-----------+------+------------+---------+---------+--------+
| dname      | empno | ename  | job       | mgr  | hiredate   | sal     | comm    | deptno |
+------------+-------+--------+-----------+------+------------+---------+---------+--------+
| ACCOUNTING |  7782 | CLARK  | MANAGER   | 7839 | 1981-06-09 | 2450.00 |    NULL |     10 |
| ACCOUNTING |  7839 | KING   | PRESIDENT | NULL | 1981-11-17 | 5000.00 |    NULL |     10 |
| ACCOUNTING |  7934 | MILLER | CLERK     | 7782 | 1982-01-23 | 1300.00 |    NULL |     10 |
| RESEARCH   |  7369 | SMITH  | CLERK     | 7902 | 1980-12-17 |  800.00 |    NULL |     20 |
| RESEARCH   |  7566 | JONES  | MANAGER   | 7839 | 1981-04-02 | 2975.00 |    NULL |     20 |
| RESEARCH   |  7788 | SCOTT  | ANALYST   | 7566 | 1987-04-19 | 3000.00 |    NULL |     20 |
| RESEARCH   |  7876 | ADAMS  | CLERK     | 7788 | 1981-05-23 | 1100.00 |    NULL |     20 |
| RESEARCH   |  7902 | FORD   | ANALYST   | 7566 | 1981-12-03 | 3000.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 |
| 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 |
| SALES      |  7844 | TURNER | SALESMAN  | 7698 | 1981-09-08 | 1500.00 |    0.00 |     30 |
| SALES      |  7900 | JAMES  | CLERK     | 7698 | 1981-12-03 |  950.00 |    NULL |     30 |
| OPERATIONS |  NULL | NULL   | NULL      | NULL | NULL       |    NULL |    NULL |   NULL |
+------------+-------+--------+-----------+------+------------+---------+---------+--------+
15 rows in set (0.00 sec)

【17】列出至少有5个员工的所有部门
第一步：先求出每个部门有多少员工，将emp a和部门表 dept d表进行关联，条件是e.deptno=d.deptno
第二步：然后通过分组e.deptno,过来count(e.ename) >= 5

SELECT 
    e.deptno, count(e.ename) AS totalEmp
FROM
    emp e
GROUP BY
    e.deptno
HAVING
    totalEmp >= 5;

+--------+----------+
| deptno | totalEmp |
+--------+----------+
|     20 |        5 |
|     30 |        6 |
+--------+----------+
2 rows in set (0.00 sec)

这里比较关键：第一点使用了group by 字段，select 后面的字段只能是group by后面的字段e.deptno和聚合函数对应的字段count(e.ename) as totalEmp
第二点：现在要对聚合函数的结果进行过滤，totalEmp字段不是数据库中的字段，不能使用where进行限制，只能使用having

【18】列出薪水比“SMITH”多的所有员工信息

第一步：首先求出是，smith的工资

第二步：然后求出工资高于simith的

SELECT * FROM emp WHERE sal > (SELECT sal FROM emp WHERE ename = 'SMITH');

+-------+--------+-----------+------+------------+---------+---------+--------+
| 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 |    0.00 |     30 |
|  7876 | ADAMS  | CLERK     | 7788 | 1981-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 |
+-------+--------+-----------+------+------------+---------+---------+--------+
13 rows in set (0.02 sec)

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

这是一个比较综合的题目

1、第一步在emp a表中查询出那些人的job岗位是办事员

2、将emp a表和dept d表相关联就可以得到职位是办事员的emp对应的部门名称

3、查询出每个部门对应的员工总数

4、将第三步的查询结果作为一个临时表t与第二步的查询结果进行关联，关联条件是t.deptno = d.deptno

SELECT 
    d.deptno, d.dname, e.ename
FROM
    emp e
JOIN
    dept d
ON
    e.deptno = d.deptno
WHERE
    e.job = 'CLERK';
t1

+--------+------------+--------+
| deptno | dname      | ename  |
+--------+------------+--------+
|     20 | RESEARCH   | SMITH  |
|     20 | RESEARCH   | ADAMS  |
|     30 | SALES      | JAMES  |
|     10 | ACCOUNTING | MILLER |
+--------+------------+--------+
4 rows in set (0.02 sec)

求出每个部门的员工数量

SELECT
    e.deptno, count(e.ename) AS totalEmp
FROM
    emp e
GROUP BY
    e.deptno;
t2

+--------+----------+
| deptno | totalEmp |
+--------+----------+
|     10 |        3 |
|     20 |        5 |
|     30 |        6 |
+--------+----------+
3 rows in set (0.01 sec)

SELECT 
    t1.deptno, t1.dname, t1.ename, t2.totalEmp
FROM
    (
    SELECT 
        d.deptno, d.dname, e.ename
    FROM
        emp e
    JOIN
        dept d
    ON
        e.deptno = d.deptno
    WHERE
        e.job = 'CLERK'
    ) t1
JOIN
    (
    SELECT
        e.deptno, count(e.ename) AS totalEmp
    FROM
        emp e
    GROUP BY
        e.deptno
    ) t2
ON
    t1.deptno = t2.deptno;

+--------+------------+--------+----------+
| deptno | dname      | ename  | totalEmp |
+--------+------------+--------+----------+
|     20 | RESEARCH   | SMITH  |        5 |
|     20 | RESEARCH   | ADAMS  |        5 |
|     30 | SALES      | JAMES  |        6 |
|     10 | ACCOUNTING | MILLER |        3 |
+--------+------------+--------+----------+
4 rows in set (0.01 sec)

下面考查在select 后面两个聚合函数的事业

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

第一步：求出每种工作岗位的最低薪水，并且最低薪水大于15000

第二步：在第一步的基础上求出雇员数量(count *)

第一步：先求出每种工作岗位的最低薪水

SELECT 
    e.job, MIN(e.sal) AS minsal
FROM
    emp e
GROUP BY
    e.job;

+-----------+---------+
| job       | minsal  |
+-----------+---------+
| ANALYST   | 3000.00 |
| CLERK     |  800.00 |
| MANAGER   | 2450.00 |
| PRESIDENT | 5000.00 |
| SALESMAN  | 1250.00 |
+-----------+---------+

SELECT 
    e.job, MIN(e.sal) AS minsal, COUNT(e.ename) AS totalEmp
FROM
    emp e
GROUP BY
    e.job
HAVING
    minsal > 1500;

+-----------+---------+----------+
| job       | minsal  | totalEmp |
+-----------+---------+----------+
| ANALYST   | 3000.00 |        2 |
| MANAGER   | 2450.00 |        3 |
| PRESIDENT | 5000.00 |        1 |
+-----------+---------+----------+

【21】列出在部门“SALES”<销售部>工作的员工的姓名，假定不知道销售部门的部门编号

SELECT deptno FROM dept WHERE dname = 'SALES';

+--------+
| deptno |
+--------+
|     30 |
+--------+

SELECT ename FROM emp WHERE deptno = (SELECT deptno FROM dept WHERE dname = 'SALES');

+--------+
| ename  |
+--------+
| ALLEN  |
| WARD   |
| MARTIN |
| BLAKE  |
| TURNER |
| JAMES  |
+--------+
6 rows in set (0.01 sec)

接下来是对上面知识点的全部的一个综合的复习

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

相当的经典

第一步：求出薪金高于公司平均薪金的所有员工

第二步：把第一步的结果当成临时表t 将临时表t和部门表 dept d 和工资等级表salary s进行关联，求出员工所在的部门，雇员的工资等级等

关联的条件是t.deptno = d.deptno t.salary betweent s.lower and high;

第三步：求出第一步条件下的所有的上级领导，因为有的员工没有上级领导需要使用left join 左连接

第一步：求出公司的平均薪水

SELECT AVG(sal) AS avgsal FROM emp;

+-------------+
| avgsal      |
+-------------+
| 2073.214286 |
+-------------+
1 row in set (0.00 sec)

SELECT 
    d.dname, e.ename, b.ename AS leadername, s.grade
FROM
    emp e
JOIN
    dept d
ON
    e.deptno = d.deptno
LEFT JOIN
    emp b
ON
    e.mgr = b.empno
JOIN
    salgrade s
ON
    e.sal BETWEEN s.losal AND s.hisal
WHERE
    e.sal > (SELECT AVG(sal) AS avgsal FROM emp);

+------------+-------+------------+-------+
| dname      | ename | leadername | grade |
+------------+-------+------------+-------+
| RESEARCH   | JONES | KING       |     4 |
| SALES      | BLAKE | KING       |     4 |
| ACCOUNTING | CLARK | KING       |     4 |
| RESEARCH   | SCOTT | JONES      |     4 |
| RESEARCH   | FORD  | JONES      |     4 |
| ACCOUNTING | KING  | NULL       |     5 |
+------------+-------+------------+-------+
6 rows in set (0.00 sec)

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

查询出SCOTT的工作岗位

SELECT job FROM emp WHERE ename = 'SCOTT';

+---------+
| job     |
+---------+
| ANALYST |
+---------+
1 row in set (0.00 sec)

SELECT 
    d.dname, e.*
FROM
    emp e
JOIN
    dept d
ON
    e.deptno = d.deptno
WHERE
    e.job = (
            SELECT 
                job
            FROM
                emp 
            WHERE ename = 'SCOTT'
            );

+----------+-------+-------+---------+------+------------+---------+------+--------+
| dname    | empno | ename | job     | mgr  | hiredate   | sal     | comm | deptno |
+----------+-------+-------+---------+------+------------+---------+------+--------+
| RESEARCH |  7788 | SCOTT | ANALYST | 7566 | 1987-04-19 | 3000.00 | NULL |     20 |
| RESEARCH |  7902 | FORD  | ANALYST | 7566 | 1981-12-03 | 3000.00 | NULL |     20 |
+----------+-------+-------+---------+------+------------+---------+------+--------+
2 rows in set (0.00 sec)

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

SELECT DISTINCT sal FROM emp WHERE deptno = 30;

+---------+
| sal     |
+---------+
| 1600.00 |
| 1250.00 |
| 2850.00 |
| 1500.00 |
|  950.00 |
+---------+

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) AS maxsal FROM emp WHERE deptno = 30;

+---------+
| maxsal  |
+---------+
| 2850.00 |
+---------+
1 row in set (0.00 sec)

SELECT 
    d.dname, e.ename, e.sal
FROM
    emp e
JOIN
    dept d
ON
    e.deptno = d.deptno
WHERE
    e.sal > (SELECT MAX(sal) AS maxsal FROM emp WHERE deptno = 30);

+------------+-------+---------+
| dname      | ename | sal     |
+------------+-------+---------+
| ACCOUNTING | KING  | 5000.00 |
| RESEARCH   | JONES | 2975.00 |
| RESEARCH   | SCOTT | 3000.00 |
| RESEARCH   | FORD  | 3000.00 |
+------------+-------+---------+
4 rows in set (0.01 sec)

这个题很关键

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

to_days(日期类型) -> 天数

获取数据库的系统当前时间的函数式: now()

SELECT ename, (to_days(now()) - to_days(hiredate))/365 AS serveryear FROM emp;

SELECT AVG((to_days(now()) - to_days(hiredate))/365) AS avgserveryear FROM emp;

SELECT 
    e.deptno, 
    AVG(e.sal) AS avgsal, 
    AVG((to_days(now()) - to_days(e.hiredate))/365) AS avgserveryear
FROM 
    emp e
GROUP BY
    e.deptno;

+--------+-------------+---------------+
| deptno | avgsal      | avgserveryear |
+--------+-------------+---------------+
|     10 | 2916.666667 |   37.91050228 |
|     20 | 2175.000000 |   37.13808219 |
|     30 | 1566.666667 |   38.20913242 |
+--------+-------------+---------------+
3 rows in set (0.00 sec)

【27】列出所有员工的姓名、部门名称和薪水

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

+--------+------------+---------+
| ename  | dname      | sal     |
+--------+------------+---------+
| CLARK  | ACCOUNTING | 2450.00 |
| KING   | ACCOUNTING | 5000.00 |
| MILLER | ACCOUNTING | 1300.00 |
| SMITH  | RESEARCH   |  800.00 |
| JONES  | RESEARCH   | 2975.00 |
| SCOTT  | RESEARCH   | 3000.00 |
| ADAMS  | RESEARCH   | 1100.00 |
| FORD   | RESEARCH   | 3000.00 |
| ALLEN  | SALES      | 1600.00 |
| WARD   | SALES      | 1250.00 |
| MARTIN | SALES      | 1250.00 |
| BLAKE  | SALES      | 2850.00 |
| TURNER | SALES      | 1500.00 |
| JAMES  | SALES      |  950.00 |
| NULL   | OPERATIONS |    NULL |
+--------+------------+---------+

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

SELECT 
    d.*, COUNT(e.ename)
FROM
    dept d
LEFT JOIN
    emp e
ON
    d.deptno = e.deptno
GROUP BY
    d.deptno;

+--------+------------+----------+----------------+
| deptno | dname      | loc      | COUNT(e.ename) |
+--------+------------+----------+----------------+
|     10 | ACCOUNTING | NEW YORK |              3 |
|     20 | RESEARCH   | DALLAS   |              5 |
|     30 | SALES      | CHICAGO  |              6 |
|     40 | OPERATIONS | BOSTON   |              0 |
+--------+------------+----------+----------------+

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

SELECT
    e.job, MIN(e.sal) AS minsal
FROM
    emp e
GROUP BY 
    e.job;

+-----------+---------+
| job       | minsal  |
+-----------+---------+
| ANALYST   | 3000.00 |
| CLERK     |  800.00 |
| MANAGER   | 2450.00 |
| PRESIDENT | 5000.00 |
| SALESMAN  | 1250.00 |
+-----------+---------+
5 rows in set (0.00 sec)

将以上查询结果当成临时表t(job,minsal)

SELECT
    e.ename
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;

+--------+
| ename  |
+--------+
| SCOTT  |
| FORD   |
| SMITH  |
| CLARK  |
| KING   |
| WARD   |
| MARTIN |
+--------+
7 rows in set (0.00 sec)

【30】列出每个部门MANAGER的最低薪水

SELECT 
    e.deptno, MIN(e.sal) AS minsal
FROM 
    emp e
WHERE 
    e.job = 'MANAGER'
GROUP BY 
    e.deptno;

+--------+---------+
| deptno | minsal  |
+--------+---------+
|     10 | 2450.00 |
|     20 | 2975.00 |
|     30 | 2850.00 |
+--------+---------+
3 rows in set (0.00 sec)

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

SELECT 
    e.ename, (e.sal + IFNULL(comm, 0)) * 12 AS yearsal
FROM 
    emp e
ORDER BY
    yearsal;

+--------+----------+
| ename  | yearsal  |
+--------+----------+
| SMITH  |  9600.00 |
| JAMES  | 11400.00 |
| ADAMS  | 13200.00 |
| MILLER | 15600.00 |
| TURNER | 18000.00 |
| WARD   | 21000.00 |
| ALLEN  | 22800.00 |
| CLARK  | 29400.00 |
| MARTIN | 31800.00 |
| BLAKE  | 34200.00 |
| JONES  | 35700.00 |
| SCOTT  | 36000.00 |
| FORD   | 36000.00 |
| KING   | 60000.00 |
+--------+----------+
14 rows in set (0.00 sec)

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

SELECT 
    e.ename, b.ename AS mgr, b.sal
FROM
    emp e
JOIN 
    emp b
ON 
    e.mgr = b.empno
WHERE
    b.sal > 3000;

+-------+------+---------+
| ename | mgr  | sal     |
+-------+------+---------+
| JONES | KING | 5000.00 |
| BLAKE | KING | 5000.00 |
| CLARK | KING | 5000.00 |
+-------+------+---------+
3 rows in set (0.00 sec)

【33】求部门名称中带"s"字符的部门员工的工资合计、部门人数

SELECT
    d.dname, sum(e.sal) AS sumsal, COUNT(e.ename) AS totalEmp
FROM
    emp e
JOIN
    dept d
ON
    e.deptno = d.deptno
WHERE
    d.dname LIKE '%s%'
GROUP BY
    d.deptno;

+----------+----------+----------+
| dname    | sumsal   | totalEmp |
+----------+----------+----------+
| RESEARCH | 10875.00 |        5 |
| SALES    |  9400.00 |        6 |
+----------+----------+----------+
2 rows in set (0.01 sec)

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

CREATE TABLE emp_bak AS SELECT * FROM emp;

UPDATE emp_bak set sal = sal * 1.1 WHERE (to_days(now()) - to_days(hiredate))/365 > 30;

狗辣子

关注

1
点赞
踩
4

收藏

觉得还不错? 一键收藏
0
评论
34道经典SQL试题

创建部门表：DROP TABLE IF EXISTS `dept`;CREATE TABLE `dept` ( `deptno` int(2) NOT NULL COMMENT '部门编号', `dname` varchar(14) DEFAULT NULL COMMENT '部门名称', `loc` varchar(13) DEFAULT NULL COMMENT '位置',...
复制链接

扫一扫