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,
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

How to drop all tables from a database with one SQL query?

USE Databasename

SELECT  'DROP TABLE [' + name + '];'
FROM    sys.tables

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


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