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 Tree:

Practice Online

Sample Database: soccer

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.