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.

  1. r NUMBER(6,3);
  2. a NUMBER(6,3);
  3. i NUMBER(2):=3;
  4. r:=3;
  5. i:=3;
  6. WHILE(i<=7)
  7. a:=3.14*r*r;
  8. DBMS_OUTPUT.put_line(r);
  9. DBMS_OUTPUT.put_line(a);
  10. INSERT INTO circle VALUES(r,a);
  11. r:=r+1;
  12. i:=i+1;
  13. END;
  14.  

 
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.
  1.  
  2. p product_master.productno%TYPE;
  3. c product_master.costprice%TYPE;
  4. SELECT productno,costprice INTO p,c FROM product_master WHERE productno='p00001';
  5. IF(c<4000) THEN
  6. UPDATE product_master SET costprice=4000 WHERE productno='p00001';
  7. INSERT INTO old_price VALUES(p,c);
  8. DBMS_OUTPUT.put_line(p);
  9. DBMS_OUTPUT.put_line(c);
  10. END;
  11.  

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.
 
  1.  
  2. s emp.salary%TYPE;
  3. UPDATE emp SET salary=salary+(salary*.15) WHERE job_id='IT-PROG';
  4. IF(sql%found) THEN
  5. DBMS_OUTPUT.put_line('record found');
  6. DBMS_OUTPUT.put_line('record found');
  7. END;
  8.  

 
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
 
  1.  
  2. CURSOR c_emp IS SELECT first_name,salary FROM emp,dept WHERE dept.department_id=emp.department_id ORDER BY salary DESC;
  3. d emp.department_id%TYPE;
  4. str_ename emp.first_name%TYPE;
  5. num_salary emp.salary%TYPE;
  6. OPEN c_emp;
  7. DBMS_OUTPUT.put_line('Name Salary');
  8. DBMS_OUTPUT.put_line('---- ------');
  9. FETCH c_emp INTO str_ename,num_salary;
  10. EXIT WHEN c_emp%rowcount = 10 OR c_emp%notfound;
  11. DBMS_OUTPUT.put_line(str_ename ||' '||num_salary);
  12. DBMS_OUTPUT.put_line(sql%rowcount);
  13. CLOSE c_emp;
  14. END;
  15.  

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
  1.  
  2. CREATE OR REPLACE TRIGGER trig after
  3. UPDATE OF salary ON emp FOR each ROW
  4. s emp.salary%TYPE;
  5. eid emp.employee_id%TYPE;
  6. d DATE;
  7. s:=:old.salary;
  8. d:=SYSDATE;
  9. INSERT INTO a1 VALUES(s,eid,d);
  10. 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.