大家可以自行网上找资源（网上资源比较多，不建议下载我的），也可以在我这里下载：
1.取得每个部门最高薪水的人员名称：正确 一共有4个单位，要进行左外连接 其中一个单位没得员工
SELECT dept.dname, emp.ename, emp.sal FROM emp RIGHT JOIN dept ON emp.deptno = dept.deptno RIGHT JOIN ( SELECT dept.dname, MAX(emp.sal) AS 'max_sal' FROM emp RIGHT JOIN dept ON emp.deptno = dept.deptno GROUP BY dept.dname )AS tt ON tt.dname = dept.dname AND tt.max_sal = emp.sal GROUP BY dname;2.哪些人的薪水在部门平均薪水之上：不确定
SELECT dept.dname, emp.ename, emp.sal FROM emp INNER JOIN dept ON emp.deptno = dept.deptno INNER JOIN ( SELECT dept.dname, AVG(emp.sal) AS 'avg_sal' FROM emp INNER JOIN dept ON emp.deptno = dept.deptno GROUP BY dname )AS tt ON tt.dname = dept.dname AND tt.avg_sal <= emp.sal;3.取得部门中所有人的平均薪水等级
SELECT tt.dname, salgrade.grade FROM ( SELECT dept.dname, AVG(emp.sal) AS 'avg_sal' FROM emp RIGHT JOIN dept ON emp.deptno = dept.deptno GROUP BY dname ) AS tt, salgrade WHERE tt.avg_sal >= salgrade.losal AND tt.avg_sal <= hisal;4.不准使用组函数（MAX），取得最高薪水
SELECT emp.ename, emp.sal FROM emp ORDER BY sal DESC -- 由高到底， asc 由低到高 LIMIT 0, 1;5.取得平均薪水最高的部门的部门编号
SELECT deptno FROM ( SELECT dept.deptno, AVG(emp.sal) AS 'avg_sal' FROM emp INNER JOIN dept ON emp.deptno = dept.deptno GROUP BY dname ORDER BY avg_sal DESC LIMIT 0, 1 )AS tt;6.取得平均薪水最高的部门的部门名称
SELECT dname FROM ( SELECT dept.dname, AVG(emp.sal) AS 'avg_sal' FROM emp INNER JOIN dept ON emp.deptno = dept.deptno GROUP BY dname ORDER BY avg_sal DESC LIMIT 0, 1 )AS tt;7.求平均薪水的等级最低的部门的部门名称
SELECT dname FROM ( SELECT t1.dname , SALGRADE.`GRADE` FROM ( SELECT dept.dname, AVG(emp.sal) AS 'avg_sal' FROM emp INNER JOIN dept ON emp.deptno = dept.deptno GROUP BY dname ) AS t1 , SALGRADE WHERE t1.avg_sal >= SALGRADE.`LOSAL` AND t1.avg_sal <= SALGRADE.`HISAL` )AS tt WHERE tt.grade = ( SELECT MIN(SALGRADE.`GRADE`) FROM ( SELECT dept.dname, AVG(emp.sal) AS 'avg_sal' FROM emp INNER JOIN dept ON emp.deptno = dept.deptno GROUP BY dname ) AS t1 , SALGRADE WHERE t1.avg_sal >= SALGRADE.`LOSAL` AND t1.avg_sal <= SALGRADE.`HISAL` );8.取得比普通员工（员工代码没有在mgr上出现的）的最高薪水
SELECT emp.ename FROM emp WHERE emp.mgr IS NULL AND sal > ( SELECT MAX(sal) FROM emp WHERE mgr IS NOT NULL );9.取得薪水最高的5名员工
SELECT emp.`EMPNO` AS '编号', emp.`ENAME` AS '姓名'-- , emp.`SAL` as '薪资' FROM emp ORDER BY emp.`SAL` DESC LIMIT 0, 5;10.取得薪水最高的第六到第十名员工
SELECT emp.`EMPNO` AS '编号', emp.`ENAME` AS '姓名'-- , emp.`SAL` FROM emp ORDER BY emp.`SAL` DESC LIMIT 5, 5;11.取得最后入职的5名员工
SELECT tt.no AS '编号', tt.name AS '姓名'-- , tt.days FROM ( SELECT emp.`EMPNO` AS 'no', emp.`ENAME` AS 'name', TO_DAYS(HIREDATE) AS 'days' FROM emp ) AS tt ORDER BY days DESC LIMIT 0, 5;12.取得每个薪水等级有多少员工
SELECT salgrade.`GRADE` AS '薪资等级', COUNT(tt.`EMPNO`) AS '员工数' FROM salgrade, ( SELECT emp.`EMPNO`, emp.`SAL` FROM emp ) AS tt WHERE tt.`SAL` >= salgrade.`LOSAL` AND tt.`SAL` <= salgrade.`HISAL` GROUP BY salgrade.`GRADE`; 13.列出所有员工及领导的名字 SELECT emp.`ENAME` AS '员工名称', tt.lname AS '领导名称' FROM emp LEFT JOIN ( SELECT emp.`EMPNO`, emp.`ENAME` AS 'lname' FROM emp WHERE empno IN ( SELECT mgr FROM emp ) -- 找出所有是领导的员工no ) AS tt ON emp.`MGR` = tt.empno; 14.列出受雇日期早于直接上级的所有员工编号、姓名、部门 SELECT t2.eno AS '编号', t2.ename AS '姓名', dept.`DNAME` AS '部门名称', t2.ehiredate AS '员工受雇日期', t2.lhiredate AS '领导受雇日期' FROM dept INNER JOIN ( SELECT emp.`EMPNO` AS 'eno', emp.`ENAME`, emp.`HIREDATE` AS 'ehiredate', t1.lhiredate, emp.`DEPTNO` FROM emp LEFT JOIN ( SELECT emp.`EMPNO` AS 'lno', emp.`HIREDATE` AS 'lhiredate' FROM emp WHERE empno IN ( SELECT mgr FROM emp ) -- 找出所有是领导的员工no ) AS t1 ON emp.`MGR` = t1.lno ) AS t2 ON t2.deptno = dept.`DEPTNO` WHERE t2.ehiredate < t2.lhiredate OR t2.lhiredate IS NULL;15.列出部门名称和这些部门的员工信息，同时列出那些没有员工的部门
SELECT dept.`DNAME` AS '部门名称', emp.`ENAME` AS '员工名称', emp.`JOB` AS '职位', emp.`HIREDATE` AS '入职时间', emp.`SAL` AS '薪水' FROM dept LEFT JOIN emp ON dept.`DEPTNO` = emp.`DEPTNO`16.列出至少有5个员工的所有部门
SELECT dept.`DNAME` AS '部门名', COUNT(emp.`EMPNO`) AS '员工个数' FROM dept LEFT JOIN emp ON dept.`DEPTNO` = emp.`DEPTNO` GROUP BY dept.`DNAME` HAVING COUNT(emp.`EMPNO`) >= 5; 17.列出薪水比“SMITH”多的所有员工信息 SELECT * FROM emp WHERE emp.`SAL` > ( SELECT emp.`SAL` FROM emp WHERE emp.`ENAME` = 'SMITH' ); 18.列出所有“CLERK”办事员的姓名及其部门名称，部门人数 SELECT emp.`ENAME` AS '姓名', dept.`DNAME` AS '部门名称', tt.emp_count AS '部门人数' FROM emp LEFT JOIN dept ON emp.`DEPTNO` = dept.`DEPTNO` INNER JOIN ( SELECT dept.`DEPTNO`, COUNT(emp.`EMPNO`) AS 'emp_count' FROM dept LEFT JOIN emp ON dept.`DEPTNO` = emp.`DEPTNO` GROUP BY dept.`DEPTNO` ) AS tt ON emp.`DEPTNO` = tt.deptno WHERE emp.`JOB` = 'CLERK';19.列出最低薪水大于1500的各种工作及从事此工作的全部雇员人数
SELECT emp.`JOB` AS '工作名称', COUNT(emp.`EMPNO`) AS '雇员人数' FROM emp WHERE emp.`JOB` IN ( SELECT emp.`JOB` FROM emp GROUP BY emp.`JOB` HAVING MIN(emp.`SAL`) >1500 ) GROUP BY emp.`JOB`;-- 20.列出在部门“SALES”<销售部>工作的员工姓名（假定不知道销售部门的部门编号）
SELECT emp.`ENAME` AS '销售部人员' FROM emp WHERE emp.`DEPTNO` = ( SELECT dept.`DEPTNO` FROM dept WHERE dept.`DNAME` = 'sales' );21.列出薪水高于公司平均薪水的所有员工，所在部门、上级领导、雇员的工资等级
SELECT t1.ename AS '员工名称', dept.`DNAME` AS '部门名称', t2.ename AS '上级名称', t1.grade AS '工资等级', t1.sal AS '工资' FROM ( SELECT emp.`EMPNO`, emp.`DEPTNO`, emp.`ENAME`, emp.`SAL`, emp.`MGR`,salgrade.`GRADE` FROM emp, salgrade WHERE emp.`SAL` > ( SELECT AVG(sal) AS 'avg_sal' FROM emp ) AND emp.`SAL` >= salgrade.`LOSAL` AND emp.`SAL` <= salgrade.`HISAL` ) AS t1 LEFT JOIN ( SELECT emp.`EMPNO` AS 'mgr', emp.`ENAME` -- 注意这里是查出所有领导的empno，作为mgr编号 FROM emp WHERE emp.`EMPNO` IN ( SELECT DISTINCT(emp.`MGR`) -- 必须要distinct 不然查出来的mgr有重复，后面上层再查重复值接近笛卡尔积 FROM emp WHERE mgr IS NOT NULL ) ) AS t2 ON t1.mgr = t2.mgr LEFT JOIN dept ON t1.deptno = dept.`DEPTNO`;22.列出“SCOTT”从事相同工作的所有员工及部门名称
SELECT emp.`EMPNO` AS '员工编号', emp.`ENAME` AS '员工名称', dept.`DNAME` AS '部门名称'-- , emp.`DEPTNO` FROM emp, dept WHERE emp.`JOB` = ( SELECT emp.`JOB` -- 查询出SCOTT从事的职业 FROM emp WHERE emp.`ENAME` = 'SCOTT' ) AND emp.`ENAME` <> 'SCOTT' AND emp.`DEPTNO` = dept.`DEPTNO`;23.列出薪水等于部门30中员工的薪水的其它员工的姓名和薪水
SELECT emp.`EMPNO` AS '员工编号', emp.`ENAME` AS '员工名称', emp.`SAL` AS '员工薪水' FROM emp WHERE emp.`DEPTNO` <> 30 AND emp.`SAL` IN( SELECT DISTINCT emp.`SAL` -- 查询出部门为30的所有的员工的sal 不重复 FROM emp WHERE emp.`DEPTNO` = 30 );24.列出薪金高于在部门 30 工作的所有员工的薪金的员工姓名和薪金、部门名称
SELECT emp.`EMPNO` AS '员工编号', emp.`ENAME` AS '员工名称', emp.`SAL` AS '员工薪水', dept.`DNAME` AS '部门名称' FROM emp, dept WHERE emp.`DEPTNO` <> 30 AND emp.`SAL` >( SELECT MAX(emp.`SAL`) -- 查询出部门为30的所有的员工的sal 不重复 FROM emp WHERE emp.`DEPTNO` = 30 ) AND dept.`DEPTNO` = emp.`DEPTNO`;25. 列出在每个部门工作的员工数量、平均工资和平均服务期限
SELECT dept.`DNAME` AS '部门名称', COUNT(emp.`EMPNO`) AS '员工数量', ROUND(AVG(emp.`SAL`), 2) AS '平均工资', t2.avg_year AS '平均服务年限' FROM dept LEFT JOIN emp ON dept.`DEPTNO` = emp.`DEPTNO` LEFT JOIN ( SELECT t1.deptno, ROUND(AVG(t1.year), 0) AS 'avg_year' -- 四舍五入，保留0位小数 FROM ( SELECT emp.`DEPTNO`, ROUND((TO_DAYS(NOW())-TO_DAYS(emp.`HIREDATE`))/366, 0) AS 'year' FROM emp ) AS t1 GROUP BY t1.deptno ) AS t2 ON dept.`DEPTNO` = t2.deptno GROUP BY dept.`DEPTNO`;26. 列出所有员工的姓名、部门名称和工资
SELECT emp.`ENAME` AS '员工名称', dept.`DNAME` AS '部门名称', emp.`SAL` AS '工资' FROM emp, dept WHERE dept.`DEPTNO` = emp.`DEPTNO`;27. 列出所有部门的详细信息和人数
SELECT dept.`DEPTNO` AS '部门编号', dept.`DNAME` AS '部门名称', dept.`LOC` AS '部门地址', tt.num AS '部门人数' FROM dept LEFT JOIN ( SELECT emp.`DEPTNO`, COUNT(emp.`EMPNO`) AS 'num' FROM emp GROUP BY emp.`DEPTNO` ) AS tt ON dept.`DEPTNO` = tt.`DEPTNO`;28. 列出各种工作的最低工资及从事此工作的雇员姓名
SELECT emp.`SAL`, emp.`ENAME` FROM emp, ( SELECT emp.`JOB`, MIN(emp.`SAL`) AS 'min_sal' FROM emp GROUP BY emp.`JOB` )AS tt WHERE emp.`JOB` = tt.job AND emp.`SAL` = tt.min_sal;29. 列出各个部门 MANAGER 的最低薪金
SELECT dept.`DNAME` AS '部门名称', MIN(tt.sal) AS '最低薪资' FROM dept LEFT JOIN ( SELECT emp.`DEPTNO`, emp.`SAL` FROM emp WHERE emp.`JOB` = 'manager' ) AS tt ON dept.`DEPTNO` = tt.deptno GROUP BY dept.`DEPTNO`;30. 列出所有员工的年工资，按年薪从低到高排序
SELECT emp.`EMPNO` AS '员工编号', emp.`ENAME` AS '员工名称', (emp.`SAL`*12) AS '年薪' FROM emp ORDER BY 年薪 ASC;31. 求出员工领导的薪水超过 3000 的员工名称和领导名称 SELECT tt.ename AS '领导名称', emp.`ENAME` AS '雇员名称' FROM emp RIGHT JOIN ( SELECT emp.`EMPNO`, emp.`ENAME` FROM emp WHERE emp.`EMPNO` IN ( SELECT DISTINCT emp.`MGR` FROM emp WHERE emp.`MGR` IS NOT NULL ) AND emp.`SAL` >= 3000 ) AS tt ON emp.`MGR` = tt.empno32. 求部门名称中带“S”字符的部门员工的工资合计、部门人数
SELECT tt.dname AS '部门名称', SUM(emp.`SAL`) AS '工资合计', COUNT(emp.`EMPNO`) AS '部门人数' FROM emp RIGHT JOIN ( SELECT dept.`DEPTNO`, dept.`DNAME` FROM dept WHERE dept.`DNAME` LIKE '%s%' ) AS tt ON emp.`DEPTNO` = tt.deptno GROUP BY emp.`DEPTNO`;32+.查询名字中有s的员工的工资总和（按照部门进行分组）
SELECT dept.`DNAME` AS '部门名称', SUM(tt.`SAL`) AS '工资' FROM dept LEFT JOIN ( SELECT emp.`EMPNO`, emp.`ENAME`, emp.`SAL`, emp.`DEPTNO` FROM emp WHERE emp.`ENAME` LIKE '%s%' ) AS tt ON dept.`DEPTNO` = tt.deptno GROUP BY dept.`DEPTNO`;33. 给任职日期超过 30 年的员工加薪 10% UPDATE emp SET emp.`SAL` = emp.`SAL` + emp.`SAL`*10 WHERE emp.`EMPNO` IN( SELECT tt.`EMPNO` FROM ( SELECT emp.`EMPNO`, ROUND((TO_DAYS(NOW())-TO_DAYS(emp.`HIREDATE`))/366, 0) AS 'work_year' FROM emp ) AS tt WHERE tt.work_year > 30 );