SQL Exercise: List of players with shot numbers in penalty shootouts
25. From the following table, write a SQL query to find the players with shot numbers they took in penalty shootout matches. Return match_no, Team, player_name, jersey_no, score_goal, kick_no.
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: penalty_shootout
kick_id | match_no | team_id | player_id | score_goal | kick_no
---------+----------+---------+-----------+------------+---------
1 | 37 | 1221 | 160467 | Y | 1
2 | 37 | 1213 | 160297 | Y | 2
3 | 37 | 1221 | 160477 | N | 3
4 | 37 | 1213 | 160298 | Y | 4
5 | 37 | 1221 | 160476 | Y | 5
6 | 37 | 1213 | 160281 | Y | 6
7 | 37 | 1221 | 160470 | Y | 7
8 | 37 | 1213 | 160287 | Y | 8
9 | 37 | 1221 | 160469 | Y | 9
..........
37 | 47 | 1208 | 160166 | Y | 18
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 penalty shootout kicks,
-- including the match number, team name, player details, goal score, and kick number.
SELECT c.match_no, a.country_name AS "Team",
-- Selects the match number and aliases the 'country_name' column as "Team" from the 'soccer_country' table.
b.player_name, b.jersey_no, c.score_goal, c.kick_no
-- Selects player details, goal score, and kick number from the 'player_mast' and 'penalty_shootout' tables.
FROM soccer_country a, penalty_shootout c, player_mast b
-- Specifies the tables involved in the query, using aliases 'a', 'c', and 'b'.
WHERE c.team_id = a.country_id
-- Connects rows in 'penalty_shootout' where 'team_id' matches 'country_id' in 'soccer_country'.
AND c.player_id = b.player_id;
-- Connects rows in 'penalty_shootout' where 'player_id' matches 'player_id' in 'player_mast'.
Sample Output:
match_no | Team | player_name | jersey_no | score_goal | kick_no
----------+-------------+-------------------------+-----------+------------+---------
37 | Switzerland | Stephan Lichtsteiner | 2 | Y | 1
37 | Poland | Robert Lewandowski | 9 | Y | 2
37 | Switzerland | Granit Xhaka | 10 | N | 3
37 | Poland | Arkadiusz Milik | 7 | Y | 4
37 | Switzerland | Xherdan Shaqiri | 23 | Y | 5
37 | Poland | Kamil Glik | 15 | Y | 6
37 | Switzerland | Fabian Schar | 22 | Y | 7
37 | Poland | Jakub Blaszczykowski | 16 | Y | 8
37 | Switzerland | Ricardo Rodriguez | 13 | Y | 9
37 | Poland | Grzegorz Krychowiak | 10 | Y | 10
45 | Portugal | Cristiano Ronaldo | 7 | Y | 1
45 | Poland | Robert Lewandowski | 9 | Y | 2
45 | Portugal | Renato Sanches | 16 | Y | 3
45 | Poland | Arkadiusz Milik | 7 | Y | 4
45 | Portugal | Joao Moutinho | 8 | Y | 5
45 | Poland | Kamil Glik | 15 | Y | 6
45 | Portugal | Nani | 17 | Y | 7
45 | Poland | Jakub Blaszczykowski | 16 | N | 8
45 | Portugal | Ricardo Quaresma | 20 | Y | 9
47 | Italy | Lorenzo Insigne | 20 | Y | 1
47 | Germany | Toni Kroos | 18 | Y | 2
47 | Italy | Simone Zaza | 7 | N | 3
47 | Germany | Thomas Muller | 13 | N | 4
47 | Italy | Andrea Barzagli | 15 | Y | 5
47 | Germany | Mesut ozil | 8 | N | 6
47 | Italy | Graziano Pelle | 9 | N | 7
47 | Germany | Julian Draxler | 11 | Y | 8
47 | Italy | Leonardo Bonucci | 19 | N | 9
47 | Germany | Bastian Schweinsteiger | 7 | N | 10
47 | Italy | Emanuele Giaccherini | 23 | Y | 11
47 | Germany | Mats Hummels | 5 | Y | 12
47 | Italy | Marco Parolo | 18 | Y | 13
47 | Germany | Joshua Kimmich | 21 | Y | 14
47 | Italy | Mattia De Sciglio | 2 | Y | 15
47 | Germany | Jerome Boateng | 17 | Y | 16
47 | Italy | Matteo Darmian | 4 | N | 17
47 | Germany | Jonas Hector | 3 | Y | 18
(37 rows)
Code Explanation:
The said query in SQL that fetches the match number the country name , the player name , the jersey number , the score and the kick number for each penalty shootout from the tables soccer_country, penalty_shootout, and player_mast.
By the use of JOIN clause the team_id column in penalty_shootout matches the country_id column in soccer_country, and the player_id column in penalty_shootout matches the player_id column in player_mast.
Go to:
PREV : Number of shots missed or saved in penalty shootouts.
NEXT : Find the number of penalty shots taken by the teams.
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.