SQL Exercise: Matches, one goal wins except decided by penalty kicks

Last update on January 13 2026 12:20:26 (UTC/GMT +8 hours)

14. From the following table, write a SQL query to calculate the number of matches that ended in a single goal win, excluding matches decided by penalty shootouts. Return number of matches.

Sample table: match_details

 match_no | play_stage | team_id | win_lose | decided_by | goal_score | penalty_score | ass_ref | player_gk
----------+------------+---------+----------+------------+------------+---------------+---------+-----------
        1 | G          |    1207 | W        | N          |          2 |               |   80016 |    160140
        1 | G          |    1216 | L        | N          |          1 |               |   80020 |    160348
        2 | G          |    1201 | L        | N          |          0 |               |   80003 |    160001
        2 | G          |    1221 | W        | N          |          1 |               |   80023 |    160463
        3 | G          |    1224 | W        | N          |          2 |               |   80031 |    160532
        3 | G          |    1218 | L        | N          |          1 |               |   80025 |    160392
        4 | G          |    1206 | D        | N          |          1 |               |   80008 |    160117
        4 | G          |    1217 | D        | N          |          1 |               |   80019 |    160369
        5 | G          |    1222 | L        | N          |          0 |               |   80011 |    160486
..........
       51 | F          |    1207 | L        | N          |          0 |               |   80007 |    160140

View the table

Sample Solution:

-- This SQL query calculates the count of non-null 'goal_score' values in the 'match_details' table
-- where certain conditions are met: 'win_lose' is 'W', 'decided_by' is not 'P', and 'goal_score' is 1.

SELECT COUNT(goal_score) 
-- COUNT(goal_score) counts the number of non-null 'goal_score' values in the result set.
FROM match_details 
-- 'match_details' is the name of the table being queried.
WHERE win_lose='W' 
-- The WHERE clause filters rows where the 'win_lose' column has the value 'W'.
AND decided_by<>'P'
-- Further filters rows where the 'decided_by' column is not equal to 'P'.
AND goal_score=1;
-- Further filters rows where the 'goal_score' column has the value 1.

Sample Output:

 count
-------
    13
(1 row)

Code Explanation:

The said query in SQL that selects the count of goal scores where the win/lose column has a value of 'W', the decided_by column does not have a value of 'P', and the goal_score column has a value of 1 from the table 'match_details'.
This query returns a single value that represents the number of times a team has won a match with a score of 1-0 in a match that was not decided by a penalty shootout.

Relational Algebra Expression: