SQL WEEK 8

1___________

set serveroutput on;

declare 

coursor emp_e is select ename from emp where sal>30000;

begin

for i in emp_e

loop

dbms_output.put_line(i.ename||'drawas'||i.sal);

end loop;

end;

/

--------

2____________

set serveroutput on;

declare

cursor emp_e is select dno,prj_no,prj_fund,prj_exdit from proj ;

p_fund number(10);

e_fund number(10);

begin

for i in emp_e loop

p_fund:=(i.pexdate*10/100)*i.prj_fund;

update depart set dept_budeget=dept_budeget+((30/100)*p_fund)

where deptno+i.dno;

e_fund:=(70/100)*p_fund;

select count(*) int count_no from emp where prj_id := i.prj_no and deptno=i.dno;

update emp set sal=sal+(e_fund/count_no) where prj_id := i.prj_no and deptno=i.dno;

end loop;

end;

/

--------------------

3____________

set serveroutput on;

declare

cursor emp_e (jb varchar2, dep varchar2 )is select ename from emp where job=jb and deptno=dep;

v_ename emp.ename%type;

begin

dbms_output.put_line('name is');

for i in emp_e loop

exit when emp_e % NOTFOUND;

dbms_output.put_line(i.ename);

end loop;

end;

/

-------------

4_________________

declare

count_no number:=0;

cursor emp_e (pid varchar2)is select ename,sal,dname from emp e ,depart d where proj_id=pid and e.deptno=d.dno order by sal;

begin

for i in emp_e loop

if count_no <2 then 

exit when emp_e % NOTFOUND;

dbms_output.put_line(i.ename||' '||i.dname||' '||i.sal);

count_no:=count_no+1;

end if;

end loop;

end;

/