DBMS-Slip26

Slip26
Consider the following Entities and 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.

Solution:-

Create a Database in 3NF & write queries for following.
•Display players from ‘Delhi’.
SQL>Select pname from players where address=’mumbai’;

•List all games which require more than 4 players.
SQL>select gname from game,players,gp where game.gid=gp.gno and players.pid=gp.pid and  no_of_players>4;

•Find the total number of cricket players of 'sports club”.
SQL>Select sum(no_of_players) from game,players,gp where game.gid=gp.gno and players.pid=gp.pid and gname=’cricket’ and club_name=’sports’;

•Display games having more number of players than that of football.
SQL> Select max(game.no_of_players) from game,players,gp where game.gid=gp.gno and   players.pid=gp.pid and game. no_of_players <=( select max(no_of_players) from game,players,gp where game.gid=gp.gno and players.pid=gp.pid and gname=’football’);

•Displaycoach wise player details.
SQL>select  coach_name ,pname,address  from game,players,gp where game.gid=gp.gno and players.pid=gp.pid order by coach_name asc;