w3resource

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 Expression: Find the defender who scored goal for his team.

Relational Algebra Tree:

Relational Algebra Tree: Find the defender who scored goal for his team.

Practice Online


Sample Database: soccer

soccer database relationship structure

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.

What is the difficulty level of this exercise?

Test your Programming skills with w3resource's quiz.



Follow us on Facebook and Twitter for latest update.

SQL: Tips of the Day

What is the best way to paginate results in SQL Server?

SELECT  *
FROM    ( SELECT    ROW_NUMBER() OVER ( ORDER BY OrderDate ) AS RowNum, *
          FROM      Orders
          WHERE     OrderDate >= '1980-01-01'
        ) AS RowConstrainedResult
WHERE   RowNum >= 1
    AND RowNum < 20
ORDER BY RowNum

Database: SQL Server

Ref: https://bit.ly/3MGrNlk

 





We are closing our Disqus commenting system for some maintenanace issues. You may write to us at reach[at]yahoo[dot]com or visit us at Facebook