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

2) Consider the following entities and their relationship.
Student (s_reg_no, s_name, s_class)
Competition (comp_no, comp_name, comp_type)
Relationship between Student and Competition is many-to-many with descriptive attribute rank and year.
Constraints: primary key, foreign key,
primary key for third table(s_reg_no, comp_no, year),
s_name and comp_name should not be null,
comp_type can be sports or academic.
Create a RDB in 3NF and write PL/SQL blocks in Oracle for the following:
Write a function which will accept s_reg_no of student and returns total number of competition in which student has participated in a given year.
Write a cursor which will display year wise details of competitions held. (Use parameterized cursor)


 create table Student(s_reg_no number primary key, s_name varchar2(20) not null, s_class varchar2(20));


create table Competition (comp_no number primary key, comp_name varchar2(20), comp_type varchar2(20));
create table stud_comp(s_reg_no number references Student(s_reg_no),comp_no number references Competition(comp_no),rank number,year number,primary key(s_reg_no,comp_no,year));


insert into Student values(1,'raj','f.y.bca');

insert into Student values(2,'rajesh','s.y.bca');

insert into Student values(3,'amol','t.y.bca');


insert into Competition values(101,'cricket','sports');

insert into Competition values(102,'tennis','sports');

insert into Competition values(103,'IPL','academic');

insert into Competition values(104,'football','academic');


insert into stud_comp values(1,101,1,2016);

insert into stud_comp values(1,102,1,2016);

insert into stud_comp values(1,103,1,2016);

insert into stud_comp values(1,104,1,2016);

insert into stud_comp values(2,101,2,2016);

insert into stud_comp values(3,101,2,2016);

Q1)


create or replace function fun1(sregno in number, yr in number)

return number

is 

total number;

begin

select count(c.comp_no) into total from Student s, Competition c, stud_comp sc 

where s.s_reg_no=sc.s_reg_no and c.comp_no=sc.comp_no and s.s_reg_no=sregno

and year=yr;

return total;

end;

SELECT fun1(1,2016) from dual;

Q2)


declare 

     cursor c1(name Competition.comp_type%type)

     is select comp_name,comp_type,year

     from

    Competition c,stud_comp sc

    where c.comp_no=sc.comp_no

    order by year;

    res c1%rowtype;

    v_name Competition.comp_type%type;

   begin

   for res in c1(&v_name)

   loop

   dbms_output.put_line(res.comp_name||' '||res.comp_type||' '||res.year);

   end loop;

   end;

Search

Project Categories

Recent Posts

Sign In