w3resource

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 Expression: Find the scorer of only goal along with his country and jersey number in the final of EURO cup 2016.

Relational Algebra Tree:

Relational Algebra Tree: Find the scorer of only goal along with his country and jersey number in the final of EURO cup 2016.

Practice Online


Sample Database: soccer

soccer database relationship structure

Query Visualization:

Duration:

Query visualization of Find the scorer of only goal along with his country and jersey number in the final of EURO cup 2016 - Duration

Rows:

Query visualization of Find the scorer of only goal along with his country and jersey number in the final of EURO cup 2016 - Rows

Cost:

Query visualization of Find the scorer of only goal along with his country and jersey number in the final of EURO cup 2016 - 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.

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