SQL Exercise: Match where no stoppage time added in 1st half of play

SQL soccer Database: Joins Exercise-19 with Solution

19. From the following tables, write a SQL query to find the match where there was no stoppage time in the first half. Return match number, country name.

Sample table: match_details

Sample table: match_mast

Sample table: soccer_country

Sample Solution:

SQL Code:

SELECT match_details.match_no, soccer_country.country_name 
FROM match_mast
JOIN match_details 
ON match_mast.match_no=match_details.match_no
JOIN soccer_country
ON match_details.team_id=soccer_country.country_id
WHERE stop1_sec=0;

Sample Output:

 match_no | country_name
----------+--------------
        4 | England
        4 | Russia
(2 rows)

Code Explanation:

The given query in SQL that selects the match number and the name of the country associated with the team playing in that match from the tables match_mast, match_details, and soccer_country.
The JOIN clause is used to joins the tables using the match_no and team_id columns, which are primary keys in the match_mast and soccer_country tables respectively, and foreign keys in the match_details table.
The WHERE clause filters the results to only show matches where the stop1_sec column in the match_details table is equal to 0.

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 venue with number of goals that has seen.
Next SQL Exercise: Team(s) who conceded the most goals in EURO cup 2016.