SQL Exercise: Two teams that scored three goals in a single game

SQL soccer Database: Joins Exercise-27 with Solution

27. From the following tables, write a SQL query to find the two teams in this tournament that have scored three goals in a single game. Return match number and country name.

Sample table: match_details

Sample table: soccer_country

Sample Solution:

SQL Code:

SELECT match_no,country_name
FROM match_details a
JOIN soccer_country b
ON a.team_id=b.country_id
WHERE goal_score=3  AND win_lose='D';

Sample Output:

 match_no | country_name
       34 | Hungary
       34 | Portugal
(2 rows)

Code Explanation:

The given query in SQL that retrieves the match number and country name of all matches where the goal score is 3 and the match resulted in a draw.
The uses of JOIN operation combines the information from two tables, match_details and soccer_country, based on their corresponding columns team_id and country_id, respectively.
The WHERE clause filters the results to only include matches with a goal score of 3 and a win/lose result of 'D' (draw).

Relational Algebra Expression:

Relational Algebra Expression: Find those two teams which scored three goals in a single game at this tournament.

Relational Algebra Tree:

Relational Algebra Tree: Find those two teams which scored three goals in a single game at this tournament.

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: Oldest player to have played in a EURO cup 2016 match.
Next SQL Exercise: Bottom of their groups and conceded 4 goals in 3 games.

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?

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

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