1. 各个部门平均、最大、最小工资、人数，按照部门号升序排列。
SELECT deptno AS 部门号,AVG(sal) AS 平均工资 ,MAX(sal) AS 最高工资,MIN(sal) AS 最低工资 ,COUNT(*) AS 人数 FROM emp GROUP BY deptno ORDER BY deptno ASC;
2. 各个部门中工资大于5000的员工人数。
SELECT deptno,COUNT(*) FROM emp WHERE sal > 5000 GROUP BY deptno;
3. 各个部门平均工资和人数，按照部门名字升序排列。
SELECT DNAME,AVG(SAL),COUNT(*) FROM (SELECT (SELECT DEPT.DNAME FROM DEPT WHERE DEPT.DEPTNO = EMP.DEPTNO) DNAME,EMP.SAL FROM EMP) GROUP BY DNAME ORDER BY DNAME;
4. 列出每个部门中有同样工资的员工的统计信息，列出他们的部门号，工资，人数。
SELECT EMP1.DEPTNO,EMP1.SAL,COUNT(*) FROM EMP EMP1,EMP EMP2
WHERE EMP1.DEPTNO = EMP2.DEPTNO
AND EMP1.SAL = EMP2.SAL
AND EMP1.EMPNO <> EMP2.EMPNO
GROUP BY EMP1.DEPTNO,EMP1.SAL;
5. 列出同部门中工资高于1000 的员工数量超过2 人的部门，显示部门名字、地区名称。
SELECT
D.DNAME,D.LOC,COUNT(*)
FROM EMP E,DEPT D
WHERE E.DEPTNO = D.DEPTNO AND
E.SAL > 1000
GROUP BY D.DNAME,D.LOC
HAVING COUNT(*) > 2;
6. 哪些员工的工资，高于整个公司的平均工资，列出员工的名字和工资（降序）。
SELECT ENAME,SAL
FROM EMP
WHERE SAL> (
SELECT AVG(SAL)
FROM EMP
)
ORDER BY SAL DESC;
7. 哪些员工的工资，介于10号 和30号部门平均工资之间。
SELECT ENAME,SAL
FROM EMP
WHERE SAL
BETWEEN
(SELECT AVG(SAL) FROM EMP
WHERE DEPTNO = 10)
AND (SELECT AVG(SAL) FROM EMP
WHERE DEPTNO = 80);
SELECT * FROM EMP
8. 所在部门平均工资高于5000 的员工名字。
SELECT ENAME,SAL
FROM EMP
WHERE DEPTNO IN
(SELECT DEPTNO FROM EMP
GROUP BY DEPTNO
HAVING AVG(SAL) > 5000);
9. 列出各个部门中工资最高的员工的信息：名字、部门号、工资。
SELECT ENAME
,SAL ,DEPTNO
FROM EMP
WHERE (DEPTNO,SAL ) IN
(SELECT DEPTNO,MAX(SAL)
FROM EMP
GROUP BY DEPTNO);
10. 最高的部门平均工资是多少。
SELECT MAX(AVGSALARY)
FROM(SELECT DEPTNO,AVG(SAL) AVGSALARY
FROM EMP
GROUP BY DEPTNO);