Page | 1
Employee Database
:-An enterprise wishes to maintain a database to automate its operations. Enterprise divided into certain department consists of employees. The following two tables describe the automation schemas.
Dept (dno, dname, loc) Employ (eno, ename, job, mgr, sal, comm, dno, hire date)
Write a Query to display all the details of dept table. SQL> select * from dept;
Write a Query to display all the details of employ table. SQL> select * from employ;
Write a Query to display the unique jobs from the employ table. SQL> select distinct(job) from employ;
Write a Query to display the employ record for eno=4. SQL> select * from employ where eno=4;
Write a Query to display the employ record for eno! =2. SQL> select * from employ where eno!=2;
Write a Query to display the employ record for sal>15000. SQL> select * from employ where sal>15000;
Write a Query to display the employ record for eno greater than any of 2, 3, 4. SQL> select * from employ where eno>any(2,3,4);
Write a Query to display the employ record for eno greater than of all 2, 3, 4. SQL> select * from employ where eno>all(2,3,4);
Write a Query to display the employ record for ename like start with any character, second character
must be ‘a’ and any of the remaining characters. SQL> select * from employ where ename like ‘_a%’;
Write a Query to display the employ record for ename not like start with any character, second
character must be ‘a’ and any of the remaining characters. SQL> select * from employ where ename not like ‘_a%’;
Page | 2
Write a Query to display the employ record for eno is not in 2, 3, 4. SQL> select * from employ where eno not in(2,3,4);
Write a Query to display the employ record for eno between 1&3. SQL> select * from employ where eno between 1 and 3;
Write a Query to display the employ record for eno not between 1&3. SQL> select * from employ where eno not between 1 and 3;
Write a Query to display the employ record for hire date is ’01- JAN-1996’ and eno>3. SQL> select * from employ where hiredate>’01-JAN-1996’ and eno>3;
Write a Query to display the employ record for hire date is ’01-JAN-1996’ or eno>3. SQL> select * from employ where hiredate>’01-JAN-1996’ or eno>3;
Write a Query to display the employ record for hire date is null. SQL> select * from employ where hiredate is null;
Write a Query to display the employ record in the descending order of ename. SQL> select * from employ order by ename desc;
Write a Query to display the employ record in the ascending order of job and descending order of ename.
SQL> select * from employ order by job asc,ename desc;
Write a Query to display the initial letters of the ename in the upper case. SQL> select initcap(ename) from employ;
Write a Query to display ename in the lower case. SQL> select lower(ename) from employ;
Write a Query to display ename in the Upper case. SQL> select upper(ename) from employ;
Write a Query to display length of ename. SQL> select length(ename) from employ;
Write a Query to display the maximum salary of employees SQL> select max(sal) from employ;
Write a Query to display the minimum salary of employees. SQL> select min(sal) from employ;
Page | 3
Write a Query to count the values in the commission. SQL> select count(comm) from employ;
Write a Query to display the average salaries of employees. SQL> select avg(sal) from employ;
Write a Query to display the total salaries of employees in job wise. SQL> select job,sum(sal) from employ group by job order by job;
Write a Query to display the total salaries of employees for job in lecturer. SQL> select job,sum(sal) from employ group by job having job=’lecturer’;
Write a Query to display the employees with their department names. SQL> select ename,dname from employ,dept where employ.dno=dept.dno;
Write a Query to display the total salaries in department wise in ascending order. SQL> select dname,sum(sal) from employ,dept where employ.dno=dept.dno group by dname order by
dname;
Write a Query to display the total salary of computer science department. SQL> select dname,sum(sal) from employ,dept where employ.dno=dept.dno group by dname having
dname=’computerscience’;
Write a Query to display the employ record matched or unmatched with department. SQL> select * from employ left outer join dept on employ.dno=dept.dno;
Write a Query to display the employ record who are having the same job for eno=4. SQL> select * from employ where job in (select job from employ where eno=4);
Display those employees who joined the company before 15th of month.
SQL> select * from employ where to_char(hiredate,’dd’)<15;
Display those employees who completed 32 years of service. SQL> delete from employ where months_between (sysdate,hiredate)/12>=32;
Update the employ salary by 25% whose experience is >10years.
SQL> update employ set sal=sal+sal*25/100 where months between (sys date,hiredate)/12>10; Determine the employ whose total salary is like the minimum salary of any department.
SQL> select * from employ where sal * 1 in(select min(sal) from employ dept where employ.dno=dno group by ename);
List the employees whose salary is greater than his manager’s salary.
Page | 4
List the eno, ename, job, dname, loc for those who are working as managers. SQL>select eno,ename,job,dname,loc from employ,dept where job=’lecturer’ and
employ.dno=dept.dno;
List the ename, job and manager who are without manager. SQL>select ename,job,mgr from employ where mgr is null;
List the names of departments where at least 2employees are working in each department. SQL>select dname.count(employ.dno) from employ dept where employ.dno=dno group by dname
having count(employ.dno)>3;
Find out the first 2 earners of the company in ascending orders. SQL>select * from employ where eno<3 order by sal asc;
Find out the number of employees whose salary is greater than their manager’s salary. Select * from emp e where sal>(select sal from emp where empno=e.mgr);
List first 50% of character of ename in lower case and remaining are upper case. Select ename, lower(substr(ename,length(ename)/2), uppercase(substr(ename, (length(ename)/2+1,length(ename))) from emp;
List the employees who are working as managers. SQL>select * from employ where eno in (select mgr from employ);
List the employees who joined in the company on the same date. SQL>select * from employ where hiredate in (select hiredate from employ having count (hiredate)>1
group by hiredate;
List the employees with hire date in format June 4,1988. Select to_char(hiredate,’month dd yyyy’)from emp;
Find out the employees who joined in the company before their managers.
Select * from emp e where hiredate<(select hiredate from emp where empno=e.mgr);
List the department numbers where there are no employees. SQL>select dno,dname from dept where dno not in (select dno from employ);
Display the jobs which are unique to dept no 10.
select job from emp where deptno=10 minus select job from empwhere deptno!=10; (or)
Page | 5
Shipment Database:
An enterprise wishes to maintain the details about his suppliers and other corresponding details to that it uses the following tables.
Table s (sid, sname, address) SQL>select * from s;
Table p (pid, pname, color) SQL>select * from p;
Table cat (sid, pid, cost) SQL>select * from cat;
Find the pname of parts for which there is same supplier. SQL>select p.pname from s,p,cat where s.sid=cat.sid and cat.pid=p.pid;
Find the sname of suppliers who supply part. SQL>select distinct(sname) from s,cat where s.sid=cat.sid;
Find the sname of suppliers who supply every black part. SQL>select distinct(sname) from s,p,cat where s.sid=cat.sid and cat.pid=p.pid and p.color=’black’;
Find the pname of part supplied by Akividu supplier. SQL>select pname from s,p,cat where address=’akvd’ and s.sid=cat.sid and cat.pid=p.pid;
Using group by with having clause get the pact numbers for all the parts supplied by more than one supplier.
Select pid from s,p where s.sid=cat.sid groupby pid having count distinct(cat.sid)>1;
Find the Sid of suppliers who supply a black and a blue part. SQL>select distinct(s.sid) from s,p,cat where s.sid=cat.sid and cat.pid=p.pid and p.color=’black’
intersect distinct(s.sid) from s,p,cat where s.sid=cat.sid and cat.pid=p.pid and p.color=’blue’;
Find the Sid of suppliers who supply a black or blue part. SQL>select distinct(s.sid) from s,p,cat where s.sid=cat.sid and cat.pid=p.pid and p.color=’black’ union
select distinct(s.sid) from s,p,cat where s.sid=cat.sid and cat.pid=p.pid and p.color=’blue’;
Page | 6
Order of Tracking Database:
The order tracking database consists of the following six relation schemes. Employee (eno, ename, zip, hdate)
Parts(pno,pname,qoh,price,level) Customer(cno,cname,street,zip,phone)
Orders(ono,cno,eno,received_date,shipped date) Odetails(ono,pno,qty)
Zip code(zip,city)
Employee (eno, ename, zip, hdate)
SQL>select * from employee;
Parts (pno, pname, qoh, price, level) SQL>select * from parts;
Customer (cno, cname, street, zip, phone) SQL>select * from customer;
Orders (ono, cno, eno, received data, shipped data) SQL>select * from orders;
Odetails (ono, pno, qty) SQL>select * from odetails;
Zip codes (zip, city) SQL>select * from zip;
Get all parts of customer number for customer on same zip code. SQL>select cno,cname,street,zip from customer where zip in(select zip from customer group by zip
having count(zip)>1;
Retrieve the names of customers who are having same street and same city of customer sunny. SQL>select * from customer where (street,zip) in (select street,zip from customer where
cname=’sunny’;
Get part numbers for parts that have been ordered by more than one customer. SQL>select p.pno from parts p,odetails od,orders o where p.pno=od.pno and od.ono=o.ono group by
Page | 7 For each odetails row gets ono, pno, pname, qty and price values along with the total price for the item. Select od.ono,od.pno,p.pname,od.qty*p.price=’total price’ from odetails od,parts p where
od.pno=p.pno
Get customer names living in Hyderabad (or) Bangalore . SQL>select c.cno,c.cname,z.city from customer c,zip z where c.zip=z.zip and (z.city=’hyderabad’ or
z.city=’banglore’);
Get pname values of parts with the lowest price. SQL>select pname from parts where price=(select min(price) from parts);
Get the cities in which customers or employee are located. SQL>select z.city from customer c,zip z where c.zip=z.zip union select z.city from employee e,zip z
where e.zip=z.zip;
Get the total sales in rupees on all orders. SQL>select sum(od.qty*p.price) from odetails od,parts p where od.pno=p.pno;
Get pname value that cost more than the average cost of all parts. SQL>select pname from parts where price>(select avg(price) from parts);
Get for each part pno and total sales. SQL>select p.pno,sum(p.price *od.qty) from parts p, odetails od where p.pno=od.pno group by p.pno;
For each part get pno, total sales whose total sales exceeds low. SQL>select p.pno,sum(p.price *od.qty) from parts p, odetails od where p.pno=od.pno group by p.pno
Page | 8
University Database:
University wishes to computerize their operations by using the following relations.
Student (sname, snum, major, lev, age) Class (cname, hour, room, fid) Enrolled (snum, ename) Faculty (fid, fname, deptid) Dept (deptid, dname, location)
Student (sname, snum, major, lev, age)
SQL>select * from student;
Class (cname, hour, room, fid) SQL>select * from class;
Enrolled (snum, ename) SQL>select * from enrolled;
Faculty (fid, fname, deptid) SQL>select * from faculty;
Dept (deptid, dname, location) SQL>select * from dept1;
Find the names of all juniors(lev=’jr’) who are enrolled in a class taught by ravindra. SQL>select * from student,class,enrolled,faculty where lev=’jr’ and student.snum=enrolled.snum and
enrolled.ename=class.cname and class.fid=faculty.fid and fname=’ravindra’;
Find the age of the oldest student who is either a history or is enrolled in the course of ravindra. SQL>select max(age) from student,class,enrolled,faculty where major=’history’ or
student.snum=enrolled.snum and enrolled.ename=class.cname and class.fid=faculty.fid and fname=’ravindra’;
Find the level and average age of students for that level for each level. SQL>select lev,avg(age) from student group by lev;
Page | 9
Find the names of the student who are not enrolled in any class. SQL>select * from student where snum not in(select student.snum from student,enrolled where
student.snum=enrolled.snum );
Find the names of the student who are enrolled in the maximum number of classes. SQL>select * from student where snum in(select snum from enrolled group by snum having
count(snum) = (select max(count(snum))from enrolled group by snum));
Find the names of all classes that either meet in 514 or have two or more students enrolled. SQL> select cname from class where room=514 or cname in(select cname from enrolled group by
cname having count ( snum))=2);
Find the names of the student who are enrolled in two classes. SQL> select * from student where snum in (select snum from enrolled group by snum having count
(snum)>1);
Page | 10
PL/SQL PROGRAMS
1. Write a PL/SQL program to swap two numbers without using third variable.
Declare
a number(3);
b number(3);
Begin
a: =10;
b: =20;
dbms_output.put_line ('before swapping a='||a);
dbms_output.put_line ('before swapping b='||b);
a:=a+b;
b:=a-b;
a:=a-b;
dbms_output.put_line ('after swapping a='||a);
dbms_output.put_line ('after swapping b='||b);
End;
/
O/P:-
2. Write a PL/SQL program to find the biggest of three given number.
Declare
a number(3);
Page | 11
c number(3);
Begin
a:=&a;
b:=&b;
c:=&c;
if(a>b)then
if(a>c)then
dbms_output.put_line ('a is big');
else
dbms_output.put_line ('b is big');
end if;
else
if(b>c)then
dbms_output.put_line ('b is big');
else
dbms_output.put_line ('c is big');
end if;
end if;
end;
/
Page | 12
3. Write a PL/SQL program to
display “sum” of even numbers and sum of odd
numbers in the given range.
Declare
n number(5);
even_sum number(5);
odd_sum number (5);
i number(5);
begin
n:=&n;
even_sum:=0;
odd_sum:=0;
for i in 1..n loop
if mod(i,2)=0then
even_sum:=even_sum+i;
end if;
if mod(i,2)=1 then
odd_sum:=odd_sum+i;
end if;
end loop;
dbms_output.put_line ('even_sum is'||even_sum);
dbms_output.put_line('odd_sum is'||odd_sum);
End;
Page | 13
O/P:-
4. Write a PL/SQL program to generate Multiplication table.
SQL> declare
i number(5);
j number(5);
begin
i:=2;
while(i<=6)loop
for j in 1..20 loop
dbms_output.put_line(j || '*' || i
end loop;
Page | 14
end loop;
end;
/
Output:
1*2 = 2
2*2 = 4
3*2 = 6
4*2 = 8
5*2 = 10
6*2 = 12
7*2 = 14
8*2 = 16
9*2 = 18
10*2 = 20
11*2 = 22
12*2 = 24
13*2 = 26
14*2 = 28
15*2 = 30
16*2 = 32
17*2 = 34
18*2 = 36
19*2 = 38
20*2 = 40
1*4 = 4
2*4 = 8
3*4 = 12
4*4 = 16
5*4 = 20
6*4 = 24
7*4 = 28
8*4 = 32
9*4 = 36
10*4 = 40
11*4 = 44
12*4 = 48
13*4 = 52
14*4 = 56
15*4 = 60
16*4 = 64
17*4 = 68
18*4 = 72
19*4 = 76
20*4 = 80
1*6 = 6
2*6 = 12
3*6 = 18
4*6 = 24
5*6 = 30
6*6 = 36
7*6 = 42
8*6 = 48
9*6 = 54
10*6 = 60
11*6 = 66
12*6 = 72
13*6 = 78
14*6 = 84
15*6 = 90
16*6 = 96
17*6 = 102
18*6 = 108
19*6 = 114
20*6 = 120
5. W
rite a PL/SQL program to generate prime no’s in a given range.
declare
i number := #
flag number(1) :=0;
begin
for l in 2..i loop
for m in 2..l-1 loop
if mod(l,m) = 0 then
Page | 15
exit;
end if;
end loop;
if flag = 0 then
dbms_output.put_line('* '||l);
end if;
flag := 0;
end loop;
* end;
/
Output:
Enter value for num: 10
old 2: i number := #
new 2: i number := 10;
* 2
* 3
* 5
* 7
PL/SQL procedure successfully completed.
SQL> /
Enter value for num: 20
old 2: i number := #
new 2: i number := 20;
* 2
* 3
Page | 16
* 7
* 11
* 13
* 17
* 19
PL/SQL procedure successfully completed.
6. write a PL/SQL program to find the Armstrong numbers in a given range.
declare
i number(5);
j number(5);
s number(5);
r number(5);
n number(5);
m number(5);
begin
n:=&n;
for i in 1..n loop
s:=0;
j:=i;
m:=j;
while(j>0) loop
r:=mod(j,10);
s:=s+(r*r*r);
j:=trunc(j/10);
end loop;
Page | 17
dbms_output.put_line (i || 'amstrong');
end if;
end loop;
end;
/
Enter value for n: 300
old 9: n:=&n;
new 9: n:=300;
1amstrong
153amstrong
7. Write a PL/SQL program to generate strong numbers in a given range.
declare
i number(5);
j number(5);
s number(20);
r number(5);
n number(5);
k number(5);
f number(20);
m number(5);
begin
n:=&n;
for i in 1..n loop
s:=0;
j:=i;
Page | 18
while(j>0) loop
r:=mod (j,10);
f:=1;
for k in 1..r loop
f:=f * k;
end loop;
s:=s+f;
j:=trunc(j/10);
end loop;
if s=m then
dbms_output.put_line(i || 'strong');
end if;
end loop;
end;
Output:
Enter value for n: 50
old 11: n:=&n;
new 11: n:=50;
1strong
2strong
PL/SQL procedure successfully completed.
SQL> /
Enter value for n: 300
old 11: n:=&n;
new 11: n:=300;
Page | 19
2strong
145strong
PL/SQL procedure successfully completed.
8. Write a PL/SQL program to generate palindrome numbers in a given range
declare
n number(5);
s number(5);
r number(5);
i number(5);
j number(5);
begin
i:=1;
n:=&n;
for i in 1..n loop
j:=i;
s:=0;
while (j>0) loop
r:=mod(j,10);
s:=(s*10)+r;
j:=trunc(j/10);
end loop;
if i=s then
dbms_output.put_line(i || ' is palindrome');
end if;
end loop;
Page | 20
/
Output:
SQL> /
Enter value for n: 100
old 9: n:=&n;
new 9: n:=100;
1 is palindrome
2 is palindrome
3 is palindrome
4 is palindrome
5 is palindrome
6 is palindrome
7 is palindrome
8 is palindrome
9 is palindrome
11 is palindrome
22 is palindrome
33 is palindrome
44 is palindrome
55 is palindrome
66 is palindrome
77 is palindrome
88 is palindrome
99 is palindrome
Page | 21
9. Human Resource Developer (HRD) has decided to rais the employee salary by
15%. Write a PL/SQL program to update the salary of the employee;
Creating emp40 table:
Create table emp40 (eno number (5), ename char (10),sal number(10));
insert into emp41 values(1,'Anjali',10000);
insert into emp41 values(2,'bhavana',20000);
insert into emp41 values(3,'suma',15000);
insert into emp41 values(4,'ramya',25000);
insert into emp41 values(5,'maha',11000);
Select * from emp40;
eno ename sal
---
1. anjali 10000
2. bhavana 20000
3. suma 15000
4. ramya 25000
5. maha 11000
Program:
Declare
no emp41.eno %type;
v_name emp41.ename %type;
v_no emp41.eno %type;
v_sal emp41.sal %type;
flag number(5);
Cursor c_emp is select * from emp41;
Begin
Page | 22
no:=1;
flag:=1;
loop
fetch c_emp into v_no,v_name,v_sal;
exit when c_emp %notfound;
if(v_no=no)then
flag:=0;
end if;
end loop;
if flag=0 then
update emp41 set sal=sal+sal * 15/100 where eno=no;
dbms_output.put_line('salary updated');
else
dbms_output.put_line('employee not found');
end if;
end;
/
Page | 23
10. Write a PL/SQL program to prepare an Electricity Bill using the following table.
Write a pl/sql procedure to prepare an electricity bill using the following table.
Table name is elect
Name
Type
mno
number(3)not null
ename
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)
Program:
Create table elect(mno number(3)not null,ename 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));
insert into elect(mno,ename,cur_read,prev_read)values(001,'mahesh',500,350);
insert into elect(mno,ename,cur_read,prev_read)values(002,'teja',400,250);
insert into elect(mno,ename,cur_read,prev_read)values(003,'banti',600,150);
insert into elect(mno,ename,cur_read,prev_read)values(004,'syam',400,100);
Page | 24
select * from elect;
MN
O
ENAM
E
CUR_RE
AD
PREV_RE
AD
NO_UNI
TS
AMOU
NT
SER_TA
X
NET_A
MT
1.
mahes
h
500
350
2.
Teja
600
250
3.
Banti
600
150
4.
Syam
500
100
5.
Teja
700
450
Main Program:
declare
v_mno elect.mno %type;
v_ename elect.ename %type;
v_cur_read elect.cur_read %type;
v_prev_read elect.prev_read %type;
cursor c_elect is select mno,ename,cur_read,prev_read from elect;
begin
open c_elect;
loop
fetch c_elect into v_mno,v_ename,v_cur_read,v_prev_read;
exit when c_elect %notfound;
end loop;
end;
Page | 25
Creating procedure:
create or replace procedure elect_bill(t_mno number,
t_ename varchar2,
t_cur_read number,
t_prev_read number)
is
v_no_units elect.no units %type;
v_amount elect.amount %type;
v_ser_tax elect.ser_tax %type;
v_net_amt elect.net_amt %type;
v_rate number;
begin
v_no_units:=t_cur_read-t_prev_read;
if v_no_units<=100 then
v_rate:=1.50;
elsif v_no_units<=300 then
v_rate:=2.50;
else
v_rate:=300;
end if;
v_amount:=v_no_units*v_rate;
v_ser_tax:=v_amounts*(7.5/100);
v_net_amt:=v_amount+v_ser_tax;
update elect set no_units=v_no_unit,amount=v_amount,ser_tax=v_ser_tax,
net_amt=v_net_amt where mno=t_mno;
Page | 26
end;
/
Output:
Select * from elect;
MN
O
ENAM
E
CUR_RE
AD
PREV_RE
AD
NO_UNI
TS
AMOU
NT
SER_TA
X
NET_A
MT
1.
mahes
h
500
350
2.
Teja
600
250
3.
Banti
600
150
4.
Syam
500
100
5.
Teja
700
450
Note:
After executing the program write the n NO_UNITS, AMOUNT, SER_TAX, NET_AMT.
11. Write a PL/SQL program Procedure to evaluate the grade of student with
following conditions:
1.
For pass: All marks > 40
2.
For I class :Total percent>59
3.
For II class :Total percent B/W >40 and < 60
4.
For III class :Total percent =40
Table std:
NAME TYPE
No number not null
Name varchar2(20)
Class varchar2(10)
m1,m2,m3,m4,m5 number
And also maintain the details in abstract table details in abstract table.
Table Abstract:
Name Type
std-no number
Page | 27
tot number
grade varchar2(10)
percent number
date-enter date
Program:
Creating student table:
create table std(sno number(5),name varchar2(10),class varchar2(10),m1
number(5),m2 number(5),m3 number(5),m4 number(5),m5 number(5));
insert into std values(1,'Rameshwar','bsc',56,76,49,79,81);
insert into std values(2,'Santosh','bsc',35,50,70,66,70);
insert into std values(3,'Chaitu','bsc',48,50,70,46,60);
insert into std values(4,'Varma','bsc',79,68,93,88,70);
insert into std values(5,'Towfi','bsc',43,49,47,50,45);
select * from std;
NO
NAME
CLASS M1 M2 M3 M4 M5
1.
Rameshwar bsc 56 76 49 79 81
2. Santhosh bsc 35 50 70 66 70
3. Chaitu bsc 48 50 70 46 60
4. Varma bsc 79 68 93 88 70
5. Syam bsc 43 49 47 50 45
Creating Abstract table
:
create table Abstract(std_no number(5),std_name varchar2(10),class varchar2(10),tot
number(5),grade varchar2(10),percent number,date_enter date);
Creating procedure:
create or replace procedure evaluate_grade(v_no number,v_name varchar2,v_class
varchar2,v_M1 number,v_M2 number,v_M3 number,v_M4 number,v_M5
number);
Page | 28
if(v_m1>40 and v_m2>40 and v_m3>40 and v_m4>40 and v_m5>40)then
if(v_percent>59)then
v_grade:='first';
else if(v_percent>40)then
v_grade:='second';
else
v_grade:='third';
end if;
else
v_grade:='fail';
end if;
insert into Abstract
values(v_no,v_name,v_name,v_class,v_total,v_grade,v_percent,sys date);
end;
/
Main program:
declare
v_no std1.no %type;
v_name std1.name %type;
v_class std1.class %type;
v_m1 std1.m1 %type;
v_m2 std1.m2 %type;
v_m3 std1.m3 %type;
v_m4 std1.m4 %type;
v_m5 std1.m5 %type;
cusor cur_std1 is select * from std1;
begin
open cur_std1;
loop
fetch cur_sur into v_no,v_name,v_class,v_m1,v_m2,v_m3,v_m4,v_m5;
exit when cur_std1 %notfound;
evaluate_grade(v_no,v_name,v_class,v_m1,v_m2,v_m3,v_m4,v_m5);
end loop;
Page | 29
Output:
Std.no
Std_name
Class
Tot
Grade
Percent
Date_enter
1
Rameshwar Bsc
341
First
68.2
29dec
-2014
2
Santosh
Bsc
291
Fail
58.2
29dec
-2014
3
Chaitu
Bsc
274
Second
54.8
29dec
-2014
4
Varma
Bsc
398
First
79.6
29dec
-2014
5
syam
Bsc
234
Third
4608
29dec
-2014
12. Write a PL/SQL program to find the Factorial of a given number.
Creating a function:
create or replace function factorial(n number)return number as
i number(3);
f number(3):=1;
begin
for i in 1..n loop
f:=f*i;
end loop;
return f;
end;
Calling function:
declare
Page | 30
f number(3);
begin
n:=5;
dbms_output.put_line('the factorial is'||factorial(n));
end;
/
Output:
SQL> /
The factorial is 120
PL/SQL procedure successfully completed.
13. Write a trigger on price of the product table. Fire the trigger when the product
price is decreased above 25%.
create a product table:
create table product1(prod_id number(5),prod_name varchar2(10),price number(6,2));
insert into product1 values(101,'tubes',300);
insert into product1 values(102,'tyers',500);
insert into product1 values(103,'bolts',100);
insert into product1 values(104,'nuts',700);
insert into product1 values(105,'seats',900);
select * from product1;
PROD_ID PROD_NAME PRICE
______________________________
101 tubes 300
102 tyers
500
103 bolts
100
104 nuts
700
Page | 31
