SQL Exercise: Match where no stoppage time added in 1st half of play
SQL soccer Database: Joins Exercise-19 with Solution
19. From the following tables, write a SQL query to find the match where there was no stoppage time in the first half. Return match number, country name.
Sample table: match_detailsmatch_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 | 160140Sample 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 | 181Sample 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:
-- Selecting match_no and country_name from match_details and soccer_country
SELECT match_details.match_no, soccer_country.country_name
-- From clause with JOINs between match_mast, match_details, and soccer_country
FROM match_mast
JOIN match_details ON match_mast.match_no = match_details.match_no
JOIN soccer_country ON match_details.team_id = soccer_country.country_id
-- Where clause to filter results for matches with stop1_sec equal to 0
WHERE stop1_sec = 0;
Sample Output:
match_no | country_name ----------+-------------- 4 | England 4 | Russia (2 rows)
Code Explanation:
The given query in SQL that selects the match number and the name of the country associated with the team playing in that match from the tables match_mast, match_details, and soccer_country.
The JOIN clause is used to joins the tables using the match_no and team_id columns, which are primary keys in the match_mast and soccer_country tables respectively, and foreign keys in the match_details table.
The WHERE clause filters the results to only show matches where the stop1_sec column in the match_details table is equal to 0.
Alternative Solutions:
Using Subquery in the WHERE Clause:
-- Selecting match_no and country_name from match_mast and soccer_country
SELECT m.match_no, sc.country_name
-- From clause with JOINs between match_mast, match_details, and soccer_country
FROM match_mast m
JOIN match_details md ON m.match_no = md.match_no
JOIN soccer_country sc ON md.team_id = sc.country_id
-- Where clause to filter results for matches where match_no is in the subquery result
WHERE m.match_no IN (
-- Subquery to select match_no values from match_details where stop1_sec is equal to 0
SELECT match_no
FROM match_details
WHERE stop1_sec = 0
);
Explanation:
This query uses a subquery in the WHERE clause to filter the matches. It first selects match numbers from match_details where stop1_sec = 0, and then checks if the match number is in the list of matches.
Using Subquery with EXISTS:
-- Selecting match_no and country_name from match_mast and soccer_country
SELECT m.match_no, sc.country_name
-- From clause with JOINs between match_mast, match_details, and soccer_country
FROM match_mast m
JOIN match_details md ON m.match_no = md.match_no
JOIN soccer_country sc ON md.team_id = sc.country_id
-- Where clause to filter results for matches where there exists a match_details record with stop1_sec equal to 0
WHERE EXISTS (
-- Subquery to check if there is at least one match_details record for the same match_no with stop1_sec equal to 0
SELECT 1
FROM match_details md2
WHERE md2.match_no = m.match_no AND m.stop1_sec = 0
);
Explanation:
This query uses an EXISTS subquery to check if there's at least one record in match_details with stop1_sec = 0 for each match. It joins match_mast, match_details, and soccer_country, and then uses the EXISTS clause to filter the results.
Relational Algebra Expression:
Relational Algebra Tree:
Practice Online
Sample Database: soccer
Have another way to solve this solution? Contribute your code (and comments) through Disqus.
Previous SQL Exercise: Find the venue with number of goals that has seen.
Next SQL Exercise: Team(s) who conceded the most goals in EURO cup 2016.
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://www.w3resource.com/sql-exercises/soccer-database-exercise/sql-joins-exercise-soccer-database-19.php
- Weekly Trends and Language Statistics
- Weekly Trends and Language Statistics