w3resource
Soccer Database Exercises

SQL Joins exercises on soccer Database: Find the most number of cards shown in the matches

SQL soccer Database: Joins Exercise-44 with Solution

44. Write a query in SQL to find the most number of cards shown in the matches.

Sample table: soccer_country


Sample table: player_booked


Sample table: player_mast


Sample Solution:

SQL Code:

SELECT b.match_no,COUNT(b.*) Booked 
FROM soccer_country a
JOIN player_booked b ON a.country_id=b.team_id
JOIN player_mast c ON b.player_id=c.player_id
GROUP BY b.match_no
having COUNT(b.*)=(
SELECT MAX(mm) FROM (
SELECT COUNT(*) mm 
FROM player_booked 
GROUP BY player_id) inner_result)
order by match_no;

Sample Output:

 match_no | booked
----------+--------
        6 |      3
        9 |      3
       11 |      3
       15 |      3
       19 |      3
       21 |      3
       29 |      3
       36 |      3
       44 |      3
(9 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 number of players booked for each team.
Next: Write a query in SQL to list the name of assistant referees with their countries for each matches.

What is the difficulty level of this exercise?



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