Mini project: IPL player statistics

50 min · Beginner

A sports channel's on-screen graphics are answers to SQL questions: who has the most runs, who scores fastest, who is closest to a hundred. With one table of batting records, every graphic is one SELECT.

What you'll build

You write one SQL file, queries.sql. The repository already contains data/shop.sql, which creates a table ipl_batting with one season of batting records for ten players (made-up numbers for practice) and fills it with rows. A strike rate is runs per 100 balls, and a batting average is runs divided by the number of times the player got out.

Your file answers seven questions from a sports channel, each as one commented query: top run scorers, strike rate and average, power hitters, share of runs from boundaries, a batting style label, near-hundreds, and short on-screen cards. Running the file prints seven result tables.

Sample run

+----------------+------+------+
| player         | team | runs |
+----------------+------+------+
| Kunal Bhatia   | RCB  |  601 |
| Arjun Rathore  | CSK  |  512 |
| Rohit Deshmukh | GT   |  455 |
| Vivek Nair     | MI   |  438 |
| Harsh Gill     | MI   |  389 |
+----------------+------+------+
+----------------+------+-------------+---------+
| shown_as       | team | strike_rate | average |
+----------------+------+-------------+---------+
| Sameer Qureshi | KKR  |      172.00 | 37.63   |
| Pranav Iyer    | CSK  |      168.00 | not out |
| Faiz Ansari    | GT   |      166.43 | 38.83   |
| Manish Yadav   | RR   |      163.64 | 33.00   |
| Vivek Nair     | MI   |      155.87 | 36.50   |
| KB             | RCB  |      149.50 | 46.23   |
| Rocket         | CSK  |      139.13 | 42.67   |
| Desh           | GT   |      130.00 | 35.00   |
| Gilly          | MI   |      121.56 | 27.79   |
| Dev Malhotra   | RCB  |      105.07 | 24.17   |
+----------------+------+-------------+---------+
+----------------+-------+-------------+
| player         | balls | strike_rate |
+----------------+-------+-------------+
| Sameer Qureshi |   175 |      172.00 |
| Faiz Ansari    |   140 |      166.43 |
| Manish Yadav   |   121 |      163.64 |
| Vivek Nair     |   281 |      155.87 |
+----------------+-------+-------------+
+---------------+------+---------------+--------------+
| player        | team | boundary_runs | boundary_pct |
+---------------+------+---------------+--------------+
| Vivek Nair    | MI   |           300 |         68.5 |
| Pranav Iyer   | CSK  |            54 |         64.3 |
| Arjun Rathore | CSK  |           318 |         62.1 |
| Harsh Gill    | MI   |           208 |         53.5 |
+---------------+------+---------------+--------------+
+----------------+------------+
| player         | style      |
+----------------+------------+
| Manish Yadav   | Aggressive |
| Sameer Qureshi | Aggressive |
| Arjun Rathore  | Balanced   |
| Kunal Bhatia   | Balanced   |
| Rohit Deshmukh | Balanced   |
| Vivek Nair     | Balanced   |
| Harsh Gill     | Anchor     |
+----------------+------------+
+---------------------+---------+
| batter              | highest |
+---------------------+---------+
| Arjun Rathore (CSK) |      98 |
| Vivek Nair (MI)     |      87 |
| Rohit Deshmukh (GT) |      79 |
+---------------------+---------+
+---------+-------------+----------------+
| code    | short_name  | runs_per_match |
+---------+-------------+----------------+
| RCB-003 | K. Bhatia   |           46.2 |
| CSK-001 | A. Rathore  |           36.6 |
| MI-008  | H. Gill     |           27.8 |
| GT-010  | F. Ansari   |           21.2 |
| RCB-009 | D. Malhotra |           18.1 |
+---------+-------------+----------------+

Requirements

Your program should do all of these.

queries.sql contains only the seven queries; the table ipl_batting comes from data/shop.sql (columns: player_id, player, team, nickname, matches, innings, not_outs, runs, balls, fours, sixes, highest).
Every query starts with a comment that states the question in plain words, and every multi-row answer has an ORDER BY that settles ties by player (or code in Q7). Q1 lists the top 5 run scorers with columns player, team, runs, most runs first.
Q2 lists every player with columns shown_as (nickname, or the player name when there is none), team, strike_rate (runs per 100 balls, 2 decimals) and average (runs divided by times out, 2 decimals, or the text not out for a player who was never dismissed), best strike rate first.
Q3 lists players with a strike rate of 150 or more from at least 100 balls: columns player, balls, strike_rate (2 decimals), highest strike rate first.
Q4 lists CSK and MI players: columns player, team, boundary_runs (fours x 4 plus sixes x 6) and boundary_pct (boundary runs as a percentage of runs, 1 decimal), highest percentage first.
Q5 labels each regular player (12 or more matches) in a column style: Aggressive from a strike rate of 160, Balanced from 130, otherwise Anchor. Columns player, style; Aggressive first, then Balanced, then Anchor, and by player inside each label.
Q6 shows the three best innings from 50 to 99: columns batter (such as Vivek Nair (MI)) and highest, best first.
Q7 shows on-screen cards for players whose name starts with a letter from A to K: columns code (team, a dash and the player id padded to 3 digits, such as RCB-003), short_name (first initial, a dot and the surname, such as K. Bhatia) and runs_per_match (1 decimal), highest first, then by code.

