sql练习

##每个部门最高工资人员名称

EXPLAIN SELECT d.deptno,e.empno,e.ename FROM emp e
INNER JOIN
(SELECT deptno, MAX(sal) MAXSAL FROM emp GROUP BY deptno) d
ON e.deptno = d.deptno AND e.sal= d.maxsal
ORDER BY deptno;

##那些人的薪水在平均部门工资之上
SELECT e.sal,e.deptno,d.age_sal,e.ename
FROM emp e
INNER JOIN (SELECT deptno, AVG(sal) age_sal FROM emp GROUP BY deptno) d
ON e.sal > d.age_sal
ORDER BY deptno;

##平均薪水等级
SELECT s.grade,s.hisal,s.losal,d.age_sal
FROM salgrade s
INNER JOIN (SELECT deptno, AVG(sal) age_sal FROM emp GROUP BY deptno) d
ON d.age_sal BETWEEN s.losal AND s.hisal;
SELECT deptno, sal FROM emp ORDER BY deptno DESC LIMIT 1;
SELECT sal FROM emp WHERE sal NOT IN(SELECT DISTINCT A.sal FROM emp A INNER JOIN emp B ON (a.sal < b.sal));

##取得最高平均薪水的部门编号
SELECT deptno, AVG(sal) age_sal FROM emp GROUP BY deptno ORDER BY deptno ASC LIMIT 1;

##取得最高平均薪水的部门名称

SELECT dname FROM dept d INNER JOIN (SELECT deptno, AVG(sal) age_sal
FROM emp GROUP BY deptno ORDER BY deptno ASC LIMIT 1) e
ON e.deptno = d.deptno;

##取得平均薪水d的等级最低的部门名称

SELECT s.grade,s.hisal,s.losal,d.age_sal
FROM salgrade s
INNER JOIN (SELECT deptno, AVG(sal) age_sal FROM emp GROUP BY deptno) d
ON d.age_sal BETWEEN s.losal AND s.hisal ORDER BY grade ASC LIMIT 1 ;

##取得薪水最高的前五名员工
SELECT sal,ename FROM emp ORDER BY sal DESC LIMIT 5;

##取得薪水最高的6到10员工 （索引默认0开始）
SELECT sal,ename FROM emp ORDER BY sal DESC LIMIT 5,5;

##取得每个薪水等级有多少员工
SELECT COUNT(grade),grade FROM emp e INNER JOIN salgrade s ON e.sal BETWEEN s.losal AND s.hisal
GROUP BY s.grade ORDER BY grade DESC ;

##学生表（学号 姓名） 课程表（课号 课名 任课老师） 学生课程表（学号 课号 成绩） （多对多）