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

SQL soccer Database: Joins Exercise-17 with Solution

17. From the following tables, write a SQL query to count the yellow cards received by each country. Return country name and number of yellow cards.

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)

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: From the following tables, write a SQL query to find those teams that scored only one goal to the tournament. Return country_name as "Team", team in the group, goal_for.
Next: From the following tables, write a SQL query to count number of goals that has seen. Return venue name and number of goals.