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. Write a query in SQL to find the match no, play stage, date of match, number of gole scored, and the result of the match where Portugal played against Hungary.
Sample table: match_mast
Sample table: match_details
Sample table: soccer_country
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);
match_no | play_stage | play_date | results | goal_score ----------+------------+------------+---------+------------ 34 | G | 2016-06-22 | DRAW | 3-3 (1 row)
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?