The new WITH clause

You can now (to some degree) hide sub-query complexity using the new WITH clause

SQL>   WITH dept_salaries AS (
  2        SELECT dname, SUM(sal) dept_sal
  3           FROM emp e, dept d
  4           WHERE e.deptno = d.deptno
  5        GROUP BY dname),
  6     avg_sal AS ( SELECT AVG(dept_sal) asal
  7                  FROM dept_salaries)
  8
  9   SELECT * FROM dept_salaries d, avg_sal a
 10   WHERE d.dept_sal > a.asal
 11   ORDER BY d.dname;

Notice as well, the querys within the WITH clause can also reference each other.