SQL Exercise: Find 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
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
10 | G | 2016-06-14 | WIN | N | 0-2 | 20004 | 70005 | 55408 | 160244 | 63 | 189
11 | G | 2016-06-14 | WIN | N | 0-2 | 20001 | 70018 | 34424 | 160197 | 61 | 305
12 | G | 2016-06-15 | DRAW | N | 1-1 | 20009 | 70004 | 38742 | 160320 | 15 | 284
13 | G | 2016-06-15 | WIN | N | 1-2 | 20003 | 70001 | 38989 | 160405 | 62 | 189
14 | G | 2016-06-15 | DRAW | N | 1-1 | 20007 | 70015 | 43576 | 160477 | 74 | 206
15 | G | 2016-06-16 | WIN | N | 2-0 | 20005 | 70013 | 63670 | 160154 | 71 | 374
16 | G | 2016-06-16 | WIN | N | 2-1 | 20002 | 70003 | 34033 | 160540 | 62 | 212
17 | G | 2016-06-16 | WIN | N | 0-2 | 20004 | 70016 | 51043 | 160262 | 7 | 411
18 | G | 2016-06-17 | DRAW | N | 0-0 | 20008 | 70008 | 73648 | 160165 | 6 | 208
19 | G | 2016-06-17 | WIN | N | 1-0 | 20010 | 70007 | 29600 | 160248 | 2 | 264
20 | G | 2016-06-17 | DRAW | N | 2-2 | 20009 | 70005 | 38376 | 160086 | 71 | 280
21 | G | 2016-06-18 | WIN | N | 3-0 | 20006 | 70010 | 33409 | 160429 | 84 | 120
22 | G | 2016-06-18 | WIN | N | 3-0 | 20001 | 70004 | 39493 | 160064 | 11 | 180
23 | G | 2016-06-18 | DRAW | N | 1-1 | 20005 | 70015 | 60842 | 160230 | 61 | 280
24 | G | 2016-06-19 | DRAW | N | 0-0 | 20007 | 70011 | 44291 | 160314 | 3 | 200
25 | G | 2016-06-20 | WIN | N | 0-1 | 20004 | 70016 | 49752 | 160005 | 125 | 328
26 | G | 2016-06-20 | DRAW | N | 0-0 | 20003 | 70001 | 45616 | 160463 | 60 | 122
27 | G | 2016-06-21 | WIN | N | 0-3 | 20010 | 70006 | 28840 | 160544 | 62 | 119
28 | G | 2016-06-21 | DRAW | N | 0-0 | 20009 | 70012 | 39051 | 160392 | 62 | 301
29 | G | 2016-06-21 | WIN | N | 0-1 | 20005 | 70017 | 58874 | 160520 | 29 | 244
30 | G | 2016-06-21 | WIN | N | 0-1 | 20007 | 70018 | 44125 | 160177 | 21 | 195
31 | G | 2016-06-22 | WIN | N | 0-2 | 20002 | 70013 | 32836 | 160504 | 60 | 300
32 | G | 2016-06-22 | WIN | N | 2-1 | 20001 | 70008 | 37245 | 160085 | 70 | 282
33 | G | 2016-06-22 | WIN | N | 2-1 | 20008 | 70009 | 68714 | 160220 | 7 | 244
34 | G | 2016-06-22 | DRAW | N | 3-3 | 20004 | 70002 | 55514 | 160322 | 70 | 185
35 | G | 2016-06-23 | WIN | N | 0-1 | 20003 | 70014 | 44268 | 160333 | 79 | 221
36 | G | 2016-06-23 | WIN | N | 0-1 | 20006 | 70003 | 34011 | 160062 | 63 | 195
37 | R | 2016-06-25 | WIN | P | 1-1 | 20009 | 70005 | 38842 | 160476 | 126 | 243
38 | R | 2016-06-25 | WIN | N | 1-0 | 20007 | 70002 | 44342 | 160547 | 5 | 245
39 | R | 2016-06-26 | WIN | N | 0-1 | 20002 | 70012 | 33523 | 160316 | 61 | 198
40 | R | 2016-06-26 | WIN | N | 2-1 | 20004 | 70011 | 56279 | 160160 | 238 | 203
41 | R | 2016-06-26 | WIN | N | 3-0 | 20003 | 70009 | 44312 | 160173 | 62 | 124
42 | R | 2016-06-27 | WIN | N | 0-4 | 20010 | 70010 | 28921 | 160062 | 3 | 133
43 | R | 2016-06-27 | WIN | N | 2-0 | 20008 | 70004 | 76165 | 160235 | 63 | 243
44 | R | 2016-06-28 | WIN | N | 1-2 | 20006 | 70001 | 33901 | 160217 | 5 | 199
45 | Q | 2016-07-01 | WIN | P | 1-1 | 20005 | 70003 | 62940 | 160316 | 58 | 181
46 | Q | 2016-07-02 | WIN | N | 3-1 | 20003 | 70001 | 45936 | 160550 | 14 | 182
47 | Q | 2016-07-03 | WIN | P | 1-1 | 20001 | 70007 | 38764 | 160163 | 63 | 181
48 | Q | 2016-07-04 | WIN | N | 5-2 | 20008 | 70008 | 76833 | 160159 | 16 | 125
49 | S | 2016-07-07 | WIN | N | 2-0 | 20004 | 70006 | 55679 | 160322 | 2 | 181
50 | S | 2016-07-08 | WIN | N | 2-0 | 20005 | 70011 | 64078 | 160160 | 126 | 275
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
5 | G | 1204 | W | N | 1 | | 80022 | 160071
6 | G | 1213 | W | N | 1 | | 80036 | 160279
6 | G | 1212 | L | N | 0 | | 80029 | 160256
7 | G | 1208 | W | N | 2 | | 80014 | 160163
7 | G | 1223 | L | N | 0 | | 80006 | 160508
8 | G | 1219 | W | N | 1 | | 80018 | 160416
8 | G | 1205 | L | N | 0 | | 80012 | 160093
9 | G | 1215 | D | N | 1 | | 80017 | 160324
9 | G | 1220 | D | N | 1 | | 80010 | 160439
10 | G | 1203 | L | N | 0 | | 80004 | 160047
10 | G | 1211 | W | N | 2 | | 80007 | 160231
11 | G | 1202 | L | N | 0 | | 80026 | 160024
11 | G | 1209 | W | N | 2 | | 80028 | 160187
12 | G | 1214 | D | N | 1 | | 80009 | 160302
12 | G | 1210 | D | N | 1 | | 80015 | 160208
13 | G | 1217 | L | N | 1 | | 80001 | 160369
13 | G | 1218 | W | N | 2 | | 80002 | 160392
14 | G | 1216 | D | N | 1 | | 80030 | 160348
14 | G | 1221 | D | N | 1 | | 80032 | 160463
15 | G | 1207 | W | N | 2 | | 80033 | 160140
15 | G | 1201 | L | N | 0 | | 80027 | 160001
16 | G | 1206 | W | N | 2 | | 80005 | 160117
16 | G | 1224 | L | N | 1 | | 80013 | 160531
17 | G | 1223 | L | N | 0 | | 80035 | 160508
17 | G | 1212 | W | N | 2 | | 80034 | 160256
18 | G | 1208 | D | N | 0 | | 80021 | 160163
18 | G | 1213 | D | N | 0 | | 80024 | 160278
19 | G | 1211 | W | N | 1 | | 80016 | 160231
19 | G | 1220 | L | N | 0 | | 80020 | 160439
20 | G | 1205 | D | N | 2 | | 80004 | 160093
20 | G | 1204 | D | N | 2 | | 80007 | 160071
21 | G | 1219 | W | N | 3 | | 80017 | 160416
21 | G | 1222 | L | N | 0 | | 80010 | 160486
22 | G | 1203 | W | N | 3 | | 80009 | 160047
22 | G | 1215 | L | N | 0 | | 80015 | 160324
23 | G | 1210 | D | N | 1 | | 80030 | 160208
23 | G | 1209 | D | N | 1 | | 80032 | 160187
24 | G | 1214 | D | N | 0 | | 80008 | 160302
24 | G | 1202 | D | N | 0 | | 80019 | 160024
25 | G | 1216 | L | N | 0 | | 80035 | 160348
25 | G | 1201 | W | N | 1 | | 80034 | 160001
26 | G | 1221 | D | N | 0 | | 80001 | 160463
26 | G | 1207 | D | N | 0 | | 80002 | 160140
27 | G | 1217 | L | N | 0 | | 80011 | 160369
27 | G | 1224 | W | N | 3 | | 80022 | 160531
28 | G | 1218 | D | N | 0 | | 80003 | 160392
28 | G | 1206 | D | N | 0 | | 80023 | 160117
29 | G | 1223 | L | N | 0 | | 80031 | 160508
29 | G | 1213 | W | N | 1 | | 80025 | 160278
30 | G | 1212 | L | N | 0 | | 80026 | 160256
30 | G | 1208 | W | N | 1 | | 80028 | 160163
31 | G | 1205 | L | N | 0 | | 80033 | 160093
31 | G | 1222 | W | N | 2 | | 80027 | 160486
32 | G | 1204 | W | N | 2 | | 80021 | 160071
32 | G | 1219 | L | N | 1 | | 80024 | 160416
33 | G | 1210 | W | N | 2 | | 80018 | 160208
33 | G | 1202 | L | N | 1 | | 80012 | 160024
34 | G | 1209 | D | N | 3 | | 80014 | 160187
34 | G | 1214 | D | N | 3 | | 80006 | 160302
35 | G | 1211 | L | N | 0 | | 80036 | 160233
35 | G | 1215 | W | N | 1 | | 80029 | 160324
36 | G | 1220 | L | N | 0 | | 80005 | 160439
36 | G | 1203 | W | N | 1 | | 80013 | 160047
37 | R | 1221 | L | P | 1 | 4 | 80004 | 160463
37 | R | 1213 | W | P | 1 | 5 | 80007 | 160278
38 | R | 1224 | W | N | 1 | | 80014 | 160531
38 | R | 1212 | L | N | 0 | | 80006 | 160256
39 | R | 1204 | L | N | 0 | | 80003 | 160071
39 | R | 1214 | W | N | 1 | | 80023 | 160302
40 | R | 1207 | W | N | 2 | | 80008 | 160140
40 | R | 1215 | L | N | 1 | | 80019 | 160324
41 | R | 1208 | W | N | 3 | | 80018 | 160163
41 | R | 1218 | L | N | 0 | | 80012 | 160392
42 | R | 1209 | L | N | 0 | | 80017 | 160187
42 | R | 1203 | W | N | 4 | | 80010 | 160047
43 | R | 1211 | W | N | 2 | | 80009 | 160231
43 | R | 1219 | L | N | 0 | | 80015 | 160416
44 | R | 1206 | L | N | 1 | | 80001 | 160117
44 | R | 1210 | W | N | 2 | | 80002 | 160208
45 | Q | 1213 | L | P | 1 | 3 | 80005 | 160278
45 | Q | 1214 | W | P | 1 | 5 | 80013 | 160302
46 | Q | 1224 | W | N | 3 | | 80001 | 160531
46 | Q | 1203 | L | N | 1 | | 80002 | 160047
47 | Q | 1208 | W | P | 1 | 6 | 80016 | 160163
47 | Q | 1211 | L | P | 1 | 5 | 80020 | 160231
48 | Q | 1207 | W | N | 5 | | 80021 | 160140
48 | Q | 1210 | L | N | 2 | | 80024 | 160208
49 | S | 1214 | W | N | 2 | | 80011 | 160302
49 | S | 1224 | L | N | 0 | | 80022 | 160531
50 | S | 1207 | W | N | 2 | | 80008 | 160140
50 | S | 1208 | L | N | 1 | | 80019 | 160163
51 | F | 1214 | W | N | 1 | | 80004 | 160302
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
1210 | ISL | Iceland
1211 | ITA | Italy
1212 | NIR | Northern Ireland
1213 | POL | Poland
1214 | POR | Portugal
1215 | IRL | Republic of Ireland
1216 | ROU | Romania
1217 | RUS | Russia
1218 | SVK | Slovakia
1219 | ESP | Spain
1220 | SWE | Sweden
1221 | SUI | Switzerland
1222 | TUR | Turkey
1223 | UKR | Ukraine
1224 | WAL | Wales
1225 | SLO | Slovenia
1226 | NED | Netherlands
1227 | SRB | Serbia
1228 | SCO | Scotland
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.
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 SQL Exercise: Find the match in which Germany played against Poland.
Next SQL Exercise: Players scored number of goals in every matches.
What is the difficulty level of this exercise?
Test your Programming skills with w3resource's quiz.
It will be nice if you may share this link in any developer community or anywhere else, from where other developers may find this content. Thanks.
https://w3resource.com/sql-exercises/soccer-database-exercise/sql-subqueries-exercise-soccer-database-5.php
- Weekly Trends and Language Statistics
- Weekly Trends and Language Statistics