SQL exercises on soccer Database: Find the match no, play stage, date of match, number of gole scored, and the result of the match where Portugal played against Hungary

SQL soccer Database: Subqueries Exercise-5 with Solution

5. 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.

Sample table: match_mast

Sample table: match_details

Sample table: soccer_country

Sample Solution:

SQL Code:

SELECT match_no, play_stage, play_date, results, goal_score
FROM match_mast
WHERE match_no 
IN(
SELECT match_no 
FROM match_details 
WHERE team_id=(
	SELECT country_id 
	FROM soccer_country 
	WHERE country_name='Portugal') OR team_id=(
		SELECT country_id 
		FROM soccer_country 
		WHERE country_name='Hungary') 
GROUP BY match_no 
HAVING COUNT(DISTINCT team_id)=2);

Sample Output:

 match_no | play_stage | play_date  | results | goal_score
----------+------------+------------+---------+------------
       34 | G          | 2016-06-22 | DRAW    | 3-3
(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 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.
Next: 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.