子查询四(在select子句中使用子查询)

SELECT d.deptno,d.dname,d.loc,
  (SELECT  COUNT(empno) FROM emp WHERE emp.deptno=d.deptno GROUP BY deptno) con,
  (SELECT AVG(sal) FROM emp WHERE emp.deptno=d.deptno GROUP BY deptno) avgsal
FROM dept d

WITH e AS(
SELECT * FROM emp)
SELECT * FROM e;

--查询每个部门的编号，名称，位置，部门平均工资，人数
WITH e AS(
  SELECT deptno dno,ROUND(AVG(sal),2) avgsal,COUNT(empno) con
  FROM emp
  GROUP BY deptno
)
SELECT d.deptno,d.dname,d.loc,e.avgsal,e.con
FROM dept d,e
WHERE d.deptno=e.dno(+);

WITH e AS(
   SELECT deptno dno,MAX(sal) maxsal
   FROM emp
   GROUP BY deptno
)
SELECT em.ename,em.job,em.hiredate,em.sal,d.deptno,d.dname
FROM emp em,dept d,e
WHERE em.deptno=e.dno
       AND em.sal=e.maxsal
       AND d.deptno=e.dno
       ORDER BY em.deptno;