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


Sample table: soccer_city


Sample table: match_mast


Sample Solution:

SQL Code:

SELECT a.venue_name, b.city, count(c.match_no)
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
GROUP BY venue_name,city
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.

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.



Follow us on Facebook and Twitter for latest update.

SQL: Tips of the Day

How to drop all tables from a database with one SQL query?

USE Databasename

SELECT  'DROP TABLE [' + name + '];'
FROM    sys.tables

Ref: https://bit.ly/3PIUmPL

 





We are closing our Disqus commenting system for some maintenanace issues. You may write to us at reach[at]yahoo[dot]com or visit us at Facebook