SQL Exercise: Player of portugal taken the 7th kick against poland

Last update on June 06 2024 13:19:30 (UTC/GMT +8 hours)

SQL soccer Database: Subqueries Exercise-29 with Solution

From the following table, write a SQL query to find the player of ‘Portugal’ who took the seventh kick against ‘Poland’. Return match number, player name and kick number.

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:

SELECT a.match_no,
       b.player_name,
       a.kick_no
FROM penalty_shootout a,
     player_mast b
WHERE a.player_id=b.player_id
  AND kick_no=7
  AND match_no=
    (SELECT match_no
     FROM penalty_shootout
     WHERE team_id =
         (SELECT country_id
          FROM soccer_country
          WHERE country_name='Portugal' )
     GROUP BY match_no)
GROUP BY match_no,
         player_name,
         kick_id;

Sample Output:

 match_no | player_name | kick_no
----------+-------------+---------
       45 | Nani        |       7
(1 row)

Code Explanation:

The said query in SQL that selects the match number, player name, and kick number for penalty shootout kicks that occurred in the 7th round of penalty kicks for a specific match
from penalty_shootout and player_mast table in which Portugal was one of the teams playing.
The first condition joins the two tables based on the player_id column.
The second condition specified in the query should only retrieve data for the 7th kick in the penalty shootout.
The third condition retrieves data for a specific match in which Portugal was one of the teams playing. This condition uses a subquery to find the match_no for the specified match. The GROUP BY clause groups the data by match_no, player_name, and kick_id.

Alternative Solution:

Subquery with MAX and HAVING:


SELECT a.match_no, b.player_name, a.kick_no
FROM penalty_shootout a
JOIN player_mast b ON a.player_id = b.player_id
WHERE a.kick_no = 7
  AND a.match_no = (
    SELECT match_no
    FROM penalty_shootout
    WHERE team_id = (
      SELECT country_id
      FROM soccer_country
      WHERE country_name = 'Portugal'
    )
    GROUP BY match_no
    HAVING COUNT(*) > 0
  )
GROUP BY a.match_no, b.player_name, a.kick_no;

JOIN with Subquery:


SELECT a.match_no, b.player_name, a.kick_no
FROM penalty_shootout a
JOIN player_mast b ON a.player_id = b.player_id
JOIN (
  SELECT match_no
  FROM penalty_shootout
  WHERE team_id = (
    SELECT country_id
    FROM soccer_country
    WHERE country_name = 'Portugal'
  )
  GROUP BY match_no
  HAVING COUNT(*) > 0
) c ON a.match_no = c.match_no
WHERE a.kick_no = 7
GROUP BY a.match_no, b.player_name, a.kick_no;

Using EXISTS:


SELECT a.match_no, b.player_name, a.kick_no
FROM penalty_shootout a
JOIN player_mast b ON a.player_id = b.player_id
WHERE a.kick_no = 7
  AND EXISTS (
    SELECT 1
    FROM penalty_shootout p
    WHERE p.match_no = a.match_no
      AND p.team_id = (
        SELECT country_id
        FROM soccer_country
        WHERE country_name = 'Portugal'
      )
  )
GROUP BY a.match_no, b.player_name, a.kick_no;

Using JOIN with Subquery:


SELECT a.match_no, b.player_name, a.kick_no
FROM penalty_shootout a
JOIN player_mast b ON a.player_id = b.player_id
JOIN (
  SELECT match_no
  FROM penalty_shootout
  WHERE team_id = (
    SELECT country_id
    FROM soccer_country
    WHERE country_name = 'Portugal'
  )
  GROUP BY match_no
  HAVING COUNT(*) > 0
) c ON a.match_no = c.match_no
WHERE a.kick_no = 7
GROUP BY a.match_no, b.player_name, a.kick_no;