SQL Exercise: Number of shots missed or saved in penalty shootouts

Last update on December 20 2024 12:02:48 (UTC/GMT +8 hours)

24. From the following table, write a SQL query to count the number of shots missed or saved in penalty shootout matches. Return number of shots missed as "Goal missed or saved by Penalty Kicks".

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 Solution:

SQL Code:

-- This SQL query calculates the count of rows in the 'penalty_shootout' table
-- where the 'score_goal' column has the value 'N', indicating goals missed or saved by penalty kicks.
-- It aliases the result as "Goal missed or saved by Penalty Kicks".

SELECT COUNT(*) AS "Goal missed or saved by Penalty Kicks"
-- COUNT(*) is an aggregate function that counts the number of rows in a result set, and the alias is set to "Goal missed or saved by Penalty Kicks".
FROM penalty_shootout
-- 'penalty_shootout' is the name of the table being queried.
WHERE score_goal='N';
-- The WHERE clause filters rows where the 'score_goal' column has the value 'N'.

Sample Output:

 Goal missed or saved by Penalty Kicks
---------------------------------------
                                     9
(1 row)

Code Explanation:

The said query in SQL that counts the number of penalties that were either missed or saved by the goalkeeper from the table 'penalty_shootout' and aliases the result column as "Goal missed or saved by Penalty Kicks". The filter condition WHERE clause only consider those penalty kicks where a goal was not scored, i.e., the value of the "score_goal" column is 'N'.

Practice Online