Description

COL362/632
General Instructions
1. Do this assignment in groups of 2 people.
3. Use Postgres for this assignment. These tutorials will help you get started
• http://www.w3resource.com/PostgreSQL/tutorial.php
• http://www.tutorialspoint.com/postgresql/index.htm
4. You will submit 1 .sql file: assignment2 < Entry number 1> <Entry number 2>.sql. ONLY ONE MEMBER OF THE GROUP SHOULD SUBMIT ON MOODLE. Please make sure you follow this naming convention and mention your entry numbers in the file name (of the form 2014CSXXXXX), and not the Kerberos IDs (like csXXXXXXX).
5. The .sql files are run automatically, so please ensure that there are no syntax errors in the file. If we are unable to run your file, you get an automatic reduction to 0 marks.
6. The format of the file should be as follows. All queries should be in a single line. Leave a blank line after each query. Write the question number in comments before each query.An example solution file can be found here
(www.cse.iitd.ac.in/~prajna/col362_2018/Assignment_2/example.sql).
7. Many of the queries below require an ‘ORDER BY’ clause. Using these clauses would by default return results in increasing order. If you omitted this clause, your answer will be evaluated as incorrect and zero marks will be awarded.
8. Errors with respect to equality and inequality conditions will also be evaluated as incorrect and zero marks will be awarded.
9. No changes are allowed in the i) data, ii) attribute names, iii) table names
10. You are NOT allowed to use views.
1 Dataset
1.1 Instructions
1. www.cse.iitd.ac.in/~prajna/col362_2018/Assignment_2/player.csv
This file lists the information of all the players in IPL. The schema is described as follows:
Column Name Data Type
player id integer
player name text
batting hand text
bowling skill text
country name text
2. www.cse.iitd.ac.in/~prajna/col362_2018/Assignment_2/team.csv This file describes the teams participating in IPL. Following is the schema:
Column Name Data Type
team id integer
name text
3. www.cse.iitd.ac.in/~prajna/col362_2018/Assignment_2/match.csv It contains the information about all matches.
Column Name Data Type
match id integer
team 1 integer
team 2 integer
season id integer
venue text
toss winner integer
toss decision text
win type text
win margin integer
outcome type text
match winner integer
man of the match integer
Note : season id can take values from 1-9
4. www.cse.iitd.ac.in/~prajna/col362_2018/Assignment_2/player_match.csv Describes the role and the team represented by the player in a match.
Column Name Data Type
match id integer
player id integer
role text
team id integer
5. www.cse.iitd.ac.in/~prajna/col362_2018/Assignment_2/ball_by_ball.csv Describes the ball by ball information of any match.
Column Name Data Type
match id integer
over id integer
ball id integer
innings no integer
team batting integer
team bowling integer
striker batting position integer
striker integer
non striker integer
bowler integer
Note : over id can take values from 1-20, ball id can take values from 1-9 and innings no can take values from 1-4
6. www.cse.iitd.ac.in/~prajna/col362_2018/Assignment_2/batsman_scored.csv Contains information about run scored in every ball of match.
Column Name Data Type
match id integer
over id integer
ball id integer
runs scored integer
innings no integer
7. www.cse.iitd.ac.in/~prajna/col362_2018/Assignment_2/wicket_taken.csv Contains information about the batsman who got out in a ball of the match.
Column Name Data Type
match id integer
over id integer
ball id integer
player out integer
kind out text
innings no integer
8. www.cse.iitd.ac.in/~prajna/col362_2018/Assignment_2/extra_runs.csv Extra runs given in a particular ball of a match.
Column Name Data Type
match id integer
over id integer
ball id integer
extra type text
extra runs integer
innings no integer
1.2 Queries (the column ordering of outputs are mentioned in braces after each query. Your output should come exactly in that order. Please do not print the angular braces in the output.) Total: 53 marks
1. List the names of all left-handed batsmen from England. Order the results alphabetically. (<player name>)
[2 marks]
3. List the match ids and toss winning team IDs where the toss winner of a match decided to bat first. Order result in increasing order of match ids. (<match id, toss winner>) [2 marks]
4. In the match with match id 335987, list the over ids and runs scored where at most 7 runs were scored. Order the over ids in decreasing order of runs scored. Resolve ties by listing the over ids in increasing order. (<over id, runs scored>)[2 marks]
5. List the names of those batsmen who were bowled at least once in alphabetical order of their names. (<player name>) [2 marks]
6. List all the match ids along with the names of teams participating (team 1, team 2), name of the wining team, and win margin where the win margin is at least 60 runs, in increasing order of win margin. Resolve ties by listing the match ids in increasing order. (<match id, team 1, team 2, winning team name, win margin>) [2 marks]
8. List the match wise total for the entire series. The output should be match id, total runs. Return the results in increasing order of match ids. (<match id, total runs>) [2 marks]
9. For each match id, list the maximum runs scored in any over and the bowler bowling in that over. If there is more than one over having maximum runs, return all of them and order them in increasing order of over id. Order results in increasing order of match ids. (<match id, maximum runs, player name>) [3 marks]
10. List the names of batsmen and the number of times they have been “run out” in decreasing order of being “run out”. Resolve ties alphabetically. (<player name, number>) [2 marks]
11. List the number of times any batsman has got out for any out type. Return results in decreasing order of the numbers. Resolve ties alphabetically (on the out type name). (<out type, number>) [2 marks]
12. List the team name and the number of times any player from the team has received man of the match award. Order results alphabetically on the name of the team. (<name, number>) [2 marks]
13. Find the venue where the maximum number of wides have been given. In case of ties, return the one that comes before in alphabetical ordering. Output should contain only 1 row. (<venue>) [2 marks]
14. Find the venue(s) where the team bowling first has won the match. If there are more than 1 venues, list all of them in order of the number of wins (by the bowling team). Resolve ties alphabetically. (<venue>) [3 marks]
15. Find the bowler who has the best average overall. Bowling average is calculated using the following formula:
(1)
Calculate the average upto 3 decimal places and return the bowler with the lowest average runs per wicket. In case of tie, return the results in alphabetical order. (<player name>) [4 marks]
16. List the players and the corresponding teams where the player played as “CaptainKeeper” and won the match. Order results alphabetically on the player’s name. (<player name, name>) [3 marks]
17. List the names of all players and their runs scored (who have scored at least 50 runs in any match). Order result in decreasing order of runs scored. Resolve ties alphabetically. (<player name, runs scored>) [2 marks]
18. List the player names who scored a century but their teams lost the match. Order results alphabetically.
(<player name>) [3 marks]
19. List match ids and venues where KKR has lost the game. Order result in increasing order of match ids. (<match id, venue>) [3 marks]
20. List the names of top 10 players who have the best batting average in season 5. Batting average can be calculated according to the following formula:
(2)
The output should contain exactly 10 rows. Report results upto 3 decimal places. Resolve ties alphabetically. (<player name>) [4 marks]
21. Using the formula provided in the previous query, find out the batting average of all players across all seasons. The batting average of a country X is:
(3)