26) Consider the following entities and their relationship.
Researcher (r_no, r_name, r_city)
Research_Paper (rp_no, rp_title, rp_subject, rp_level)
Relationship between Researcher and Research_Paper is many-to-many with descriptive attribute year.
Constraints: primary key, foreign key,
r_name and rp_title should not be null,
rp_subject can be computer or electronics or finance.
rp_level can be state, national or international.
Create a RDB in 3NF and write PL/SQL blocks in Oracle for the following:
1)Write a function which will return name of subject for which maximum numbers of national level papers were presented by researcher in year 2013.
2)Write a cursor which will display rp_level wise and researcher wise details of research paper presented by them.
Q1)
create or replace function f1
return varchar is
subj varchar2(20);
Begin
select rp_subject into subj
from Research_Paper rp,R_RP rrp
where rp.rp_no=rrp.rp_no
and year=2013
and rp_level='national'
group by rp_subject having count(rp.rp_no)>=all(select count(rp.rp_no) from Research_Paper rp,R_RP rrp
where rp.rp_no=rrp.rp_no
and rp_level='national'
and year=2013);
return subj;
end;
Q2)
declare
cursor c1 is select distinct rp_level from Research_Paper;
cursor c2 is select r_name from Researcher;
cursor c3 (l Research_Paper.rp_level%type,rname Researcher.r_name%type) is select r.* from Researcher r1,Research_Paper r,R_RP rrp
where r.rp_no=rrp.rp_no and r1.r_no=rrp.r_no and r.rp_level=l and r1.r_name=rname ;
begin
for a in c1 loop
dbms_output.put_line(a.rp_level);
for b in c2 loop
dbms_output.put_line(b.r_name);
for c in c3(a.rp_level,b.r_name) loop
dbms_output.put_line(c.rp_no||' '||c.rp_title||' '||c.rp_subject||' '||c.rp_level);
end loop;
end loop;
end loop;
end;
Search
Recent Posts
Mail Management System
Posted on 2019-07-18Online food ordering system
Posted on 2019-07-18Library Management System
Posted on 2019-07-17Health center system project
Posted on 2019-07-17Gym Management System
Posted on 2019-07-17furniture management system
Posted on 2019-07-17Electronic shop management system
Posted on 2019-07-17Automobile Workshop Management
Posted on 2019-07-17Online Visa Processing System
Posted on 2019-07-17Inventory management System
Posted on 2019-07-17petrol-management system
Posted on 2019-07-17Cloths management system
Posted on 2019-07-17Society Management system
Posted on 2019-07-17Mall management system
Posted on 2019-07-17school management system
Posted on 2019-07-17Sales Order Processing System
Posted on 2019-07-17Retail sales management
Posted on 2019-07-17Raw Materials Management
Posted on 2019-07-17railway reservation system
Posted on 2019-07-17purchase and sales management system
Posted on 2019-07-17Placement Management System
Posted on 2019-07-17Pet Shop Management System
Posted on 2019-07-17petrol pump management system
Posted on 2019-07-17Patient Information System
Posted on 2019-07-17news agency system
Posted on 2019-07-17Cinema Booking System
Posted on 2019-07-17Medical Store System
Posted on 2019-07-17leave management System
Posted on 2019-07-17Laboratory Information Management System
Posted on 2019-07-17content management system
Posted on 2019-07-17Inventory management System
Posted on 2019-07-17Institute Management System
Posted on 2019-07-17Hotel management System
Posted on 2019-07-17Gym Management System
Posted on 2019-07-17Garage Management System
Posted on 2019-07-17Furniture shop management system
Posted on 2019-07-17Fisheries management System
Posted on 2019-07-17Fertilizer scheduling system
Posted on 2019-07-17online eye care system
Posted on 2019-07-17Dental Clinic Management System
Posted on 2019-07-17Cyber Café Management
Posted on 2019-07-17Milk Billing System
Posted on 2019-07-17Colddrink management system
Posted on 2019-07-17Cable management System
Posted on 2019-07-17Beauty parlor management system
Posted on 2019-07-17Facebook Clone
Posted on 2019-05-28Dance Class Management System
Posted on 2019-05-24Library Management System
Posted on 2019-05-24Cab Management System
Posted on 2019-05-23Blood Bank Management system
Posted on 2019-05-23Beauty Parlour Management System
Posted on 2019-05-23vissa proccesing system
Posted on 2019-05-23Toll Plazza
Posted on 2019-05-23BILLING APPLICATION
Posted on 2019-05-23FLORICULTURE MANAGEMENT SYSTEM
Posted on 2019-05-23Car On Rent
Posted on 2019-05-23E-commers Shop
Posted on 2019-05-23