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
 kick_id | match_no | team_id | player_id | score_goal | kick_no
---------+----------+---------+-----------+------------+---------
       1 |       37 |    1221 |    160467 | Y          |       1
       2 |       37 |    1213 |    160297 | Y          |       2
       3 |       37 |    1221 |    160477 | N          |       3
       4 |       37 |    1213 |    160298 | Y          |       4
       5 |       37 |    1221 |    160476 | Y          |       5
       6 |       37 |    1213 |    160281 | Y          |       6
       7 |       37 |    1221 |    160470 | Y          |       7
       8 |       37 |    1213 |    160287 | Y          |       8
       9 |       37 |    1221 |    160469 | Y          |       9
      10 |       37 |    1213 |    160291 | Y          |      10
      11 |       45 |    1214 |    160322 | Y          |       1
      12 |       45 |    1213 |    160297 | Y          |       2
      13 |       45 |    1214 |    160316 | Y          |       3
      14 |       45 |    1213 |    160298 | Y          |       4
      15 |       45 |    1214 |    160314 | Y          |       5
      16 |       45 |    1213 |    160281 | Y          |       6
      17 |       45 |    1214 |    160320 | Y          |       7
      18 |       45 |    1213 |    160287 | N          |       8
      19 |       45 |    1214 |    160321 | Y          |       9
      20 |       47 |    1211 |    160251 | Y          |       1
      21 |       47 |    1208 |    160176 | Y          |       2
      22 |       47 |    1211 |    160253 | N          |       3
      23 |       47 |    1208 |    160183 | N          |       4
      24 |       47 |    1211 |    160234 | Y          |       5
      25 |       47 |    1208 |    160177 | N          |       6
      26 |       47 |    1211 |    160252 | N          |       7
      27 |       47 |    1208 |    160173 | Y          |       8
      28 |       47 |    1211 |    160235 | N          |       9
      29 |       47 |    1208 |    160180 | N          |      10
      30 |       47 |    1211 |    160244 | Y          |      11
      31 |       47 |    1208 |    160168 | Y          |      12
      32 |       47 |    1211 |    160246 | Y          |      13
      33 |       47 |    1208 |    160169 | Y          |      14
      34 |       47 |    1211 |    160238 | Y          |      15
      35 |       47 |    1208 |    160165 | Y          |      16
      36 |       47 |    1211 |    160237 | N          |      17
      37 |       47 |    1208 |    160166 | Y          |      18
Sample table: soccer_country
 country_id | country_abbr |    country_name
------------+--------------+---------------------
       1201 | ALB          | Albania
       1202 | AUT          | Austria
       1203 | BEL          | Belgium
       1204 | CRO          | Croatia
       1205 | CZE          | Czech Republic
       1206 | ENG          | England
       1207 | FRA          | France
       1208 | GER          | Germany
       1209 | HUN          | Hungary
       1210 | ISL          | Iceland
       1211 | ITA          | Italy
       1212 | NIR          | Northern Ireland
       1213 | POL          | Poland
       1214 | POR          | Portugal
       1215 | IRL          | Republic of Ireland
       1216 | ROU          | Romania
       1217 | RUS          | Russia
       1218 | SVK          | Slovakia
       1219 | ESP          | Spain
       1220 | SWE          | Sweden
       1221 | SUI          | Switzerland
       1222 | TUR          | Turkey
       1223 | UKR          | Ukraine
       1224 | WAL          | Wales
       1225 | SLO          | Slovenia
       1226 | NED          | Netherlands
       1227 | SRB          | Serbia
       1228 | SCO          | Scotland
       1229 | NOR          | Norway

Sample Solution:

SQL Code:

-- This query selects the match number and country name for penalty shootouts where the team is different from the team that took kick with kick_id 26.

SELECT 
    match_no, -- Selecting the match number
    country_name -- Selecting the country name
FROM 
    penalty_shootout a -- Specifying the penalty_shootout table with an alias 'a'
JOIN 
    soccer_country c ON a.team_id = c.country_id -- Joining the penalty_shootout table with the soccer_country table based on the team_id
