Use of with AS statements in Oracle

I. Introduction to WITH AS:

With AS is actually a subquery that can be used to enhance the readability of SQL, because the query is executed only once and the results are stored in the user temp table space.

Can be referenced more than once, enhancing performance.

Two. With AS usage:

With temp as (     select * from XXX) SELECT * from temp;

two. With as combat:

The department that inquires the total salary of the department is greater than the average total salary of all departments:

Department table s_dept, employee table s_emp;

--Step1: Query The total salary of department name and department with Dept_costs as (select A.name,sum (b.salary) dept_total from   s_dept a,s_emp b   wher E a.id=b.dept_id   GROUP by A.name),--Step2: Calculates the average total salary of the Department avg_costs as (select sum (dept_total)/count (*) with the results of the previous with query. Dept_avg from Dept_costs)--step3: Compare and output query results from two with queries select Name,dept_total from   dept_costswheredept_total > (  select Dept_avg from avg_costs) order by name;

Use of with AS statements in Oracle