SQL Exercise: Find the defender who scored goal for his team

SQL soccer Database: Joins Exercise-57 with Solution

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

Sample table: player_mast

Sample table: soccer_country

Sample Solution:

SQL Code:

SELECT player_name,
       jersey_no,
       country_name,
       age,
       playing_club
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
WHERE posi_to_play='DF'
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.

Relational Algebra Expression:

Relational Algebra Tree:

Practice Online

Sample Database: soccer

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

Previous SQL Exercise: Find the player of each team who wear jersey number 10.
Next SQL Exercise: Find the position of the player who scored an own goal.