This is the Part-3 of PLSQL Interview Questions And Answers For Practice. These are most common PLSQL Interview Questions with Answers that can help you crack Interviews and leave a good Impression on the interviewer.


PLSQL means Procedural Language SQL. PL/SQL enhances the capabilities of SQL by adding control Structures found in different
 procedural language. PLSQL provides both, the flexibility of SQL along with Powerful feature of 3rd generation Language. PLSQL can be used in both Client Side Application development tools and in Oracle Server Database.

 

What are the Advantages of using PLSQL over Traditional SQL

PLSQL has support for SQL and supports Object-Oriented Programming. It’s integration with Oracle provides better performance, portability and  higher productivity.
  • PLSQL Supports the declaration and manipulation of object types and collections.
  • PLSQL Allows the calling of external functions and procedures.
  • PLSQL Contains new libraries of built in packages.d] with PL/SQL , an multiple sql statements can be processed in a single command line statement.

Developers and Database Administrators use PLSQL coding on a daily basis, whether for application development, finding problems, fine-tuning solutions to those problems, or other critical Database Administration tasks. Our aim here is to provide answers to PLSQL questions that Database Administrators come across daily.

PLSQL Interview Questions And Answers For Practice - Part 3

declare

r emp_dup%rowtype;

begin

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

if r.ename='KEVIN' then

r.job:='SA_CSR';

r.deptno:=100;

update emp_dup set job=r.job where ename='KEVIN';

update emp_dup set deptno=r.deptno where ename='KEVIN';

end if;

end;

SQL> select * from emp_dup where ename=’KEVIN’;

declare

p emp_dup%rowtype;

begin

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

if p.ename ='KEVIN' and p.sal>10000 then

p.deptno:=100;

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

end if;

end;

SQL> select * from emp_dup where ename=’KEVIN’;

declare

p emp_dup%rowtype;

begin

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

if p.deptno=100 or p.hiredate>'01-jan-2000' then

p.mgr:=100;

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

end if;

end;

SQL> select * from emp_dup where ename=’KEVIN’;

declare

      value_grade varchar2(5):=('&p_grade');

      value_appraisal varchar2(15);

begin

    if value_grade='A' then

value_appraisal :='EXCELLENT';

elsif value_grade='B' then

value_appraisal :='VERY GOOD';

elsif value_grade='C' then

value_appraisal :='GOOD';

elsif value_grade='D' then

value_appraisal :='BAD';

end if;

dbms_output.put_line('THE APPRISAL CATEGORY WAS'||value_appraisal );

end;
begin

    for i in 1..15

loop

insert into messages values(i);

end loop;

insert into messages values(17);

for i in 19..20

loop

insert into messages values(i);

end loop;

commit;

end;

SQL> select * from messages;

declare

       cursor emp_cursor is select empno,ename from emp_csr;

r emp_cursor%rowtype;

begin

     open emp_cursor;

loop

fetch emp_cursor into r;

dbms_output.put_line('THE EMPLOYEE DETAILS WAS'||r.empno||r.ename);

exit when emp_cursor%rowcount>=10;

end loop;

close emp_cursor;

end;
declare

      cursor dept_cursor is select * from emp_dup where deptno=100;

r emp_dup%rowtype;

begin

    open dept_cursor;

loop

   fetch dept_cursor into r;

dbms_output.put_line('THE EMPLOYEE DETAILS FOR DEPT 100 WAS'||r.empno||' '

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

exit when dept_cursor%rowcount>=5;

end loop;

close dept_cursor;

end;
declare

      cursor emp_20 is select empno,ename from emp_dup;

r emp_20%rowtype;

begin

    open emp_20;

loop

   fetch emp_20 into r;

dbms_output.put_line('THE EMPLOYEE DETAILS WAS'||r.empno||' '||r.ename);

exit when emp_20 %rowcount>=20;

end loop;

dbms_output.put_line('THE NO OF RECORDS DISPLAYED ARE'||emp_20%rowcount);

close emp_20 ;

end;
declare

      cursor temp_insert is select empno,ename from emp_dup;

emp_record temp_insert%rowtype;

begin

    open temp_insert;

loop

   fetch temp_insert into emp_record;

exit when temp_insert%notfound;

insert into temp_list(empid,tname) values(emp_record.empno,emp_record.ename);

end loop;

close temp_insert;

end;
declare
      v_deptid number;

      v_loc varchar2(15):='NRT';

      v_dname varchar2(15):='CREDIT';

      v_counter number:=1;

begin

     select max(deptno) into v_deptid from dept;

loop

   insert into dept(deptno,dname,loc) values((v_deptid+v_counter),v_dname,v_loc);

v_counter:=v_counter+1;

exit when v_counter>3;

end loop;

end;
declare
      v_counter number:=1;

      v_ename varchar2(15):='GVREDDY';

      v_job varchar2(15):='SRINU';

      v_empno number;

begin

    select max(empno) into v_empno from emp_dup;

while v_counter<=3 loop

insert into emp_dup(empno,ename,job) values((v_empno+v_counter),

v_ename,v_job);

v_counter:=v_counter+1;

end loop;

end;
SQL> SELECT * FROM EMP_DUP;
declare
      v_counter number:=1;

      v_ename varchar2(15):='GVREDDY';

      v_job varchar2(15):='SRINU';

      v_empno number;

begin

    select max(empno) into v_empno from emp_dup;

while v_counter<=3 loop

insert into emp_dup(empno,ename,job) values((v_empno+v_counter),

v_ename,v_job);

v_counter:=v_counter+1;

end loop;

end;
SQL> SELECT * FROM EMP_DUP;

Also Read : PLSQL Interview Questions And Answers

PLSQL Interview Questions And Answers For Practice - Part 4

http://techhowdy.com/wp-content/uploads/2018/05/PLSQL-Interview-Questions-And-Answers-For-Practice-Part-3.pnghttp://techhowdy.com/wp-content/uploads/2018/05/PLSQL-Interview-Questions-And-Answers-For-Practice-Part-3-150x150.pngDemonDatabase ProgrammingThis is the Part-3 of PLSQL Interview Questions And Answers For Practice. These are most common PLSQL Interview Questions with Answers that can help you crack Interviews and leave a good Impression on the interviewer.PLSQL means Procedural Language SQL. PL/SQL enhances the capabilities of SQL by adding control Structures found...Latest technology news