w3resource
Soccer Database Exercises

SQL exercises on soccer Database: Find the yellow cards received by each country

SQL soccer Database: Joins Exercise-17 with Solution

17. Write a query in SQL to find the yellow cards received by each country.

Sample table: soccer_country


Sample table: player_booked


Sample Solution:

SQL Code:

SELECT country_name, COUNT(*)
FROM soccer_country 
JOIN player_booked
ON soccer_country.country_id=player_booked.team_id
GROUP BY country_name
ORDER BY COUNT(*) DESC;

Sample Output:

    country_name     | count
---------------------+-------
 Italy               |    16
 France              |    13
 Portugal            |    13
 Hungary             |    12
 Iceland             |    12
 Wales               |    11
 Germany             |    11
 Romania             |    10
 Albania             |    10
 Poland              |    10
 Republic of Ireland |     9
 Slovakia            |     9
 Belgium             |     9
 Croatia             |     8
 Turkey              |     7
 Austria             |     7
 Northern Ireland    |     6
 Czech Republic      |     5
 Spain               |     5
 Ukraine             |     5
 Switzerland         |     5
 England             |     3
 Sweden              |     3
 Russia              |     2
(24 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 the teams that scored only one goal to the torunament.
Next: Write a query in SQL to find the venue with number of goals that has seen.

What is the difficulty level of this exercise?



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