Write a PL/SQL code block to calculate the area of a circle for
a value of radius varying from 3 to 7. Store the radius and
corresponding values of calculated area in an empty table
named Areas, consisting of two columns Radius and Area.
create table Areas (radius number(5) , area number(5,2) );
Declare
radius number(5);
area number(5,2);
pi number;
Begin
pi:=3.14;
radius:=3;
Loop
area:=pi*power(radius,2);
insert into Areas values (radius,area);
radius:=radius+1;
Exit when radius>7;
End loop;
End;
/
SQL> start Z:/p1.sql;
Table created.
PL/SQL procedure successfully completed.
SQL> select * from areas;
RADIUS AREA
---------- ----------
3 28.26
4 50.24
5 78.5
6 113.04
7 153.86
**************************************************
**********************
Write a PL/SQL block of code for inverting a number 5639 to
9365.
Declare
num number(5);
rev number(5);
rem number(5);
Begin
num:=5639;
dbms_output.put_line('Number = '||num);
rev:=0;
Loop
rem:=mod(num,10);
rev:=(rev*10)+rem;
num:=floor(num/10);
Exit when num=0;
End loop;
dbms_output.put_line('Reverse = '||rev);
End;
/
SQL> start Z:\p3.sql;
Number = 5639
Reverse = 9365
PL/SQL procedure successfully completed.
Write a PL/SQL block of code for inverting a number
accepted from the console.
Declare
num number(5);
rev number(5);
rem number(5);
Begin
num:=#
dbms_output.put_line('Number = '||num);
rev:=0;
Loop
rem:=mod(num,10);
rev:=(rev*10)+rem;
num:=floor(num/10);
Exit when num=0;
End loop;
dbms_output.put_line('Reverse = '||rev);
End;
/
SQL> start Z:\p3.sql;
Enter value for num: 54721
old 7: num:=#
new 7: num:=54721;
Number = 54721
Reverse = 12745
PL/SQL procedure successfully completed.
Write a PL/SQL code block that will accept an account
number from the user and debit an amount of Rs.2000 from
the account if the account has a minimum balance of 500 after
the amount is debited. The process is fired on the Accounts
table.
create table accounts (acc_no number(10),bal number(10,2) );
insert into accounts values (&acc_no , &bal );
Declare
accno number(5);
balance number(5);
Begin
accno:=&accno;
select bal into balance from accounts where acc_no=accno;
balance:=balance-2000;
if balance<500 then
dbms_output.put_line('Amount can not be Withdrawn');
else
dbms_output.put_line('Amount debited');
update accounts set bal=balance where acc_no=accno;
end if;
End;
/
SQL> start Z:\p3.sql;
Table created.
Enter value for acc_no: 101
Enter value for bal: 5000
old 1: insert into accounts values (&acc_no , &bal )
new 1: insert into accounts values (101 , 5000 )
1 row created.
Enter value for accno: 101
old 6: accno:=&accno;
new 6: accno:=101;
Amount debited
PL/SQL procedure successfully completed.
Write a PL/SQL block of code that updates the salaries of
Maria Jacob and Albert by Rs. 2000/- and Rs.2500/-
respectively. Then check to see that the total salary does not
exceed 75000. If the total salary is greater than 75000, then
undo the updates made to salaries of both. (Use savepoint,
rollback and commit).
Declare
sal number(10);
Begin
savepoint sp;
update employee set salary=salary+2000 where
empname='Maria Jacob';
update employee set salary = salary+2500 where
empname='Albert Sebastian';
select sum(salary) into sal from employee;
if (sal > 75000) then
rollback to savepoint sp;
dbms_output.put_line('rollback finish');
else
commit;
end if;
end;
/
The HRD manager decides to raise the salary of employees by
0.15. Write a PL/SQL block to accept an employee number
and update the salary of that employee. Display appropriate
message based on the existence of the record in the employee
table.
create table employe(empno varchar2(10),salary
number(6),empname varchar2(10),job varchar2(10));
insert into employe values('101',20000,'anu','analyst');
insert into employe values('102',26000,'amal','designer');
insert into employe values('103',30000,'deena','analyst');
declare
empnum number(6);
begin
empnum:=&empnum;
update employe set salary=salary+(salary*0.15) where
empno=empnum;
if (sql%found) then
dbms_output.put_line('employee exist');
else
dbms_output.put_line('employee not exist');
end if;
end;
/
The HRD manager decides to raise the salary of employees
working as ‘analyst’ by 0.15. Write a cursor to update the
salary of the employees. Display the no. of employee records
that has been modified.
begin
update employe set salary=salary+(salary*0.15) where
job='analyst';
if(SQL%ROWCOUNT>0) then
dbms_output.put_line(SQL%ROWCOUNT||' records found
and salary is updated');
else
dbms_output.put_line('not found');
end if;
end;
/
Write an explicit cursor to display the name, department,
salary of the first 5 employees getting the highest salary.
DECLARE
CURSOR cur_emp is select empname, depno, salary
from(select * from employee order by salary desc)where
rownum<=5;
emp cur_emp%rowtype;
BEGIN
open cur_emp;
fetch cur_emp into emp;
DBMS_OUTPUT.PUT_LINE('Name Dept Salary');
WHILE cur_emp%found LOOP
DBMS_OUTPUT.PUT_LINE(emp.empname||'
'||emp.depno||' '||emp.salary);
fetch cur_emp into emp;
END LOOP;
close cur_emp;
END;
/
DECLARE
CURSOR cur_emp is select empno,salary from employee
where job ='ANALYST';
emno employee.empno%type;
sal employee.salary%type;
BEGIN
open cur_emp;
fetch cur_emp into emno,sal;
WHILE cur_emp%found LOOP
update employee set salary=salary+(salary*.15) where
empno=emno;
select salary into sal from employee where empno=emno;
insert into emp_raise values(emno,sysdate,sal);
fetch cur_emp into emno,sal;
END LOOP;
close cur_emp;
END;
/
