w3resource

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 Expression: Find the match where no stoppage time added in 1st half of play.

Relational Algebra Tree:

Relational Algebra Tree: Find the match where no stoppage time added in 1st half 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: Find the venue with number of goals that has seen.
Next SQL Exercise: Team(s) who conceded the most goals in EURO cup 2016.

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?

SELECT  *
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
ORDER BY RowNum

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