SQL Joins exercises on soccer Database: Find the assistant referees of each countries assists the number of matches
SQL soccer Database: Joins Exercise-46 with Solution
46. Write a query in SQL to find the assistant referees of each countries assists the number of matches.
Sample table: match_details
Sample table: asst_referee_mast
Sample table: soccer_country
SELECT country_name, count(DISTINCT match_no) FROM match_details a JOIN asst_referee_mast c ON a.ass_ref=c.ass_ref_id JOIN soccer_country b ON c.country_id=b.country_id GROUP BY country_name ORDER BY count(*) DESC;
country_name | count ---------------------+------- England | 7 Slovenia | 4 Italy | 4 Turkey | 3 Hungary | 3 Netherlands | 3 Poland | 3 Germany | 3 Spain | 3 Sweden | 3 Serbia | 3 Russia | 2 France | 2 Norway | 2 Romania | 2 Republic of Ireland | 2 Czech Republic | 2 Scotland | 2 Slovakia | 2 (19 rows)
Sample Database: soccer
Have another way to solve this solution? Contribute your code (and comments) through Disqus.
Previous: Write a query in SQL to list the name of assistant referees with their countries for each matches.
Next: Write a query in SQL to find the countries from where the assistant referees assist most of the matches.
What is the difficulty level of this exercise?