w3resource

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 Expression: Find the matchs ending with a goalless draw in group stage of play.

Relational Algebra Tree:

Relational Algebra Tree: Find the matchs ending with a goalless draw in group stage of play.

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

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