Experiment 2
Create database for ODI cricket. Refer Schema given below.
1. Create databse & tables
2. Display description of each table
3. Insert appropriate data in each table
4. Write SQL quires to extract desired information from database
Q1a. Print ODIMatch table.
Q1b. Print Player table.
Q1c. Print Bowling table.
Q1d. Print Batting table.
Q2. Find the Ids, First name, Last name as Surname of all cricket
players.
Q3a. Find all the information from table Player about players from India.
Q3b. Find the player Ids, First name & birth place of Indian cricket
players.
Q4. Find all the information from table Player about players from India
who were born after 1980
Q5. Find the details of all matches played by Pakistan
Q6a. Find the details of all matches played by India as a Second team.
Q6b. Find the details of all matches played by India or Australia as
Team1.
Q6c. Find the match details Team2, Winner & ground in which India won the
toss
(assume Team1 is toss winner)
Q7. Find Ids of all players that have bowled in an ODI match in the
databse
Q8. Find names of teams and grounds where India has played an ODI match
outside India.
(assume Team1 is the host country)
Q9. Find Ids of all players that batted in match 2755
Q10a. Display list of ground names where Australia has played as Team1
Q10b. Display sorted list of ground names where Australia has played as
Team1
Q11. Find name of all players whose last name starts with Sin
Q12. Find Ids, First name of all players who played in match 2755.
Q13. Find match Ids of all matches in which Tendulkar batted.
Q14. Find match information of all matches in which dhoni has batted.
CREATE DATABASE stud1_match;
USE stud1_match;
create table ODIMatch (
MatchID int unsigned,
Team1 varchar(15),
Team2 varchar(15),
Ground varchar(20),
Date date,
Winner varchar(10),
PRIMARY KEY (MatchID));
insert into ODIMatch (MatchID,Team1,Team2,Ground,Date,Winner) values (2324,'Pakistan','India','Peshwar','2006-2-6','Team1');
insert into ODIMatch (MatchID,Team1,Team2,Ground,Date,Winner) values (2327,'Pakistan','India','Rawalpindi','2006-2-11','Team2');
insert into ODIMatch (MatchID,Team1,Team2,Ground,Date,Winner) values (2357,'India','England','Delhi','2006-3-28','Team1');
insert into ODIMatch (MatchID,Team1,Team2,Ground,Date,Winner) values (2377,'West Indies','India','Kingston','2006-5-18','Team2');
insert into ODIMatch (MatchID,Team1,Team2,Ground,Date,Winner) values (2404,'Sri Lanka','India','Colombo','2006-8-16','Abandoned');
insert into ODIMatch (MatchID,Team1,Team2,Ground,Date,Winner) values (2440,'India','Australia','Mohali','2006-10-29','Team2');
insert into ODIMatch values (2449,'South Africa','India','Cape Town','2006-11-26','Team1');
insert into ODIMatch values (2480,'India','West Indies','Nagpur','2007-1-21','Team1');
insert into ODIMatch values (2493,'India','west Indies','Vadodara','2007-1-31','Team1');
insert into ODIMatch values (2520,'India','Sri Lanka','Rajkot','2007-2-11','Team2');
insert into ODIMatch values (2611,'England','India','Southampton','2007-8-21','Team1');
insert into ODIMatch values (2632,'India','Australia','Mumbai','2007-10-17','Team1');
insert into ODIMatch values (2643,'India','Pakistan','Guwahati','2007-11-5','Team1'),
(2675,'Australia','India','Melbourne','2008-2-10','Team2'),
(2681,'India','Sri Lanka','Adelaide','2008-2-19','Team1'),
(2688,'Australia','India','Sydney','2008-3-2','Team2'),
(2689,'Australia','India','Brisbane','2008-3-4','Team2'),
(2717,'Pakistan','India','Karachi','2008-6-26','Team2'),
(2750,'Sri Lanka','India','Colombo','2008-8-24','Team2'),
(2755,'Sri Lanka','India','Colombo','2008-8-24','Team2');
create table Player (
PlayerID int unsigned,
LName varchar(15),
FName varchar(15),
Country varchar(25),
Yborn int,
Bplace varchar(30),
FTest int,
PRIMARY KEY (PlayerID));
insert into Player (PlayerID,Lname,Fname,Country,Yborn,Bplace,Ftest) values (89001,'Tendulkar','Sachin','India',1973,'Mumbai',1989);
insert into Player (PlayerID,Lname,Fname,Country,Yborn,Bplace,Ftest) values (90001,'Lara','Brain','West Indies',1969,'Santa Cruz',1990);
insert into Player (PlayerID,Lname,Fname,Country,Yborn,Bplace,Ftest) values (95001,'Ponting','Ricky','Australia',1974,'Launceston',1995);
insert into Player (PlayerID,Lname,Fname,Country,Yborn,Bplace,Ftest) values (96001,'Dravid','Rahul','India',1973,'Indore',1996);
insert into Player values (96002,'Gibbs','Herschelle','South Africa',1974,'Cape Town',1996),
(92001,'Warne','Shane','Australia',1969,'Melbourne',1992),
(95002,'Pollock','Shaun','South Africa',1973,'Port Elizabeth',1995),
(99003,'Vaughan','Michale','England',1974,'Manchester',1999),
(92003,'UI-Huq','Inzamam','Pakistan',1970,'Multan',1992),
(94004,'Fleming','Stephen','New Zealand',1973,'Christchurch',1994),
(93002,'Streak','Heath','Zimbabwe',1974,'Bulawayo',1993),
(90002,'Kumble','Anil','India',1970,'Bangalore',1990),
(93003,'Kirsten','Gary','South Africa',1967,'Cape Town',1993),
(95003,'Kallis','Jacques','South Africa',1975,'Cape Town',1995),
(94002,'Vaas','Chaminda','Sri Lanka',1974,'Mattumagala',1994),
(92002,'Murlitharan','Muthiah','Sri Lanka',1972,'Kandy',1992),
(97004,'Vettori','Daniel','New Zealand',1979,'Auckland',1997),
(25001,'Dhoni','MS','India',1981,'Ranchi',2005),
(23001,'Singh','Yuvraj','India',1981,'Chandigarh',2003),
(96003,'Ganguly','Saurav','India',1972,'Calcutta',1996),
(99002,'Gilchrist','Adam','Australia',1971,'Bellingen',1999),
(24001,'Symonds','Andrew','Australia',1975,'Birmingham',2004),
(99001,'Lee','Breet','Australia',1976,'Wollongong',1999),
(91001,'Jayasuriya','Sanath','Sri Lanka',1969,'Matara',1991),
(21001,'Sehwag','Virender','India',1978,'Delhi',2001),
(98001,'Afridi','Shahid','Pakistan',1980,'Khyber Agency',1998),
(98002,'Singh','Harbhajan','India',1980,'Jalandhar',1998),
(27002,'Sharma','Ishant','India',1988,'Delhi',2007);
insert into Player (PlayerID,Lname,Fname,Country,Yborn,Bplace) values (27001,'Kumar','Praveen','India',1986,'Meerut');
Note- for Pravin Kumar Ftest is deliberately not inserted
CREATE TABLE Batting (
MatchID INT UNSIGNED,
PID INT UNSIGNED,
POrder TINYINT UNSIGNED,
HOut VARCHAR(5),
FOW VARCHAR(5),
NRuns SMALLINT UNSIGNED,
Mts SMALLINT UNSIGNED,
Nballs TINYINT UNSIGNED,
Fours TINYINT UNSIGNED,
Six TINYINT UNSIGNED,
FOREIGN KEY (MatchID) REFERENCES ODIMatch(MatchID),
FOREIGN KEY (PID) REFERENCES Player(PlayerID));
INSERT INTO Batting (MatchID,PID,POrder,Hout,FOW,Nruns,Mts,Nballs,Fours,Six) VALUES (2755,23001,3,'C',51,0,12,6,0,0);
INSERT INTO Batting (MatchID,PID,POrder,Hout,FOW,Nruns,Mts,Nballs,Fours,Six) VALUES (2755,25001,5,'C',232,71,104,80,4,0);
INSERT INTO Batting (MatchID,PID,POrder,Hout,FOW,Nruns,Mts,Nballs,Fours,Six) VALUES (2755,91001,1,'C',74,60,85,52,8,2);
INSERT INTO Batting VALUES (2755,94002,7,'LBW',157,17,23,29,1,0),
(2755,92002,11,'NO','NO',1,11,7,0,0),
(2689,89001,2,'C',205,91,176,121,7,0),
(2689,23001,4,'C',175,38,27,38,2,2),
(2689,25001,5,'C',240,36,52,37,2,1),
(2689,99002,1,'C',2,2,3,3,0,0),
(2689,95001,3,'C',8,1,9,7,0,0),
(2689,24001,5,'C',123,42,81,56,2,1),
(2689,99001,8,'B',228,7,20,12,0,0),
(2689,27001,9,'C',255,7,7,7,1,0),
(2755,27001,9,'B',257,2,7,6,0,0),
(2689,98002,8,'LBW',249,3,7,3,0,0),
(2755,98002,8,'RO',253,2,7,4,0,0);
CREATE TABLE Bowling (
MatchID INT UNSIGNED,
PID INT UNSIGNED,
NOvers TINYINT UNSIGNED,
Maidens TINYINT UNSIGNED,
Nruns SMALLINT UNSIGNED ,
Nwickets TINYINT UNSIGNED,
FOREIGN KEY (MatchID) REFERENCES ODIMatch(MatchID),
FOREIGN KEY (PID) REFERENCES Player(PlayerID));
INSERT INTO Bowling (MatchID,PID,Novers,Maidens,Nruns,Nwickets) VALUES (2689,99001,10,0,58,1);
INSERT INTO Bowling (MatchID,PID,Novers,Maidens,Nruns,Nwickets) VALUES (2689,24001,3,0,27,1);
INSERT INTO Bowling VALUES (2689,23001,3,0,15,0),
(2755,94002,9,1,40,1),
(2755,92002,10,0,56,1),
(2755,91001,4,0,29,0),
(2755,23001,10,0,53,2),
(2755,98002,10,0,40,3),
(2689,98002,10,0,44,1);
mysql> describe ODIMatch;
+---------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------+--------------+------+-----+---------+-------+
| MatchID | int unsigned | NO | PRI | NULL | |
| Team1 | varchar(15) | YES | | NULL | |
| Team2 | varchar(15) | YES | | NULL | |
| Ground | varchar(20) | YES | | NULL | |
| Date | date | YES | | NULL | |
| Winner | varchar(10) | YES | | NULL | |
+---------+--------------+------+-----+---------+-------+
6 rows in set (0.01 sec)
mysql> describe Player;
+----------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+--------------+------+-----+---------+-------+
| PlayerID | int unsigned | NO | PRI | NULL | |
| LName | varchar(15) | YES | | NULL | |
| FName | varchar(15) | YES | | NULL | |
| Country | varchar(25) | YES | | NULL | |
| Yborn | int | YES | | NULL | |
| Bplace | varchar(30) | YES | | NULL | |
| FTest | int | YES | | NULL | |
+----------+--------------+------+-----+---------+-------+
7 rows in set (0.00 sec)
mysql> describe Bowling;
+----------+-------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+-------------------+------+-----+---------+-------+
| MatchID | int unsigned | YES | MUL | NULL | |
| PID | int unsigned | YES | MUL | NULL | |
| NOvers | tinyint unsigned | YES | | NULL | |
| Maidens | tinyint unsigned | YES | | NULL | |
| Nruns | smallint unsigned | YES | | NULL | |
| Nwickets | tinyint unsigned | YES | | NULL | |
+----------+-------------------+------+-----+---------+-------+
6 rows in set (0.00 sec)
mysql> describe Batting;
+---------+-------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------+-------------------+------+-----+---------+-------+
| MatchID | int unsigned | YES | MUL | NULL | |
| PID | int unsigned | YES | MUL | NULL | |
| POrder | tinyint unsigned | YES | | NULL | |
| HOut | varchar(5) | YES | | NULL | |
| FOW | varchar(5) | YES | | NULL | |
| NRuns | smallint unsigned | YES | | NULL | |
| Mts | smallint unsigned | YES | | NULL | |
| Nballs | tinyint unsigned | YES | | NULL | |
| Fours | tinyint unsigned | YES | | NULL | |
| Six | tinyint unsigned | YES | | NULL | |
+---------+-------------------+------+-----+---------+-------+
10 rows in set (0.01 sec)
Q1a. Print ODIMatch table.
mysql> select * from ODIMatch;
or
mysql> SELECT * FROM ODIMatch;
+---------+--------------+-------------+-------------+------------+-----------+
| MatchID | Team1 | Team2 | Ground | Date | Winner |
+---------+--------------+-------------+-------------+------------+-----------+
| 2324 | Pakistan | India | Peshwar | 2006-02-06 | Team1 |
| 2327 | Pakistan | India | Rawalpindi | 2006-02-11 | Team2 |
| 2357 | India | England | Delhi | 2006-03-28 | Team1 |
| 2377 | West Indies | India | Kingston | 2006-05-18 | Team2 |
| 2404 | Sri Lanka | India | Colombo | 2006-08-16 | Abandoned |
| 2440 | India | Australia | Mohali | 2006-10-29 | Team2 |
| 2449 | South Africa | India | Cape Town | 2006-11-26 | Team1 |
| 2480 | India | West Indies | Nagpur | 2007-01-21 | Team1 |
| 2493 | India | west Indies | Vadodara | 2007-01-31 | Team1 |
| 2520 | India | Sri Lanka | Rajkot | 2007-02-11 | Team2 |
| 2611 | England | India | Southampton | 2007-08-21 | Team1 |
| 2632 | India | Australia | Mumbai | 2007-10-17 | Team1 |
| 2643 | India | Pakistan | Guwahati | 2007-11-05 | Team1 |
| 2675 | Australia | India | Melbourne | 2008-02-10 | Team2 |
| 2681 | India | Sri Lanka | Adelaide | 2008-02-19 | Team1 |
| 2688 | Australia | India | Sydney | 2008-03-02 | Team2 |
| 2689 | Australia | India | Brisbane | 2008-03-04 | Team2 |
| 2717 | Pakistan | India | Karachi | 2008-06-26 | Team2 |
| 2750 | Sri Lanka | India | Colombo | 2008-08-24 | Team2 |
| 2755 | Sri Lanka | India | Colombo | 2008-08-24 | Team2 |
+---------+--------------+-------------+-------------+------------+-----------+
20 rows in set (0.00 sec)
Q1b. Print Player table.
mysql> select * from Player;
+----------+-------------+------------+--------------+-------+----------------+-------+
| PlayerID | LName | FName | Country | Yborn | Bplace | FTest |
+----------+-------------+------------+--------------+-------+----------------+-------+
| 21001 | Sehwag | Virender | India | 1978 | Delhi | 2001 |
| 23001 | Singh | Yuvraj | India | 1981 | Chandigarh | 2003 |
| 24001 | Symonds | Andrew | Australia | 1975 | Birmingham | 2004 |
| 25001 | Dhoni | MS | India | 1981 | Ranchi | 2005 |
| 27001 | Kumar | Praveen | India | 1986 | Meerut | NULL |
| 27002 | Sharma | Ishant | India | 1988 | Delhi | 2007 |
| 89001 | Tendulkar | Sachin | India | 1973 | Mumbai | 1989 |
| 90001 | Lara | Brain | West Indies | 1969 | Santa Cruz | 1990 |
| 90002 | Kumble | Anil | India | 1970 | Bangalore | 1990 |
| 91001 | Jayasuriya | Sanath | Sri Lanka | 1969 | Matara | 1991 |
| 92001 | Warne | Shane | Australia | 1969 | Melbourne | 1992 |
| 92002 | Murlitharan | Muthiah | Sri Lanka | 1972 | Kandy | 1992 |
| 92003 | UI-Huq | Inzamam | Pakistan | 1970 | Multan | 1992 |
| 93002 | Streak | Heath | Zimbabwe | 1974 | Bulawayo | 1993 |
| 93003 | Kirsten | Gary | South Africa | 1967 | Cape Town | 1993 |
| 94002 | Vaas | Chaminda | Sri Lanka | 1974 | Mattumagala | 1994 |
| 94004 | Fleming | Stephen | New Zealand | 1973 | Christchurch | 1994 |
| 95001 | Ponting | Ricky | Australia | 1974 | Launceston | 1995 |
| 95002 | Pollock | Shaun | South Africa | 1973 | Port Elizabeth | 1995 |
| 95003 | Kallis | Jacques | South Africa | 1975 | Cape Town | 1995 |
| 96001 | Dravid | Rahul | India | 1973 | Indore | 1996 |
| 96002 | Gibbs | Herschelle | South Africa | 1974 | Cape Town | 1996 |
| 96003 | Ganguly | Saurav | India | 1972 | Calcutta | 1996 |
| 97004 | Vettori | Daniel | New Zealand | 1979 | Auckland | 1997 |
| 98001 | Afridi | Shahid | Pakistan | 1980 | Khyber Agency | 1998 |
| 98002 | Singh | Harbhajan | India | 1980 | Jalandhar | 1998 |
| 99001 | Lee | Breet | Australia | 1976 | Wollongong | 1999 |
| 99002 | Gilchrist | Adam | Australia | 1971 | Bellingen | 1999 |
| 99003 | Vaughan | Michale | England | 1974 | Manchester | 1999 |
+----------+-------------+------------+--------------+-------+----------------+-------+
29 rows in set (0.00 sec)
Q1c. Print Bowling table.
mysql> select * from Bowling;
+---------+-------+--------+---------+-------+----------+
| MatchID | PID | NOvers | Maidens | Nruns | Nwickets |
+---------+-------+--------+---------+-------+----------+
| 2689 | 99001 | 10 | 0 | 58 | 1 |
| 2689 | 24001 | 3 | 0 | 27 | 1 |
| 2689 | 23001 | 3 | 0 | 15 | 0 |
| 2755 | 94002 | 9 | 1 | 40 | 1 |
| 2755 | 92002 | 10 | 0 | 56 | 1 |
| 2755 | 91001 | 4 | 0 | 29 | 0 |
| 2755 | 23001 | 10 | 0 | 53 | 2 |
| 2755 | 98002 | 10 | 0 | 40 | 3 |
| 2689 | 98002 | 10 | 0 | 44 | 1 |
+---------+-------+--------+---------+-------+----------+
9 rows in set (0.00 sec)
Q1d. Print Batting table.
mysql> select * from Batting;
+---------+-------+--------+------+------+-------+------+--------+-------+------+
| MatchID | PID | POrder | HOut | FOW | NRuns | Mts | Nballs | Fours | Six |
+---------+-------+--------+------+------+-------+------+--------+-------+------+
| 2755 | 23001 | 3 | C | 51 | 0 | 12 | 6 | 0 | 0 |
| 2755 | 25001 | 5 | C | 232 | 71 | 104 | 80 | 4 | 0 |
| 2755 | 91001 | 1 | C | 74 | 60 | 85 | 52 | 8 | 2 |
| 2755 | 94002 | 7 | LBW | 157 | 17 | 23 | 29 | 1 | 0 |
| 2755 | 92002 | 11 | NO | NO | 1 | 11 | 7 | 0 | 0 |
| 2689 | 89001 | 2 | C | 205 | 91 | 176 | 121 | 7 | 0 |
| 2689 | 23001 | 4 | C | 175 | 38 | 27 | 38 | 2 | 2 |
| 2689 | 25001 | 5 | C | 240 | 36 | 52 | 37 | 2 | 1 |
| 2689 | 99002 | 1 | C | 2 | 2 | 3 | 3 | 0 | 0 |
| 2689 | 95001 | 3 | C | 8 | 1 | 9 | 7 | 0 | 0 |
| 2689 | 24001 | 5 | C | 123 | 42 | 81 | 56 | 2 | 1 |
| 2689 | 99001 | 8 | B | 228 | 7 | 20 | 12 | 0 | 0 |
| 2689 | 27001 | 9 | C | 255 | 7 | 7 | 7 | 1 | 0 |
| 2755 | 27001 | 9 | B | 257 | 2 | 7 | 6 | 0 | 0 |
| 2689 | 98002 | 8 | LBW | 249 | 3 | 7 | 3 | 0 | 0 |
| 2755 | 98002 | 8 | RO | 253 | 2 | 7 | 4 | 0 | 0 |
+---------+-------+--------+------+------+-------+------+--------+-------+------+
16 rows in set (0.00 sec)
Q2. Find the Ids, First name, Last name as Surname of all cricket players.
mysql> select PlayerID,Fname,Lname as Surname from Player;
+----------+------------+-------------+
| PlayerID | Fname | Surname |
+----------+------------+-------------+
| 21001 | Virender | Sehwag |
| 23001 | Yuvraj | Singh |
| 24001 | Andrew | Symonds |
| 25001 | MS | Dhoni |
| 27001 | Praveen | Kumar |
| 27002 | Ishant | Sharma |
| 89001 | Sachin | Tendulkar |
| 90001 | Brain | Lara |
| 90002 | Anil | Kumble |
| 91001 | Sanath | Jayasuriya |
| 92001 | Shane | Warne |
| 92002 | Muthiah | Murlitharan |
| 92003 | Inzamam | UI-Huq |
| 93002 | Heath | Streak |
| 93003 | Gary | Kirsten |
| 94002 | Chaminda | Vaas |
| 94004 | Stephen | Fleming |
| 95001 | Ricky | Ponting |
| 95002 | Shaun | Pollock |
| 95003 | Jacques | Kallis |
| 96001 | Rahul | Dravid |
| 96002 | Herschelle | Gibbs |
| 96003 | Saurav | Ganguly |
| 97004 | Daniel | Vettori |
| 98001 | Shahid | Afridi |
| 98002 | Harbhajan | Singh |
| 99001 | Breet | Lee |
| 99002 | Adam | Gilchrist |
| 99003 | Michale | Vaughan |
+----------+------------+-------------+
29 rows in set (0.00 sec)
Q3a. Find all the information from table Player about players from India.
mysql> select * from Player where Country='India';
+----------+-----------+-----------+---------+-------+------------+-------+
| PlayerID | LName | FName | Country | Yborn | Bplace | FTest |
+----------+-----------+-----------+---------+-------+------------+-------+
| 21001 | Sehwag | Virender | India | 1978 | Delhi | 2001 |
| 23001 | Singh | Yuvraj | India | 1981 | Chandigarh | 2003 |
| 25001 | Dhoni | MS | India | 1981 | Ranchi | 2005 |
| 27001 | Kumar | Praveen | India | 1986 | Meerut | NULL |
| 27002 | Sharma | Ishant | India | 1988 | Delhi | 2007 |
| 89001 | Tendulkar | Sachin | India | 1973 | Mumbai | 1989 |
| 90002 | Kumble | Anil | India | 1970 | Bangalore | 1990 |
| 96001 | Dravid | Rahul | India | 1973 | Indore | 1996 |
| 96003 | Ganguly | Saurav | India | 1972 | Calcutta | 1996 |
| 98002 | Singh | Harbhajan | India | 1980 | Jalandhar | 1998 |
+----------+-----------+-----------+---------+-------+------------+-------+
10 rows in set (0.00 sec)
Q3b. Find the player Ids, First name & birth place of Indian cricket players.
mysql> select PlayerID,Fname,Bplace from Player where Country='India';
+----------+-----------+------------+
| PlayerID | Fname | Bplace |
+----------+-----------+------------+
| 21001 | Virender | Delhi |
| 23001 | Yuvraj | Chandigarh |
| 25001 | MS | Ranchi |
| 27001 | Praveen | Meerut |
| 27002 | Ishant | Delhi |
| 89001 | Sachin | Mumbai |
| 90002 | Anil | Bangalore |
| 96001 | Rahul | Indore |
| 96003 | Saurav | Calcutta |
| 98002 | Harbhajan | Jalandhar |
+----------+-----------+------------+
10 rows in set (0.00 sec)
Q4. Find all the information from table Player about players from India who were born after 1980
mysql> select * from Player where Country='India' and Yborn > 1980;
+----------+--------+---------+---------+-------+------------+-------+
| PlayerID | LName | FName | Country | Yborn | Bplace | FTest |
+----------+--------+---------+---------+-------+------------+-------+
| 23001 | Singh | Yuvraj | India | 1981 | Chandigarh | 2003 |
| 25001 | Dhoni | MS | India | 1981 | Ranchi | 2005 |
| 27001 | Kumar | Praveen | India | 1986 | Meerut | NULL |
| 27002 | Sharma | Ishant | India | 1988 | Delhi | 2007 |
+----------+--------+---------+---------+-------+------------+-------+
4 rows in set (0.00 sec)
Q5. Find the details of all matches played by Pakistan
mysql> SELECT * FROM ODIMatch WHERE Team1='Pakistan' OR Team2='Pakistan';
+---------+----------+----------+------------+------------+--------+
| MatchID | Team1 | Team2 | Ground | Date | Winner |
+---------+----------+----------+------------+------------+--------+
| 2324 | Pakistan | India | Peshwar | 2006-02-06 | Team1 |
| 2327 | Pakistan | India | Rawalpindi | 2006-02-11 | Team2 |
| 2643 | India | Pakistan | Guwahati | 2007-11-05 | Team1 |
| 2717 | Pakistan | India | Karachi | 2008-06-26 | Team2 |
+---------+----------+----------+------------+------------+--------+
4 rows in set (0.00 sec)
Q6a. Find the details of all matches played by India as a Second team.
mysql> SELECT * FROM ODIMatch WHERE Team2='India';
+---------+--------------+-------+-------------+------------+-----------+
| MatchID | Team1 | Team2 | Ground | Date | Winner |
+---------+--------------+-------+-------------+------------+-----------+
| 2324 | Pakistan | India | Peshwar | 2006-02-06 | Team1 |
| 2327 | Pakistan | India | Rawalpindi | 2006-02-11 | Team2 |
| 2377 | West Indies | India | Kingston | 2006-05-18 | Team2 |
| 2404 | Sri Lanka | India | Colombo | 2006-08-16 | Abandoned |
| 2449 | South Africa | India | Cape Town | 2006-11-26 | Team1 |
| 2611 | England | India | Southampton | 2007-08-21 | Team1 |
| 2675 | Australia | India | Melbourne | 2008-02-10 | Team2 |
| 2688 | Australia | India | Sydney | 2008-03-02 | Team2 |
| 2689 | Australia | India | Brisbane | 2008-03-04 | Team2 |
| 2717 | Pakistan | India | Karachi | 2008-06-26 | Team2 |
| 2750 | Sri Lanka | India | Colombo | 2008-08-24 | Team2 |
| 2755 | Sri Lanka | India | Colombo | 2008-08-24 | Team2 |
+---------+--------------+-------+-------------+------------+-----------+
12 rows in set (0.00 sec)
Q6b. Find the details of all matches played by India or Australia as Team1.
mysql> SELECT * FROM ODIMatch WHERE Team1='Australia' OR Team1='India';
mysql> SELECT * FROM ODIMatch WHERE Team1 IN ('Australia','India');
+---------+-----------+-------------+-----------+------------+--------+
| MatchID | Team1 | Team2 | Ground | Date | Winner |
+---------+-----------+-------------+-----------+------------+--------+
| 2357 | India | England | Delhi | 2006-03-28 | Team1 |
| 2440 | India | Australia | Mohali | 2006-10-29 | Team2 |
| 2480 | India | West Indies | Nagpur | 2007-01-21 | Team1 |
| 2493 | India | west Indies | Vadodara | 2007-01-31 | Team1 |
| 2520 | India | Sri Lanka | Rajkot | 2007-02-11 | Team2 |
| 2632 | India | Australia | Mumbai | 2007-10-17 | Team1 |
| 2643 | India | Pakistan | Guwahati | 2007-11-05 | Team1 |
| 2675 | Australia | India | Melbourne | 2008-02-10 | Team2 |
| 2681 | India | Sri Lanka | Adelaide | 2008-02-19 | Team1 |
| 2688 | Australia | India | Sydney | 2008-03-02 | Team2 |
| 2689 | Australia | India | Brisbane | 2008-03-04 | Team2 |
+---------+-----------+-------------+-----------+------------+--------+
11 rows in set (0.00 sec)
Q6c. Find the match details Team2, Winner & ground in which India won the toss
(assume Team1 is toss winner)
mysql> SELECT Team2,Winner,Ground FROM ODIMatch WHERE Team1 = 'India';
+-------------+--------+----------+
| Team2 | Winner | Ground |
+-------------+--------+----------+
| England | Team1 | Delhi |
| Australia | Team2 | Mohali |
| West Indies | Team1 | Nagpur |
| west Indies | Team1 | Vadodara |
| Sri Lanka | Team2 | Rajkot |
| Australia | Team1 | Mumbai |
| Pakistan | Team1 | Guwahati |
| Sri Lanka | Team1 | Adelaide |
+-------------+--------+----------+
8 rows in set (0.00 sec)
Q7. Find Ids of all players that have bowled in an ODI match in the databse
mysql> SELECT DISTINCT PID FROM Bowling;
+-------+
| PID |
+-------+
| 23001 |
| 24001 |
| 91001 |
| 92002 |
| 94002 |
| 98002 |
| 99001 |
+-------+
7 rows in set (0.00 sec)
Q8. Find names of teams and grounds where India has played an ODI match outside India.
(assume Team1 is the host country)
mysql> SELECT DISTINCT Team1,Ground FROM ODIMatch WHERE Team2='India';
+--------------+-------------+
| Team1 | Ground |
+--------------+-------------+
| Pakistan | Peshwar |
| Pakistan | Rawalpindi |
| West Indies | Kingston |
| Sri Lanka | Colombo |
| South Africa | Cape Town |
| England | Southampton |
| Australia | Melbourne |
| Australia | Sydney |
| Australia | Brisbane |
| Pakistan | Karachi |
+--------------+-------------+
10 rows in set (0.00 sec)
Q9. Find Ids of all players that batted in match 2755
mysql> SELECT PID FROM Batting WHERE MatchID=2755;
+-------+
| PID |
+-------+
| 23001 |
| 25001 |
| 91001 |
| 94002 |
| 92002 |
| 27001 |
| 98002 |
+-------+
7 rows in set (0.01 sec)
Q10a. Display list of ground names where Australia has played as Team1
mysql> SELECT Ground FROM ODIMatch WHERE Team1='Australia';
+-----------+
| Ground |
+-----------+
| Melbourne |
| Sydney |
| Brisbane |
+-----------+
3 rows in set (0.00 sec)
Q10b. Display sorted list of ground names where Australia has played as Team1
mysql> SELECT Ground FROM ODIMatch WHERE Team1='Australia' ORDER BY Ground;
+-----------+
| Ground |
+-----------+
| Brisbane |
| Melbourne |
| Sydney |
+-----------+
3 rows in set (0.00 sec)
Q11. Find name of all players whose last name starts with Sin
mysql> SELECT Fname,Lname FROM Player WHERE Lname LIKE 'Sin%';
+-----------+-------+
| Fname | Lname |
+-----------+-------+
| Yuvraj | Singh |
| Harbhajan | Singh |
+-----------+-------+
2 rows in set (0.00 sec)
Q12. Find Ids, First name of all players who played in match 2755.
mysql> SELECT PlayerID,Fname FROM Player WHERE PlayerID IN (SELECT PID FROM Batting WHERE MatchID=2755);
+----------+-----------+
| PlayerID | Fname |
+----------+-----------+
| 23001 | Yuvraj |
| 25001 | MS |
| 91001 | Sanath |
| 94002 | Chaminda |
| 92002 | Muthiah |
| 27001 | Praveen |
| 98002 | Harbhajan |
+----------+-----------+
7 rows in set (0.00 sec)
Q13. Find match Ids of all matches in which Tendulkar batted.
mysql> SELECT MatchID FROM Batting WHERE PID IN (SELECT PlayerID FROM Player WHERE Lname='Tendulkar');
+---------+
| MatchID |
+---------+
| 2689 |
+---------+
1 row in set (0.00 sec)
Q14. Find match information of all matches in which dhoni has batted.
mysql> SELECT MatchID,Team1,Team2,Ground,Date FROM ODIMatch WHERE MatchID IN (SELECT MatchID FROM Batting WHERE PID IN(SELECT PlayerID
FROM Player WHERE Lname='Dhoni'));
+---------+-----------+-------+----------+------------+
| MatchID | Team1 | Team2 | Ground | Date |
+---------+-----------+-------+----------+------------+
| 2755 | Sri Lanka | India | Colombo | 2008-08-24 |
| 2689 | Australia | India | Brisbane | 2008-03-04 |
+---------+-----------+-------+----------+------------+
2 rows in set (0.01 sec)
