SQL Exercise: Goals scored by players based on their position
60. From the following table, write a SQL query to find the goal scored by each player according to their position. Return country name, position to play, and number of goals.
Sample table: goal_details
goal_id | match_no | player_id | team_id | goal_time | goal_type | play_stage | goal_schedule | goal_half
---------+----------+-----------+---------+-----------+-----------+------------+---------------+-----------
1 | 1 | 160159 | 1207 | 57 | N | G | NT | 2
2 | 1 | 160368 | 1216 | 65 | P | G | NT | 2
3 | 1 | 160154 | 1207 | 89 | N | G | NT | 2
4 | 2 | 160470 | 1221 | 5 | N | G | NT | 1
5 | 3 | 160547 | 1224 | 10 | N | G | NT | 1
6 | 3 | 160403 | 1218 | 61 | N | G | NT | 2
7 | 3 | 160550 | 1224 | 81 | N | G | NT | 2
8 | 4 | 160128 | 1206 | 73 | N | G | NT | 2
9 | 4 | 160373 | 1217 | 93 | N | G | ST | 2
.........
108 | 51 | 160319 | 1214 | 109 | N | F | ET | 2
Sample table: player_mast
player_id | team_id | jersey_no | player_name | posi_to_play | dt_of_bir | age | playing_club
-----------+---------+-----------+-------------------------+--------------+------------+-----+---------------------
160001 | 1201 | 1 | Etrit Berisha | GK | 1989-03-10 | 27 | Lazio
160008 | 1201 | 2 | Andi Lila | DF | 1986-02-12 | 30 | Giannina
160016 | 1201 | 3 | Ermir Lenjani | MF | 1989-08-05 | 26 | Nantes
160007 | 1201 | 4 | Elseid Hysaj | DF | 1994-02-20 | 22 | Napoli
160013 | 1201 | 5 | Lorik Cana | MF | 1983-07-27 | 32 | Nantes
160010 | 1201 | 6 | Frederic Veseli | DF | 1992-11-20 | 23 | Lugano
160004 | 1201 | 7 | Ansi Agolli | DF | 1982-10-11 | 33 | Qarabag
160012 | 1201 | 8 | Migjen Basha | MF | 1987-01-05 | 29 | Como
160017 | 1201 | 9 | Ledian Memushaj | MF | 1986-12-17 | 29 | Pescara
........
160548 | 1224 | 23 | Simon Church | FD | 1988-12-10 | 27 | MK Dons
Sample table: soccer_country
country_id | country_abbr | country_name
------------+--------------+---------------------
1201 | ALB | Albania
1202 | AUT | Austria
1203 | BEL | Belgium
1204 | CRO | Croatia
1205 | CZE | Czech Republic
1206 | ENG | England
1207 | FRA | France
1208 | GER | Germany
1209 | HUN | Hungary
.......
1229 | NOR | Norway
Sample Solution:
SQL Code:
-- Selecting the country name, playing position, and the number of goals scored by players in each position
SELECT
country_name, -- Selecting the name of the country
posi_to_play, -- Selecting the playing position
count(*) AS "Number of goals" -- Counting the number of goals scored
FROM
goal_details a -- Selecting from the goal_details table with alias 'a'
JOIN
player_mast b ON a.player_id = b.player_id -- Joining with the player_mast table with alias 'b' based on player ID
JOIN
soccer_country c ON a.team_id = c.country_id -- Joining with the soccer_country table with alias 'c' based on team ID
GROUP BY
country_name, -- Grouping the results by country name
posi_to_play -- Grouping the results by playing position
-- Ordering the results by country name
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)
Code Explanation:
The given query in SQL that returns a list of countries and positions to play, along with the number of goals scored for each combination. The results are grouped by country and position, and sorted by country name.
The JOIN clause combines the goal_details and player_mast tables based on the player_id column and goal_details and soccer_country tables based on the team_id and country_id columns.
The GROUP BY clause is used to aggregate data based on one or more columns.
The result set groups by the country name and position to play columns.
This sorts the results by country name in alphabetical order.
Alternative Solutions:
Using a Self-Join:
-- Selecting the country name, playing position, and the number of goals scored by players in each position
SELECT
c.country_name, -- Selecting the name of the country
b.posi_to_play, -- Selecting the playing position
COUNT(*) AS "Number of goals" -- Counting the number of goals scored
FROM
goal_details a -- Selecting from the goal_details table with alias 'a'
JOIN
player_mast b ON a.player_id = b.player_id -- Joining with the player_mast table with alias 'b' based on player ID
JOIN
player_mast d ON a.team_id = d.team_id -- Joining with the player_mast table with alias 'd' based on team ID
JOIN
soccer_country c ON d.team_id = c.country_id -- Joining with the soccer_country table with alias 'c' based on team ID
GROUP BY
c.country_name, -- Grouping the results by country name
b.posi_to_play -- Grouping the results by playing position
-- Ordering the results by country name
ORDER BY
c.country_name;
Explanation:
This query uses a self-join on the player_mast table to establish a relationship between the player_id and country_id. It then joins with the soccer_country table to get the country names.
Using a Subquery with IN Clause:
-- Selecting the country name, playing position, and the number of goals scored by players in each position
SELECT
country_name, -- Selecting the name of the country
posi_to_play, -- Selecting the playing position
COUNT(*) AS "Number of goals" -- Counting the number of goals scored
FROM
goal_details a -- Selecting from the goal_details table with alias 'a'
JOIN
player_mast b ON a.player_id = b.player_id -- Joining with the player_mast table with alias 'b' based on player ID
JOIN
soccer_country c ON a.team_id = c.country_id -- Joining with the soccer_country table with alias 'c' based on team ID
WHERE
b.team_id IN ( -- Filtering based on team ID present in the soccer_country table
SELECT country_id -- Subquery to select country IDs
FROM soccer_country -- Subquery selecting from the soccer_country table
)
GROUP BY
country_name, -- Grouping the results by country name
posi_to_play -- Grouping the results by playing position
-- Ordering the results by country name
ORDER BY
country_name;
Explanation:
This query uses a subquery with the IN clause to filter the results based on the country_id. It ensures that the country_id is present in the soccer_country table.
Using EXISTS Clause:
-- Selecting the country name, playing position, and the number of goals scored by players in each position
SELECT
country_name, -- Selecting the name of the country
posi_to_play, -- Selecting the playing position
COUNT(*) AS "Number of goals" -- Counting the number of goals scored
FROM
goal_details a -- Selecting from the goal_details table with alias 'a'
JOIN
player_mast b ON a.player_id = b.player_id -- Joining with the player_mast table with alias 'b' based on player ID
JOIN
soccer_country c ON a.team_id = c.country_id -- Joining with the soccer_country table with alias 'c' based on team ID
WHERE
EXISTS ( -- Checking for the existence of a related record in the soccer_country table
SELECT 1 -- Subquery to select '1'
FROM soccer_country d -- Subquery selecting from the soccer_country table with alias 'd'
WHERE b.team_id = d.country_id -- Matching team IDs
)
GROUP BY
country_name, -- Grouping the results by country name
posi_to_play -- Grouping the results by playing position
-- Ordering the results by country name
ORDER BY
country_name;
Explanation:
This query uses the EXISTS clause to check for the existence of a matching country_id in the soccer_country table.
Go to:
PREV : Find the results of penalty shootout matches.
NEXT : Players who entered the field in the most recent play.
Practice Online
Sample Database: soccer
Have another way to solve this solution? Contribute your code (and comments) through Disqus.
What is the difficulty level of this exercise?
Test your Programming skills with w3resource's quiz.