Aggregations and Group By Coding Practice

 

The database consists of

player_match_details

table that stores the information of players' details like name, match, score, year, number of fours and sixes scored.

This practice set helps you get well versed with GROUP BY and HAVING clauses. Let’s dive in!

name	match	score	fours	sixes	year
Ram	RR vs SRH	62	2	7	2011
Joseph	SRH vs CSK	44	2	4	2012
Lokesh	DC vs DD	99	2	13	2013
David	SRH vs CSK	96	1	13	2014
Joseph	SRH vs CSK				2012
Viraj	RCB vs RR	53	2	5	2010
Shyam	MI vs RCB	75	2	9	2011
Stark	MI vs DC	75	2	9	2012
Stark	MI vs SRH				2012
Ram	RR vs MI	84	1	11	2013
Joseph	SRH vs RR	42	1	4	2014
David	SRH vs MI				2014
Ramesh	CSK vs RR	9	0	0	2010
Ram	RR vs DC	75	2	9	2011
Joseph	SRH vs MI	30	5	0	2012
Lokesh	DC vs RR	87	2	11	2013
Ram	RR vs SRH				2011
David	SRH vs MI	9	0	0	2014

1.

Get the total score of each player

Note: Output must contain rows in the descending order of

total_score

Expected Output Format:

name	total_score
...	...

2.

Get the number of half centuries scored by each player.

Note: Output must contain rows in the descending order of

half_centuries

Expected Output Format:

name	half_centuries
...	...

3.

Get the total number of 4's hit by each player as

no_of_fours

.

Note: Output must contain rows in the descending order of

no_of_fours

Expected Output Format:

name	no_of_fours
...	...

4.

Get the highest score of every player as

max_score

.

Note: Output must contain rows in the descending order of

max_score

of the player.

Expected Output Format:

name	max_score
...	...

5.

Get player

name

and the total number of matches played as

no_of_matches

by each player in the year 2012.

Note: Output must contain rows in the descending order of

no_of_matches

of each player.

Expected Output Format:

name	no_of_matches
...	...

6.

Get the year-wise performance, i.e.,

no_of_matches

and

runs_scored

by each player.

Note: Output must contain rows in the ascending order of

name

&

year

Expected Output Format:

name	year	no_of_matches	runs_scored
...	...	...	...

7.

Get

name

, average score of players as

avg_score

, and total number of sixes scored by the players as

total_sixes

whose average score is greater than 50.

Note: Output must contain rows in the ascending order of

name

of the player.

Expected Output Format:

name	avg_score	total_sixes
...	...	...

8.

For each player who scored more than 50 in at least 2 matches, get the total number of matches where the players scored more than 50.

Note: Output must contain rows in the ascending order of

name

of the player.

Expected Output Format:

name	no_of_matches
...	...