w3resource

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 Expression: Find the results of penalty shootout matches.

Relational Algebra Tree:

Relational Algebra Tree: Find the results of penalty shootout matches.

Practice Online


Sample Database: soccer

soccer database relationship structure

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.

What is the difficulty level of this exercise?

Test your Programming skills with w3resource's quiz.



Follow us on Facebook and Twitter for latest update.

SQL: Tips of the Day

What is the best way to paginate results in SQL Server?

SELECT  *
FROM    ( SELECT    ROW_NUMBER() OVER ( ORDER BY OrderDate ) AS RowNum, *
          FROM      Orders
          WHERE     OrderDate >= '1980-01-01'
        ) AS RowConstrainedResult
WHERE   RowNum >= 1
    AND RowNum < 20
ORDER BY RowNum

Database: SQL Server

Ref: https://bit.ly/3MGrNlk

 





We are closing our Disqus commenting system for some maintenanace issues. You may write to us at reach[at]yahoo[dot]com or visit us at Facebook