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
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
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;