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. From the following table, write a SQL query to find the goal scored by the players according to their playing position. Return country name, position to play, number of goals.

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

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

Previous: From the following table, write a SQL query to find the results of penalty shootout matches. Return match number, play stage, country name and penalty score.
Next: From the following tables, write a SQL query to find those players who came into the field at the last time of play. Return match number, country name, player name, jersey number and time in out.