WITH SQL Clause Concepts

[admin]

Show data of those employees who are having salary more than the average of total salary.

Code: Select all

WITH avg_sal_query (AVG_SAL) AS
(
    select round(avg(sal),2) from emp 
    )
select e.empno, e.ename, d.dname, e.sal, a.avg_sal from emp e, dept d, avg_sal_query a
where e.deptno = d.deptno
and e.sal > a.avg_sal

Show data of those employees who are having salary more than the average of total salary of his/her department.

Code: Select all

WITH avg_sal_query as
(select deptno, round(avg(sal),2) as AVG_SAL from emp group by deptno)
select * from emp e, dept d, avg_sal_query a
where e.deptno = d.deptno
and e.deptno = a.deptno
and e.sal > a.avg_sal

how to use WITH clause inside plsql cursor/for loop

Code: Select all

begin

for i in (
with avg_sal_query as
(select deptno, round(avg(sal),2) as AVG_SAL from emp group by deptno)
select ename, sal, avg_sal from emp e, dept d, avg_sal_query a
where e.deptno = d.deptno
and e.deptno = a.deptno
and e.sal >= a.avg_sal) loop

dbms_output.put_line('ENAME :' ||  i.ename);
dbms_output.put_line('ENAME :' ||  i.sal);
dbms_output.put_line('ENAME :' ||  i.avg_sal);
end loop;

end;