w3resource

SQL Exercise: Assistant referees of each countries assists matches

SQL soccer Database: Joins Exercise-46 with Solution

46. From the following table, write a SQL query to find the assistant referees of each country assist the number of matches. Sort the result-set in descending order on number of matches. Return country name, number of matches.

Sample table: match_details


Sample table: asst_referee_mast


Sample table: soccer_country


Sample Solution:

SQL Code:

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;

Sample Output:

    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)

Code Explanation:

The said query in SQL that returns the number of distinct match_no values for each country in the soccer_country table.
This query is identifies which countries have the most soccer matches with the specified assistant referees.
The JOIN clause joins the match_details, asst_referee_mast, and soccer_country tables using their respective keys (ass_ref in match_details, ass_ref_id in asst_referee_mast, and country_id in soccer_country) to match the data between them.
This orders the results by the count of match_no values in descending order.

Relational Algebra Expression:

Relational Algebra Expression: Find the assistant referees of each countries assists the number of matches.

Relational Algebra Tree:

Relational Algebra Tree: Find the assistant referees of each countries assists the number of matches.

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: Each match assistant referee and their country.
Next SQL Exercise: Countries where assistant referees assist most matches.

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

What is the best way to paginate results in SQL Server?

SELECT  *
FROM    ( SELECT    ROW_NUMBER() OVER ( ORDER BY OrderDate ) AS RowNum, *
          FROM      Orders
          WHERE     OrderDate >= '1980-01-01'
        ) AS RowConstrainedResult
WHERE   RowNum >= 1
    AND RowNum < 20
ORDER BY RowNum

Database: SQL Server

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

 





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