w3resource

SQL Exercise: Number of matches played at each venue and their city

SQL soccer Database: Joins Exercise-14 with Solution

14. From the following tables, write a SQL query to count the number of matches played at each venue. Sort the result-set on venue name. Return Venue name, city, and number of matches.

Sample table:soccer_venue
 venue_id |       venue_name        | city_id | aud_capacity
----------+-------------------------+---------+--------------
    20001 | Stade de Bordeaux       |   10003 |        42115
    20002 | Stade Bollaert-Delelis  |   10004 |        38223
    20003 | Stade Pierre Mauroy     |   10005 |        49822
    20004 | Stade de Lyon           |   10006 |        58585
    20005 | Stade VElodrome         |   10007 |        64354
    20006 | Stade de Nice           |   10008 |        35624
    20007 | Parc des Princes        |   10001 |        47294
    20008 | Stade de France         |   10002 |        80100
    20009 | Stade Geoffroy Guichard |   10009 |        42000
    20010 | Stadium de Toulouse     |   10010 |        33150
Sample table: soccer_city
 city_id |     city      | country_id
---------+---------------+------------
   10001 | Paris         |       1207
   10002 | Saint-Denis   |       1207
   10003 | Bordeaux      |       1207
   10004 | Lens          |       1207
   10005 | Lille         |       1207
   10006 | Lyon          |       1207
   10007 | Marseille     |       1207
   10008 | Nice          |       1207
   10009 | Saint-Etienne |       1207
   10010 | Toulouse      |       1207
Sample table: match_mast
 match_no | play_stage | play_date  | results | decided_by | goal_score | venue_id | referee_id | audence | plr_of_match | stop1_sec | stop2_sec
