学步园

一：视图

--创建视图（要权限）
CREATE VIEW v$_dept_avg_sal_info 
AS (SELECT avg(sal) a_s,deptno s_t FROM emp GROUP BY deptno);

--查询视图
SELECT * FROM v$_dept_avg_sal_info;

二：子查询示例

--部门平均薪水的等级
SELECT a.a_s ,a.deptno,s.grade
FROM salgrade s
JOIN (SELECT avg(sal) a_s,deptno FROM emp GROUP BY deptno)a
ON a.a_s BETWEEN s.losal AND s.hisal;

--薪水等级的平均值（按部门分组）,这题没什么意义……
SELECT a.e_d ,avg(a.s_g)
FROM 
(SELECT emp.deptno e_d,emp.ename , emp.sal , salgrade.grade s_g
FROM emp
JOIN salgrade ON sal BETWEEN salgrade.losal AND salgrade.hisal)a
GROUP BY a.e_d;

--选出所有经理
SELECT ename 
FROM emp --没想到加DISTINCT
WHERE empno IN (SELECT DISTINCT mgr FROM emp);

--平均薪水最高的部门名称
SELECT st3.s_t,st2.m_s
FROM 
(SELECT max(st1.a_s) m_s
FROM (SELECT avg(sal) a_s,deptno s_t FROM emp GROUP BY deptno)st1)st2
JOIN (SELECT avg(sal) a_s,deptno s_t FROM emp GROUP BY deptno)st3
ON st2.m_s = st3.a_s;

--比普通员工最高薪水还高的经理
--注意空值处理
SELECT ename
FROM emp
JOIN (SELECT max(sal) m_s
FROM emp 
WHERE empno NOT IN (SELECT DISTINCT mgr FROM emp WHERE mgr IS NOT null))st1
ON sal>st1.m_s
WHERE empno IN (SELECT DISTINCT mgr FROM emp);

--不用租函数求最大值
--子表中通过自连接找出e1中比e2小的值，去除重复
--主表中NOT IN，不在这个范围内
SELECT DISTINCT sal 
FROM emp
WHERE sal NOT IN
(SELECT DISTINCT e1.sal FROM emp e1 JOIN emp e2 ON (e1.sal<e2.sal));