﻿ SQL: Find the results of penalty shootout matches

# 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.

What is the difficulty level of this exercise?

Test your Programming skills with w3resource's quiz.

﻿

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