PL/SQL Programming Interview Questions

There 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; 
 

2.  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; 
 

3.  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; 
 

4.  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; 
 

5.  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;