Skip to main content

PL/SQL Programming Interview Questions

No replies
Offline
Joined: 14 Dec 2011

PL/SQL LogoThere are few programming question which are usually asked during PL/SQL programming job role. I have posted 5 such questions and will try to post/update more time to time.
These questions can be helpful to fresehrs or Entry level PL/SQL programmers. For any queries, please leave comment here. You can even add questions too as a comment.
 

  1.  write a pl/sql to calculate area of a circle for values of radius varying from 3 to 7 and store the radius and corresponding values of calculated area in a table called area.

declare
r number(6,3);
a number(6,3);
i number(2):=3;
begin
r:=3;
i:=3;
while(i<=7)
loop
a:=3.14*r*r;
dbms_output.put_line(r);
dbms_output.put_line(a);
insert into circle values(r,a);
r:=r+1;
i:=i+1;
end loop;
end;
 

 
OUTPUT :-
Output 1 PL/SQL
 

  1.  Write a pl/sql block to achieve the following scenario if the price of product p0001< 4000 then change price to 4000.the price is to be changed is recorded in the old price table along with product_no & date on which modification is done.

declare
p product_master.productno%type;
c product_master.costprice%type;
begin
select productno,costprice into p,c from product_master where productno='p00001';
if(c<4000) then
update product_master set costprice=4000 where productno='p00001';
insert into old_price values(p,c);
end if;
dbms_output.put_line(p);
dbms_output.put_line(c);
end;
 

OUTPUT
 
Output 2 PL/SQL

 
  1.  The manager has decided to increase the salaries of employees working as programmer by 15%. Write a pl/sql code to update salaries of employees & find out how many rows are updated.
 
declare
s emp.salary%type;
begin
update emp set salary=salary+(salary*.15) where job_id='IT-PROG';
if(sql%found) then
dbms_output.put_line('record found');
else
dbms_output.put_line('record found');
end if;
end;
 

 
Output 3 PL/SQL
 
 
  1.  write a pl/sql code that will display department no, employee name, salary of first 10 employees getting highest salary
 
declare
cursor c_emp is select first_name,salary from emp,dept where dept.department_id=emp.department_id order by salary desc;
d emp.department_id%type;
str_ename emp.first_name%type;
num_salary emp.salary%type;
begin
open c_emp;
dbms_output.put_line('Name        Salary');
dbms_output.put_line('----        ------');
loop
fetch c_emp into str_ename,num_salary;
exit when c_emp%rowcount = 10 or c_emp%notfound;
dbms_output.put_line(str_ename ||'  '||num_salary);
dbms_output.put_line(sql%rowcount);
end loop;
close c_emp;
end;
 

Name        Salary
----        ------
Steven  24000
Neena  17000
Lex  17000
John  14000
Karen  13500
Michael  13000
Nancy  12000
Alberto  12000
Shelley  12000

Statement processed.

 
  1.  create a trigger for updating operation on the employee table we need to keep track of employee’s old salary and in new salary & date on which updating is made along with employee no

create or replace trigger trig after
update of salary on emp for each row
declare
s emp.salary%type;
eid emp.employee_id%type;
d date;
begin
s:=:old.salary;
d:=sysdate;
insert into a1 values(s,eid,d);
end; 


Trigger created.

Image Source (Logo) : http://www.downloadatoz.com/
 

Post new comment

The content of this field is kept private and will not be shown publicly.
CAPTCHA
This question is for testing whether you are a human visitor and to prevent automated spam submissions.
Image CAPTCHA
Enter the characters shown in the image.