SQL实战

💡 SQL实战练习——基于经典的EMP-DEPT-SALGRADE三表结构，覆盖多表连接、子查询、分组聚合、窗口函数、自连接、排序分页等核心SQL技能。共19道题，由浅入深，每道题配有纯SQL答案。

一、数据库表结构

-- 1. 部门表 (dept)
CREATE TABLE dept (
    deptno INT PRIMARY KEY COMMENT '部门编号',
    dname  VARCHAR(14) COMMENT '部门名称',
    loc    VARCHAR(13) COMMENT '部门位置'
);

-- 2. 员工表 (emp)
CREATE TABLE emp (
    empno    INT PRIMARY KEY COMMENT '员工编号',
    ename    VARCHAR(10) COMMENT '员工姓名',
    job      VARCHAR(9)  COMMENT '职位',
    mgr      INT         COMMENT '上级编号',
    hiredate DATE        COMMENT '入职日期',
    sal      DECIMAL(7,2) COMMENT '工资',
    comm     DECIMAL(7,2) COMMENT '奖金',
    deptno   INT         COMMENT '部门编号',
    FOREIGN KEY (deptno) REFERENCES dept(deptno)
);

-- 3. 工资等级表 (salgrade)
CREATE TABLE salgrade (
    grade INT PRIMARY KEY COMMENT '工资等级',
    losal DECIMAL(7,2) COMMENT '最低薪',
    hisal DECIMAL(7,2) COMMENT '最高薪'
);

二、插入模拟数据

-- 插入部门数据
INSERT INTO dept (deptno, dname, loc) VALUES
(10, 'ACCOUNTING', 'NEW YORK'),
(20, 'RESEARCH',   'DALLAS'),
(30, 'SALES',      'CHICAGO'),
(40, 'OPERATIONS', 'BOSTON'),
(80, 'TEST',       'LOS ANGELES');