----------+------------+------------+---------+------------+------------+----------+------------+---------+--------------+-----------+-----------
        1 | G          | 2016-06-11 | WIN     | N          | 2-1        |    20008 |      70007 |   75113 |       160154 |       131 |       242
        2 | G          | 2016-06-11 | WIN     | N          | 0-1        |    20002 |      70012 |   33805 |       160476 |        61 |       182
        3 | G          | 2016-06-11 | WIN     | N          | 2-1        |    20001 |      70017 |   37831 |       160540 |        64 |       268
        4 | G          | 2016-06-12 | DRAW    | N          | 1-1        |    20005 |      70011 |   62343 |       160128 |         0 |       185
        5 | G          | 2016-06-12 | WIN     | N          | 0-1        |    20007 |      70006 |   43842 |       160084 |       125 |       325
        6 | G          | 2016-06-12 | WIN     | N          | 1-0        |    20006 |      70014 |   33742 |       160291 |         2 |       246
        7 | G          | 2016-06-13 | WIN     | N          | 2-0        |    20003 |      70002 |   43035 |       160176 |        89 |       188
        8 | G          | 2016-06-13 | WIN     | N          | 1-0        |    20010 |      70009 |   29400 |       160429 |       360 |       182
        9 | G          | 2016-06-13 | DRAW    | N          | 1-1        |    20008 |      70010 |   73419 |       160335 |        67 |       194
       10 | G          | 2016-06-14 | WIN     | N          | 0-2        |    20004 |      70005 |   55408 |       160244 |        63 |       189
       11 | G          | 2016-06-14 | WIN     | N          | 0-2        |    20001 |      70018 |   34424 |       160197 |        61 |       305
       12 | G          | 2016-06-15 | DRAW    | N          | 1-1        |    20009 |      70004 |   38742 |       160320 |        15 |       284
       13 | G          | 2016-06-15 | WIN     | N          | 1-2        |    20003 |      70001 |   38989 |       160405 |        62 |       189
       14 | G          | 2016-06-15 | DRAW    | N          | 1-1        |    20007 |      70015 |   43576 |       160477 |        74 |       206
       15 | G          | 2016-06-16 | WIN     | N          | 2-0        |    20005 |      70013 |   63670 |       160154 |        71 |       374
       16 | G          | 2016-06-16 | WIN     | N          | 2-1        |    20002 |      70003 |   34033 |       160540 |        62 |       212
       17 | G          | 2016-06-16 | WIN     | N          | 0-2        |    20004 |      70016 |   51043 |       160262 |         7 |       411
       18 | G          | 2016-06-17 | DRAW    | N          | 0-0        |    20008 |      70008 |   73648 |       160165 |         6 |       208
       19 | G          | 2016-06-17 | WIN     | N          | 1-0        |    20010 |      70007 |   29600 |       160248 |         2 |       264
       20 | G          | 2016-06-17 | DRAW    | N          | 2-2        |    20009 |      70005 |   38376 |       160086 |        71 |       280
       21 | G          | 2016-06-18 | WIN     | N          | 3-0        |    20006 |      70010 |   33409 |       160429 |        84 |       120
       22 | G          | 2016-06-18 | WIN     | N          | 3-0        |    20001 |      70004 |   39493 |       160064 |        11 |       180
       23 | G          | 2016-06-18 | DRAW    | N          | 1-1        |    20005 |      70015 |   60842 |       160230 |        61 |       280
       24 | G          | 2016-06-19 | DRAW    | N          | 0-0        |    20007 |      70011 |   44291 |       160314 |         3 |       200
       25 | G          | 2016-06-20 | WIN     | N          | 0-1        |    20004 |      70016 |   49752 |       160005 |       125 |       328
       26 | G          | 2016-06-20 | DRAW    | N          | 0-0        |    20003 |      70001 |   45616 |       160463 |        60 |       122
       27 | G          | 2016-06-21 | WIN     | N          | 0-3        |    20010 |      70006 |   28840 |       160544 |        62 |       119
       28 | G          | 2016-06-21 | DRAW    | N          | 0-0        |    20009 |      70012 |   39051 |       160392 |        62 |       301
       29 | G          | 2016-06-21 | WIN     | N          | 0-1        |    20005 |      70017 |   58874 |       160520 |        29 |       244
       30 | G          | 2016-06-21 | WIN     | N          | 0-1        |    20007 |      70018 |   44125 |       160177 |        21 |       195
       31 | G          | 2016-06-22 | WIN     | N          | 0-2        |    20002 |      70013 |   32836 |       160504 |        60 |       300
       32 | G          | 2016-06-22 | WIN     | N          | 2-1        |    20001 |      70008 |   37245 |       160085 |        70 |       282
       33 | G          | 2016-06-22 | WIN     | N          | 2-1        |    20008 |      70009 |   68714 |       160220 |         7 |       244
       34 | G          | 2016-06-22 | DRAW    | N          | 3-3        |    20004 |      70002 |   55514 |       160322 |        70 |       185
       35 | G          | 2016-06-23 | WIN     | N          | 0-1        |    20003 |      70014 |   44268 |       160333 |        79 |       221
       36 | G          | 2016-06-23 | WIN     | N          | 0-1        |    20006 |      70003 |   34011 |       160062 |        63 |       195
       37 | R          | 2016-06-25 | WIN     | P          | 1-1        |    20009 |      70005 |   38842 |       160476 |       126 |       243
       38 | R          | 2016-06-25 | WIN     | N          | 1-0        |    20007 |      70002 |   44342 |       160547 |         5 |       245
       39 | R          | 2016-06-26 | WIN     | N          | 0-1        |    20002 |      70012 |   33523 |       160316 |        61 |       198
       40 | R          | 2016-06-26 | WIN     | N          | 2-1        |    20004 |      70011 |   56279 |       160160 |       238 |       203
       41 | R          | 2016-06-26 | WIN     | N          | 3-0        |    20003 |      70009 |   44312 |       160173 |        62 |       124
       42 | R          | 2016-06-27 | WIN     | N          | 0-4        |    20010 |      70010 |   28921 |       160062 |         3 |       133
       43 | R          | 2016-06-27 | WIN     | N          | 2-0        |    20008 |      70004 |   76165 |       160235 |        63 |       243
       44 | R          | 2016-06-28 | WIN     | N          | 1-2        |    20006 |      70001 |   33901 |       160217 |         5 |       199
       45 | Q          | 2016-07-01 | WIN     | P          | 1-1        |    20005 |      70003 |   62940 |       160316 |        58 |       181
       46 | Q          | 2016-07-02 | WIN     | N          | 3-1        |    20003 |      70001 |   45936 |       160550 |        14 |       182
       47 | Q          | 2016-07-03 | WIN     | P          | 1-1        |    20001 |      70007 |   38764 |       160163 |        63 |       181
       48 | Q          | 2016-07-04 | WIN     | N          | 5-2        |    20008 |      70008 |   76833 |       160159 |        16 |       125
       49 | S          | 2016-07-07 | WIN     | N          | 2-0        |    20004 |      70006 |   55679 |       160322 |         2 |       181
       50 | S          | 2016-07-08 | WIN     | N          | 2-0        |    20005 |      70011 |   64078 |       160160 |       126 |       275
       51 | F          | 2016-07-11 | WIN     | N          | 1-0        |    20008 |      70005 |   75868 |       160307 |       161 |       181

Sample Solution:

SQL Code:

-- Selecting venue_name, city, and the count of matches for each venue
SELECT a.venue_name, b.city, COUNT(c.match_no)
-- From clause with JOINs between soccer_venue, soccer_city, and match_mast
FROM soccer_venue a
JOIN soccer_city b ON a.city_id = b.city_id
JOIN match_mast c ON a.venue_id = c.venue_id
-- Grouping the results by venue_name and city to count matches for each venue
GROUP BY venue_name, city
-- Ordering the results by venue_name
ORDER BY venue_name;

