SQL Joins exercises on soccer Database: 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 those defenders who scored goal 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)

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: From the following tables, write a SQL query to find those players of each team who wore jersey number 10. Return country name, player name, position to play, age and playing club.
Next: From the following table, write a SQL query to find those players who accidentally scores against his own team. Return player name, jersey number, country name, age, position to play, and playing club.