Functions


1)PL/SQL function to return account balance of a given Customer
Sol.

CREATE TABLE CUSTOMER (CNO NUMBER PRIMARY KEY NOT NULL, CNAME VARCHAR2(20),CITY VARCHAR2(20))

insert into customer values (1,’Rajesh’,’pune’)
insert into customer values (2,’Girish’,’pune’)
insert into customer values (3,’Nilesh’,’Mumbai’)
insert into customer values (4,’Sandip’,’Mumbai’)
insert into customer values (5,’Raj’,’Nashik’)

CREATE TABLE ACCOUNT (ANO NUMBER PRIMARY KEY NOT NULL,ACC_TYPE VARCHAR2(20),BALANCE NUMBER,CNO NUMBER REFERENCES CUSTOMER(CNO))

insert into account values (1,’Saving’,8000,1)
insert into account values (2,’Current’,12000,2)
insert into account values (3,’Saving’,9000,3)
insert into account values (4,’Current’,11000,4)
insert into account values (5,’Saving’,14000,5)

create or replace function customerbalance (cname1 in varchar2)
return number
as
cursor d is select balance from account,customer where
customer.cno=( select cno from customer where cname=cname1) and account.cno=cutomer.cno;
d1 d %rowtype;
sendbalance number;
begin
open d;
loop
fetch d into d1;
exit when d%notfound;
sendbalance:=d1.balance;
end loop;
close d;
return sendbalance;
end customerbalance;

Calling Program
declare
cname varchar2(20):=’c’;
result number:=0;
begin
result:=customerbalance (cname);
dbms_output.put_line(‘The Acoount Balance of ‘||cname||’ is ‘|| result);
end;

2) Create or Replace a PL/SQL function to return total number of employees working on any project for more than 25 days.

CREATE TABLE EMPLOYEE(ENO NUMBER PRIMARY KEY NOT NULL ENABLE, ENAME VARCHAR2(20),CITY VARCHAR2(20), DEPTNAME VARCHAR2(20))

insert into employee values (1,’Ajay’,’Pune’,’Computer’)
insert into employee values (2,’Vijay’,’Pune’,’Computer’)
insert into employee values (3,’Sanjay’,’Pune’,’Marketing’)
insert into employee values (4,’Jay’,’Pune’,’Production’)

CREATE TABLE PROJECT(PNO NUMBER PRIMARY KEY NOT NULL,PNAME VARCHAR2(20),STATUS VARCHAR2(20))

insert into PROJECT values (1,’Shree’,’C’)
insert into PROJECT values (2,’Jai’,’P’)
insert into PROJECT values (3,’Om’,’P’)
insert into PROJECT values (4,’Sai’,’I’)

CREATE TABLE EMPLOYEE_PROJECT(ENO NUMBER REFERENCES EMPLOYEE (ENO), PNO NUMBER REFERENCES PROJECT (PNO),NUMBEROFDAY NUMBER)

insert into EMPLOYEE_PROJECT values (1,1,45)
insert into EMPLOYEE_PROJECT values (2,1,65)
insert into EMPLOYEE_PROJECT values (3,2,25)
insert into EMPLOYEE_PROJECT values (2,3,35)
insert into EMPLOYEE_PROJECT values (4,3,75)
insert into EMPLOYEE_PROJECT values (2,4,35)
insert into EMPLOYEE_PROJECT values (4,4,75)
Sol.

create or replace function empno(days1 in number)
return number
as
cursor c is select employee.eno from
employee,project,employee_project where
employee.eno=employee_project.eno and
employee_project.pno= project.pno
and employee_project.NUMBEROFDAY > days1;

c1 c%rowtype;
sendresult number:=0;
begin
open c ;
loop
fetch c into c1;
exit when c%notfound;
sendresult :=sendresult +1;
end loop;
close c;
return sendresult ;
end empno;

Calling program
declare
days number:=25;
result number;
begin
result:=empno(days );
dbms_output.put_line(result);
end;

3)PL/SQL function to return number of incomplete projects of given employee.
Sol.

CREATE TABLE EMPLOYEE(ENO NUMBER PRIMARY KEY NOT NULL ENABLE,ENAME VARCHAR2(20),CITY VARCHAR2(20),DEPTNAME VARCHAR2(20))