Sample Output:

       venue_name        |     city      | count
-------------------------+---------------+-------
 Parc des Princes        | Paris         |     5
 Stade Bollaert-Delelis  | Lens          |     4
 Stade de Bordeaux       | Bordeaux      |     5
 Stade de France         | Saint-Denis   |     7
 Stade de Lyon           | Lyon          |     6
 Stade de Nice           | Nice          |     4
 Stade Geoffroy Guichard | Saint-Etienne |     4
 Stade Pierre Mauroy     | Lille         |     6
 Stade VElodrome         | Marseille     |     6
 Stadium de Toulouse     | Toulouse      |     4
(10 rows)

Code Explanation:

The said query in SQL that selects the venue name, city, and count of matches played in each venue and city combination, by joining tables soccer_venue, soccer_city, and match_mast on the venue_id and city_id fields.
The result is grouped by venue_name and city, and ordered by venue_name in ascending order.

Alternative Solutions:

Using Subquery with Aggregation:

-- Selecting venue_name, city, and the count of matches for each venue using a subquery
SELECT a.venue_name, b.city,
       -- Subquery to count the number of matches for each venue
       (SELECT COUNT(*) 
        -- From clause within the subquery to select from match_mast (aliased as c)
        FROM match_mast c 
        -- Condition for joining match_mast based on venue_id
        WHERE a.venue_id = c.venue_id) as match_count
-- From clause with JOIN between soccer_venue and soccer_city using city_id
FROM soccer_venue a
JOIN soccer_city b ON a.city_id = b.city_id
-- Ordering the results by venue_name
ORDER BY a.venue_name;

Explanation:

This query uses a subquery in the select clause to calculate the count of matches for each venue. It correlates the subquery with the outer query using the venue_id. The subquery counts the number of matches for each venue, and the result is selected alongside the venue name and city.

Using a Correlated Subquery in the JOIN:

-- Selecting venue_name, city, and the count of matches for each venue using LEFT JOIN
SELECT a.venue_name, b.city, COUNT(c.match_no)
-- From clause with JOINs between soccer_venue, soccer_city, and match_mast (using LEFT JOIN)
FROM soccer_venue a
JOIN soccer_city b ON a.city_id = b.city_id
LEFT JOIN match_mast c ON a.venue_id = c.venue_id
-- Grouping the results by venue_name and city to count matches for each venue
GROUP BY a.venue_name, b.city
-- Ordering the results by venue_name
ORDER BY a.venue_name;

Explanation:

This query uses a correlated subquery within the JOIN clause. It joins all venues with their respective matches, and then groups the results by venue name and city. This ensures that each venue is counted only once.

Using a Subquery in the FROM Clause:

-- Selecting venue_name, city, and the count of matches for each venue using LEFT JOIN and COALESCE
SELECT a.venue_name, b.city, COALESCE(match_count, 0) as match_count
-- From clause with JOINs between soccer_venue, soccer_city, and a subquery (aliased as c) with LEFT JOIN
FROM soccer_venue a
JOIN soccer_city b ON a.city_id = b.city_id
LEFT JOIN (
    -- Subquery to count the number of matches for each venue
    SELECT venue_id, COUNT(match_no) as match_count
    -- From clause to select from match_mast
    FROM match_mast
    -- Grouping the results by venue_id to count matches for each venue
    GROUP BY venue_id
) c ON a.venue_id = c.venue_id
-- Ordering the results by venue_name
ORDER BY a.venue_name;

Explanation:

This query uses a subquery in the FROM clause to calculate the match counts for each venue. It then performs a LEFT JOIN with the venues table, ensuring that all venues are included in the result, even if they have no matches. The COALESCE function is used to handle cases where there are no matches.

Relational Algebra Expression:

Relational Algebra Expression: Compute a report that contain the number of matches played in each venue along with their city.

Relational Algebra Tree:

Relational Algebra Tree: Compute a report that contain the number of matches played in each venue along with their city.

Practice Online


Sample Database: soccer

soccer database relationship structure

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

Previous SQL Exercise: Stadium hosted the final match of EURO cup 2016.
Next SQL Exercise: Which player was the first to be sent off at Euro 2016.

What is the difficulty level of this exercise?

Test your Programming skills with w3resource's quiz.



Become a Patron!

Follow us on Facebook and Twitter for latest update.

It will be nice if you may share this link in any developer community or anywhere else, from where other developers may find this content. Thanks.

https://www.w3resource.com/sql-exercises/soccer-database-exercise/sql-joins-exercise-soccer-database-14.php