SQL exercises on soccer Database: Find the match no in which Germany played against Poland

SQL soccer Database: Subqueries Exercise-4 with Solution

4. From the following tables, write a SQL query to find the match number in which Germany played against Poland. Group the result set on match number. Return match number.

Sample table: match_details

Sample table: soccer_country

Sample Solution:

SQL Code:

SELECT match_no 
FROM match_details 
WHERE team_id=(
SELECT country_id 
FROM soccer_country 
WHERE country_name='Germany') 
OR team_id=(
SELECT country_id 
FROM soccer_country 
WHERE country_name='Poland') 
GROUP BY match_no 
HAVING COUNT(DISTINCT team_id)=2;

Sample Output:

 match_no
----------
       18
(1 row)

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 table, write a SQL query to find the most watched match in the world. Return match_no, play_stage, goal_score, audience.
Next: From the following tables, write a SQL query to find the result of the match where Portugal played against Hungary. Return match_no, play_stage, play_date, results, goal_score.