insert into employee values (1,’Ajay’,’Pune’,’Computer’)
insert into employee values (2,’Vijay’,’Pune’,’Computer’)
insert into employee values (3,’Sanjay’,’Pune’,’Marketing’)
insert into employee values (4,’Jay’,’Pune’,’Production’)

CREATE TABLE PROJECT(PNO NUMBER PRIMARY KEY NOT NULL,PNAME VARCHAR2(20),STATUS VARCHAR2(20))

insert into PROJECT values (1,’Shree’,’C’)
insert into PROJECT values (2,’Jai’,’P’)
insert into PROJECT values (3,’Om’,’P’)
insert into PROJECT values (4,’Sai’,’I’)

CREATE TABLE EMPLOYEE_PROJECT(ENO NUMBER REFERENCES EMPLOYEE (ENO),PNO NUMBER REFERENCES PROJECT (PNO),NUMBEROFDAY NUMBER)

insert into EMPLOYEE_PROJECT values (1,1,45)
insert into EMPLOYEE_PROJECT values (2,1,65)
insert into EMPLOYEE_PROJECT values (3,2,25)
insert into EMPLOYEE_PROJECT values (2,3,35)
insert into EMPLOYEE_PROJECT values (4,3,75)
insert into EMPLOYEE_PROJECT values (2,4,35)
insert into EMPLOYEE_PROJECT values (4,4,75)

create or replace function noofemp(eno1 in number)
return number
as
cursor c is select project.pno from
project,employee,employee_project
where employee.eno=employee_project.eno and
employee_project.pno=project.pno
and project.status=’I’ and employee.eno=eno1;
c1 c%rowtype;
sendresult number:=0;
begin
open c;
loop
fetch c into c1;
exit when c%notfound;
sendresult:=sendresult +1 ;
end loop;
close c;
return sendresult ;
end noofemp ;
calling program
declare
eno number:=4;
result number;
begin
result :=noofemp (eno );
dbms_output.put_line(result );
end;

4)PL/SQL function to find total loan amount from ‘Mumbai’ city.
Sol.

CREATE TABLE CUSTOMER (CNO NUMBER PRIMARY KEY NOT NULL, CNAME VARCHAR2(20),CITY VARCHAR2(20))

insert into customer values (1,’Rajesh’,’pune’)
insert into customer values (2,’Girish’,’pune’)
insert into customer values (3,’Nilesh’,’Mumbai’)
insert into customer values (4,’Sandip’,’Mumbai’)
insert into customer values (5,’Raj’,’Nashik’)

CREATE TABLE LOAN (LNO NUMBER PRIMARY KEY NOT NULL , LOAN_AMT NUMBER, NO_OF_YEARS NUMBER , CNO NUMBER REFERENCES CUSTOMER(CNO))

insert into Loan values (1,20000,4,1)
insert into Loan values (2,10000,2,2)
insert into Loan values (3,30000,5,3)
insert into Loan values (4,50000,7,4)

create or replace function totalloan (city1 in varchar2)
return number
as
Cursor c is select loan_amt from customer,loan where
customer.cno=loan.cno and city=city1 ;
c1 c%rowtype;
result number:=0;
begin
open c;
loop
fetch c into c1;
exit when c%notfound;
result :=result +c1.loan_amt ;
end loop;
close c;
return result ;
end totalloan ;
calling program
declare
city varchar2(20):=’Mumbai’;
result number:=0;
begin
result:=totalloan(city);
dbms_output.put_line(result);
end;

5)PL/SQL function to return total number of movies of ‘Aishwarya’.
Sol.

CREATE TABLE MOVIE (MVNO NUMBER PRIMARY KEY NOT NULL,MVNAME VARCHAR2(20),RELEASEYEAR NUMBER )

insert into Movie values (1,’Lawaris’,1990)
insert into Movie values (2,’Gajani’,2008)
insert into Movie values (3,’Don’,1988)
insert into Movie values (4,’Taal’,2002)
insert into Movie values (5,’Devdas’,2002)

CREATE TABLE ACTOR (ACTNO NUMBER PRIMARY KEY NOT NULL, ACTNAME VARCHAR2(20))

