SQL exercises on soccer Database: Compute a report that contain the number of matches played in each venue along with their city
SQL soccer Database: Joins Exercise-14 with Solution
14. Write a query in SQL to compute a report that contain the number of matches played in each venue along with their city.
Sample table: soccer_venue
Sample table: soccer_city
Sample table: match_mast
SELECT a.venue_name, b.city, count(c.match_no) FROM soccer_venue a JOIN soccer_city b ON a.city_id=b.city_id JOIN match_mast c ON a.venue_id=c.venue_id GROUP BY venue_name,city ORDER BY venue_name;
venue_name | city | count -------------------------+---------------+------- Parc des Princes | Paris | 5 Stade Bollaert-Delelis | Lens | 4 Stade de Bordeaux | Bordeaux | 5 Stade de France | Saint-Denis | 7 Stade de Lyon | Lyon | 6 Stade de Nice | Nice | 4 Stade Geoffroy Guichard | Saint-Etienne | 4 Stade Pierre Mauroy | Lille | 6 Stade VElodrome | Marseille | 6 Stadium de Toulouse | Toulouse | 4 (10 rows)
Sample Database: soccer
Have another way to solve this solution? Contribute your code (and comments) through Disqus.
Previous: Write a query in SQL to find the stadium hosted the final match of EURO cup 2016 along with the capacity, and audance for that match.
Next: Write a query in SQL to find the player who was the first player to be sent off at the tournament EURO cup 2016.
What is the difficulty level of this exercise?