33道SQL练习题及答案
建表命令:
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
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:
试题:
- 取得每个部门最高薪水的人员名称
- 哪些人的薪水在部门平均薪水之上
- 取得部门中(所有人的)平均薪水等级
- 不准用组函数(MAX),取得最高薪水(给出两种解决方案)
- 取得平均薪水最高的部门的部门编号(至少两个方案)
- 取得平均薪水最高的部门的部门名称
- 求平均薪水的等级最低的部门的部门名称
- 取得比普通员工(员工代码没有在mgr上出现的)的最高薪水还要高的经理人姓名
- 取得薪水最高的前五名员工
- 取得薪水最高的第六到第十名员工
- 取得最后入职的5名员工
- 取得每个薪水等级有多少员工
- 列出所有员工及领导的名字
- 列出受雇日期早于其直接上级的所有员工编号、姓名、部门名称
- 列出部门名称和这些部门的员工信息,同时列出那些没有员工的部门
- 列出至少有5个员工的所有部门
- 列出薪水比“SMITH”多的所有员工信息
- 列出所有“CLERK”(办事员)的姓名及其部门名称,部门人数
- 列出最低薪水大于1500的各种工作及从事此工作的全部雇员人数
- 列出在部门“SALES”<销售部>工作的员工的姓名,假定不知道销售部门的部门编号
- 列出薪金高于公司平均薪金的所有员工,所在部门、上级领导、雇员的工资等级
- 列出与“SCOTT”从事相同工作的所有员工及部门名称
- 列出薪金等于部门30中员工的薪金的其它员工的姓名和薪金
- 列出薪金高于在部门30工作的所有员工的薪金的员工姓名和薪金、部门名称
- 列出在每个部门工作的员工数量、平均工资和平均服务期限
- 列出所有员工的姓名、部门名称和工资
- 列出所有部门的详细信息和人数
- 列出各种工作的最低工资及从事此工作的雇员姓名
- 列出各个部门MANAGER的最低薪金
- 列出所有员工的年工资,按年薪从低到高顺序
- 求出员工领导的薪水超过3000的员工名称和领导名称
- 求部门名称中带“S”字符的部门员工的工资合计、部门人数
- 给任职日期超过30年的员工加薪10%
答案:
- 取得每个部门最高薪水的人员名称
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
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 ) ;
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
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. 哪些人的薪水在部门平均薪水之上
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
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. 取得部门中(所有人的)平均薪水等级
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
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;
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
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),取得最高薪水(给出两种解决方案)
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
SELECT e.sal FROM emp AS e ORDER BY e.sal DESC LIMIT 0,1;
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
SELECT sal FROM emp WHERE sal not in( SELECT DISTINCT a.sal from emp a join emp b on a.sal <b.sal);
5. 取得平均薪水最高的部门的部门编号(至少两个方案)
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
SELECT avg( sal ) avgsal,emp.deptno FROM emp GROUP BY deptno ORDER BY avgsal DESC LIMIT 1;
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
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);
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
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. 取得平均薪水最高的部门的部门名称
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
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 )) ;
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
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 ;
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
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. 求平均薪水的等级最低的部门的部门名称
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
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上出现的)的最高薪水还要高的经理人姓名
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
SELECT * FROM emp WHERE EMPNO IN ( SELECT DISTINCT MGR FROM emp WHERE MGR );
9. 取得比普通员工(员工代码没有在mgr上出现的)的最高薪水还要高的经理人姓名
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
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. 取得薪水最高的第六到第十名员工
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
SELECT * FROM emp ORDER BY SAL DESC LIMIT 5
11. 取得最后入职的5名员工
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
SELECT ENAME, HIREDATE FROM emp ORDER BY HIREDATE DESC LIMIT 5;
12. 取得每个薪水等级有多少员工
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
SELECT s.GRADE, COUNT(*) FROM emp e JOIN salgrade s ON e.SAL BETWEEN s.LOSAL AND s.HISAL GROUP BY s.GRADE;
13. 列出所有员工及领导的名字
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
SELECT a.ENAME, b.ENAME AS leaertName FROM emp a LEFT JOIN emp b ON a.MGR = b.EMPNO;
14. 列出受雇日期早于其直接上级的所有员工编号、姓名、部门名称
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
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. 列出部门名称和这些部门的员工信息,同时列出那些没有员工的部门
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
SELECT d.DEPTNO, d.DNAME, e.ENAME FROM emp e RIGHT JOIN dept d ON e.DEPTNO = d.DEPTNO ORDER BY DEPTNO ;
16. 列出至少有5个员工的所有部门
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
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”多的所有员工信息
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
SELECT * FROM emp WHERE SAL > ( SELECT sal FROM emp WHERE ENAME = 'SMITH' );
18. 列出所有“CLERK”(办事员)的姓名及其部门名称,部门人数
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
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的各种工作及从事此工作的全部雇员人数
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
SELECT JOB, MIN( SAL ), COUNT(*) FROM emp GROUP BY JOB HAVING MIN( SAL )> 1500;
20. 列出在部门“SALES”<销售部>工作的员工的姓名,假定不知道销售部门的部门编号
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
SELECT e.ENAME FROM emp e WHERE e.DEPTNO =( SELECT DEPTNO FROM dept WHERE DNAME = 'SALES' );
21. 列出薪金高于公司平均薪金的所有员工,所在部门、上级领导、雇员的工资等级
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
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”从事相同工作的所有员工及部门名称
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
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中员工的薪金的其它员工的姓名和薪金
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
SELECT ENAME, SAL, DEPTNO FROM emp WHERE SAL IN ( SELECT SAL FROM emp WHERE DEPTNO = 30 ) ;
24. 列出薪金高于在部门30工作的所有员工的薪金的员工姓名和薪金、部门名称
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
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. 列出在每个部门工作的员工数量、平均工资和平均服务期限
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
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. 列出所有员工的姓名、部门名称和工资
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
SELECT e.ENAME, d.DNAME, e.SAL FROM emp e JOIN dept d ON e.DEPTNO = d.DEPTNO;
27. 列出所有部门的详细信息和人数
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
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. 列出各种工作的最低工资及从事此工作的雇员姓名
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
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的最低薪金
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
SELECT DEPTNO, MIN( SAL ) AS minsal FROM emp WHERE JOB = 'MANAGER' GROUP BY DEPTNO;
30. 列出所有员工的年工资,按年薪从低到高顺序
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
SELECT ENAME, (sal + IFNULL( COMM, 0 ))* 12 AS yearSal FROM emp ORDER BY yearSal ASC;
31. 求出员工领导的薪水超过3000的员工名称和领导名称
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
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”字符的部门员工的工资合计、部门人数
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
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%
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
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;