insert into Actor values (1,’Amitabh’)
insert into Actor values (2,’Amir’)
insert into Actor values (3,’Aishwarya’)
insert into Actor values (4,’Anil’)

CREATE TABLE MOVIE_ACTOR (MVNO NUMBER REFERENCES MOVIE(MVNO),ACTNO NUMBER REFERENCES ACTOR(ACTNO),RATE NUMBER)

insert into Movie_Actor values (1,1,200000)
insert into Movie_Actor values (2,2,2000000)
insert into Movie_Actor values (2,4,1000000)
insert into Movie_Actor values (3,1,300000)
insert into Movie_Actor values (4,3,1000000)
insert into Movie_Actor values (5,3,1000000)

create or replace function actor2(actname in varchar2)return number
as
cursor c2 is select mvname, actname from movie, actor
where movie .mvno=actor .mvno and actor .actname=actname;
c c2 %rowtype;
cnt number:=0;
begin
open c2;
loop
fetch c2 into c;
exit when c2%notfound;
if (c.actname=actname) then
cnt:=cnt+1;
end if;
end loop;
close c2;
return cnt;
end actor2;

declare
dname varchar2(20);
result number;
begin
dname:=’aishwarya’;
result:=actor2(dname);
dbms_output.put_line(result);
end;

6)SQL function to return total number of books purchased by a given department.
Sol.

CREATE TABLE DEPARTMENT (DNO NUMBER PRIMARY KEY NOT NULL, DNAME VARCHAR2(20));

insert into Department values(1,’Computer’)
insert into Department values(2,’Science’)
insert into Department values(3,’Arts’)
insert into Department values(4,’Commerce’)

CREATE TABLE BOOK (BNO NUMBER PRIMARY KEY NOT NULL, BNAME VARCHAR2(20), PUBNAME VARCHAR2(20),PRICE NUMBER, DNO NUMBER REFERENCES DEPARTMENT(DNO));

insert into book values(1,’C’,’BPB’,170,1)
insert into book values(2,’C++’,’BPB’,270,1)
insert into book values(3,’Mathematics’,’BPB’,140,2)
insert into book values(4,’Economics’,’Vision’,170,3)
insert into book values(5,’Accounting’,’Nirali’,240,4)

create or replace function noofbook(dname1 in varchar2)
return number
as
cursor c is select bno from department,book
where book.dno=department.dno and
department.dname=dname1;
c1 c%rowtype;
sendresult number:=0;
begin
open c;
loop
fetch c into c1;
exit when c%notfound;
sendresult:=sendresult+1;
end loop;
close c;
return sendresult;
end noofbook ;
declare
dep varchar2(20):=’arts’;
result number;
begin
result:=noofbook(dep);
dbms_output.put_line(result);
end;

7)Create or Replace PL/SQL function to count the total number of books of ‘BPB’ Publication.
Sol.

CREATE TABLE BOOK (BNO NUMBER PRIMARY KEY NOT NULL, BNAME VARCHAR2(20), PUBNAME VARCHAR2(20), PRICE NUMBER);

insert into book values(1,’C’,’BPB’,170)
insert into book values(2,’C++’,’BPB’,270)
insert into book values(3,’Mathematics’,’BPB’,140)
insert into book values(4,’Economics’,’Vision’,170)
insert into book values(5,’Accounting’,’Nirali’,240)

CREATE TABLE AUTHOR(ANO NUMBER PRIMARY KEY NOT NULL, ANAME VARCHAR2(20))

insert into author values(1,’Kanetkar’)
insert into author values(2,’Balgurusamy’)
insert into author values(3,’Wrox’)
insert into author values(4,’Fonde’)

CREATE TABLE BOOK_AUTHOR(BNO NUMBER REFERENCES BOOK(BNO),ANO NUMBER REFERENCES AUTHOR(ANO))

insert into book_author values(1,1)
insert into book_author values(2,1)
insert into book_author values(3,2)
insert into book_author values(4,3)
Create OR Replace Function countbooks
(pubname1 IN varchar2)
Return Number
Is
Cursor c is select bno from book where pubname=pubname1;
c1 c %ROWTYPE;
sendcount number:=0;
Begin
Open c;
loop
fetch c into c1;
exit when c%notfound;
sendcount :=sendcount +1;
end loop;
Close c;
return sendcount;
End countbooks;

