Write a Pl/Sql program to raise the employee salary by 30%, who have completed their 40 years of service. declare cursor c3 is select * from emp where extract(year from sysdate)-extract(year from hiredate)>40 for update; e emp%rowtype; begin open c3; loop fetch c3 into e; exit when c3%notfound; update emp set sal=e. sal+(30/100)*e. sal where current of c3; end loop; close c3; end; / 17. Write a Pl/Sql program to check the given number is Armstrong ‘or’ not. eclare n number(3); s number(3):=0; t number(3); begin n:=&n; t:=n; while t>0 loop s:=s+power((t mod 10),3); t:=trunc(t/10); end loop; if(s=n) then dbms_output. put_line(‘The given number ‘ || n || ‘is an armstrong number’); else dbms_output. put_line(‘The given number ‘ || n || ‘is not an armstrong number’); end if; end; / 18. Write a Pl/Sql program to display top 10 rows in emp table based on their job and salary. declare cursor c1 is select * from emp order by sal desc; e emp%rowtype; begin IIMC Prashanth Kuma r K (Head-Dept of Compute rs) open c1; loop fetch c1 into e; exit when c1%rowcount=11 or c1%notfound; dbms_output. put_line(‘top ::’||c1%rowcount||’ employee’); dbms_output. put_line(’employee no:’||e. empno); dbms_output. put_line(’employee name’||e. ename); dbms_output. put_line(’employee job is ‘||e. job); dbms_output. put_line(’employee salary is ‘||e. sal); dbms_output. put_line(‘*-*-*-*-*-*-*-*-*-*-*-*-*-*’); end loop; end; / 19. Write a Pl/Sql program to swap two numbers without using third variable. declare a number(3); b number(3); begin a:=&a; b:=&b; dbms_output. ut_line(‘before swapping a= ‘||a||’ and b= ‘||b); a:=a+b; b:=a-b; a:=a-b; dbms_output. put_line(‘after swapping a= ‘||a||’ and b= ‘||b); end; / 20. The hrd manager has decided to raise the employee salary by 20%. Write a Pl/Sql block to accept the employee number and update the salary of that employee. Display appropriate message based on the existence of the record in emp table. declare e emp%rowtype; no emp. empno %type; sa emp. sal%type; begin no:=&no; select * into e from emp where empno=no; sa:=e. sal+(15/100)*e. sal; update emp set sal=sa where empno=no; dbms_output. ut_line(’employee record is modified’); dbms_output. put_line(’employee number is ‘||e. empno); dbms_output. put_line(’employee name is ‘||e. ename); dbms_output. put_line(’employee job is ‘||e. job); dbms_output. put_line(’employee sal is ‘||sa); end; / IIMC Prashanth Kuma r K (Head-Dept of Compute rs)

5 21. Write a Pl/Sql program to generate multiplication tables for 3 & 7. declare i number(3); begin i:=1; for i in 1.. 10 loop dbms_output. put_line(3||’ * ‘||i||’ = ‘||3*i); end loop; dbms_output. put_line(‘***********************’); for i in 1.. 0 loop dbms_output. put_line(7||’ * ‘||i||’ = ‘||7*i); end loop; end; / 22. Write a Pl/Sql program to display the given number is prime or not? DECLARE n number:=&n; j number:=2; counter number:=0; BEGIN WHILE(j10000) then raise_application_error(-20000,’Not allowed to update’); end if; IIMC Prashanth Kuma r K (Head-Dept of Compute rs) 6 end; SQL> update emp Set sal=15000; 24. Write a Pl/Sql program to raise the employee salary by 10%, for department number 30 people and also maintain the raised details in the raise table. reate table raise_emp (empid number(4) primary key, name varchar2(10), desig varchar2(9), mgr number(4), doj date, salary number(7,2), comm number(7,2), dno number(2)); declare cursor c2 is select * from emp where deptno=30 for update; e emp%rowtype; begin open c2; loop fetch c2 into e; exit when c2%notfound; update emp set sal=e. sal+(10/100)*e. sal where current of c2; end loop; dbms_output. put_line(’employee details are stored in the emp_raise table’); insert into raise_emp(select * from emp where deptno=30); close c2; end; / 25. Write a procedure to update the salary of employee, who are not getting ommission 9%. create or replace procedure raise_comm as begin update emp set sal=sal+(10/100)*sal where comm is null or comm=0; end; / 26. Write Pl/Sql program to check the given string is palindrome or not. declare IIMC Prashanth Kuma r K (Head-Dept of Compute rs) 7 s1 varchar2(20); s2 varchar2(20); begin s1:=’;s1′; select reverse(s1) into s2 from dual; if s1=s2 then dbms_output. put_line(‘given string is palindrome’); else dbms_output. put_line(‘given string is not palindrome’); end if; end; / 27. Write a Pl/Sql procedure to prepare a telephone bill by using following table.