-- 插入员工数据
INSERT INTO emp (empno, ename, job, mgr, hiredate, sal, comm, deptno) VALUES
(7369, 'SMITH',  'CLERK',    7902, '1980-12-17',  800.00, NULL,     20),
(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, '1987-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),
(8000, 'TEST_EMP','CLERK',   NULL, '2026-01-01', 1000.00, NULL,     80);

-- 插入工资等级数据
INSERT INTO salgrade (grade, losal, hisal) VALUES
(1,  700.00, 1200.00),
(2, 1201.00, 1400.00),
(3, 1401.00, 2000.00),
(4, 2001.00, 3000.00),
(5, 3001.00, 9999.00);

三、练习题

第1题列出员工表中每个部门的员工数、部门编号和部门名。

第2题列出员工表中每个部门的员工数（员工数必须大于3）和部门名称。

第3题找出工资比 JONES 多的员工。

第4题列出所有员工的姓名和其上级的姓名。

第5题以职位分组，找出平均工资最高的两种职位。

第6题查找出不在部门20，且比部门20中任何一个人工资都高的员工姓名、部门名称。

第7题得到平均工资大于2000的工作职种。

第8题分部门得到工资大于2000的所有员工的平均工资，并且平均工资还要大于2500。

第9题得到每个月工资总数最少的那个部门的部门编号、部门名称、部门位置。

第10题分部门得到平均工资等级为2级（等级表）的部门编号。

第11题查找出部门10和部门20中，工资最高第3名到工资第5名的员工的员工名字、部门名字、部门位置。

第12题查找出收入（工资加上奖金）下级比自己上级还高的员工编号、员工名字、员工收入。

第13题查找出工资等级不为4级的员工的员工名字、部门名字、部门位置。

第14题查找出职位和MARTIN或者SMITH一样的员工的平均工资。

第15题查找出不属于任何部门的员工。

第16题按部门统计员工数，查出员工数最多的部门的第二名到第三名（列出部门名字、部门位置）。

第17题查询出KING所在部门的部门号、部门名称、部门人数。

第18题查询出KING所在部门的工作年限最大的员工名字。

第19题查询出工资成本最高的部门的部门号和部门名称。

四、答案SQL

第1题

SELECT d.deptno, d.dname, COUNT(e.empno) AS emp_count
FROM dept d
LEFT JOIN emp e ON d.deptno = e.deptno
GROUP BY d.deptno, d.dname;

第2题

SELECT d.dname, COUNT(e.empno) AS emp_count
FROM dept d
JOIN emp e ON d.deptno = e.deptno
GROUP BY d.dname
HAVING COUNT(e.empno) > 3;

第3题

SELECT ename, sal
FROM emp
WHERE sal > (SELECT sal FROM emp WHERE ename = 'JONES');

第4题

SELECT e.ename AS 员工, m.ename AS 上级
FROM emp e
LEFT JOIN emp m ON e.mgr = m.empno;

第5题

SELECT job, AVG(sal) AS avg_sal
FROM emp
GROUP BY job
ORDER BY avg_sal DESC
LIMIT 2;

第6题

SELECT e.ename, d.dname, e.sal
FROM emp e
JOIN dept d ON e.deptno = d.deptno
WHERE e.deptno != 20
  AND e.sal > ALL (SELECT sal FROM emp WHERE deptno = 20);

第7题

SELECT job, AVG(sal) AS avg_sal
FROM emp
GROUP BY job
HAVING AVG(sal) > 2000;

第8题

SELECT deptno, AVG(sal) AS avg_sal
FROM emp
WHERE sal > 2000
GROUP BY deptno
HAVING AVG(sal) > 2500;

第9题

SELECT d.deptno, d.dname, d.loc
FROM dept d
JOIN emp e ON d.deptno = e.deptno
GROUP BY d.deptno, d.dname, d.loc
ORDER BY SUM(e.sal) ASC
LIMIT 1;

第10题

SELECT deptno, AVG(sal) AS avg_sal
FROM emp
GROUP BY deptno
HAVING AVG(sal) BETWEEN (
    SELECT losal FROM salgrade WHERE grade = 2
) AND (
    SELECT hisal FROM salgrade WHERE grade = 2
);

第11题

SELECT ename, dname, loc
FROM (
    SELECT e.ename, d.dname, d.loc,
           DENSE_RANK() OVER (ORDER BY e.sal DESC) AS rnk
    FROM emp e
    JOIN dept d ON e.deptno = d.deptno
    WHERE e.deptno IN (10, 20)
) t
WHERE rnk BETWEEN 3 AND 5;

第12题

SELECT e.empno, e.ename, (e.sal + COALESCE(e.comm, 0)) AS income
FROM emp e
JOIN emp m ON e.mgr = m.empno
WHERE (e.sal + COALESCE(e.comm, 0)) > (m.sal + COALESCE(m.comm, 0));

第13题

SELECT e.ename, d.dname, d.loc
FROM emp e
JOIN dept d ON e.deptno = d.deptno
JOIN salgrade s ON e.sal BETWEEN s.losal AND s.hisal
WHERE s.grade != 4;

第14题

SELECT job, AVG(sal) AS avg_sal
FROM emp
WHERE job IN (
    SELECT job FROM emp WHERE ename IN ('MARTIN', 'SMITH')
)
GROUP BY job;

第15题

SELECT e.*
FROM emp e
LEFT JOIN dept d ON e.deptno = d.deptno
WHERE d.deptno IS NULL;

第16题

SELECT dname, loc, emp_count
FROM (
    SELECT d.dname, d.loc, COUNT(e.empno) AS emp_count,
           DENSE_RANK() OVER (ORDER BY COUNT(e.empno) DESC) AS rnk
    FROM dept d
    JOIN emp e ON d.deptno = e.deptno
    GROUP BY d.deptno, d.dname, d.loc
) t
WHERE rnk IN (2, 3);

第17题

SELECT d.deptno, d.dname, COUNT(e.empno) AS emp_count
FROM dept d
JOIN emp e ON d.deptno = e.deptno
WHERE d.deptno = (SELECT deptno FROM emp WHERE ename = 'KING')
GROUP BY d.deptno, d.dname;

第18题

SELECT ename, hiredate,
       TIMESTAMPDIFF(YEAR, hiredate, CURDATE()) AS work_years
FROM emp
WHERE deptno = (SELECT deptno FROM emp WHERE ename = 'KING')
ORDER BY hiredate ASC
LIMIT 1;

第19题

SELECT d.deptno, d.dname, SUM(e.sal) AS total_sal
FROM dept d
JOIN emp e ON d.deptno = e.deptno
GROUP BY d.deptno, d.dname
ORDER BY total_sal DESC
LIMIT 1;

✅ 练习建议：先把19道题独立做一遍 → 对照答案理解思路 → 重点掌握：JOIN连接、子查询、GROUP BY + HAVING、窗口函数、ALL运算符、COALESCE处理NULL、LIMIT分页。