Slip 19 - C) 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: 1) Write a function which will return total number of football players of “Sports Club”. 2) Write a cursor which will display club wise details of players.

Solution:

> 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  /