SQL Exercise: Find the results of penalty shootout matches

SQL soccer Database: Joins Exercise-59 with Solution

59. From the following table, write a SQL query to find the results of penalty shootout matches. Return match number, play stage, country name and penalty score.

Sample table: match_details

Sample table: soccer_country

Sample Solution:

SQL Code:

SELECT match_no,
       play_stage,
       country_name,
       penalty_score
FROM match_details a
JOIN soccer_country b ON a.team_id=b.country_id
WHERE decided_by='P'
ORDER BY match_no;

Sample Output:

 match_no | play_stage | country_name | penalty_score
----------+------------+--------------+---------------
       37 | R          | Poland       |             5
       37 | R          | Switzerland  |             4
       45 | Q          | Poland       |             3
       45 | Q          | Portugal     |             5
       47 | Q          | Germany      |             6
       47 | Q          | Italy        |             5
(6 rows)

Code Explanation:

The said query in SQL that selects a list of matches that were decided by penalty shootouts, along with their relevant information such as the stage of play, the name of the country that played in the match, and the penalty score.
The JOIN clause combines match_details and soccer_country tables based on the team_id and country_id columns.
The WHERE clause filters the results to only include matches that were decided by penalties. The 'P' is an abbreviation for penalty shootouts.
The results are sorted by match number in ascending order.

Relational Algebra Expression:

Relational Algebra Tree:

Practice Online

Sample Database: soccer

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

Previous SQL Exercise: Find the position of the player who scored an own goal.
Next SQL Exercise: Goals scored by players based on their position.