w3resource

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

SQL soccer Database: Exercise-24 with Solution

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


Sample Database: soccer

soccer database relationship structure

Query Visualization:

Duration:

Query visualization of Find the number of shots missed or saved in penalty shootout matches - Duration

Rows:

Query visualization of Find the number of shots missed or saved in penalty shootout matches - Rows

Cost:

Query visualization of Find the number of shots missed or saved in penalty shootout matches - Cost

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

Previous SQL Exercise: Find the number of shots scored in penalty shootouts.
Next SQL Exercise: List of players with shot numbers in penalty shootouts.

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.