SQL> @select_with.sql

DNAME DEPT_TOTAL
-------------- ----------

RESEARCH 10875

SQL> l
  1 WITH
  2 dept_cost AS
  3 (
  4 SELECT d.dname, SUM(e.sal) dept_total
  5 FROM emp e, dept d
  6 WHERE e.deptno = d.deptno
  7 GROUP BY d.dname
  8 ),
  9 avg_cost AS
 10 (
 11 SELECT SUM(dept_total)/COUNT(*) avg
 12 FROM dept_cost
 13 )
 14 SELECT dname, dept_total FROM dept_cost
 15 WHERE dept_total > (SELECT avg FROM avg_cost)
 16* ORDER BY dname