子查询练习

例：查询工资高于平均工资的员工
SELECT E.*
FROM (SELECT DEPTNO,AVG(SAL) AVG_SAL
FROM EMP
GROUP BY DEPTNO) A
JOIN EMP E ON E.DEPTNO = A.DEPTNO
WHERE E.SAL > A.AVG_SAL;

先查询平均工资，
将这整个结果作为一个 表，
然后和工资表EMP进行关联，然后再进行比较。

练习：

1.查询每个部门的最高工资对应的员工信息
SELECT *
FROM(SELECT DEPTNO, MAX(SAL) MAX_SAL
FROM EMP
GROUP BY DEPTNO) A
JOIN EMP E ON E.DEPTNO = A.DEPTNO
WHERE E.SAL = A.MAX_SAL;




2.查询SCOTT 所在的部门名称和工资等级（3种方法）
--1.
SELECT D.DNAME ,S.GRADE
FROM EMP E
JOIN DEPT D ON E.DEPTNO = D.DEPTNO
JOIN SALGRADE S ON E.SAL BETWEEN S.LOSAL AND HISAL
WHERE E.ENAME = 'SCOTT';
--2.（错误子查询）
SELECT A.DNAME, S.GRADE
FROM SALGRADE S
JOIN (SELECT * FROM EMP E
JOIN DEPT D ON D.DEPTNO = E.DEPTNO
) A ON A.SAL BETWEEN S.LOSAL AND S.HISAL
WHERE A.ENAME = 'SCOTT';`
--3.(正确子查询)
SELECT D.DNAME , S.GRADE
FROM (
SELECT DEPTNO, SAL
FROM EMP 
WHERE  ENAME = 'SCOTT') A
JOIN DEPT D ON A.DEPTNO = D.DEPTNO
JOIN SALGRADE S ON A.SAL BETWEEN S.LOSAL AND S.HISAL ;

其中第二题的正确的子查询应该是第三种，目的要明确，题目要求得到SCOTT的部门名称和工资等级，而员工信息表内的数据只有人的名称，部门编号，工资。
首先利用emp表内的**SOCTT(独有)**求出其他两表需要的关联信息——部门编号和工资

SELECT DEPTNO, SAL
FROM EMP 
WHERE  ENAME = 'SCOTT'

然后将得到的信息作为一张表，用部门编号关联部门名称表，用工资关联工资等级表，最后求的SCOTT的部门名称和工资等级。

这题实际上没啥意义，练习使用。子查询的搜索速度会比直接查询的速度慢，不如就直接查询来的快。但是实际操作中有不少要求会使用到子查询。

--查询比 SMITH 工资高并且比SCOTT工资低的员工的信息以及他们的工资等级
SELECT A.*,S.GRADE
FROM(
SELECT *
FROM EMP
WHERE SAL > (SELECT SAL
FROM EMP
WHERE ENAME ='SMITH') AND SAL< (SELECT SAL
FROM EMP
WHERE ENAME ='SCOTT') ) A
JOIN SALGRADE S ON A.SAL BETWEEN S.LOSAL AND S.HISAL;
 ----关联表时可以先关联 放在后面关联其实不太好

另一套表

STUDENT,
COURSE,
TEACHER,
SCORE

–9、查询所有课程分数都大于 70 小于 90 的学生

SELECT X.SNO
FROM (
SELECT SNO,COUNT(*) C
FROM SCORE
WHERE DEGREE BETWEEN 70 AND 90 
GROUP BY SNO) X
JOIN SCORE SC  ON X.SNO = SC.SNO
GROUP BY X.SNO,X.C
HAVING X.C = COUNT(DEGREE)

（）–第一步：查询每个学生选修课程的个数

–第二步：查询课程分数大于70 小于90 的学生选修课程个数

–第三步：当学生选修的课程个数=筛选后选修的课程个数时，即所有课程分数都大于 70 小于 90 ）可看可不看

–10、查询 “95033” 班学生各课程的平均分

SELECT CNO , AVG(DEGREE)
FROM STUDENT ST 
JOIN SCORE S ON ST.SNO = S.SNO
WHERE CLASS = 95033
GROUP BY CNO

–11、查询（选修编号为"3-105" 课程的）并且（成绩至少高于选修编号为"3-245" 的同学的成绩的）同学的 Cno 、Sno

--和 Degree ，并按 Degree 从高到低次序排序


SELECT A.CNO,A.SNO ,A.DEGREE
FROM(
SELECT *
FROM SCORE 
WHERE CNO = '3-105' ) A

WHERE A.DEGREE>(SELECT MAX(DEGREE)
FROM SCORE 
WHERE CNO = '3-245')
ORDER BY DEGREE DESC;