33道SQL练习题及答案

建表命令:

DROP TABLE IF EXISTS EMP;
DROP TABLE IF EXISTS DEPT;
DROP TABLE IF EXISTS SALGRADE;

CREATE TABLE DEPT
 (DEPTNO int(2) not null ,
    DNAME VARCHAR(14) ,
    LOC VARCHAR(13),
    primary key (DEPTNO)
    );
CREATE TABLE EMP
(EMPNO int(4)  not null ,
    ENAME VARCHAR(10),
    JOB VARCHAR(9),
    MGR INT(4),
    HIREDATE DATE  DEFAULT NULL,
    SAL DOUBLE(7,2),
    COMM DOUBLE(7,2),
    primary key (EMPNO),
    DEPTNO INT(2) 
    )
    ;

CREATE TABLE SALGRADE
( GRADE INT,
    LOSAL INT,
    HISAL INT );




INSERT INTO DEPT ( DEPTNO, DNAME, LOC ) VALUES ( 
10, 'ACCOUNTING', 'NEW YORK'); 
INSERT INTO DEPT ( DEPTNO, DNAME, LOC ) VALUES ( 
20, 'RESEARCH', 'DALLAS'); 
INSERT INTO DEPT ( DEPTNO, DNAME, LOC ) VALUES ( 
30, 'SALES', 'CHICAGO'); 
INSERT INTO DEPT ( DEPTNO, DNAME, LOC ) VALUES ( 
40, 'OPERATIONS', 'BOSTON'); 
commit;
 
INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,
DEPTNO ) VALUES ( 
7369, 'SMITH', 'CLERK', 7902,  '1980-12-17'
, 800, NULL, 20); 
INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,
DEPTNO ) VALUES ( 
7499, 'ALLEN', 'SALESMAN', 7698,  '1981-02-20'
, 1600, 300, 30); 
INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,
DEPTNO ) VALUES ( 
7521, 'WARD', 'SALESMAN', 7698,  '1981-02-22'
, 1250, 500, 30); 
INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,
DEPTNO ) VALUES ( 
7566, 'JONES', 'MANAGER', 7839,  '1981-04-02'
, 2975, NULL, 20); 
INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,
DEPTNO ) VALUES ( 
7654, 'MARTIN', 'SALESMAN', 7698,  '1981-09-28'
, 1250, 1400, 30); 
INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,
DEPTNO ) VALUES ( 
7698, 'BLAKE', 'MANAGER', 7839,  '1981-05-01'
, 2850, NULL, 30); 
INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,
DEPTNO ) VALUES ( 
7782, 'CLARK', 'MANAGER', 7839,  '1981-06-09'
, 2450, NULL, 10); 
INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,
DEPTNO ) VALUES ( 
7788, 'SCOTT', 'ANALYST', 7566,  '1987-04-19'
, 3000, NULL, 20); 
INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,
DEPTNO ) VALUES ( 
7839, 'KING', 'PRESIDENT', NULL,  '1981-11-17'
, 5000, NULL, 10); 
INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,
DEPTNO ) VALUES ( 
7844, 'TURNER', 'SALESMAN', 7698,  '1981-09-08'
, 1500, 0, 30); 
INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,
DEPTNO ) VALUES ( 
7876, 'ADAMS', 'CLERK', 7788,  '1987-05-23'
, 1100, NULL, 20); 
INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,
DEPTNO ) VALUES ( 
7900, 'JAMES', 'CLERK', 7698,  '1981-12-03'
, 950, NULL, 30); 
INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,
DEPTNO ) VALUES ( 
7902, 'FORD', 'ANALYST', 7566,  '1981-12-03'
, 3000, NULL, 20); 
INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,
DEPTNO ) VALUES ( 
7934, 'MILLER', 'CLERK', 7782,  '1982-01-23'
, 1300, NULL, 10); 
commit;
 
