TOURNAMENT DATABASE

CREATE table Team (Team_id integer PRIMARY KEY, T_name varchar(20), City
varchar(20), Coach varchar(20), Player_Count integer, Captain varchar(20));

Team_id T_name City Coach Player_Count Captian

INSERT into Team values (71,’CSK’, ‘Chennai’, ‘Vettori’, 20, NULL);

INSERT into Team values (73, ‘MI’, ‘Mumbai’, ‘McCullum’s’, 20, NULL);
INSERT into Team values (75, ‘SRH’, ‘Hyderabad’, ‘Mott’, 20, NULL);
INSERT into Team values (77, ‘RR’, ‘Rajasthan’, ‘V Kumar’, 20, NULL);
INSERT into Team values (79, ‘RCB’, ‘Bangalore’, ‘ABD’, 20, NULL);

SELECT * from Team;

Team_id T_name City Coach Player_Count Captian
71 CSK Chennai Vettori 20 NULL
73 MI Mumbai McCullums’s 20 NULL
75 SRH Hyderabad Mott 20 NULL
77 RR Rajasthan V Kumar 20 NULL
79 RCB Bangalore ABD 20 NULL

UPDATE team set captain='1' WHERE team_id=71;

UPDATE team set captain='1' WHERE team_id=73;
UPDATE team set captain='3' WHERE team_id=75;
UPDATE team set captain='7' WHERE team_id=77;
UPDATE team set captain='9' WHERE team_id=79;

SELECT * from Team;

Team_id T_name City Coach Player_Count Captian
71 CSK Chennai Vettori 20 1
73 MI Mumbai McCullums’s 20 1
75 SRH Hyderabad Mott 20 3
77 RR Rajasthan V Kumar 20 7
79 RCB Bangalore ABD 20 9

CREATE table Player (Player_id integer PRIMARY KEY, P_name varchar(20), Age integer,
Team_id integer, FOREIGN KEY(Team_id) REFERENCES Team(Team_id));

Player_id P_name Age Team_id

INSERT into Player values (1, 'Dhoni', 40, 71);

INSERT into Player values (3, 'Sachin', 48, 73);
INSERT into Player values (5, 'Warner', 28, 75);
INSERT into Player values (7, 'Rohit', 40, 77);
INSERT into Player values (9, 'Virat', 35, 79);

Kavana Patil USN-2KE23MC019 2023-24

SELECT * from Player;
Player_id P_name Age Team_id
1 Dhoni 40 71
3 Sachin 48 73
5 Warner 28 75
7 Rohit 40 77
9 Virat 35 79

CREATE table Stadium (Stadium_id integer PRIMARY KEY, S_name varchar(20), Area
varchar(20), City varchar(20), Pincode varchar(20));

Stadium_id S_name Area City Pincode

INSERT into Stadium values (100, “Chinnaswamy”, “Raj Nagar”, “Bangalore”, 560001);
INSERT into Stadium values (200, “Wankhede”, “Church Gate”, “Mumbai”, 400020);
INSERT into Stadium values (300, “Eden Gardens”, “BBD Baagh”, “Kolkata”, 700021);
INSERT into Stadium values (400, “JN Stadium”, “Pragati Vihar”, “Delhi”, 110003);
INSERT into Stadium values (500, “MA Chi”, “Victoria Road”, “Chennai”, 600005);

SELECT * from Stadium;

Stadium_id S_name Area City Pincode

100 Chinnaswamy Raj Nagar Bangalore 560001
200 Wankhede Church Gate Mumbai 400020
300 Eden Gardens BBD Baagh Kolkata 700021
400 JN Statdium Pragati Vihar Delhi 110003
500 MA Chi Victoria Road Chennai 600005

CREATE table Matches (Match_id integer PRIMARY KEY, Team1 integer, Team2 integer,
Stadium_id integer, Played_date date, Play_time time, Team_won integer, Team_loss integer,
Man_of_the_match integer, FOREIGN KEY(Stadium_id) References Stadium(Stadium_id));

Match_id Team1 Team2 Stadium_id Played_date Play_time Team_ Team Man_of_t

won _loss he match

