DBMS Practical Slips

7.Game_Players

create table game2
(
g_name varchar(20)primary key,
no_of_players number(12)check(no_of_players>0),
co_name varchar2(20)
);

create table players3
(
pid number(12)primary key,
pname varchar2(20),
address varchar(20),
club_name varchar2(20)
);

create table game_players4
(
g_name varchar(20)references game2(g_name),
pid number(12)references players3(pid)
);

insert into game2 values('football',10,'ankit');
insert into game2 values('cricket',4,'laxman');
insert into game2 values('hockey',11,'rohit');
insert into game2 values('tenis',12,'hitesh');

insert into players3 values(101,'vivek','delhi','sports club');
insert into players3 values(102,'vikas','pune','store club');
insert into players3 values(103,'pramod','delhi','sports club');
insert into players3 values(104,'sagar','mumbai','success club');

insert into game_players4 values('football',101);
insert into game_players4 values('cricket',102);
insert into game_players4 values('hockey',103);
insert into game_players4 values('tenis',104);

1. select pname from players3
where players3.address='delhi';

ADDRESS
--------------------
delhi
delhi

1. select players3.address from players3
 where players3.address='delhi' ;

PNAME
--------------------
vivek
pramod


2. select game2.g_name from game2,players3,game_players4
where game2.g_name=game_players4.g_name AND
players3.pid=game_players4.pid AND
game2.no_of_players>=4;

G_NAME
--------------------
football
cricket
hockey
tenis

3. select SUM(game2.no_of_players) from game2,players3,game_players4
where game2.g_name=game_players4.g_name AND
players3.pid=game_players4.pid AND
players3.club_name='sports club';

SUM(GAME2.NO_OF_PLAYERS)
--------------------------------------------
                                                       21

4. select g_name from game2
where game2.no_of_players>=12;

G_NAME
--------------------
tenis


5. select players3.* from game2,players3,game_players4
where  game2.g_name=game_players4.g_name AND
players3.pid=game_players4.pid
ORDER BY game2.co_name;

       PID PNAME                ADDRESS              CLUB_NAME
---------- -------------------- -------------------- --------------------
       101 vivek                delhi                sports club
       104 sagar                mumbai               success club
       102 vikas                pune                 store club
       103 pramod               delhi                sports club