Declare
pname varchar2(20):=’BPB’;
result number;
Begin
result:=countbooks(pname);
dbms_output.put_line(‘Number of Books of ‘||
pname||’Publication are ‘||result);
end;
8)PL/SQL function to count the total number of doctors who are visiting to ‘KEM’ hospital.
Sol.

CREATE TABLE DOCTOR(DNO NUMBER PRIMARY KEY NOT NULL,DNAME VARCHAR2(20), DCITY VARCHAR2(20))

insert into doctor values (1,’Ganesh’,’Pune’);
insert into doctor values (2,’Rajesh’,’Pune’);
insert into doctor values (3,’Sachin’,’Nashik’);
insert into doctor values (4,’Swapnil’,’Mumbai’);
insert into doctor values (5,’Nilesh’,’Karad’);
CREATE TABLE HOSPITAL(HNO NUMBER PRIMARY KEY NOT NULL,HNAME VARCHAR2(20),HCITY VARCHAR2(20))

insert into HOSPITAL values (1,’KEM’,’Pune’);
insert into HOSPITAL values (2,’Sasun’,’Pune’);
insert into HOSPITAL values (3,’Krishna’,’Karad’);
insert into HOSPITAL values (4,’Lilavati’,’Mumbai’);

CREATE TABLE DOCTOR_HOSPITAL (DNO NUMBER REFERENCES DOCTOR (DNO),HNO NUMBER REFERENCES HOSPITAL (HNO))

insert into DOCTOR_HOSPITAL values (1,1);
insert into DOCTOR_HOSPITAL values (1,2);
insert into DOCTOR_HOSPITAL values (2,1);
insert into DOCTOR_HOSPITAL values (3,3);

create or replace function visit(hname2 in varchar2)
return number
as
cursor c is select doctor.dno from
doctor,hospital,doctor_hospital
where doctor.dno=doctor_hospital.dno and
doctor_hospital.hno=hospital.hno
and hospital.hname= hname2;
c1 c%rowtype;
sendresult number:=0;
begin
open c;
loop
fetch c into c1;
exit when c%notfound;
sendresult:= sendresult+1;
end loop;
close c;
return sendresult;
end visit;

declare
hname varchar2(20):=’KEM’;
result number;
begin
result:=visit(hname );
dbms_output.put_line(result);
end;

9)function to return employee name having minimum salary inComputer Department.
Sol.

CREATE TABLE DEPARTMENT (DEPTNO NUMBER PRIMARY KEY NOT NULL, DEPTNAME VARCHAR2(20),LOCATION VARCHAR2(20))

insert into department values (1,’Computer’,’pune’)
insert into department values (2,’Marketing’,’pune’)
insert into department values (3,’Production’,’pune’)
insert into department values (4,’Quality’,’pune’)

CREATE TABLE EMPLOYEE(EMPNO NUMBER PRIMARY KEY NOT NULL, EMPNAME VARCHAR2 (20),SALARY NUMBER,COMMISSION NUMBER, DESIGNATION VARCHAR2(20),DEPTNO NUMBER REFERENCES DEPARTMENT(DNO))

insert into employee values (1,’Ajay’,10000,500,’Software Enginieer’,1)
insert into employee values (2,’Vijay’,12000,700,’Software Enginieer’,1)
insert into employee values (3,’Sanjay’,10000,500,’Supervisior’,2)
insert into employee values (4,’Jay’,15000,100,’Manager’,3)
create or replace function minsal(dname1 in varchar2)
return varchar2
as
cursor c is select employee. empname from
employee,department where employee.salary=(select min(salary) from
employee,department where employee. deptno =department.
deptno and deptname=dname1)and department. deptno
=employee. deptno and deptname=dname1;
c1 c%rowtype;
result varchar2(20);
begin
open c ;
loop
fetch c into c1;
exit when c%notfound;
result :=c1. empname;
end loop;
close c;
return result;
end;
calling program
declare
dname varchar2(20):=’Computer’;
result varchar2(20);
begin
result:=minsal(dname );
dbms_output.put_line(result);
end;
10)PL/SQL function to return student name that has scored maximum marks in ‘TY’.
Sol.

