w3resource
Soccer Database Exercises

SQL Joins exercises on soccer Database: Find the defender who scored goal for his team

SQL soccer Database: Joins Exercise-57 with Solution

57. Write a query in SQL to find the defender who scored goal for his team.

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)

Practice Online


Sample Database: soccer

soccer database relationship structure

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

Previous: Write a query in SQL to find the player of each team who wear jersey number 10.
Next: Write a query in SQL to find the position of a player to play who scored own goal.

What is the difficulty level of this exercise?



New Content: Composer: Dependency manager for PHP, R Programming