INSERT INTO SALGRADE ( GRADE, LOSAL, HISAL ) VALUES ( 
1, 700, 1200); 
INSERT INTO SALGRADE ( GRADE, LOSAL, HISAL ) VALUES ( 
2, 1201, 1400); 
INSERT INTO SALGRADE ( GRADE, LOSAL, HISAL ) VALUES ( 
3, 1401, 2000); 
INSERT INTO SALGRADE ( GRADE, LOSAL, HISAL ) VALUES ( 
4, 2001, 3000); 
INSERT INTO SALGRADE ( GRADE, LOSAL, HISAL ) VALUES ( 
5, 3001, 9999); 
commit;
 

 dept:

 

emp:

salagrade:

试题: 

1. 取得每个部门最高薪水的人员名称
2. 哪些人的薪水在部门平均薪水之上
3. 取得部门中（所有人的）平均薪水等级
4. 不准用组函数（MAX），取得最高薪水（给出两种解决方案）
5. 取得平均薪水最高的部门的部门编号（至少两个方案）
6. 取得平均薪水最高的部门的部门名称
7. 求平均薪水的等级最低的部门的部门名称
8. 取得比普通员工（员工代码没有在mgr上出现的）的最高薪水还要高的经理人姓名
9. 取得薪水最高的前五名员工
10. 取得薪水最高的第六到第十名员工
11. 取得最后入职的5名员工
12. 取得每个薪水等级有多少员工
13. 列出所有员工及领导的名字
14. 列出受雇日期早于其直接上级的所有员工编号、姓名、部门名称
15. 列出部门名称和这些部门的员工信息，同时列出那些没有员工的部门
16. 列出至少有5个员工的所有部门
17. 列出薪水比“SMITH”多的所有员工信息
18. 列出所有“CLERK”（办事员）的姓名及其部门名称，部门人数
19. 列出最低薪水大于1500的各种工作及从事此工作的全部雇员人数
20. 列出在部门“SALES”<销售部>工作的员工的姓名，假定不知道销售部门的部门编号
21. 列出薪金高于公司平均薪金的所有员工，所在部门、上级领导、雇员的工资等级
22. 列出与“SCOTT”从事相同工作的所有员工及部门名称
23. 列出薪金等于部门30中员工的薪金的其它员工的姓名和薪金
24. 列出薪金高于在部门30工作的所有员工的薪金的员工姓名和薪金、部门名称
25. 列出在每个部门工作的员工数量、平均工资和平均服务期限
26. 列出所有员工的姓名、部门名称和工资
27. 列出所有部门的详细信息和人数
28. 列出各种工作的最低工资及从事此工作的雇员姓名
29. 列出各个部门MANAGER的最低薪金
30. 列出所有员工的年工资，按年薪从低到高顺序
31. 求出员工领导的薪水超过3000的员工名称和领导名称
32. 求部门名称中带“S”字符的部门员工的工资合计、部门人数
33. 给任职日期超过30年的员工加薪10%

答案:

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

SELECT
    emp.ename , emp.sal,emp.deptno    
FROM
    emp
    LEFT JOIN dept ON emp.deptno = dept.deptno 
WHERE
    emp.sal IN(
    SELECT
        MAX( emp.sal ) 
    FROM
        emp
        LEFT JOIN dept ON emp.deptno = dept.deptno 
    GROUP BY
    emp.deptno 
    )    ;

SELECT
    e.ename,
    t.* 
FROM
    emp e
JOIN 
        ( SELECT deptno, max( sal ) AS maxsal FROM emp GROUP BY deptno ) t 
ON 
        t.deptno = e.deptno AND t.maxsal = e.sal;    

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

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

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

SELECT
    s.GRADE,e.* 
FROM
    salgrade AS s
JOIN 
    ( SELECT AVG( sal ) avgsal FROM emp GROUP BY deptno )  e
ON 
avgsal >= losal  AND avgsal <= hisal;

SELECT
    t.*,s.grade 
FROM
    salgrade AS s
JOIN 
    ( SELECT deptno, avg( sal ) AS avgsal FROM emp e GROUP BY deptno ) AS t 
