Ready Project |BCA Project | MCA Project | BBA Project | MBA Project |INDIA'S BIGGEST COLLEGE PROJECT STORE

14) Consider the following entities and their relationship.
Company (c_no, c_name, c_city, c_share_value)
create table Company (c_no number primary key, c_name varchar2(20), c_city varchar2(20), c_share_value number);
create table Person (p_no number primary key, p_name varchar2(20), p_city varchar2(20), p_ph_no number);
create table CO_P(c_no number references Company(c_no),p_no number references Person(p_no),no_of_shares number);
Relationship between Company and Person is many-to-many with descriptive attribute no_of_shares.
Constraints: primary key, foreign key,
c_name and p_name should not be null,
no_of_shares should be greater than zero.
Create a RDB in 3NF and write PL/SQL blocks in Oracle for the following:
Write a function which will return name of person having maximum number of shares of given company.
Write a cursor which will display person wise share details. (Use parameterized cursor)


insert into Company values(101,'infosys','pune',19);

insert into Company values(102,'Wipro','pune',20);

insert into Company values(103,'vinsys','mumbai',11);

insert into Person values(1,'yogesh','pune',9096188754);

insert into Person values(2,'dipali','pune',9096188755);

insert into Person values(3,'amol','mumbai',9096188764);

insert into CO_P values(101,1,5000);

insert into CO_P values(101,2,4000);

insert into CO_P values(102,1,6000);

insert into CO_P values(103,3,5000);



Q1)

create or replace function f1

(cnm Company.c_name%type)

return varchar is

pnm Person.p_name%type;

begin

select p_name into pnm

from Company c,Person p,CO_P cp

where c.c_no=cp.c_no

and p.p_no=cp.p_no

and no_of_shares=(select max(no_of_shares) from CO_P)

and c_name =cnm;

return pnm;

end;

Q2)


Declare

cursor c1 is select p_name from Person;

cursor c2(pnm Person.p_name%type) is

select c_name,c_share_value,no_of_shares

from Person p,Company c,CO_P cp

where c.c_no=cp.c_no

and p.p_no=cp.p_no

and p_name=pnm;

r1 c1%rowtype;

r2 c2%rowtype;

Begin

open c1;

loop

fetch c1 into r1;

exit when c1%notfound;

dbms_output.put_line(r1.p_name);

open c2(r1.p_name);

loop

fetch c2 into r2;

exit when c2%notfound;

dbms_output.put_line(r2.c_name||r2.c_share_value||r2.no_of_shares);

end loop;

close c2;

end loop;

close c1;

end;

Search

Project Categories

Recent Posts

Sign In