SQL exercises on soccer Database: Find the teams who played the heighest audience match

SQL soccer Database: Subqueries Exercise-7 with Solution

7. From the following tables, write a SQL query to find the highest audience match. Return country name of the teams.

Sample table: soccer_country

Sample table: goal_details

Sample table: match_mast

Sample Solution:

SQL Code:

SELECT country_name
FROM soccer_country
WHERE country_id IN(
   SELECT team_id 
   FROM goal_details 
   WHERE match_no=(
     SELECT match_no 
     FROM match_mast 
     WHERE audence=(
       SELECT max(audence) 
       FROM match_mast)
ORDER BY audence DESC));

Sample Output:

 country_name
--------------
 France
 Iceland
(2 rows)

Practice Online

Sample Database: soccer

Query Visualization:

Duration:

Rows:

Cost:

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 players who scored number of goals in every match. Group the result set on match number, country name and player name. Sort the result-set in ascending order by match number. Return match number, country name, player name and number of matches.
Next: From the following tables, write a SQL query to find the player who scored the last goal for Portugal against Hungary. Return player name.