SQL Exercise: List of the players of each match against each match
36. From the following table, write a SQL query to prepare a list for the “player of the match” against each match. Return match number, play date, country name, player of the Match, jersey number.
Sample table: match_mast
match_no | play_stage | play_date | results | decided_by | goal_score | venue_id | referee_id | audence | plr_of_match | stop1_sec | stop2_sec
----------+------------+------------+---------+------------+------------+----------+------------+---------+--------------+-----------+-----------
1 | G | 2016-06-11 | WIN | N | 2-1 | 20008 | 70007 | 75113 | 160154 | 131 | 242
2 | G | 2016-06-11 | WIN | N | 0-1 | 20002 | 70012 | 33805 | 160476 | 61 | 182
3 | G | 2016-06-11 | WIN | N | 2-1 | 20001 | 70017 | 37831 | 160540 | 64 | 268
4 | G | 2016-06-12 | DRAW | N | 1-1 | 20005 | 70011 | 62343 | 160128 | 0 | 185
5 | G | 2016-06-12 | WIN | N | 0-1 | 20007 | 70006 | 43842 | 160084 | 125 | 325
6 | G | 2016-06-12 | WIN | N | 1-0 | 20006 | 70014 | 33742 | 160291 | 2 | 246
7 | G | 2016-06-13 | WIN | N | 2-0 | 20003 | 70002 | 43035 | 160176 | 89 | 188
8 | G | 2016-06-13 | WIN | N | 1-0 | 20010 | 70009 | 29400 | 160429 | 360 | 182
9 | G | 2016-06-13 | DRAW | N | 1-1 | 20008 | 70010 | 73419 | 160335 | 67 | 194
...........
51 | F | 2016-07-11 | WIN | N | 1-0 | 20008 | 70005 | 75868 | 160307 | 161 | 181
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 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 Solution:
SQL Code:
-- This query selects the match number, play date, country name, player name (as "Player of the Match"), and jersey number from the relevant tables.
SELECT
match_no, -- Selecting the match number
play_date, -- Selecting the play date
country_name, -- Selecting the country name
player_name AS "Player of the Match", -- Selecting the player name and aliasing it as "Player of the Match"
jersey_no -- Selecting the jersey number
FROM
match_mast a -- Specifying the match_mast table with an alias 'a'
JOIN
player_mast b ON a.plr_of_match = b.player_id -- Joining the match_mast table with the player_mast table based on the player of the match
-- Joining the player_mast table with the soccer_country table based on the team_id
JOIN
soccer_country c ON b.team_id = c.country_id;
Sample Output:
match_no | play_date | country_name | Player of the Match | jersey_no
----------+------------+---------------------+-----------------------+-----------
25 | 2016-06-20 | Albania | Arlind Ajeti | 18
22 | 2016-06-18 | Belgium | Axel Witsel | 6
42 | 2016-06-27 | Belgium | Eden Hazard | 10
36 | 2016-06-23 | Belgium | Eden Hazard | 10
32 | 2016-06-22 | Croatia | Ivan PeriSic | 4
20 | 2016-06-17 | Croatia | Ivan Rakitic | 7
5 | 2016-06-12 | Croatia | Luka Modric | 10
4 | 2016-06-12 | England | Eric Dier | 17
50 | 2016-07-08 | France | Antoine Griezmann | 7
40 | 2016-06-26 | France | Antoine Griezmann | 7
15 | 2016-06-16 | France | Dimitri Payet | 8
1 | 2016-06-11 | France | Dimitri Payet | 8
48 | 2016-07-04 | France | Olivier Giroud | 9
47 | 2016-07-03 | Germany | Manuel Neuer | 1
30 | 2016-06-21 | Germany | Mesut ozil | 8
41 | 2016-06-26 | Germany | Julian Draxler | 11
18 | 2016-06-17 | Germany | Jerome Boateng | 17
7 | 2016-06-13 | Germany | Toni Kroos | 18
11 | 2016-06-14 | Hungary | Laszlo Kleinheisler | 15
44 | 2016-06-28 | Iceland | Ragnar Sigurdsson | 6
23 | 2016-06-18 | Iceland | Kolbeinn Sigthorsson | 9
33 | 2016-06-22 | Iceland | Kari Arnason | 14
19 | 2016-06-17 | Italy | Eder | 17
43 | 2016-06-27 | Italy | Leonardo Bonucci | 19
10 | 2016-06-14 | Italy | Emanuele Giaccherini | 23
17 | 2016-06-16 | Northern Ireland | Gareth McAuley | 4
6 | 2016-06-12 | Poland | Grzegorz Krychowiak | 10
51 | 2016-07-11 | Portugal | Pepe | 3
49 | 2016-07-07 | Portugal | Cristiano Ronaldo | 7
34 | 2016-06-22 | Portugal | Cristiano Ronaldo | 7
24 | 2016-06-19 | Portugal | Joao Moutinho | 8
45 | 2016-07-01 | Portugal | Renato Sanches | 16
39 | 2016-06-26 | Portugal | Renato Sanches | 16
12 | 2016-06-15 | Portugal | Nani | 17
35 | 2016-06-23 | Republic of Ireland | Robbie Brady | 19
9 | 2016-06-13 | Republic of Ireland | Wes Hoolahan | 20
13 | 2016-06-15 | Slovakia | Marek Hamsik | 17
28 | 2016-06-21 | Slovakia | MatusKozacik | 23
21 | 2016-06-18 | Spain | Andres Iniesta | 6
8 | 2016-06-13 | Spain | Andres Iniesta | 6
26 | 2016-06-20 | Switzerland | Yann Sommer | 1
14 | 2016-06-15 | Switzerland | Granit Xhaka | 10
37 | 2016-06-25 | Switzerland | Xherdan Shaqiri | 23
2 | 2016-06-11 | Switzerland | Xherdan Shaqiri | 23
31 | 2016-06-22 | Turkey | Burak Yilmaz | 17
29 | 2016-06-21 | Ukraine | Ruslan Rotan | 14
16 | 2016-06-16 | Wales | Joe Allen | 7
3 | 2016-06-11 | Wales | Joe Allen | 7
46 | 2016-07-02 | Wales | Hal Robson-Kanu | 9
27 | 2016-06-21 | Wales | Aaron Ramsey | 10
38 | 2016-06-25 | Wales | Gareth Bale | 11
(51 rows)
Code Explanation:
The said query in SQL that retrieves information about the player of the match in each soccer match, including their name, jersey number, the country they belong to, the match number, and the date the match was played from the tables match_mast, player_mast, and soccer_country.
The JOIN clause combines the tables'match_mast' and 'player_mast' alias as 'a' and 'b' based on the common columns "plr_of_match" and "player_id" respectively. Then, the resulting table is joined with the 'soccer_country' table alias as 'c' based on the common column "team_id" and "country_id" respectively in 'b' and 'c'.
Alternative Solutions:
Using INNER JOINs with WHERE Clause:
-- This query selects the match number, play date, country name, player name (as "Player of the Match"), and jersey number from the relevant tables.
SELECT
match_mast.match_no, -- Selecting the match number from the match_mast table
match_mast.play_date, -- Selecting the play date from the match_mast table
soccer_country.country_name, -- Selecting the country name from the soccer_country table
player_mast.player_name AS "Player of the Match", -- Selecting the player name from the player_mast table and aliasing it as "Player of the Match"
player_mast.jersey_no -- Selecting the jersey number from the player_mast table
FROM
match_mast, -- Specifying the match_mast table
player_mast, -- Specifying the player_mast table
soccer_country -- Specifying the soccer_country table
WHERE
match_mast.plr_of_match = player_mast.player_id -- Joining the match_mast table with the player_mast table based on the player of the match
-- Joining the player_mast table with the soccer_country table based on the team_id
AND player_mast.team_id = soccer_country.country_id;
Explanation:
This query uses a WHERE clause to filter the rows based on the specified conditions. It combines match_mast, player_mast, and soccer_country tables.
Using INNER JOINs with Subquery:
-- This query selects the match number, play date, country name, player name (as "Player of the Match"), and jersey number from the relevant tables.
SELECT
a.match_no, -- Selecting the match number from the match_mast table aliased as 'a'
a.play_date, -- Selecting the play date from the match_mast table aliased as 'a'
c.country_name, -- Selecting the country name from the soccer_country table aliased as 'c'
b.player_name AS "Player of the Match", -- Selecting the player name from the player_mast subquery aliased as 'b' and aliasing it as "Player of the Match"
b.jersey_no -- Selecting the jersey number from the player_mast subquery aliased as 'b'
FROM
match_mast a -- Specifying the match_mast table with an alias 'a'
JOIN
(
-- Subquery to select player information from the player_mast table
SELECT
player_id, -- Selecting the player ID
player_name, -- Selecting the player name
jersey_no, -- Selecting the jersey number
team_id -- Selecting the team ID
FROM
player_mast -- Specifying the player_mast table for the subquery
) b ON a.plr_of_match = b.player_id -- Joining the subquery with the match_mast table based on the player of the match
-- Joining the soccer_country table with the player_mast subquery based on the team_id
JOIN
soccer_country c ON b.team_id = c.country_id;
Explanation:
This query uses a subquery to first select necessary columns from player_mast. It then performs INNER JOINs to retrieve the desired information.
Go to:
PREV : Players who came onto the field during the first half.
NEXT : Find the player who took the 26th penalty shot.
Practice Online
Sample Database: soccer
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.