SQL exercises on soccer Database: Prepare a list of players with shot number they taken in penalty shootout matches

SQL soccer Database: Exercise-25 with Solution

25. From the following tables, write a SQL query to find the players with shot number they taken in penalty shootout matches. Return match_no, Team, player_name, jersey_no, score_goal, kick_no.

Sample table: soccer_country

Sample table: penalty_shootout

Sample table: player_mast

Sample Solution:

SQL Code:

SELECT c.match_no,a.country_name AS "Team", 
b.player_name, b.jersey_no, c.score_goal ,c.kick_no
FROM soccer_country a, penalty_shootout c, player_mast b
WHERE c.team_id=a.country_id
AND c.player_id=b.player_id;

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)

Practice Online

Sample Database: soccer

Query Visualization:

Duration:

Rows:

Cost:

Have another way to solve this solution? Contribute your code (and comments) through Disqus.

Previous: From the following table, write a SQL query to count the number of shots missed or saved in penalty shootout matches. Return number of shots missed as "Goal missed or saved by Penalty Kicks".
Next: From the following tables, write a SQL query to count the number of penalty shots taken by the teams. Return country name, number of shots as "Number of Shots".