SQL Exercise: Matches end in a goalless draw in group stage of play

SQL soccer Database: Joins Exercise-22 with Solution

22. From the following tables, write a SQL query to find the matches that ended in a goalless draw at the group stage. Return match number, country name.

Sample table: match_details

Sample table: soccer_country

Sample Solution:

SQL Code:

SELECT match_no,country_name 
FROM match_details
JOIN soccer_country ON soccer_country.country_id=match_details.team_id
WHERE win_lose='D' AND goal_score=0 AND play_stage='G' 
ORDER BY match_no;

Sample Output:

 match_no | country_name
----------+--------------
       18 | Germany
       18 | Poland
       24 | Austria
       24 | Portugal
       26 | Switzerland
       26 | France
       28 | England
       28 | Slovakia
(8 rows)

Code Explanation:

The said query in SQL that selects the match number and country name of all soccer matches from the tables 'match_details' and 'soccer_country' where the result was a draw, no goals were scored , and the match was played during the group stage . The results are ordered by match number.
The INNER JOIN is used to joins the 'match_details' and 'soccer_country' tables based on their common column 'team_id' and 'country_id', respectively.
The WHERE clause filters the results and include those rows for goal scored is 0 and match ended with a draw and the match was in group stage. The ORDER BY clause sorts the results by match number.

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: Matches with most stoppage time added in the 2nd half.
Next SQL Exercise: 2nd highest stoppage time in the 2nd half of matches.