﻿ SQL: Assistant referees of each countries assists matches

# 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 Tree:

## Practice Online

Sample Database: soccer

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.

﻿

## 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