WHERE 
    match_no = (
        -- Subquery to select the match number of the penalty shootout with kick_id 26
        SELECT 
            match_no 
        FROM 
            penalty_shootout 
        WHERE 
            kick_id = 26
    )
    AND country_name <> (
        -- Subquery to select the country name of the team that took kick with kick_id 26
        SELECT 
            country_name 
        FROM 
            soccer_country 
        WHERE 
            country_id = (
                -- Subquery to select the team ID of the team that took kick with kick_id 26
                SELECT 
                    team_id 
                FROM 
                    penalty_shootout 
                WHERE 
                    kick_id = 26
            )
    )
	-- Grouping the results by match number and country name
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.

Alternative Solutions:

Using JOINs and IN Clause:

-- This query selects the match number and country name for penalty shootouts where the team is different from the team that took kick with kick_id 26.

SELECT 
    ps.match_no, -- Selecting the match number from the penalty_shootout table aliased as 'ps'
    sc.country_name -- Selecting the country name from the soccer_country table aliased as 'sc'
FROM 
    penalty_shootout ps -- Specifying the penalty_shootout table with an alias 'ps'
JOIN 
    soccer_country sc ON ps.team_id = sc.country_id -- Joining the penalty_shootout table with the soccer_country table based on the team_id
WHERE 
    ps.match_no IN (
        -- Subquery to select the match numbers of the penalty shootouts with kick_id 26
        SELECT 
            match_no 
        FROM 
            penalty_shootout 
        WHERE 
            kick_id = 26
    )
    AND sc.country_name <> (
        -- Subquery to select the country name of the team that took kick with kick_id 26
        SELECT 
            country_name 
        FROM 
            soccer_country 
        WHERE 
            country_id IN (
                -- Subquery to select the team IDs of the teams that took kick with kick_id 26
                SELECT 
                    team_id 
                FROM 
                    penalty_shootout 
                WHERE 
                    kick_id = 26
            )
    )
	-- Grouping the results by match number and country name
GROUP BY 
    ps.match_no, sc.country_name; 

Explanation:

This query uses JOINs and the IN clause to filter and retrieve the desired results. It selects match numbers and team IDs based on kick_id=26, then compares country names.

Using JOINs and EXISTS Clause:

-- This query retrieves match numbers and country names for penalty shootouts where the team is different from the team that took kick with kick_id 26.

SELECT 
    ps.match_no, -- Selecting the match number from the penalty_shootout table aliased as 'ps'
    sc.country_name -- Selecting the country name from the soccer_country table aliased as 'sc'
FROM 
    penalty_shootout ps -- Specifying the penalty_shootout table with an alias 'ps'
JOIN 
    soccer_country sc ON ps.team_id = sc.country_id -- Joining the penalty_shootout table with the soccer_country table based on the team_id
WHERE 
    EXISTS (
        -- Subquery to check if there exists a record with kick_id 26 and the same match number as the outer query
        SELECT 
            1 
        FROM 
            penalty_shootout 
        WHERE 
            kick_id = 26 AND match_no = ps.match_no
    )
    AND NOT EXISTS (
        -- Subquery to check if there doesn't exist a record in the soccer_country table for the team that took kick with kick_id 26 and has the same country name as the team in the outer query
        SELECT 
            1 
        FROM 
            soccer_country 
        WHERE 
            country_id = (
                -- Subquery to select the team ID of the team that took kick with kick_id 26 and has the same match number as the outer query
                SELECT 
                    team_id 
                FROM 
                    penalty_shootout 
                WHERE 
                    kick_id = 26 AND match_no = ps.match_no
            )
            AND country_name = sc.country_name
    )
	-- Grouping the results by match number and country name
GROUP BY 
    ps.match_no, sc.country_name; 

Explanation:

This query uses JOINs and the EXISTS clause to filter and retrieve the desired results. It checks for the existence of match numbers with kick_id=26 and compares country names.

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.



Become a Patron!

Follow us on Facebook and Twitter for latest update.

It will be nice if you may share this link in any developer community or anywhere else, from where other developers may find this content. Thanks.

https://www.w3resource.com/sql-exercises/soccer-database-exercise/sql-joins-exercise-soccer-database-38.php