Slip19
Consider the following entities and their relationships.
Game(game_name, no_of_players, coach_name)
Player (pid, pname, address, club_name)
Relation between Game and Player is Many to Many.
Constraint: Primary key, no_of_players should be > 0.
Create a RDB in 3NF and write PL/SQL blocks in Oracle for the following:
> create table game(gno int primary key,gname varchar(15),nplayer
varchar(15),cname varchar(15));
> insert into game values ('1','cricket','12','patil');
> create table play(pid int primary key,pname varchar(20),address
varchar(20),c1name varchar(20));
> insert into play values('5','swarup','pune','city');
> create table gp(gno int primary key,pid int);
> insert into gp values('11','12');
1)Write a function which
will return total number of football players of “Sports Club”.
SQL> set serveroutput on
SQL> create or replace function
f1(abc varchar) return number as pqr number;
2 begin
3 select count(game.nplayer) into pqr from
game,play,gp where game.gno=gp.gno and play.pid=gp.pid and gname=’football’ and
c1name=’sports’ ;
4 if sql %found then
5 return(pqr);
6 else
7 return null;
8 end if;
9 end f1;
10 /
Function created.
SQL> begin
2
dbms_output.put_line('play-'||f1 ('football'));
3
end;
4
/
2)Write a cursor which
will display club wise details of players.
SQL> set
serveroutput on
SQL> declare
2
cursor c1 is select gname, pname from game, play,gp game.gno=gp.gno and
play.pid=gp.pid order by c1name;
3 c
c1%rowtype;
4 begin
5 open c1;
6 loop
7 fetch c1 into c;
8 exit when c1%notfound;
9 dbms_output.put_line(c. gname ||''||c.pname);
10
end loop;
11
close c1;
12
end;
13 /
