w3resource
Soccer Database Exercises

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


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

soccer database relationship structure

Query Visualization:

Duration:

Query visualization of Find the match no, play stage, date of match, number of gole scored, and the result of the match where Portugal played against Hungary - Duration

Rows:

Query visualization of Find the match no, play stage, date of match, number of gole scored, and the result of the match where Portugal played against Hungary - Rows

Cost:

Query visualization of Find the match no, play stage, date of match, number of gole scored, and the result of the match where Portugal played against Hungary - Cost

Have another way to solve this solution? Contribute your code (and comments) through Disqus.

Previous: Write a query in SQL to find the match no in which Germany played against Poland.
Next: Write a query in SQL to display the list of players scored number of goals in every matches.

What is the difficulty level of this exercise?