w3resource

SQL Exercise: Find the number of shots scored in penalty shootouts

SQL soccer Database: Exercise-23 with Solution

23. From the following table, write a SQL query to count the number of shots that were scored in penalty shootouts matches. Return number of shots scored goal as "Goal Scored 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 'Y', and aliases the result as "Goal Scored by Penalty Kicks".

SELECT COUNT(*) AS "Goal Scored 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 Scored by Penalty Kicks".
FROM penalty_shootout
-- 'penalty_shootout' is the name of the table being queried.
WHERE score_goal='Y';
-- The WHERE clause filters rows where the 'score_goal' column has the value 'Y'.

Sample Output:

 Goal Scored by Penalty Kicks
------------------------------
                           28
(1 row)

Code Explanation:

The said query in SQL that counts the number of goals scored by penalty kicks from the table 'penalty_shootout' and aliases the result column as "Goal Scored by Penalty Kicks". The WHERE clause in the query will consider the total number of penalty kicks resulting in a goal being scored.

Practice Online


Sample Database: soccer

soccer database relationship structure

Query Visualization:

Duration:

Query visualization of Find the number of shots socred goal in penalty shootout matches - Duration

Rows:

Query visualization of Find the number of shots socred goal in penalty shootout matches - Rows

Cost:

Query visualization of Find the number of shots socred goal in penalty shootout matches - Cost

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

Previous SQL Exercise: Number of shots taken in penalty shootout matches.
Next SQL Exercise: Number of shots missed or saved 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.