w3resource
Soccer Database Exercises

SQL Joins exercises on soccer Database: Find the number of players booked for each team

SQL soccer Database: Joins Exercise-43 with Solution

43. Write a query in SQL to find the number of players booked for each team.

Sample table: soccer_country


Sample table: player_booked


Sample Solution:

SQL Code:

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

Sample Output:

    country_name     | booked
---------------------+--------
 Italy               |     16
 France              |     13
 Portugal            |     13
 Iceland             |     12
 Hungary             |     12
 Germany             |     11
 Wales               |     11
 Romania             |     10
 Poland              |     10
 Albania             |     10
 Belgium             |      9
 Slovakia            |      9
 Republic of Ireland |      9
 Croatia             |      8
 Austria             |      7
 Turkey              |      7
 Northern Ireland    |      6
 Ukraine             |      5
 Czech Republic      |      5
 Spain               |      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 players who booked most number of times.
Next: Write a query in SQL to find the most number of cards shown in the matches.

What is the difficulty level of this exercise?



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