w3resource

SQL Exercise: Find the player who took the 26th penalty shot

SQL soccer Database: Joins Exercise-37 with Solution

37. From the following tables, write a SQL query to find the player who took the penalty shot number 26. Return match number, country name, player name.

Sample table: penalty_shootout


Sample table: player_mast


Sample table: soccer_country


Sample Solution:

SQL Code:

SELECT match_no,
       country_name,
       player_name
FROM penalty_shootout a
JOIN player_mast b ON a.player_id=b.player_id
JOIN soccer_country c ON b.team_id=c.country_id
WHERE kick_id=26;

Sample Output:

 match_no | country_name |  player_name
----------+--------------+----------------
       47 | Italy        | Graziano Pelle
(1 row)

Code Explanation:

The said query in SQL that retrieves the match number, country name, and player name from the penalty_shootout table, player_mast table, and soccer_country table respectively, for the kick with kick_id 26.
The JOIN statements joins the penalty_shootout table aliased as "a", and the player_mast table aliased as "b" based on the player_id column, and then join the result with the soccer_country table aliased as "c" based on the team_id and country_id columns.
The WHERE clause filters the results to only include rows where the kick_id column is equal to 26.

Relational Algebra Expression:

Relational Algebra Expression: Find the player along with his country who taken the penalty shot number 26.

Relational Algebra Tree:

Relational Algebra Tree: Find the player along with his country who taken the penalty shot number 26.

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: List of the players of each match against each match.
Next SQL Exercise: Find the team that took penalty shot number 26.

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

How to drop all tables from a database with one SQL query?

USE Databasename

SELECT  'DROP TABLE [' + name + '];'
FROM    sys.tables

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

 





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