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

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.

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