SQL Joins exercises on soccer Database: Find the referees managed the number of matches in each venue

SQL soccer Database: Joins Exercise-53 with Solution

53. From the following tables, write a SQL query to find those referees who managed the number of matches in each venue. Return referee name, country name, venue name, number of matches.

Sample table: match_mast

Sample table: referee_mast

Sample table: soccer_country

Sample table: soccer_venue

Sample Solution:

SQL Code:

SELECT c.referee_name,
       b.country_name,
       d.venue_name,
       count(a.match_no)
FROM match_mast a
JOIN referee_mast c ON a.referee_id=c.referee_id
JOIN soccer_country b ON c.country_id=b.country_id
JOIN soccer_venue d ON a.venue_id=d.venue_id
GROUP BY c.referee_name,
         country_name,
         venue_name
ORDER BY referee_name;

Sample Output:

      referee_name       |  country_name  |       venue_name        | count
-------------------------+----------------+-------------------------+-------
 Bjorn Kuipers           | Netherlands    | Stade de Bordeaux       |     1
 Bjorn Kuipers           | Netherlands    | Stade de France         |     2
 Carlos Velasco Carballo | Spain          | Stade Bollaert-Delelis  |     2
 Carlos Velasco Carballo | Spain          | Stade Geoffroy Guichard |     1
 Clement Turpin          | France         | Parc des Princes        |     1
 Clement Turpin          | France         | Stade de Bordeaux       |     1
 Cuneyt Cakir            | Turkey         | Stade de Bordeaux       |     1
 Cuneyt Cakir            | Turkey         | Stade de France         |     1
 Cuneyt Cakir            | Turkey         | Stade Geoffroy Guichard |     1
 Damir Skomina           | Slovenia       | Stade de Nice           |     1
 Damir Skomina           | Slovenia       | Stade Pierre Mauroy     |     3
 Felix Brych             | Germany        | Stade Bollaert-Delelis  |     1
 Felix Brych             | Germany        | Stade de Nice           |     1
 Felix Brych             | Germany        | Stade VElodrome         |     1
 Jonas Eriksson          | Sweden         | Parc des Princes        |     1
 Jonas Eriksson          | Sweden         | Stade de Lyon           |     1
 Jonas Eriksson          | Sweden         | Stadium de Toulouse     |     1
 Mark Clattenburg        | England        | Stade de France         |     1
 Mark Clattenburg        | England        | Stade de Lyon           |     1
 Mark Clattenburg        | England        | Stade Geoffroy Guichard |     2
 Martin Atkinson         | England        | Parc des Princes        |     1
 Martin Atkinson         | England        | Stade de Lyon           |     1
 Martin Atkinson         | England        | Stade Pierre Mauroy     |     1
 Milorad Mazic           | Serbia         | Stade de France         |     1
 Milorad Mazic           | Serbia         | Stade de Nice           |     1
 Milorad Mazic           | Serbia         | Stadium de Toulouse     |     1
 Nicola Rizzoli          | Italy          | Parc des Princes        |     1
 Nicola Rizzoli          | Italy          | Stade de Lyon           |     1
 Nicola Rizzoli          | Italy          | Stade VElodrome         |     2
 Ovidiu Hategan          | Romania        | Stade de Nice           |     1
 Ovidiu Hategan          | Romania        | Stade Pierre Mauroy     |     1
 Pavel Kralovec          | Czech Republic | Stade de Lyon           |     2
 Sergei Karasev          | Russia         | Parc des Princes        |     1
 Sergei Karasev          | Russia         | Stade VElodrome         |     1
 Svein Oddvar Moen       | Norway         | Stade de Bordeaux       |     1
 Svein Oddvar Moen       | Norway         | Stade VElodrome         |     1
 Szymon Marciniak        | Poland         | Stade de France         |     1
 Szymon Marciniak        | Poland         | Stade Pierre Mauroy     |     1
 Szymon Marciniak        | Poland         | Stadium de Toulouse     |     1
 Viktor Kassai           | Hungary        | Stade de Bordeaux       |     1
 Viktor Kassai           | Hungary        | Stade de France         |     1
 Viktor Kassai           | Hungary        | Stadium de Toulouse     |     1
 William Collum          | Scotland       | Stade Bollaert-Delelis  |     1
 William Collum          | Scotland       | Stade VElodrome         |     1
(44 rows)

Relational Algebra Expression:

Relational Algebra Tree:

Practice Online

Sample Database: soccer

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

Previous: From the following tables, write a SQL query to find those referees who managed most of the matches. Return referee name, country name and number of matches.
Next: From the following tables, write a SQL query to find the referees and number of booked they made. Return referee name, number of matches.