w3resource

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

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;

Practice Online


Sample Database: soccer

soccer database relationship structure

Query Visualization:

Duration:

Query visualization of Find the player of portugal who taken the 7th kick against poland - Duration

Rows:

Query visualization of Find the player of portugal who taken the 7th kick against poland - Rows

Cost:

Query visualization of Find the player of portugal who taken the 7th kick against poland - Cost

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

Previous SQL Exercise: Match highest number of penalty shots had been taken.
Next SQL Exercise: Find where the penalty kick number 23 had been taken.

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.