CREATE TABLE STUDENT(ROLLNO NUMBER PRIMARY KEY NOT NULL,NAME VARCHAR2(20), TOTALMARKS NUMBER,CLASS VARCHAR2(20))

insert into STUDENT values(1,’Jay’,380,’TY’)
insert into STUDENT values(2,’Ajay’,480,’TY’)
insert into STUDENT values(3,’Vijay’,390,’FY’)
insert into STUDENT values(4,’Sanjay’,420,’TY’)
insert into STUDENT values(5,’Manjit’,380,’SY’)

CREATE TABLE TEACHER(TNO NUMBER PRIMARY KEY NOT NULL,TNAME VARCHAR2(20))

insert into TEACHER values(1,’Nilesh’)
insert into TEACHER values(2,’Rajesh’)
insert into TEACHER values(3,’Sachin’)
insert into TEACHER values(4,’Ranjeet’)

CREATE TABLE STUDENT_TEACHER(ROLLNO NUMBER REFERENCES STUDENT(ROLLNO),TNO NUMBER REFERENCES TEACHER(TNO), SUBJECT VARCHAR2(20))

insert into STUDENT_TEACHER values(1,1,’Data Structure’)
insert into STUDENT_TEACHER values(2,2,’Data Structure’)
insert into STUDENT_TEACHER values(3,3,’Account’)
insert into STUDENT_TEACHER values(1,4,’C’)
insert into STUDENT_TEACHER values(4,1,’Data Structure’)
create or replace function studentrecord(class1 in varchar2)
return varchar2
as
cursor c is select name from student where totalmarks=(select max(totalmarks) from student where );

c1 c%rowtype;
sname varchar2(20);
begin
open c ;
loop
fetch c into c1;
exit when c%notfound;
sname:=c1.name;
end loop;
close c;
return sname;
end studentrecord;
calling program
declare
sub varchar2(20):=’ty’;
result varchar2(20);
begin
result:=studentrecord(sub);
dbms_output.put_line(result);
end;

11)SQL function to return total number of teachers who are teaching subject ‘Accounts’.
Sol.

create or replace function notc1(sub1 in varchar2)
return number
as
cursor c is select distinct student_teacher.tno from
student_teacher,teacher where
student_teacher.subject=sub1;
c1 c%rowtype;
total number:=0;
begin
open c ;
loop
fetch c into c1;
exit when c%notfound;
total:=total+1;
end loop;
close c;
return total;
end notc1;
Calling program
declare
sub varchar2(20):=’Account’;
result number;
begin
result:=notc1(sub);
dbms_output.put_line(result);
end;

12)PL/SQL function to return total number of employees working in ‘Computer’ Department
Sol.

CREATE TABLE DEPARTMENT (DEPTNO NUMBER PRIMARY KEY NOT NULL,DEPTNAME VARCHAR2(20),LOCATION VARCHAR2(20))

insert into department values (1,’Computer’,’pune’)
insert into department values (2,’Marketing’,’pune’)
insert into department values (3,’Production’,’pune’)
insert into department values (4,’Quality’,’pune’)

CREATE TABLE EMPLOYEE(EMPNO NUMBER PRIMARY KEY NOT NULL, EMPNAME VARCHAR2(20),SALARY NUMBER,COMMISSION NUMBER, DESIGNATION VARCHAR2(20),DEPTNO NUMBER REFERENCES DEPARTMENT(DNO))

insert into employee values (1,’Ajay’,10000,500,’Software Enginieer’,1)
insert into employee values (2,’Vijay’,12000,700,’Software Enginieer’,1)
insert into employee values (3,’Sanjay’,10000,500,’Supervisior’,2)
insert into employee values (4,’Jay’,15000,100,’Manager’,3)
create or replace function noofemp(dname1 in varchar2)
return number
as
cursor c is select empno from employee,department1 where
department1.deptno=employee.deptno and
department1.deptno=(select deptno from department1 where
deptname=dname1);
c1 c%rowtype;
sendresult number:=0;
begin
open c;
loop
fetch c into c1;
exit when c%notfound;
sendresult:=sendresult+1;
end loop;
close c;
return sendresult;
end noofemp;