INSERT into Matches value (1, 71, 79, 100, '2023-06-27', '20:00:00', 71, 79, '1');
INSERT into Matches value (2, 73, 75, 300, '2023-06-29', '20:00:00', 75, 73, '1');
INSERT into Matches value (3, 77, 73, 400, '2023-07-01', '20:00:00', 73, 71, '3');
INSERT into Matches value (4, 75, 77, 500, '2023-07-10', '20:00:00', 79, 75, '5');
INSERT into Matches value (5, 71, 75, 100, '2023-07-12', '20:00:00', 71, 75, '5');

SELECT * from Matches;

Kavana Patil USN-2KE23MC019 2023-24

 Match_id Team1 Team2 Stadium_id Played_date Play_time Team_ Team Man_of_t
won _loss he match
1 71 79 100 2023-06-27 20:00:00 71 79 1
2 73 75 300 2023-06-29 20:00:00 75 73 1
3 77 73 400 2023-07-01 20:00:00 73 71 3
4 75 77 500 2023-07-10 20:00:00 79 75 5
5 71 75 100 2023-07-12 20:00:00 71 75 5

CREATE table Player_Phone ( Player_id integer, Phone_no int(10), FOREIGN

KEY(Player_id) REFRENCES Player(Player_id));

INSERT into Player_Phone values (1, 9988774455);

INSERT into Player_Phone values (3, 9874563210);
INSERT into Player_Phone values (5, 9992243330);
INSERT into Player_Phone values (7, 9994421110);
INSERT into Player_Phone values (9, 9877453210);

Player_id Phone_no

SELECT * from Player_Phone;

Player_id Phone_no
1 9988774455
3 9874563210
5 9992243330
7 9994421110
9 9877453210

Kavana Patil USN-2KE23MC019 2023-24

 QUERIES

1. Display the youngest player (in terms of age) name, team_name, age in which he
belongs of the tournament.

SELECT P.P_name, T.T_Name, P.Age from Player P, Team T WHERE P.Team_id =T.Team_i
d AND P.Player_id in (SELECT Player_id from Player WHERE Age=(SELECT min(Age) fr
om Player))

P_name T_name Age

Warner SRH 28

2. List the details of stadium where the maximum number of matches are played.

SELECT S.* FROM Stadium S JOIN ( SELECT M.Stadium_id, COUNT(*) AS id_count FR

OM Matches M GROUP BY M.Stadium_id ) AS Subquery ON S.Stadium_id = Subquery.Sta
dium_id WHERE Subquery.id_count = ( SELECT MAX(id_count) FROM ( SELECT COU
NT(Stadium_id) AS id_count FROM Matches GROUP BY Stadium_id ) AS n )

Stadium_id S_name Area City Pincode

100 Chinnaswamy Raj Nagar Bangalore 560001

3. List the details of the player who is not the captain but got Man of The Match
award atleast in two matches.

SELECT * from Player WHERE Player_id not in (SELECT Captain from team) and Player_id
in (SELECT man_of_the_match from matches GROUP By man_of_the_match HAVING CO
UNT(man_of_the_match) = 2)

Player_id P_name Age Team_id

5 Warner 28 75

Kavana Patil USN-2KE23MC019 2023-24

 4. Display the team details who won the maximum matches.

SELECT T.* FROM Team T JOIN ( SELECT M.Team_won, COUNT(*) AS id_count FROM
Matches M GROUP BY M.Team_won ) AS Subquery ON T.Team_id = Subquery.Team_won
WHERE Subquery.id_count = ( SELECT MAX(id_count) FROM ( SELECT COUNT(Team_
won) AS id_count FROM Matches GROUP BY Team_won ) AS n )

Team_id T_name City Coach Player_count Captain

71 CSK Chennai Vettori 20 1

5. Display the team’s name where all its won matches played in same stadium.

SELECT T.T_name FROM Team T WHERE NOT EXISTS ( SELECT * FROM Matches M
WHERE M.Stadium_id = (SELECT Stadium_id FROM Stadium WHERE S_name = 'Chinnas
wamy') AND T.Team_id = M.Team_Loss );

T_name
CSK
MI
SRH
RR

Kavana Patil USN-2KE23MC019 2023-24