w3resource
Soccer Database Exercises

SQL Joins exercises on soccer Database: Find the goal scored by the players according to their playing position

SQL soccer Database: Joins Exercise-60 with Solution

60. Write a query in SQL to find the goal scored by the players according to their playing position.

Sample table: goal_details


Sample table: player_mast


Sample table: soccer_country


Sample Solution:

SQL Code:

SELECT country_name,
       posi_to_play,
       count(*) AS "Number of goals"
FROM goal_details a
JOIN player_mast b ON a.player_id=b.player_id
JOIN soccer_country c ON a.team_id=c.country_id
GROUP BY country_name,
         posi_to_play
ORDER BY country_name;

Sample Output:

    country_name     | posi_to_play | Number of goals
---------------------+--------------+-----------------
 Albania             | FD           |               1
 Austria             | MF           |               1
 Belgium             | DF           |               1
 Belgium             | FD           |               3
 Belgium             | MF           |               5
 Croatia             | FD           |               1
 Croatia             | MF           |               4
 Czech Republic      | FD           |               2
 England             | FD           |               3
 England             | MF           |               1
 France              | FD           |               9
 France              | MF           |               4
 Germany             | DF           |               1
 Germany             | FD           |               3
 Germany             | MF           |               3
 Hungary             | FD           |               4
 Hungary             | MF           |               1
 Iceland             | DF           |               2
 Iceland             | FD           |               4
 Iceland             | MF           |               3
 Italy               | DF           |               2
 Italy               | FD           |               3
 Italy               | MF           |               1
 Northern Ireland    | DF           |               2
 Northern Ireland    | FD           |               1
 Poland              | FD           |               2
 Poland              | MF           |               2
 Portugal            | FD           |               8
 Portugal            | MF           |               1
 Republic of Ireland | DF           |               1
 Republic of Ireland | MF           |               3
 Romania             | FD           |               2
 Russia              | DF           |               1
 Russia              | MF           |               1
 Slovakia            | MF           |               3
 Spain               | DF           |               1
 Spain               | FD           |               4
 Switzerland         | DF           |               1
 Switzerland         | FD           |               1
 Switzerland         | MF           |               1
 Turkey              | FD           |               1
 Turkey              | MF           |               1
 Wales               | DF           |               2
 Wales               | FD           |               6
 Wales               | MF           |               1
(45 rows)

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 results of penalty shootout matches.
Next: Write a query in SQL to find those players who came into the field in the most last time of play.

What is the difficulty level of this exercise?



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