Description

IPL Schema for the lab:

1. Write SQL queries for the following questions to be submitted using XData on Moodle only.
1. Find names of Indian players born on or after 1992-01-01 in alphabetical order.
2. Find the names of the bowlers in alphabetical order who have given more than 2 extra runs in a single delivery.
3. Find the team name and the winning margin (sorted in descending order, tie-breaker: alphabetical order of team name) where the team wins by more than 10 runs (batting first).
4. Find player_id and names of Australian players who have captained a team and print in alphabetical order of their names.
5. Find players who have won man of the match while captaining the team
6. Find names of players who have hit sixes in 2011 or 2013.
7. Find names of right-arm medium bowlers who have at least taken a wicket by caught and bowled.
8. Find names of overseas players who have played in Eden Gardens.
9. Find names of teams who won a match in 2015 after losing the toss.
10. Find name, country of captains who have bowled in a match.
11. Find names of left-handed batsmen who have hit 4s at Pune.
12. Find names of players who have never batted from Srilanka.
13. Find names of teams that have never won a match at M Chinnaswamy Stadium.
14. Find the names of players who got dismissed by both “run out” and got bowled in 2 different matches in 2017.
15. Find the names of teams whose no player has ever got out by caught and bowled.
Grading rubric:
Query 1 – 50 points
Queries 2-6, 12-15 – 100 points each
Queries – 7, 9-11 – 70 points each
Query 8 – 130 points
Note: First solve the question in Postgres on your machine (using either psql or pgadmin4) and ONLY THEN do the submission into Xdata. This assignment on Xdata is NOT interactive. You can only submit each query once.
2. Write the RA queries for the following questions:
2, 4, 8, 12, 14
Submit the RA queries as a PDF file in Moodle. You can try out the RA queries on the RA calculator at:
http://clotho.uom.gr/relax/calc.htm?data=gist:d84b457b4136dcc8e61e319608b09f47 to ensure that they return the same data as the corresponding SQL equivalents.
We have uploaded the IPL data and schema there for your convenience already.
Grading rubric:
Query 2 – 20 points
Query 4 – 20 points
Query 8 – 20 points
Query 12 – 20 points
Query 14 – 20 points
Total – 100 points