w3resource

SQL Exercise: Find the player who socred first goal of EURO cup 2016

SQL soccer Database: Joins Exercise-7 with Solution

7. From the following tables, write a SQL query to find out who scored the first goal of the 2016 European Championship. Return player_name, jersey_no, country_name, goal_time, play_stage, goal_schedule, goal_half.

Sample table: soccer_country


Sample table: player_mast


Sample table: goal_details


Sample Solution:

SQL Code:

SELECT a.player_name,a.jersey_no,b.country_name,c.goal_time,
c.play_stage,c.goal_schedule, c.goal_half 
FROM player_mast a
JOIN soccer_country b
ON a.team_id=b.country_id
JOIN goal_details c
ON c.player_id=a.player_id
WHERE goal_id=1;

Sample Output:

   player_name   | jersey_no | country_name | goal_time | play_stage | goal_schedule | goal_half
-----------------+-----------+--------------+-----------+------------+---------------+-----------
 Olivier Giroud  |         9 | France       |        57 | G          | NT            |         2
(1 row)

Code Explanation:

The provided query in SQL that retrieves information about the player who scored the goal with goal_id equal to 1, including their name, jersey number, country name, and details about the goal from the tables player_mast, soccer_country, and goal_details.
The JOIN clause joins the tables player_mast and soccer_country based on the team_id and country_id columns, respectively, while player_mast and goal_details are joined on the player_id column.
This condition filters only rows for the goal with goal_id equal to 1 .

Relational Algebra Expression:

Relational Algebra Expression: Find the player who socred first goal of EURO cup 2016.

Relational Algebra Tree:

Relational Algebra Tree: Find the player who socred first goal of EURO cup 2016.

Practice Online


Sample Database: soccer

soccer database relationship structure

Query Visualization:

Duration:

Query visualization of Find the player who socred first goal of EURO cup 2016 - Duration

Rows:

Query visualization of Find the player who socred first goal of EURO cup 2016 - Rows

Cost:

Query visualization of Find the player who socred first goal of EURO cup 2016 - Cost

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

Previous SQL Exercise: Find the country where Football EURO cup 2016 held.
Next SQL Exercise: Name and country of referee who managed the first game.

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