Cursor


1)Cursor to display total salary (Salary + Commission) of all employees

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)

Sol.
Declare
Cursor c1 is select empname, salary, commission from employee;
c c1%rowtype;
Begin
Open c1;
dbms_output.put_line(‘Employee Name Employee Salary’);
Loop
Fetch c1 into c;
Exit when c1%notfound;
dbms_output.put_line(c.empname || ‘ ‘ || (c.salary + c.commission));
End loop;
Close c1;
End;

2)Write a cursor to display names of all movies which are released in year 2002

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)

Declare
Cursor c is select mvno, mvname, releaseyear from movie where releaseyear =2002;
C1 c%rowtype;
Begin
Open c;
Loop
Fetch c into c1;
Exit when c%notfound;
Dbms_output.put_line(c1. mvno ||’ ‘||c1. mvname ||’ ‘ ||c1.releaseyear);
End loop;
Close c;
End;

3)Write a cursor to add interest of 3 % to the balance of all accounts whose balance is greater than 10000.
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 VARCHAR(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)

Declare
Cursor c is select ano ,balance from account where balance >10000;
C1 c%rowtype;
Begin

Open c;
Loop
Fetch c into c1;
Exit when c%notfound;
update Account set balance =(c1.balance+c1.balance*0.03) where balance >10000 ;
Dbms_output.put_line(‘row updated’);
End loop;
Close c;
End;

4)Write a cursor to list all hospitals and their doctor’s details.

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);

declare
cursor c is select hospital.hname,doctor.dname,doctor.dno,doctor.dcity from doctor,hospital,
doctor_hospital where doctor_hospital.dno=doctor.dno
and doctor_hospital.hno= hospital.hno
order by hospital.hname ;
c1 c%rowtype;
begin
open c;
loop
fetch c into c1;
exit when c%notfound;
dbms_output.put_line(c1.hname||c1.dno||c1.dname||c1.dcity );
end loop;
close c;
end ;

5)Write a Cursor to display details of all Employees of all Departments.
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)

declare
cursor c is select eno,ENAME,salary,DESIGNATION,DEPTNAME from employee,department where department.dno=Employee.dno order by eno;
c1 c%rowtype;
updatedsalary number:=0;
begin
open c;
loop
fetch c into c1;
exit when c%notfound;
dbms_output.put_line(c1.eno||’ ‘||c1.ENAME||’ ‘||c1.salary||’ ‘||c1.DESIGNATION||’ ‘||c1.DEPTNAME );
end loop;
close c;
end;

6)Write a cursor to display details of all customers who have taken loan for more than 3 years.

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)

Declare
Cursor c is select customer.cno, customer.cname,customer.city from
customer,loan where customer.cno=loan.cno and loan.NO_OF_YEARS>3;
c1 c%rowtype;
begin
open c;
loop
fetch c into c1;
exit when c%notfound;
dbms_output.put_line(c1.cno||’ ‘|| c1.cname||’ ‘||c1.city );
end loop;
close c;
end;

7)Write a cursor to display list of authors and their books details.
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)

declare
cursor c is select author.aname,book.bno,book.bname,book.pubname,book.price from book,author,author_book where
book.bno=author_book.bno and author.ano=author_book.ano ;
c1 c%rowtype;
begin
dbms_output.put_line(‘The List of Author With Their Book Details Are As Follows:-‘);
dbms_output.put_line(‘Author Name Book NO. Book Name Publication Name Book Price’);
open c;
loop
fetch c into c1;
exit when c%notfound;
dbms_output.put_line(c1.aname||’ ‘||c1.bno||’ ‘||c1.bname||’ ‘||c1.pubname||’ ‘||c1.price);
end loop;
close c;
end;

8)Write a cursor to list all doctors and hospitals visited by them.
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);

declare
cursor c is select doctor.dname,hospital.hname from doctor,hospital,doctor_hospital
where doctor.dno=doctor_hospital.dno and doctor_hospital.hno=hospital.hno group by doctor.dname,hospital.hname ;
c1 c%rowtype;
begin
open c;
loop
fetch c into c1;
exit when c%notfound;
dbms_output.put_line(c1.dname||c1.hname);
end loop;
close c;
end;

9)Write a cursor to display the list of actors and their movies for which their rate is greater than 20 Lakhs.
Sol.

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

insert into Movie values (1,’Race’,2008)
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,’Saif’)
insert into Actor values (2,’Amir’)
insert into Actor values (3,’Akshay’)
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,4000000)
insert into Movie_Actor values (2,4,1000000)
insert into Movie_Actor values (1,3,300000)
insert into Movie_Actor values (1,4,3000000)

declare
cursor c is select actor.actno,actname,rate,mvname from actor,movie,movie_actor
where actor.actno=movie_actor.actno and movie.mvno=movie_actor.mvno
and rate>2000000;
c1 c%rowtype;
begin
open c;
loop
fetch c into c1;
exit when c%notfound;
dbms_output.put_line(c1.actno||’ ‘||c1.actname||’ ‘||c1.rate||’ ‘||c1.mvname );
end loop;
close c;
end;

10)Write a cursor to list all customers & their account details.
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)

declare
cursor c is select ano,cname,balance from account,customer where account.cno=customer.cno ;
c1 c%rowtype;

begin
dbms_output.put_line(‘Ano ‘ || ‘ Cname ‘||’ Balance ‘);
open c;
loop
fetch c into c1;
exit when c%notfound;
dbms_output.put_line(c1.ano|| ‘ ‘|| c1.cname || ‘ ‘||c1.balance);
end loop;
close c;
end;

11)Cursor to display details of all projects along with status and number_of_days on which a given employee works
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)
declare
cursor c is select employee.eno,employee.ename,project.pno,project.pname,project.status,employee_project.numberofday
from employee,project,employee_project where
employee.eno= employee_project.eno and project.pno=employee_project.pno ;
c1 c%rowtype;
begin
open c;
loop
fetch c into c1;
exit when c%notfound;
dbms_output.put_line(c1.eno||c1.ename||c1.pno||c1.pname||c1.status||c1.numberofday );
end loop;
close c;
end;

12)a cursor to display details of all politicians of ‘BJP’ 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’);

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);

Sol.
declare
cursor c is select pno, pname, description from politician,party where party.partyname=’BJP’
and party.partycode=politician.partycode;
c1 c%rowtype;
begin
open c;
loop
fetch c into c1;
exit when c%notfound;
dbms_output.put_line(c1.pno||’ ’||c1. pname||’ ’||c1. description);
end loop;
close c;
end;

13)Write a cursor to display department wise expenditure on books.

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)

Sol.
declare
cursor c is select department.dname,sum(book.price)as price1 from department,book
where book.dno=department.dno group by department.dname;
c1 c%rowtype;
begin
open c;
loop
fetch c into c1;
exit when c%notfound;
dbms_output.put_line(c1.dname || ‘ ‘||c1.price1);

end loop;
close c;
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