ON 
    t.avgsal BETWEEN s.LOSAL AND s.HISAL;

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

SELECT
    e.sal 
FROM
    emp AS e 
ORDER BY
    e.sal DESC
LIMIT 0,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
    avg( sal )  avgsal,emp.deptno
FROM
    emp 
GROUP BY
    deptno 
ORDER BY
    avgsal DESC
    LIMIT 1;

    SELECT 
        AVG(sal) AS avgsal,deptno
    FROM
        emp
    GROUP BY
        deptno
    HAVING
        avgsal=(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
        MAX( t.avgsal ) 
    FROM
    ( SELECT avg( sal ) avgsal, deptno FROM emp GROUP BY deptno ) t 
    );

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

SELECT
    dname ,deptno
FROM
    dept 
WHERE
    deptno =(
    SELECT
        deptno 
    FROM
        emp 
    GROUP BY
        deptno 
    HAVING
        avg( sal )=(
        SELECT
            MAX( t.avgsal )  avgsal
        FROM
        ( SELECT AVG( sal ) avgsal, deptno FROM emp GROUP BY deptno ) t 
    )) ;

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

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

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

SELECT
    t.DNAME,
    t.avgsal,
    s.GRADE 
FROM
    (
    SELECT
        d.DNAME,
        AVG( SAL ) AS avgsal 
    FROM
        emp e
        JOIN dept d ON e.DEPTNO = d.DEPTNO 
    GROUP BY
        d.DNAME 
    ) 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 e ) t
        JOIN salgrade s ON t.avgsal BETWEEN s.LOSAL 
    AND s.HISAL 
    );

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

SELECT
    * 
FROM
    emp 
WHERE
    EMPNO IN ( SELECT DISTINCT MGR FROM emp WHERE MGR );

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

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

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

SELECT
    * 
FROM
    emp 
ORDER BY
    SAL DESC 
    LIMIT 5

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

SELECT
    ENAME,
    HIREDATE 
FROM
    emp 
ORDER BY
    HIREDATE DESC 
    LIMIT 5;

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

SELECT
    s.GRADE,
    COUNT(*) 
FROM
    emp e
    JOIN salgrade s ON e.SAL BETWEEN s.LOSAL 
    AND s.HISAL 
GROUP BY
    s.GRADE;

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

SELECT
    a.ENAME,
    b.ENAME AS leaertName 
FROM
    emp a
    LEFT JOIN emp b ON a.MGR = b.EMPNO;

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

SELECT
    a.EMPNO,
    d.DNAME,
    a.ENAME,
    a.HIREDATE AS empHireDate,
    b.EMPNO AS leaderNo,
    b.ENAME AS leaderName,
    b.HIREDATE AS leaderHireDate 
FROM
    emp a
    JOIN emp b ON a.MGR = b.EMPNO
    JOIN dept d ON a.DEPTNO = d.DEPTNO 
WHERE
    a.HIREDATE < b.HIREDATE;

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

SELECT
    d.DEPTNO,
    d.DNAME,
    e.ENAME 
FROM
    emp e
    RIGHT JOIN dept d ON e.DEPTNO = d.DEPTNO 
ORDER BY
    DEPTNO ;

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

SELECT
    e.DEPTNO,
    d.DNAME,
    d.LOC,
    COUNT( e.ENAME ) 
FROM
    emp e
    JOIN dept d ON e.DEPTNO = d.DEPTNO 
GROUP BY
    e.DEPTNO,
    d.DNAME,
    d.LOC 
HAVING
    COUNT( e.EMPNO )>= 5;

　　17. 列出薪水比“SMITH”多的所有员工信息

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

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

SELECT
    e.ENAME,
    d.DNAME,
    t.totalemp 
FROM
    dept d
    JOIN emp e ON e.DEPTNO = d.DEPTNO
    JOIN ( SELECT DEPTNO, count(*) AS totalEmp FROM emp GROUP BY DEPTNO ) t ON d.DEPTNO = t.DEPTNO 
