SQL Exercise: Find the scorer of only goal along with his country

SQL soccer Database: Joins Exercise-5 with Solution

5. From the following table, write a SQL query to find out who scored in the final of the 2016 Euro Cup. Return player name, jersey number and country name.

Sample table: goal_details

Sample table: player_mast

Sample table: soccer_country

Sample Solution:

SQL Code:

SELECT player_name,jersey_no,country_name
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 play_stage='F';

Sample Output:

  player_name | jersey_no | country_name
-------------+-----------+--------------
 Eder        |         9 | Portugal
(1 row)

Code Explanation:

The above query in SQL that selects the player name, jersey number, and country name of all players who scored a goal in the final stage of a soccer tournament.
The JOIN clause joins the goal_details and player_mast tables based on the player_id column, and the goal_details and soccer_country tables using their respective columns team_id and country_id.
The results then filters to include only those goals scored in the final stage of the tournament.

Relational Algebra Expression:

Relational Algebra Tree:

Practice Online

Sample Database: soccer

Query Visualization:

Duration:

Rows:

Cost:

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

Previous SQL Exercise: Find the highest individual scorer in EURO cup 2016.
Next SQL Exercise: Find the country where Football EURO cup 2016 held.