SQL Exercise: Find the match where Portugal played against Hungary
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
match_no | play_stage | play_date | results | decided_by | goal_score | venue_id | referee_id | audence | plr_of_match | stop1_sec | stop2_sec
----------+------------+------------+---------+------------+------------+----------+------------+---------+--------------+-----------+-----------
1 | G | 2016-06-11 | WIN | N | 2-1 | 20008 | 70007 | 75113 | 160154 | 131 | 242
2 | G | 2016-06-11 | WIN | N | 0-1 | 20002 | 70012 | 33805 | 160476 | 61 | 182
3 | G | 2016-06-11 | WIN | N | 2-1 | 20001 | 70017 | 37831 | 160540 | 64 | 268
4 | G | 2016-06-12 | DRAW | N | 1-1 | 20005 | 70011 | 62343 | 160128 | 0 | 185
5 | G | 2016-06-12 | WIN | N | 0-1 | 20007 | 70006 | 43842 | 160084 | 125 | 325
6 | G | 2016-06-12 | WIN | N | 1-0 | 20006 | 70014 | 33742 | 160291 | 2 | 246
7 | G | 2016-06-13 | WIN | N | 2-0 | 20003 | 70002 | 43035 | 160176 | 89 | 188
8 | G | 2016-06-13 | WIN | N | 1-0 | 20010 | 70009 | 29400 | 160429 | 360 | 182
9 | G | 2016-06-13 | DRAW | N | 1-1 | 20008 | 70010 | 73419 | 160335 | 67 | 194
........
51 | F | 2016-07-11 | WIN | N | 1-0 | 20008 | 70005 | 75868 | 160307 | 161 | 181
Sample table: match_details
match_no | play_stage | team_id | win_lose | decided_by | goal_score | penalty_score | ass_ref | player_gk
----------+------------+---------+----------+------------+------------+---------------+---------+-----------
1 | G | 1207 | W | N | 2 | | 80016 | 160140
1 | G | 1216 | L | N | 1 | | 80020 | 160348
2 | G | 1201 | L | N | 0 | | 80003 | 160001
2 | G | 1221 | W | N | 1 | | 80023 | 160463
3 | G | 1224 | W | N | 2 | | 80031 | 160532
3 | G | 1218 | L | N | 1 | | 80025 | 160392
4 | G | 1206 | D | N | 1 | | 80008 | 160117
4 | G | 1217 | D | N | 1 | | 80019 | 160369
5 | G | 1222 | L | N | 0 | | 80011 | 160486
........
51 | F | 1207 | L | N | 0 | | 80007 | 160140
Sample table: soccer_country
country_id | country_abbr | country_name
------------+--------------+---------------------
1201 | ALB | Albania
1202 | AUT | Austria
1203 | BEL | Belgium
1204 | CRO | Croatia
1205 | CZE | Czech Republic
1206 | ENG | England
1207 | FRA | France
1208 | GER | Germany
1209 | HUN | Hungary
.......
1229 | NOR | Norway
Sample Solution:
SQL Code:
-- This SQL query retrieves match numbers from the 'match_details' table
-- where the teams are either Germany or Poland, and each match involves both teams.
SELECT match_no
-- Selects the 'match_no' column.
FROM match_details
-- 'match_details' is the name of the table being queried.
WHERE team_id = (
-- The WHERE clause filters rows where 'team_id' matches the country ID for Germany in the subquery.
SELECT country_id
-- The subquery selects the 'country_id' column.
FROM soccer_country
-- 'soccer_country' is the name of the table involved in the subquery.
WHERE country_name = 'Germany'
)
OR team_id = (
-- The OR condition allows for rows where 'team_id' matches the country ID for Poland in the subquery.
SELECT country_id
-- The subquery selects the 'country_id' column.
FROM soccer_country
-- 'soccer_country' is the name of the table involved in the subquery.
WHERE country_name = 'Poland'
)
GROUP BY match_no
-- Groups the results by 'match_no'.
HAVING COUNT(DISTINCT team_id) = 2;
-- The HAVING clause filters groups where the count of distinct 'team_id' values is equal to 2.
Sample Output:
match_no | play_stage | play_date | results | goal_score
----------+------------+------------+---------+------------
34 | G | 2016-06-22 | DRAW | 3-3
(1 row)
Code Explanation:
The said query in SQL query that selects the match number, play stage, play date, results, and goal score from the 'match_mast' table for matches where either Portugal or Hungary participated and there were exactly two teams in the match
The WHERE clause filters the results to only include rows where the "match_no" column is in the list of match numbers where either Portugal or Hungary participated and there were exactly two teams in the match.
The inner query selects the match numbers from the 'match_details' table where either Portugal or Hungary participated and there were exactly two teams in the match.
Alternative Solutions:
Using JOIN and WHERE:
SELECT mm.match_no, mm.play_stage, mm.play_date, mm.results, mm.goal_score
FROM match_mast mm
JOIN match_details md ON mm.match_no = md.match_no
JOIN soccer_country sc ON md.team_id = sc.country_id
WHERE sc.country_name IN ('Portugal', 'Hungary')
GROUP BY mm.match_no
HAVING COUNT(DISTINCT md.team_id) = 2;
Explanation:
This query joins match_mast, match_details, and soccer_country based on their respective relationships. It then filters for matches involving either Portugal or Hungary, groups the results by match number, and ensures that both teams are present in the match.
Using EXISTS:
SELECT match_no, play_stage, play_date, results, goal_score
FROM match_mast mm
WHERE EXISTS (
SELECT 1
FROM match_details md
JOIN soccer_country sc ON md.team_id = sc.country_id
WHERE mm.match_no = md.match_no
AND sc.country_name IN ('Portugal', 'Hungary')
GROUP BY md.match_no
HAVING COUNT(DISTINCT md.team_id) = 2
);
Explanation:
This query uses a correlated subquery with EXISTS to find matches where both Portugal and Hungary participated.
Using INTERSECT:
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'
)
INTERSECT
SELECT match_no
FROM match_details
WHERE team_id = (
SELECT country_id
FROM soccer_country
WHERE country_name = 'Hungary'
)
);
Explanation:
This query uses the INTERSECT operator to find matches that involve both Portugal and Hungary.
Go to:
PREV : Find the match in which Germany played against Poland.
NEXT : Players scored number of goals in every matches.
Practice Online
Sample Database: soccer
Query Visualization:
Duration:
Rows:
Cost:
Have another way to solve this solution? Contribute your code (and comments) through Disqus.
What is the difficulty level of this exercise?
Test your Programming skills with w3resource's quiz.