Calling program
declare
dep varchar2(20):=’computer’;
result number;
begin
result:=noofemp(dep);
dbms_output.put_line(result);
end;

13)Consider the following Entities and theirRelationships
Politician (pno, pname, description)
Party (partycode, partyname)
Create or Replace a PL/SQL function to return total number of politician of a given party.
Sol.

CREATE TABLE PARTY(PARTYCODE NUMBER PRIMARY KEY NOT NULL,PARTYNAME VARCHAR2(20))

insert into Party values(1,’BJP’);
insert into Party values(2,’Shivsena’);
insert into Party values(3,’Congress’);
insert into Party values(4,’MNS’);

CREATE TABLE POLITICIAN(PARTYCODE NUMBER PRIMARY KEY,PARTY NAME VARCHAR2 (20)PNO NUMBER REFERENCE PARTY(PNO));

insert into Politician values(1,’Adwani’ ,’President’,1);
insert into Politician values(2,’Bal Thakare’ ,’Head’,2);
insert into Politician values(3,’Sonia’ ,’Chif’,3);
insert into Politician values(4,’Raj Thakare’ ,’Head’,4);
insert into Politician values(5,’Mahajan’ ,’Area Head’,1);
create or replace function countpolitician(partyname1 in varchar2)
return number
as
cursor c is select politician.pname from politician,party where
party.partyname=partyname1
and party.partycode=politician.partycode;
c1 c%rowtype;
sendcount number:=0;
begin

open c;
loop
fetch c into c1;
exit when c%notfound;
—dbms_output.put_line(c1.pname);
sendcount:=sendcount +1;
end loop;
close c;
return sendcount;
end countpolitician;
declare
pname1 varchar2(20) :=’BJP’;
result number :=0;
begin
result:=countpolitician(pname1);
dbms_output.put_line(”||result);
end;

15)Create or Replace PL/SQL function to return total number of actors acted in movie ‘Gajani’.

CREATE TABLE MOVIE (MVNO NUMBER PRIMARY KEY NOT NULL,MVNAME VARCHAR2(20),RELEASEYEAR NUMBER )

insert into Movie values (1,’Lawaris’,1990)
insert into Movie values (2,’Gajani’,2008)
insert into Movie values (3,’Don’,1988)
insert into Movie values (4,’Taal’,2002)
insert into Movie values (5,’Devdas’,2002)

CREATE TABLE ACTOR (ACTNO NUMBER PRIMARY KEY NOT NULL, ACTNAME VARCHAR2(20))

insert into Actor values (1,’Amitabh’)
insert into Actor values (2,’Amir’)
insert into Actor values (3,’Aishwarya’)
insert into Actor values (4,’Anil’)

CREATE TABLE MOVIE_ACTOR (MVNO NUMBER REFERENCES MOVIE(MVNO),ACTNO NUMBER REFERENCES ACTOR(ACTNO),RATE NUMBER)

insert into Movie_Actor values (1,1,200000)
insert into Movie_Actor values (2,2,2000000)
insert into Movie_Actor values (2,4,1000000)
insert into Movie_Actor values (3,1,300000)
insert into Movie_Actor values (4,3,1000000)
insert into Movie_Actor values (5,3,1000000)

Sol.
Create or Replace Function noofactor( mname1 in varchar2)
Return number
AS
Cursor c Is select actor.ACTNO from movie,actor,movie_actor
where movie.movieno= movie_actor. movieno and
actor.actno= movie_actor.actno and movie.moviename= mname1;
c1 c%rowtype;
sendcount number:=0;
begin
open c;
loop
fetch c into c1;
exit when c%notfound;
sendcount:=sendcount + 1;

end loop;
close c;
return sendcount ;
end noofactor;

Declare
mname Varchar2(20):=’ Gajani ‘;
result number;
Begin
result:=noofactor(mname);
dbms_output.put_line(‘Number Of Actor Acted In Movie ‘||
mname||’ Are ‘||result);
End;

 

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s