WHERE
    JOB = 'CLERK';

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

SELECT
    JOB,
    MIN( SAL ),
    COUNT(*) 
FROM
    emp 
GROUP BY
    JOB 
HAVING
    MIN( SAL )> 1500;

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

SELECT e.ENAME 
FROM emp e 
WHERE e.DEPTNO =( 
SELECT DEPTNO 
FROM dept 
WHERE DNAME = 'SALES' );

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

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

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

SELECT
    e.ENAME,
    d.DNAME 
FROM
    emp e
    JOIN dept d ON e.DEPTNO = d.DEPTNO 
WHERE
    e.JOB =(
    SELECT
        job 
    FROM
        emp 
    WHERE
    ENAME = 'SCOTT' 
    );

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

SELECT
    ENAME,
    SAL,
    DEPTNO 
FROM
    emp 
WHERE
    SAL IN ( SELECT SAL FROM emp WHERE DEPTNO = 30 ) ;

 　　24. 列出薪金高于在部门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 )

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

SELECT
    d.DEPTNO,
    COUNT( e.ENAME ) AS totalEmp,
    IFNULL( AVG( SAL ), 0 ) AS avgsal,
    AVG(IFNULL( TO_DAYS( NOW()- TO_DAYS( HIREDATE ))/ 365, 0 )) AS serverTime 
FROM
    emp e
    RIGHT JOIN dept d ON e.DEPTNO = d.DEPTNO 
GROUP BY
    d.DEPTNO;

 　　26. 列出所有员工的姓名、部门名称和工资

SELECT
    e.ENAME,
    d.DNAME,
    e.SAL 
FROM
    emp e
    JOIN dept d ON e.DEPTNO = d.DEPTNO;

　　27. 列出所有部门的详细信息和人数

SELECT
    d.DEPTNO,
    d.DNAME,
    d.LOC,
    COUNT( e.ENAME ) AS totalEmp 
FROM
    emp e
    RIGHT JOIN dept d ON d.DEPTNO = e.DEPTNO 
GROUP BY
    d.DEPTNO,
    d.DNAME,
    d.LOC

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

SELECT
    e.ENAME,
    e.JOB 
FROM
    emp e
    JOIN ( SELECT JOB, MIN( SAL ) AS minsal FROM emp GROUP BY JOB ) t ON e.JOB = t.JOB 
    AND e.SAL = t.minsal;

　　29. 列出各个部门MANAGER的最低薪金

SELECT
    DEPTNO,
    MIN( SAL ) AS minsal 
FROM
    emp 
WHERE
    JOB = 'MANAGER' 
GROUP BY
    DEPTNO;

　　30. 列出所有员工的年工资，按年薪从低到高顺序

SELECT
    ENAME,
    (sal + IFNULL( COMM, 0 ))* 12 AS yearSal 
FROM
    emp 
ORDER BY
    yearSal ASC;

　　31. 求出员工领导的薪水超过3000的员工名称和领导名称

SELECT
    a.ENAME AS empName,
    a.SAL AS empSal,
    b.ENAME AS leaderName,
    b.SAL AS leaderSal 
FROM
    emp a
JOIN 
    emp b 
ON 
    a.MGR = b.EMPNO 
WHERE
    b.SAL > 3000;

　　32. 求部门名称中带“S”字符的部门员工的工资合计、部门人数

SELECT
    d.DNAME,
    IFNULL( SUM( e.SAL ), 0 ) AS deptSumsal,
    COUNT( e.ENAME ) AS totalEmp 
FROM
    emp e
    RIGHT JOIN dept d ON e.DEPTNO = d.DEPTNO 
GROUP BY
    d.DNAME;

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

DROP TABLE
IF
    EXISTS emp_bak;
CREATE TABLE emp_bak AS SELECT
* 
FROM
    emp;
UPDATE emp_bak 
SET sal = sal * 1.1 
WHERE
    (
    TO_DAYS(
    NOW())- TO_DAYS( HIREDATE ))/ 360 > 30;