Oracle's powerful with clause

SELECT * from EMP;

	EMPNO	Ename	JOB	MGR	HireDate	SAL	COMM	DEPTNO
1	7369	SMITH	Clerk	7902	1 July-December-80	800		20
2	7499	ALLEN	Salesman	7698	20月-February-81	1600	300	30
3	7521	WARD	Salesman	7698	2 February-February-81	1250	500	30
4	7566	JONES	MANAGER	7839	February-April-81	2975		20
5	7654	MARTIN	Salesman	7698	2 August-September-81	1250	1400	30
6	7698	BLAKE	MANAGER	7839	January-May-81	2850		30
7	7782	CLARK	MANAGER	7839	September-June-81	2450		10
8	7788	SCOTT	ANALYST	7566	1 September-April-87	3000		20
9	7839	KING	President		1 July-November-81	5000		10

SELECT * FROM dept;

deptno	dname	loc
1	10	accounting	new YORK
2	20	research	dallas
3	30	sales	chicago
4	40	OPERATIONS	BOSTON

The use of powerful with clauses

With
Dept_costs as (
SELECT D.dname, SUM (e.sal) as Dept_total
From EMP E, Dept D
WHERE E.deptno = D.deptno
GROUP by D.dname),
Avg_cost as (
SELECT SUM (dept_total)/count (*) as Dept_avg from Dept_costs)
SELECT * from Dept_costs
WHERE Dept_total <
(SELECT dept_avg from Avg_cost)
ORDER by Dname;

Official explanation:

The WITH Clause Usage Notes
It is used only with SELECT statements.
A query blocks (including their subquery blocks), name is visible to all with element query
Defined after it and the main query block itself (including its subquery blocks).
When the query name is the same as a existing table name, the parser searches from the inside
Out, the query block name takes precedence over the table name.
The WITH clause can hold more than one query. Each query was then separated by a comma.

Oracle's powerful with clause