Program 3 - Table Creation, Insertion &
Queries (Oracle-Compatible)
1. Table Creation
CREATE TABLE Team (
TeamID INT PRIMARY KEY,
Team_Name VARCHAR(50),
City VARCHAR(50),
Coach VARCHAR(50),
CaptainID INT
);
CREATE TABLE Player (
PlayerID INT PRIMARY KEY,
Name VARCHAR(50),
Age INT,
TeamID INT,
FOREIGN KEY (TeamID) REFERENCES Team(TeamID)
);
CREATE TABLE PhoneNumbers (
PlayerID INT,
PhoneNumber VARCHAR(15),
PRIMARY KEY (PlayerID, PhoneNumber),
FOREIGN KEY (PlayerID) REFERENCES Player(PlayerID)
);
CREATE TABLE Stadium (
StadiumID INT PRIMARY KEY,
Stadium_Name VARCHAR(50),
City VARCHAR(50),
Area_Name VARCHAR(50),
Pincode VARCHAR(10)
);
CREATE TABLE Match (
MatchID INT PRIMARY KEY,
Team1ID INT,
Team2ID INT,
MatchDateTime DATE,
StadiumID INT,
WinnerTeamID INT,
ManOfMatchID INT,
FOREIGN KEY (Team1ID) REFERENCES Team(TeamID),
FOREIGN KEY (Team2ID) REFERENCES Team(TeamID),
FOREIGN KEY (StadiumID) REFERENCES Stadium(StadiumID),
FOREIGN KEY (WinnerTeamID) REFERENCES Team(TeamID),
FOREIGN KEY (ManOfMatchID) REFERENCES Player(PlayerID)
);
2. Insert Sample Data
Team Table Inserts
INSERT INTO Team VALUES (1, 'Warriors', 'Bangalore', 'John Smith', 101);
INSERT INTO Team VALUES (2, 'Titans', 'Mumbai', 'Steve Adams', 201);
INSERT INTO Team VALUES (3, 'Strikers', 'Delhi', 'Rahul Kumar', 301);
Player Table Inserts
-- Team 1 Players
INSERT INTO Player VALUES (101, 'Rohit Sharma', 30, 1);
INSERT INTO Player VALUES (102, 'Virat Kohli', 29, 1);
INSERT INTO Player VALUES (103, 'KL Rahul', 25, 1);
-- Team 2 Players
INSERT INTO Player VALUES (201, 'Hardik Pandya', 31, 2);
INSERT INTO Player VALUES (202, 'Jasprit Bumrah', 28, 2);
INSERT INTO Player VALUES (203, 'Ishan Kishan', 24, 2);
-- Team 3 Players
INSERT INTO Player VALUES (301, 'Shikhar Dhawan', 34, 3);
INSERT INTO Player VALUES (302, 'Rishabh Pant', 26, 3);
INSERT INTO Player VALUES (303, 'Prithvi Shaw', 23, 3);
PhoneNumbers Table Inserts
INSERT INTO PhoneNumbers VALUES (101, '9876543210');
INSERT INTO PhoneNumbers VALUES (101, '9123456780');
INSERT INTO PhoneNumbers VALUES (102, '9988776655');
INSERT INTO PhoneNumbers VALUES (103, '9012345678');
INSERT INTO PhoneNumbers VALUES (201, '8888888888');
INSERT INTO PhoneNumbers VALUES (202, '7777777777');
INSERT INTO PhoneNumbers VALUES (203, '6666666666');
INSERT INTO PhoneNumbers VALUES (301, '9999999999');
INSERT INTO PhoneNumbers VALUES (302, '9898989898');
INSERT INTO PhoneNumbers VALUES (303, '9797979797');
Stadium Table Inserts
INSERT INTO Stadium VALUES (1, 'Chinnaswamy Stadium', 'Bangalore',
'Cubbon Park', '560001');
INSERT INTO Stadium VALUES (2, 'Wankhede Stadium', 'Mumbai',
'Churchgate', '400020');
INSERT INTO Stadium VALUES (3, 'Feroz Shah Kotla', 'Delhi', 'Bahadur
Shah Zafar Marg', '110002');
Match Table Inserts
INSERT INTO Match VALUES (1, 1, 2, TO_DATE('2025-07-01 15:00:00', 'YYYY-
MM-DD HH24:MI:SS'), 1, 1, 102);
INSERT INTO Match VALUES (2, 2, 3, TO_DATE('2025-07-03 18:00:00', 'YYYY-
MM-DD HH24:MI:SS'), 2, 2, 202);
INSERT INTO Match VALUES (3, 1, 3, TO_DATE('2025-07-05 15:00:00', 'YYYY-
MM-DD HH24:MI:SS'), 3, 1, 103);
INSERT INTO Match VALUES (4, 1, 2, TO_DATE('2025-07-07 15:00:00', 'YYYY-
MM-DD HH24:MI:SS'), 1, 1, 103);
INSERT INTO Match VALUES (5, 2, 3, TO_DATE('2025-07-09 18:00:00', 'YYYY-
MM-DD HH24:MI:SS'), 1, 2, 202);
3. SQL Queries (with Questions)
1. i. Display the youngest player (in terms of age) Name, Team name, age in which he
belongs of the tournament.
SELECT P.Name AS Player_Name, T.Team_Name, P.Age
FROM Player P
JOIN Team T ON P.TeamID = T.TeamID
WHERE P.Age = (SELECT MIN(Age) FROM Player);
2. ii. List the details of the stadium where the maximum number of matches were played.
SELECT S.*
FROM Stadium S
JOIN (
SELECT StadiumID
FROM Match
GROUP BY StadiumID
HAVING COUNT(*) = (
SELECT MAX(MatchCount)
FROM (
SELECT StadiumID, COUNT(*) AS MatchCount
FROM Match
GROUP BY StadiumID
)
)
) M ON S.StadiumID = M.StadiumID;
3. iii. List the details of the player who is not a captain but got the man_of_match award at
least in two matches.
SELECT P.PlayerID, P.Name, COUNT(*) AS Awards
FROM Player P
JOIN Match M ON P.PlayerID = M.ManOfMatchID
WHERE P.PlayerID NOT IN (
SELECT CaptainID FROM Team WHERE CaptainID IS NOT NULL
)
GROUP BY P.PlayerID, P.Name
HAVING COUNT(*) >= 2;
4. iv. Display the Team details who won the maximum matches.
SELECT T.*
FROM Team T
JOIN (
SELECT WinnerTeamID
FROM Match
GROUP BY WinnerTeamID
HAVING COUNT(*) = (
SELECT MAX(Wins)
FROM (
SELECT WinnerTeamID, COUNT(*) AS Wins
FROM Match
GROUP BY WinnerTeamID
)
)
) W ON T.TeamID = W.WinnerTeamID;
5. v. Display the team name where all its won matches played in the same stadium.
SELECT T.TeamID, T.Team_Name
FROM Team T
JOIN Match M ON T.TeamID = M.WinnerTeamID
GROUP BY T.TeamID, T.Team_Name
HAVING COUNT(DISTINCT M.StadiumID) = 1;
