w3resource

SQL Exercise: Number of goals scored by each team in each match

SQL soccer Database: Joins Exercise-2 with Solution

2. From the following tables, write a SQL query to find the number of goals scored by each team in each match during normal play. Return match number, country name and goal score.

Sample table: match_details


Sample table: soccer_country


Sample Solution:

SQL Code:

SELECT match_no,country_name,goal_score
FROM match_details a
JOIN soccer_country b
ON a.team_id=b.country_id
WHERE decided_by='N'
ORDER BY match_no;

Sample Output:

 match_no |    country_name     | goal_score
----------+---------------------+------------
        1 | France              |          2
        1 | Romania             |          1
        2 | Albania             |          0
        2 | Switzerland         |          1
        3 | Wales               |          2
        3 | Slovakia            |          1
        4 | England             |          1
        4 | Russia              |          1
        5 | Turkey              |          0
        5 | Croatia             |          1
        6 | Poland              |          1
        6 | Northern Ireland    |          0
        7 | Germany             |          2
        7 | Ukraine             |          0
        8 | Spain               |          1
        8 | Czech Republic      |          0
        9 | Republic of Ireland |          1
        9 | Sweden              |          1
       10 | Belgium             |          0
       10 | Italy               |          2
       11 | Austria             |          0
       11 | Hungary             |          2
       12 | Portugal            |          1
       12 | Iceland             |          1
       13 | Russia              |          1
       13 | Slovakia            |          2
       14 | Romania             |          1
       14 | Switzerland         |          1
       15 | France              |          2
       15 | Albania             |          0
       16 | England             |          2
       16 | Wales               |          1
       17 | Ukraine             |          0
       17 | Northern Ireland    |          2
       18 | Germany             |          0
       18 | Poland              |          0
       19 | Italy               |          1
       19 | Sweden              |          0
       20 | Czech Republic      |          2
       20 | Croatia             |          2
       21 | Spain               |          3
       21 | Turkey              |          0
       22 | Belgium             |          3
       22 | Republic of Ireland |          0
       23 | Iceland             |          1
       23 | Hungary             |          1
       24 | Portugal            |          0
       24 | Austria             |          0
       25 | Romania             |          0
       25 | Albania             |          1
       26 | Switzerland         |          0
       26 | France              |          0
       27 | Russia              |          0
       27 | Wales               |          3
       28 | Slovakia            |          0
       28 | England             |          0
       29 | Ukraine             |          0
       29 | Poland              |          1
       30 | Northern Ireland    |          0
       30 | Germany             |          1
       31 | Czech Republic      |          0
       31 | Turkey              |          2
       32 | Croatia             |          2
       32 | Spain               |          1
       33 | Iceland             |          2
       33 | Austria             |          1
       34 | Hungary             |          3
       34 | Portugal            |          3
       35 | Italy               |          0
       35 | Republic of Ireland |          1
       36 | Sweden              |          0
       36 | Belgium             |          1
       38 | Wales               |          1
       38 | Northern Ireland    |          0
       39 | Croatia             |          0
       39 | Portugal            |          1
       40 | France              |          2
       40 | Republic of Ireland |          1
       41 | Germany             |          3
       41 | Slovakia            |          0
       42 | Hungary             |          0
       42 | Belgium             |          4
       43 | Italy               |          2
       43 | Spain               |          0
       44 | England             |          1
       44 | Iceland             |          2
       46 | Wales               |          3
       46 | Belgium             |          1
       48 | France              |          5
       48 | Iceland             |          2
       49 | Portugal            |          2
       49 | Wales               |          0
       50 | France              |          2
       50 | Germany             |          1
       51 | Portugal            |          1
       51 | France              |          0
(96 rows)

Code Explanation:

The said query in SQL that returns a list of match numbers, the corresponding country names, and the goal score for each match by normal goal.
The query uses a JOIN operation to link the match_details and soccer_country tables based on the common country_id column .
The WHERE clause filters the result set to only include rows where the decided_by column equals 'N', which is a normal goal.
The ORDER BY clause sorts the result set in ascending order based on the match_no column.

Relational Algebra Expression:

Relational Algebra Expression: Find the number of goal scored by each team in every match within normal play schedule.

Relational Algebra Tree:

Relational Algebra Tree: Find the number of goal scored by each team in every match within normal play schedule.

Practice Online


Sample Database: soccer

soccer database relationship structure

Query Visualization:

Duration:

Query visualization of Find the number of goal scored by each team in every match within normal play schedule - Duration

Rows:

Query visualization of Find the number of goal scored by each team in every match within normal play schedule - Rows

Cost:

Query visualization of Find the number of goal scored by each team in every match within normal play schedule - Cost

Have another way to solve this solution? Contribute your code (and comments) through Disqus.

Previous SQL Exercise: Where was the final match of the EURO cup 2016 held?.
Next SQL Exercise: Goals scored by each player during normal play.

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