w3resource

SQL Exercise: Find the team that took penalty shot number 26

SQL soccer Database: Joins Exercise-38 with Solution

38. From the following tables, write a SQL query to find the team against which the penalty shot number 26 was taken. Return match number, country name.

Sample table: penalty_shootout


Sample table: soccer_country


Sample Solution:

SQL Code:

SELECT match_no,
       country_name
FROM penalty_shootout a
JOIN soccer_country c ON a.team_id=c.country_id
WHERE match_no=
    (SELECT match_no
     FROM penalty_shootout
     WHERE kick_id=26)
  AND country_name<>
    (SELECT country_name
     FROM soccer_country
     WHERE country_id=
         (SELECT team_id
          FROM penalty_shootout
          WHERE kick_id=26))
GROUP BY match_no,
         country_name;

Sample Output:

 match_no | country_name
----------+--------------
       47 | Germany
(1 row)

Code Explanation:

The said query in SQL that retrieves the match number and country name from the penalty_shootout table and the soccer_country table respectively, for the same match as the kick with kick_id 26 but for a different team. It also groups the results by match number and country name.
The JOIN clause joins the penalty_shootout table aliased as "a" and 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 match_no column is equal to the match number of the kick with kick_id 26. This is achieved by using a subquery to select the match number from the penalty_shootout table where kick_id=26.
The another WHERE clause also filters the results to exclude rows where the country_name column is equal to the country name of the team that took the kick with kick_id 26. This is achieved by using a subquery to select the team_id from the penalty_shootout table where kick_id=26, and then using another subquery to select the country_name from the soccer_country table where country_id is equal to the selected team_id.
The GROUP BY statement groups the results by match_no and country_name.

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: Find the player who took the 26th penalty shot.
Next SQL Exercise: Find the captain who was also the goalkeeper.

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