1、取得每个部门最高薪水的人员名称
SELECT ENAME,SAL,T1.DEPTNO
FROM(SELECT MAX(SAL) MAXSAL,DEPTNO FROM EMP GROUP BY DEPTNO) T1
JOIN EMP T2
ON T1.DEPTNO=T2.DEPTNO AND T1.MAXSAL=T2.SAL;
2、哪些人的薪水在部门的平均薪水之上
SELECT ENAME,SAL,AVGSAL,T1.DEPTNO
FROM (SELECT AVG(SAL) AVGSAL,DEPTNO FROM EMP GROUP BY DEPTNO) T1
JOIN EMP T2
ON T1.DEPTNO=T2.DEPTNO
WHERE SAL>AVGSAL;
3、取得部门中(所有人的)平均薪水的等级
SELECT DEPTNO,AVGSAL,GRADE
FROM (SELECT AVG(SAL) AVGSAL,DEPTNO FROM EMP GROUP BY DEPTNO) T1
JOIN SALGRADE T2
ON T1.AVGSAL BETWEEN T2.LOSAL AND T2.HISAL;
4、不准用组函数(Max),取得最高薪水
//降序,limit 1
SELECT SAL
FROM EMP
ORDER BY SAL DESC
LIMIT 1;
5、取得平均薪水最高的部门的部门编号(至少给出两种解决方案)
SELECT MAX(AVGSAL),DEPTNO
FROM(SELECT AVG(SAL) AVGSAL,DEPTNO FROM EMP GROUP BY DEPTNO) T1;
SELECT AVG(SAL) AVGSAL,DEPTNO FROM EMP GROUP BY DEPTNO
ORDER BY AVGSAL DESC
LIMIT 1;
6、取得平均薪水最高的部门的部门名称
SELECT T1.DEPTNO,MAX(AVGSAL),DNAME
FROM(SELECT AVG(SAL) AVGSAL,DEPTNO FROM EMP GROUP BY DEPTNO) T1
JOIN DEPT T2
ON T1.DEPTNO=T2.DEPTNO;
SELECT T1.DEPTNO,AVGSAL,DNAME
FROM(SELECT AVG(SAL) AVGSAL,DEPTNO FROM EMP GROUP BY DEPTNO) T1
JOIN DEPT T2
ON T1.DEPTNO=T2.DEPTNO
ORDER BY AVGSAL DESC
LIMIT 1;
7、求平均薪水的等级最低的部门的部门名称
//先求出部门平均薪资最低的 等级 --3
SELECT GRADE
FROM SALGRADE
WHERE(SELECT AVG(SAL) AVGSAL FROM EMP