SQL Exercise: Players scored number of goals in every matches
6. From the following table, write a SQL query to find the players who scored the most goals in each match. Group the result set on match number, country name and player name. Sort the result-set in ascending order by match number. Return match number, country name, player name and number of matches.
Sample table: goal_details
goal_id | match_no | player_id | team_id | goal_time | goal_type | play_stage | goal_schedule | goal_half
---------+----------+-----------+---------+-----------+-----------+------------+---------------+-----------
1 | 1 | 160159 | 1207 | 57 | N | G | NT | 2
2 | 1 | 160368 | 1216 | 65 | P | G | NT | 2
3 | 1 | 160154 | 1207 | 89 | N | G | NT | 2
4 | 2 | 160470 | 1221 | 5 | N | G | NT | 1
5 | 3 | 160547 | 1224 | 10 | N | G | NT | 1
6 | 3 | 160403 | 1218 | 61 | N | G | NT | 2
7 | 3 | 160550 | 1224 | 81 | N | G | NT | 2
8 | 4 | 160128 | 1206 | 73 | N | G | NT | 2
9 | 4 | 160373 | 1217 | 93 | N | G | ST | 2
..........
108 | 51 | 160319 | 1214 | 109 | N | F | ET | 2
Sample table: soccer_country
country_id | country_abbr | country_name
------------+--------------+---------------------
1201 | ALB | Albania
1202 | AUT | Austria
1203 | BEL | Belgium
1204 | CRO | Croatia
1205 | CZE | Czech Republic
1206 | ENG | England
1207 | FRA | France
1208 | GER | Germany
1209 | HUN | Hungary
........
1229 | NOR | Norway
Sample table: player_mast
player_id | team_id | jersey_no | player_name | posi_to_play | dt_of_bir | age | playing_club
-----------+---------+-----------+-------------------------+--------------+------------+-----+---------------------
160001 | 1201 | 1 | Etrit Berisha | GK | 1989-03-10 | 27 | Lazio
160008 | 1201 | 2 | Andi Lila | DF | 1986-02-12 | 30 | Giannina
160016 | 1201 | 3 | Ermir Lenjani | MF | 1989-08-05 | 26 | Nantes
160007 | 1201 | 4 | Elseid Hysaj | DF | 1994-02-20 | 22 | Napoli
160013 | 1201 | 5 | Lorik Cana | MF | 1983-07-27 | 32 | Nantes
160010 | 1201 | 6 | Frederic Veseli | DF | 1992-11-20 | 23 | Lugano
160004 | 1201 | 7 | Ansi Agolli | DF | 1982-10-11 | 33 | Qarabag
160012 | 1201 | 8 | Migjen Basha | MF | 1987-01-05 | 29 | Como
160017 | 1201 | 9 | Ledian Memushaj | MF | 1986-12-17 | 29 | Pescara
......
160548 | 1224 | 23 | Simon Church | FD | 1988-12-10 | 27 | MK Dons
Sample Solution:
SQL Code:
-- This SQL query retrieves information about goals scored in matches, including match number, country name, player name, and the count of goals for each combination.
SELECT match_no, country_name, player_name, COUNT(match_no)
-- Selects the 'match_no', 'country_name', 'player_name', and the count of goals for each group.
FROM goal_details a, soccer_country b, player_mast c
-- Specifies the tables involved in the query, using aliases 'a' for 'goal_details', 'b' for 'soccer_country', and 'c' for 'player_mast'.
WHERE a.team_id = b.country_id
-- Connects rows where 'team_id' in 'goal_details' matches 'country_id' in 'soccer_country'.
AND a.player_id = c.player_id
-- Further connects rows where 'player_id' in 'goal_details' matches 'player_id' in 'player_mast'.
GROUP BY match_no, country_name, player_name
-- Groups the results by 'match_no', 'country_name', and 'player_name'.
ORDER BY match_no;
-- Orders the result set based on the 'match_no' column in ascending order.
Sample Output:
match_no | country_name | player_name | count
----------+---------------------+-------------------------+-------
1 | France | Dimitri Payet | 1
1 | France | Olivier Giroud | 1
1 | Romania | Bogdan Stancu | 1
2 | Switzerland | Fabian Schar | 1
3 | Slovakia | Ondrej Duda | 1
3 | Wales | Gareth Bale | 1
3 | Wales | Hal Robson-Kanu | 1
4 | England | Eric Dier | 1
4 | Russia | Vasili Berezutski | 1
5 | Croatia | Luka Modric | 1
6 | Poland | Arkadiusz Milik | 1
7 | Germany | Bastian Schweinsteiger | 1
7 | Germany | Thomas Muller | 1
8 | Spain | Gerard Pique | 1
9 | Republic of Ireland | Ciaran Clark | 1
9 | Republic of Ireland | Wes Hoolahan | 1
10 | Italy | Emanuele Giaccherini | 1
10 | Italy | Graziano Pelle | 1
11 | Hungary | Adam Szalai | 1
11 | Hungary | Zoltan Stieber | 1
12 | Iceland | Birkir Bjarnason | 1
12 | Portugal | Nani | 1
13 | Russia | Denis Glushakov | 1
13 | Slovakia | Marek Hamsik | 1
13 | Slovakia | Vladimir Weiss | 1
14 | Romania | Bogdan Stancu | 1
14 | Switzerland | Admir Mehmedi | 1
15 | France | Antoine Griezmann | 1
15 | France | Dimitri Payet | 1
16 | England | Daniel Sturridge | 1
16 | England | Jamie Vardy | 1
16 | Wales | Gareth Bale | 1
17 | Northern Ireland | Gareth McAuley | 1
17 | Northern Ireland | Niall McGinn | 1
19 | Italy | Eder | 1
20 | Croatia | Ivan PeriSic | 1
20 | Croatia | Ivan Rakitic | 1
20 | Czech Republic | Milan Skoda | 1
20 | Czech Republic | TomasNecid | 1
21 | Spain | Alvaro Morata | 2
21 | Spain | Nolito | 1
22 | Belgium | Axel Witsel | 1
22 | Belgium | Romelu Lukaku | 2
23 | Iceland | Birkir Saevarsson | 1
23 | Iceland | Gylfi Sigurdsson | 1
25 | Albania | Armando Sadiku | 1
27 | Wales | Aaron Ramsey | 1
27 | Wales | Gareth Bale | 1
27 | Wales | Neil Taylor | 1
29 | Poland | Jakub Blaszczykowski | 1
30 | Germany | Mario Gomez | 1
31 | Turkey | Burak Yilmaz | 1
31 | Turkey | Ozan Tufan | 1
32 | Croatia | Ivan PeriSic | 1
32 | Croatia | Nikola Kalinic | 1
32 | Spain | Alvaro Morata | 1
33 | Austria | Alessandro Schopf | 1
33 | Iceland | Jon Dadi Bodvarsson | 2
34 | Hungary | Balazs Dzsudzsak | 2
34 | Hungary | Zoltan Gera | 1
34 | Portugal | Cristiano Ronaldo | 2
34 | Portugal | Nani | 1
35 | Republic of Ireland | Robbie Brady | 1
36 | Belgium | Radja Nainggolan | 1
37 | Poland | Jakub Blaszczykowski | 1
37 | Switzerland | Xherdan Shaqiri | 1
38 | Northern Ireland | Gareth McAuley | 1
39 | Portugal | Ricardo Quaresma | 1
40 | France | Antoine Griezmann | 2
40 | Republic of Ireland | Robbie Brady | 1
41 | Germany | Jerome Boateng | 1
41 | Germany | Julian Draxler | 1
41 | Germany | Mario Gomez | 1
42 | Belgium | Eden Hazard | 1
42 | Belgium | Michy Batshuayi | 1
42 | Belgium | Toby Alderweireld | 1
42 | Belgium | Yannick Carrasco | 1
43 | Italy | Giorgio Chiellini | 1
43 | Italy | Graziano Pelle | 1
44 | England | Wayne Rooney | 1
44 | Iceland | Arnor Ingvi Traustason | 1
44 | Iceland | Kolbeinn Sigthorsson | 1
45 | Poland | Robert Lewandowski | 1
45 | Portugal | Renato Sanches | 1
46 | Belgium | Radja Nainggolan | 1
46 | Wales | Ashley Williams | 1
46 | Wales | Hal Robson-Kanu | 1
46 | Wales | Sam Vokes | 1
47 | Germany | Mesut ozil | 1
47 | Italy | Leonardo Bonucci | 1
48 | France | Antoine Griezmann | 1
48 | France | Dimitri Payet | 1
48 | France | Olivier Giroud | 2
48 | France | Paul Pogba | 1
48 | Iceland | Birkir Bjarnason | 1
48 | Iceland | Kolbeinn Sigthorsson | 1
49 | Portugal | Cristiano Ronaldo | 1
49 | Portugal | Nani | 1
50 | France | Antoine Griezmann | 2
51 | Portugal | Eder | 1
(100 rows)
Code Explanation:
The said query in SQL that selects the match number, country name, player name, and the count of goals scored by each player in each match from the 'goal_details', 'soccer_country', and 'player_mast' tables. The results are grouped by match number, country name, and player name and sorted by match number.
The WHERE clause joins the tables by matching the "team_id" column in 'goal_details' to the "country_id" column in 'soccer_country' and the "player_id" column in 'goal_details' and 'player_mast' table.
The GROUP BY keyword groups the results by the "match_no", "country_name", and "player_name" columns.
The results then sorted in ascending order based on the "match_no" column.
Alternative Solutions:
Using INNER JOIN and GROUP BY:
SELECT gd.match_no, sc.country_name, pm.player_name, COUNT(gd.match_no)
FROM goal_details gd
JOIN soccer_country sc ON gd.team_id = sc.country_id
JOIN player_mast pm ON gd.player_id = pm.player_id
GROUP BY gd.match_no, sc.country_name, pm.player_name
ORDER BY gd.match_no;
Explanation:
This query uses INNER JOINs to connect the goal_details, soccer_country, and player_mast tables based on their respective IDs. It then groups the results by match_no, country_name, and player_name, and counts the occurrences of match_no.
Using WHERE Clause with Subqueries:
SELECT gd.match_no, sc.country_name, pm.player_name,
(SELECT COUNT(match_no)
FROM goal_details a
WHERE a.match_no = gd.match_no
AND a.team_id = gd.team_id
AND a.player_id = gd.player_id) as goal_count
FROM goal_details gd
JOIN soccer_country sc ON gd.team_id = sc.country_id
JOIN player_mast pm ON gd.player_id = pm.player_id
ORDER BY gd.match_no;
Explanation:
This query uses subqueries in the SELECT clause to count the number of goals for each combination of match, country, and player.
Using INNER JOIN and COUNT() with PARTITION BY:
SELECT gd.match_no, sc.country_name, pm.player_name,
COUNT(gd.match_no) OVER (PARTITION BY gd.match_no, sc.country_name, pm.player_name) as goal_count
FROM goal_details gd
JOIN soccer_country sc ON gd.team_id = sc.country_id
JOIN player_mast pm ON gd.player_id = pm.player_id
ORDER BY gd.match_no;
Explanation:
This query uses the COUNT() function with the PARTITION BY clause to count the number of goals for each combination of match, country, and player.
Using INNER JOIN and JOINs in the COUNT() Subquery:
SELECT gd.match_no, sc.country_name, pm.player_name,
(SELECT COUNT(*)
FROM goal_details a
JOIN soccer_country b ON a.team_id = b.country_id
JOIN player_mast c ON a.player_id = c.player_id
WHERE a.match_no = gd.match_no
AND b.country_name = sc.country_name
AND c.player_name = pm.player_name) as goal_count
FROM goal_details gd
JOIN soccer_country sc ON gd.team_id = sc.country_id
JOIN player_mast pm ON gd.player_id = pm.player_id
ORDER BY gd.match_no;
Explanation:
This query uses a subquery in the SELECT clause to count the number of goals for each combination of match, country, and player. It joins the necessary tables in the subquery to ensure accurate counting.
Relational Algebra Expression:
Relational Algebra Tree:
Go to:
PREV : Find the match where Portugal played against Hungary.
NEXT : Find the teams who played the heighest audience match.
Practice Online
Sample Database: soccer
Query Visualization:
Duration:
Rows:
Cost:
Have another way to solve this solution? Contribute your code (and comments) through Disqus.
What is the difficulty level of this exercise?
Test your Programming skills with w3resource's quiz.