w3resource
Soccer Database Exercises

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

SQL soccer Database: Exercise-25 with Solution

25. Write a query in SQL to prepare a list of players with number of shots they taken in penalty shootout matches.

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)

Query Visualization:

Duration:

Query visualization of Prepare a list of players with number of shots they taken in penalty shootout matches - Duration

Rows:

Query visualization of Prepare a list of players with number of shots they taken in penalty shootout matches - Rows

Cost:

Query visualization of Prepare a list of players with number of shots they taken in penalty shootout matches - Cost

Practice Online


Sample Database: soccer

soccer database relationship structure

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

Previous: Write a query in SQL to find the number of shots missed or saved in penalty shootout matches.
Next: Write a query in SQL to find the number of penalty shots taken by the teams.

What is the difficulty level of this exercise?



New Content: Composer: Dependency manager for PHP, R Programming