什么是子查询
1. select语句嵌套select语句
2. 子查询可以出现在:
select..(select).
from..(select).
where..(select).
案例: 找出薪水比公司平均薪水高的员工,显示员工姓名和薪水
SELECT
a.ENAME,
a.SAL
FROM emp a
WHERE a.SAL >
(SELECT
AVG(e.SAL)
FROM emp e);
案例: 找出每个部门的平均薪水,并且显示平均薪水的薪水等级。
SELECT
a.deptno,
a.avgsal,
s.GRADE
FROM (SELECT DEPTNO, AVG(SAL)avgsal
FROM emp e
GROUP BY DEPTNO) a
JOIN salgrade s
ON a.avgsal BETWEEN s.LOSAL AND s.HISAL