ASSIGNMENT: 10
CREATE TABLES:
Create table Player (P_ID varchar2(5) primary key, FName varchar2(10), LName
varchar2(10), Country varchar2(10), Yborn number(4), BPlace varchar2(10), Ftest date);
Create table Match (Match_ID varchar2(5) primary key, Team1 varchar2(16), Team2
varchar2(16), Ground varchar2(10), MDate date, Winner varchar2(16));
Create table Batting (P_ID varchar2(5) references Player (P_ID), Match_ID varchar2(5)
references Match (Match_ID), MTS number(4), Bat_Order number(2), Out_Type
varchar2 (10), FOW number(4), NRuns number(4), Nballs number(4), Fours number(4),
Sixes number(4));
Create table Bowling (P_ID varchar2(5) references Player (P_ID), Match_ID varchar2(5)
references Match (Match_ID), NOvers number(3), Maidens number(3), NRuns
number(4), Nwickets number(2));
INSERT VALUES:
PLAYER
Insert into Player values ('27001', 'M.S.', 'Dhoni', 'India', 1981, 'India', To_date ('1998-05-
19', 'YYYY-MM-DD'));
Insert into Player values ('27002', 'Virat', 'Kohli', 'India', 1987, 'India', To_date ('1998-05-
19', 'YYYY-MM-DD'));
Insert into Player values ('27003', 'Smriti', 'Mandhana', 'India', 1991, 'India', To_date
('1998-05-19', 'YYYY-MM-DD'));
Insert into Player values ('27004', 'Maithili', 'Raj', 'India', 1990, 'India', To_date ('1998-05-
19', 'YYYY-MM-DD'));
Insert into Player values ('27005', 'Andre', 'Russell', 'India', 1979, 'India', To_date ('1998-
05-19', 'YYYY-MM-DD'));
MATCH
Insert into Match values ('ODI23', 'India', 'Pakistan', 'Sydney', To_date ('2024-05-19',
'YYYY-MM-DD'), 'India');
Insert into Match values ('ODI45', 'India', 'Pakistan', 'Sri Lanka', To_date ('2024-05-19',
'YYYY-MM-DD'), 'India');
Insert into Match values ('WC153', 'India', 'Pakistan', 'Sydney', To_date ('2024-05-19',
'YYYY-MM-DD'), 'India');
Insert into Match values ('IPL24', 'India', 'Pakistan', 'Sweden', To_date ('2024-05-19',
'YYYY-MM-DD'), 'India');
Insert into Match values ('WC908', 'India', 'Pakistan', 'Chicago', To_date ('2024-05-19',
'YYYY-MM-DD'), 'India');
BATTING
Insert into Batting values ('27001', 'ODI23', 400, 1, 'LBW', 120, 69, 40, 6, 4);
Insert into Batting values ('27003', 'WC153', 400, 1, 'LBW', 120, 56, 40, 6, 4);
Insert into Batting values ('27005', 'ODI23', 400, 1, 'LBW', 120, 29, 40, 6, 4);
Insert into Batting values ('27003', 'ODI23', 400, 1, 'LBW', 120, 90, 40, 6, 4);
Insert into Batting values ('27001', 'WC153', 400, 1, 'LBW', 120, 64, 40, 6, 4);
Insert into Batting values ('27005', 'ODI45', 400, 1, 'LBW', 120, 94, 40, 6, 4);
BOWLING
Insert into Bowling values ('27002', 'IPL24', 10, 3, 5, 4);
Insert into Bowling values ('27004', 'ODI23', 10, 3, 5, 4);
Insert into Bowling values ('27003', 'ODI45', 10, 3, 5, 4);
Insert into Bowling values ('27002', 'IPL24', 10, 3, 5, 4);
Insert into Bowling values ('27004', 'WC153', 10, 3, 5, 4);
QUERIES
1. Find match ids of those matches in which player 27001 bats and makes more runs
than he made at every match he played at Sydney.
SELECT b1.match_id FROM Batting b1 WHERE b1.p_id = 27001 AND [Link] >
(SELECT MAX([Link]) FROM Batting b2 JOIN Match m ON b2.match_id =
m.match_id WHERE b2.p_id = 27001 AND [Link] = 'Sydney');
2. Find player ids of players who have scored more than 30 in every ODI match that
they have batted.
SELECT DISTINCT b.p_id FROM Batting b JOIN Match m ON b.match_id =
m.match_id WHERE m.match_id like 'ODI%' AND [Link] > 30;
3. Find the ids of players that had a higher average score than the average score for
all players when they played in Sri Lanka.
WITH OverallAvg AS (SELECT AVG([Link]) AS avg_score FROM Batting b),
PlayerAvgInSriLanka AS (SELECT b.p_id, AVG([Link]) AS player_avg_score FROM
Batting b JOIN Match m ON b.match_id = m.match_id WHERE [Link] = 'Sri Lanka'
GROUP BY b.p_id) SELECT p.p_id FROM PlayerAvgInSriLanka p JOIN OverallAvg o
ON p.player_avg_score > o.avg_score;