w3resource
Soccer Database Exercises

SQL exercises on soccer Database: Find the number of goal scored by each team in every match within normal play schedule

SQL soccer Database: Joins Exercise-2 with Solution

2. Write a query in SQL to find the number of goal scored by each team in every match within normal play schedule.

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)

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

Practice Online


Sample Database: soccer

soccer database relationship structure

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

Previous: Write a query in SQL to find the name of the venue with city where the EURO cup 2016 final match was played.
Next: Write a query in SQL to find the total number of goals scored by each player within normal play schedule and arrange the result set according to the heighest to lowest scorer.

What is the difficulty level of this exercise?



New Content: Composer: Dependency manager for PHP, R Programming