8.列出在部门SALES工作的员工的姓名、基本工资、雇佣日期、部门名称，假定不知道该部门的部门编号
mysql> SELECT E.ENAME,E.SAL,E.HIREDATE,D.DNAME -> FROM EMP E,DEPT D -> WHERE D.DNAME='SALES' -> AND D.DEPTNO=E.DEPTNO;
9.列出薪金高于公司平均薪金的所有员工，所在部门，上级领导，公司的工资等级
mysql> SELECT E.EMPNO,E.ENAME,E.JOB,E.SAL,D.DNAME,M.ENAME,S.GRADE -> FROM EMP E,DEPT D,EMP M,SALGRADE S -> WHERE E.SAL>( -> SELECT AVG(SAL) FROM EMP) -> AND E.DEPTNO=D.DEPTNO -> AND E.MGR=M.EMPNO -> AND E.SAL BETWEEN S.LOSAL AND S.HISAL;
10.列出与SCOTT从事相同工作的所有员工及部门名称，部门人数
mysql> SELECT E.ENAME,E.ENAME,E.JOB,D.DNAME ,TEMP.COUNT FROM EMP E,DEPT D,( SELECT DEPTNO DNO,COUNT(EMPNO) COUNT FROM EMP GROUP BY DEPTNO) TEMP WHERE E.JOB=( SELECT JOB FROM EMP WHERE ENNAME='SCOTT') AND E.ENAME<>'SCOTT' AND E.DEPTNO=D.DEPTNO AND TEMP.DNO=E.DEPTNO;
11.列出公司各个工资等级雇员的数量、平均工资
mysql> SELECT S.GRADE,COUNT(E.EMPNO),AVG(SAL) -> FROM SALGRADE S,EMP E -> WHERE E.SAL BETWEEN S.LOSAL AND S.HISAL -> GROUP BY S.GRADE,S.LOSAL,S.HISAL -> ORDER BY S.GRADE;
12.列出薪金高于在部门30工作的所有员工的薪金的员工姓名和薪金、部门名称
mysql> SELECT E.EMPNO,E.ENAME,E.SAL,D.DNAME FROM EMP E,DEPT D WHERE E.SAL>ALL( SELECT SAL FROMM EMP WHERE DEPTNO=30) AND E.DEPTNO=D.DEPTNO;
13.列出在每个部门工作的员工数量、平均工资和平均服务期限
mysql> SELECT D.DEPTNO,D.DNAME,COUNT(E.EMPNO),AVG(E.SAL), AVG(DATEDIFF(SYSDATE(),HIREDATE)/365) YEAR FROM EMP E,DEPT D WHERE E.DEPTNO=D.DEPTNO GROUP BY D.DEPTNO,D.DNAME;
14.列出所有员工的姓名、部门名称和工资
mysql> SELECT E.ENAME,D.DNAME,E.SAL -> FROM EMP E,DEPT D -> WHERE E.DEPTNO=D.DEPTNO;
15.列出所有部门的详细信息和部门人数
mysql> SELECT D.DEPTNO,D.DNAME,D.LOC,COUNT(E.EMPNO) -> FROM DEPT D,EMP E -> WHERE E.DEPTNO=D.DEPTNO -> GROUP BY D.DEPTNO,D.DNAME,D.LOC;
16.列出各种工作的最低工资及从事此工作的雇员姓名
mysql> SELECT E.ENAME,E.JOB,E.SAL FROM EMP E,( SELECT MIN(SAL) MIN,JOB FROM EMP GROUP BY JOB) TEMP WHERE E.SAL=TEMP.MIN AND E.JOB=TEMP.JOB;
17.列出各个部门的MANAGER的最低薪金、姓名、部门名称、部门人数
mysql> SELECT E.ENAME,E.SAL,D.DNAME,RES.COUNT FROM DEPT D,EMP E,( SELECT DEPTNO DNO,MIN(SAL)MIN FROM EMP WHERE JOB='MANAGER' GROUP BY DEPTNO) TEMP,( SELECT DEPTNO DNO,COUNT(EMPNO) COUNT FROM EMP GROUP BY DEPTNO) RES WHERE E.DEPTNO=TEMP.DNO AND E.SAL=TEMP.MIN AND E.JOB='MANAGER' AND D.DEPTNO=E.DEPTNO AND RES.DNO=D.DEPTNO;
18.列出所有员工的年工资，所在部门名称，按年薪从低到高排序
mysql> SELECT E.ENAME,E.SAL*12 ALLSAL,D.DNAME FROM EMP E,DEPT D WHERE D.DEPTNO=E.DEPTNO ORDER BY ALLSAL ASC;
19.列出某个员工的上级主管及所在部门名称，并要求出这些主管中的薪水超过3000
mysql> SELECT DISTINCT M.ENAME,D.DNAME,M.SAL FROM EMP E,EMP M,DEPT D WHERE E.MGR=M.EMPNO AND M.DEPTNO=D.DEPTNO AND M.SAL>3000;
20.求出部门名称中带‘S’字符的部门员工的工资合计、部门人数
mysql> SELECT D.DNAME,SUM(E.SAL),COUNT(E.EMPNO) -> FROM DEPT D,EMP E -> WHERE E.DEPTNO=D.DEPTNO -> AND D.DNAME LIKE '%S%' -> GROUP BY D.DNAME;
21.给任职日期超过30年或者87年雇佣的雇员加薪，加薪原则：10部门增长10%，20部门增长20%，30部门增长30%，以此类推
UPDATE EMP SET SAL=(1+DEPTNO/100)*SAL WHERE DATEDIFF(NOW(),HIREDATE)/365>30 OR YEAR(HIREDATE)=1987
