SQL Exercise: Find the defender who scored goal for his team
57. From the following tables, write a SQL query to find the defenders who scored goals for their team. Return player name, jersey number, country name, age and playing club.
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 player name, jersey number, country name, age, and playing club
SELECT
player_name, -- Selecting the name of the player
jersey_no, -- Selecting the jersey number of the player
country_name, -- Selecting the name of the country
age, -- Selecting the age of the player
playing_club -- Selecting the club the player is currently playing for
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
posi_to_play = 'DF' -- Filtering records where the position to play is 'DF' (defender)
-- Ordering the results by player name
ORDER BY
player_name;
Sample Output:
player_name | jersey_no | country_name | age | playing_club
-------------------------+-----------+---------------------+-----+--------------
Arnor Ingvi Traustason | 21 | Iceland | 23 | Norrkoping
Ashley Williams | 6 | Wales | 31 | Swansea
Birkir Saevarsson | 2 | Iceland | 31 | Hammarby
Ciaran Clark | 3 | Republic of Ireland | 26 | Aston Villa
Fabian Schar | 22 | Switzerland | 24 | Hoffenheim
Gareth McAuley | 4 | Northern Ireland | 36 | West Brom
Gareth McAuley | 4 | Northern Ireland | 36 | West Brom
Gerard Pique | 3 | Spain | 29 | Barcelona
Giorgio Chiellini | 3 | Italy | 31 | Juventus
Jerome Boateng | 17 | Germany | 27 | Bayern
Leonardo Bonucci | 19 | Italy | 29 | Juventus
Neil Taylor | 3 | Wales | 27 | Swansea
Toby Alderweireld | 2 | Belgium | 27 | Tottenham
Vasili Berezutski | 14 | Russia | 33 | CSKA Moskva
(14 rows)
Code Explanation:
The said query in SQL that selects player_name , jersey_no, country_name, age, and playing_club from three different tables (goal_details, player_mast, and soccer_country) using JOIN operations and filters the results based on a condition in the WHERE clause. The result set is then sorted in alphabetical order by player_name.
The goal_details and player_mast tables are joined based on the player_id column, and the goal_details and soccer_country tables are joined based on the team_id and country_id columns.
The WHERE clause filters only those records where the posi_to_play column in the goal_details table is equal to 'DF' will be returned.
The final result set is sorted by player_name in alphabetical order.
Alternative Solutions:
Using a Subquery with IN Clause:
-- Selecting player information including name, jersey number, country name, age, and playing club
SELECT
player_name, -- Selecting the name of the player
jersey_no, -- Selecting the jersey number of the player
country_name, -- Selecting the name of the country
age, -- Selecting the age of the player
playing_club -- Selecting the club the player is currently playing for
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.posi_to_play = 'DF' -- Filtering records where the position to play is 'DF' (defender)
AND b.team_id IN ( -- Filtering records where the team ID is in the list of country IDs
SELECT
country_id -- Selecting the country IDs
FROM
soccer_country -- Selecting from the soccer_country table
)
-- Ordering the results by player name
ORDER BY
b.player_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 player information including name, jersey number, country name, age, and playing club
SELECT
player_name, -- Selecting the name of the player
jersey_no, -- Selecting the jersey number of the player
country_name, -- Selecting the name of the country
age, -- Selecting the age of the player
playing_club -- Selecting the club the player is currently playing for
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.posi_to_play = 'DF' -- Filtering records where the position to play is 'DF' (defender)
AND EXISTS ( -- Ensuring that there exists at least one record in the subquery
SELECT 1 -- Selecting '1' to indicate existence
FROM
soccer_country d -- Selecting from the soccer_country table with alias 'd'
WHERE
b.team_id = d.country_id -- Checking if the team ID exists in the subquery
)
-- Ordering the results by player name
ORDER BY
b.player_name;
Explanation:
This query uses the EXISTS clause to check for the existence of a matching country_id in the soccer_country table.
Relational Algebra Expression:
Relational Algebra Tree:
Go to:
PREV : Find the player of each team who wear jersey number 10.
NEXT : Find the position of the player who scored an own goal.
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.