SQL Exercise: Match where no stoppage time added in 1st half of play
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_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: 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: 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:
-- 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:
Go to:
PREV : Find the venue with number of goals that has seen.
NEXT : Team(s) who conceded the most goals in EURO cup 2016.
Practice Online
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?
Test your Programming skills with w3resource's quiz.