Build it step by step in the Build tab (5 milestones, each ends with a program that runs). Commit and push after each one: MentorNxt checks every push and shows the result in the Submit tab.

What's next

You finished the preview of Querying Data

Python Programming has 21 sections, 110 modules and 609 lessons. An account opens every lesson and saves your progress.

Create account I already have an account

Next step

Start building

5 milestones

What you'll build

Sample run

+----------------+------+------+
| player         | team | runs |
+----------------+------+------+
| Kunal Bhatia   | RCB  |  601 |
| Arjun Rathore  | CSK  |  512 |
| Rohit Deshmukh | GT   |  455 |
| Vivek Nair     | MI   |  438 |
| Harsh Gill     | MI   |  389 |
+----------------+------+------+
+----------------+------+-------------+---------+
| shown_as       | team | strike_rate | average |
+----------------+------+-------------+---------+
| Sameer Qureshi | KKR  |      172.00 | 37.63   |
| Pranav Iyer    | CSK  |      168.00 | not out |
| Faiz Ansari    | GT   |      166.43 | 38.83   |
| Manish Yadav   | RR   |      163.64 | 33.00   |
| Vivek Nair     | MI   |      155.87 | 36.50   |
| KB             | RCB  |      149.50 | 46.23   |
| Rocket         | CSK  |      139.13 | 42.67   |
| Desh           | GT   |      130.00 | 35.00   |
| Gilly          | MI   |      121.56 | 27.79   |
| Dev Malhotra   | RCB  |      105.07 | 24.17   |
+----------------+------+-------------+---------+
+----------------+-------+-------------+
| player         | balls | strike_rate |
+----------------+-------+-------------+
| Sameer Qureshi |   175 |      172.00 |
| Faiz Ansari    |   140 |      166.43 |
| Manish Yadav   |   121 |      163.64 |
| Vivek Nair     |   281 |      155.87 |
+----------------+-------+-------------+
+---------------+------+---------------+--------------+
| player        | team | boundary_runs | boundary_pct |
+---------------+------+---------------+--------------+
| Vivek Nair    | MI   |           300 |         68.5 |
| Pranav Iyer   | CSK  |            54 |         64.3 |
| Arjun Rathore | CSK  |           318 |         62.1 |
| Harsh Gill    | MI   |           208 |         53.5 |
+---------------+------+---------------+--------------+
+----------------+------------+
| player         | style      |
+----------------+------------+
| Manish Yadav   | Aggressive |
| Sameer Qureshi | Aggressive |
| Arjun Rathore  | Balanced   |
| Kunal Bhatia   | Balanced   |
| Rohit Deshmukh | Balanced   |
| Vivek Nair     | Balanced   |
| Harsh Gill     | Anchor     |
+----------------+------------+
+---------------------+---------+
| batter              | highest |
+---------------------+---------+
| Arjun Rathore (CSK) |      98 |
| Vivek Nair (MI)     |      87 |
| Rohit Deshmukh (GT) |      79 |
+---------------------+---------+
+---------+-------------+----------------+
| code    | short_name  | runs_per_match |
+---------+-------------+----------------+
| RCB-003 | K. Bhatia   |           46.2 |
| CSK-001 | A. Rathore  |           36.6 |
| MI-008  | H. Gill     |           27.8 |
| GT-010  | F. Ansari   |           21.2 |
| RCB-009 | D. Malhotra |           18.1 |
+---------+-------------+----------------+

Requirements

Your program should do all of these.

queries.sql contains only the seven queries; the table ipl_batting comes from data/shop.sql (columns: player_id, player, team, nickname, matches, innings, not_outs, runs, balls, fours, sixes, highest).

Every query starts with a comment that states the question in plain words, and every multi-row answer has an ORDER BY that settles ties by player (or code in Q7). Q1 lists the top 5 run scorers with columns player, team, runs, most runs first.

Q2 lists every player with columns shown_as (nickname, or the player name when there is none), team, strike_rate (runs per 100 balls, 2 decimals) and average (runs divided by times out, 2 decimals, or the text not out for a player who was never dismissed), best strike rate first.

Q3 lists players with a strike rate of 150 or more from at least 100 balls: columns player, balls, strike_rate (2 decimals), highest strike rate first.

Q4 lists CSK and MI players: columns player, team, boundary_runs (fours x 4 plus sixes x 6) and boundary_pct (boundary runs as a percentage of runs, 1 decimal), highest percentage first.

Q5 labels each regular player (12 or more matches) in a column style: Aggressive from a strike rate of 160, Balanced from 130, otherwise Anchor. Columns player, style; Aggressive first, then Balanced, then Anchor, and by player inside each label.

Q6 shows the three best innings from 50 to 99: columns batter (such as Vivek Nair (MI)) and highest, best first.

Q7 shows on-screen cards for players whose name starts with a letter from A to K: columns code (team, a dash and the player id padded to 3 digits, such as RCB-003), short_name (first initial, a dot and the surname, such as K. Bhatia) and runs_per_match (1 decimal), highest first, then by code.