SQL Joins exercises on soccer Database: Find the referees of each country managed number of matches
SQL soccer Database: Joins Exercise-49 with Solution
49. Write a query in SQL to find the referees of each country managed number of matches.
Sample table: match_mast
Sample table: referee_mast
Sample table: soccer_country
SELECT country_name, count(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 GROUP BY country_name ORDER BY count(match_no) DESC;
country_name | count ----------------+------- England | 7 Italy | 4 Slovenia | 4 Spain | 3 Serbia | 3 Netherlands | 3 Turkey | 3 Germany | 3 Poland | 3 Sweden | 3 Hungary | 3 France | 2 Czech Republic | 2 Norway | 2 Russia | 2 Romania | 2 Scotland | 2 (17 rows)
Sample Database: soccer
Have another way to solve this solution? Contribute your code (and comments) through Disqus.
What is the difficulty level of this exercise?