And print the monthly bills for each customer Table used : phone. Name null? Type —————————- —————-Tel_no not null number(6) Cname varchar2(20) City varchar2(10) Pr_read number(5) Cur_read number(5) Net_units number(5) Tot_amt number(8,2) Create table phone ( Telno number(6) primary key, Cname varchar2(20), City varchar2(10), Pr_read number(5), Cur_read number(5), Net_units number(5), Tot_amt number(8,2) ); SQL>insert into phone(telno,cname,city,pr_read,cur_read) values(‘;telno’,’;cname’,’;city’,&pr_read,&net_units); create or replace procedure phonebill as nunit phone. et_units%type; tamt phone. tot_amt%type; p phone%rowtype; cursor c is select * from phone for update; begin open c; loop fetch c into p; exit when c%notfound; IIMC Prashanth Kuma r K (Head-Dept of Compute rs) 8 nunit:=p. cur_read-p. pr_read; tamt:=nunit*0. 5; update phone set net_units=nunit where current of c; update phone set tot_amt=tamt where current of c; dbms_output. put_line(‘customer phone number’||p. telno); dbms_output. put_line(‘customer name’||p. name); dbms_output. put_line(‘city’||p. city); dbms_output. put_line(‘total amount’||tamt); end loop; close c; end; SQL>exec phonebill; 28. Write a procedure to update the salary of employee, who belongs to Department 20 with a 15% percentage of raise create or replace procedure raise_salary as begin update emp set sal=sal+(15/100)*sal where deptno=20; end; / 29. Write a Pl/Sql procedure to prepare an electricity bill by using following table : elect name null?

Type —————-mno not null number(3) cname varchar2(20) cur_read number(5) prev_read number(5) no_units number(5) amount number(8,2) ser_tax number(8,2) net_amt number(9,2) create table elect (mno number(3) primary key, cname varchar2(20), cur_read number(5), prev_read number(5), no_units number(5), amount number(8,2), ser_tax number(8,2), net_amt number(9,2) ); SQL>Insert into elect(mno,cname,cur_read,prev_read) Values(&mno,’&cname’,&cur_read,&prev_read); IIMC Prashanth Kuma r K (Head-Dept of Compute rs) 9 create or replace procedure powerbill as no_un elect. o_units%type; amt elect. amount%type; stax elect. ser_tax%type; net elect. net_amt%type; e elect%rowtype; cursor c9 is select * from elect for update; begin open c9; loop fetch c9 into e; exit when c9%notfound; no_un:=e. cur_read-e. prev_read; amt:=no_un*1. 5; stax:=(5/100)*amt; net:=amt+stax; update elect set no_units=no_un,amount=amt, ser_tax=stax,net_amt=net where current of c9; dbms_output. put_line(‘customer meter number’||e. mno); dbms_output. put_line(‘customer name’||e. cname); dbms_output. put_line(‘total amount is ‘||net); end loop; close c9; end; 30.

Write a Pl/Sql program to retrieve data from emp table using cursors. declare cursor c1 is select * from emp; e emp%rowtype; begin open c1; loop fetch c1 into e; exit when c1%notfound; dbms_output. put_line(’emp ::’||c1%rowcount||’ employee’); dbms_output. put_line(’employee no:’||e. empno); dbms_output. put_line(’employee name’||e. ename); dbms_output. put_line(’employee job is ‘||e. job); dbms_output. put_line(’employee salary is ‘||e. sal); dbms_output. put_line(‘*-*-*-*-*-*-*-*-*-*-*-*-*-*’); end loop; end; IIMC Prashanth Kuma r K (Head-Dept of Compute rs)