SQL Joins exercises on soccer Database: Find the referees and number of booked he made
SQL soccer Database: Joins Exercise-54 with Solution
54. Write a query in SQL to find the referees and number of booked he made.
Sample table: player_booked
Sample table: match_mast
Sample table: referee_mast
SELECT c.referee_name, count(b.match_no) FROM player_booked a JOIN match_mast b ON a.match_no=b.match_no JOIN referee_mast c ON b.referee_id=c.referee_id GROUP BY referee_name ORDER BY count(b.match_no) DESC;
referee_name | count -------------------------+------- Mark Clattenburg | 21 Nicola Rizzoli | 20 Milorad Mazic | 13 Viktor Kassai | 12 Sergei Karasev | 12 Damir Skomina | 12 Bjorn Kuipers | 12 Cuneyt Cakir | 11 Pavel Kralovec | 11 Jonas Eriksson | 11 Carlos Velasco Carballo | 10 Szymon Marciniak | 10 Ovidiu Hategan | 9 Felix Brych | 9 Martin Atkinson | 9 William Collum | 8 Svein Oddvar Moen | 8 Clement Turpin | 3 (18 rows)
Sample Database: soccer
Have another way to solve this solution? Contribute your code (and comments) through Disqus.
What is the difficulty level of this exercise?