w3resource
Soccer Database Exercises

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. Write a query in SQL to find the referees managed the number of matches in each venue.

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)

Practice Online


Sample Database: soccer

soccer database relationship structure

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

Previous: Write a query in SQL to find those referees who managed most of the match.
Next: Write a query in SQL to find the referees and number of booked he made.

What is the difficulty level of this exercise?



New Content: Composer: Dependency manager for PHP, R Programming