Home Forums Jobs and Interview Zone Interview Questions PL/SQL Programming Interview Questions

This topic contains 0 replies, has 1 voice, and was last updated by  Sunita.Raturi 2 years, 7 months ago.

Viewing 1 post (of 1 total)
  • Author
    Posts
  • #2717

    Sunita.Raturi
    Participant

    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

    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

    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

    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.

    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/

Viewing 1 post (of 1 total)

You must be logged in to reply to this topic.