WITH SQL Clause Concepts
Posted: Wed Jan 17, 2024 4:46 am
Show data of those employees who are having salary more than the average of total salary.
Show data of those employees who are having salary more than the average of total salary of his/her department.
how to use WITH clause inside plsql cursor/for loop
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;