PLSQL Interview Questions And Answers For Practice – Part 2

declare
      v_deptno number;

      v_dname varchar2(30);

      v_loc varchar(30);

begin

     select deptno,dname,loc into v_deptno,v_dname,v_loc from dept

where dname='SALES';

dbms_output.put_line('THE DEPARTMENT NAME SALES DETAILS WAS'||'  '||

v_deptno||'  '||v_dname||'  '||v_loc);

end;

declare

p emp_dup%rowtype;

begin

     select * into p from emp_dup where empno=7686;

dbms_output.put_line('THE EMPLOYEE DETAILS WAS'||' '||p.empno||'  '

||p.ename||' '||p.sal||'  '||p.job||'  '||p.deptno);

end;

declare
      v_deptno number:=30;

      v_sum_sal number;

begin

    select deptno,sum(sal) into v_deptno,v_sum_sal from emp_dup

where deptno=v_deptno group by deptno;

dbms_output.put_line('THE SUM OF SALARY OF DEPTNO30 WAS'||v_sum_sal);

end;

begin
    insert into emp_duplicate values(7778,'GVR','manager',66,sysdate,

6000,300,50);

end;

declare
p emp_dup%rowtype;

begin

     select * into p from emp_dup where job='manager';

update emp_dup set sal=p.sal+5000 where job='manager';

end;

SQL> variable rows_deleted varchar2(30)

begin
delete from emp_dup where deptno=20;

:rows_deleted:=(sql%rowcount||' '||'rows_deleted');

end;

SQL> variable max_deptno number

declare
      v_max_deptno number;

begin

    select max(deptno) into v_max_deptno from dept;

:max_deptno:=v_max_deptno;

end;

SQL> print max_deptno;

SQL> define department_name=’EDUCATION’;

declare
      v_deptno number:=50;

      v_dname varchar2(30):='&department_name';

begin

insert into dept(deptno,dname) values(v_deptno,v_dname);

end;

SQL> select * from dept;

SQL> define p_deptno=50;

SQL> variable g_result varchar2(30)

declare
       v_deptno number:=&p_deptno;

       result varchar2(30);

begin

    delete from dept where deptno=v_deptno;

result:=(sql%rowcount||(row(s) deleted');

:g_result:=result;

end;

SQL> print g_result;

SQL> select * from dept;

declare
p emp_dup%rowtype;

begin

select * into p from emp_dup where ename='KEVIN';

if p.ename ='KEVIN' then

p.mgr:=150;

update emp_dup set mgr=p.mgr where ename='KEVIN';

end if;

end;