SQL Exercise: Number of goals Germany scored at the tournament
SQL soccer Database: Subqueries Exercise-17 with Solution
17. From the following tables, write a SQL query to find the number of goals Germany scored at the tournament.
Sample table: goal_details
goal_id | match_no | player_id | team_id | goal_time | goal_type | play_stage | goal_schedule | goal_half
---------+----------+-----------+---------+-----------+-----------+------------+---------------+-----------
1 | 1 | 160159 | 1207 | 57 | N | G | NT | 2
2 | 1 | 160368 | 1216 | 65 | P | G | NT | 2
3 | 1 | 160154 | 1207 | 89 | N | G | NT | 2
4 | 2 | 160470 | 1221 | 5 | N | G | NT | 1
5 | 3 | 160547 | 1224 | 10 | N | G | NT | 1
6 | 3 | 160403 | 1218 | 61 | N | G | NT | 2
7 | 3 | 160550 | 1224 | 81 | N | G | NT | 2
8 | 4 | 160128 | 1206 | 73 | N | G | NT | 2
9 | 4 | 160373 | 1217 | 93 | N | G | ST | 2
10 | 5 | 160084 | 1204 | 41 | N | G | NT | 1
11 | 6 | 160298 | 1213 | 51 | N | G | NT | 2
12 | 7 | 160183 | 1208 | 19 | N | G | NT | 1
13 | 7 | 160180 | 1208 | 93 | N | G | ST | 2
14 | 8 | 160423 | 1219 | 87 | N | G | NT | 2
15 | 9 | 160335 | 1215 | 48 | N | G | NT | 2
16 | 9 | 160327 | 1215 | 71 | O | G | NT | 2
17 | 10 | 160244 | 1211 | 32 | N | G | NT | 1
18 | 10 | 160252 | 1211 | 93 | N | G | ST | 2
19 | 11 | 160207 | 1209 | 62 | N | G | NT | 2
20 | 11 | 160200 | 1209 | 87 | N | G | NT | 2
21 | 12 | 160320 | 1214 | 31 | N | G | NT | 1
22 | 12 | 160221 | 1210 | 50 | N | G | NT | 2
23 | 13 | 160411 | 1218 | 32 | N | G | NT | 1
24 | 13 | 160405 | 1218 | 45 | N | G | NT | 1
25 | 13 | 160380 | 1217 | 80 | N | G | NT | 2
26 | 14 | 160368 | 1216 | 18 | P | G | NT | 1
27 | 14 | 160481 | 1221 | 57 | N | G | NT | 2
28 | 15 | 160160 | 1207 | 90 | N | G | NT | 2
29 | 15 | 160154 | 1207 | 96 | N | G | ST | 2
30 | 16 | 160547 | 1224 | 42 | N | G | NT | 1
31 | 16 | 160138 | 1206 | 56 | N | G | NT | 2
32 | 16 | 160137 | 1206 | 93 | N | G | ST | 2
33 | 17 | 160262 | 1212 | 49 | N | G | NT | 2
34 | 17 | 160275 | 1212 | 96 | N | G | ST | 2
35 | 19 | 160248 | 1211 | 88 | N | G | NT | 2
36 | 20 | 160085 | 1204 | 37 | N | G | NT | 1
37 | 20 | 160086 | 1204 | 59 | N | G | NT | 2
38 | 20 | 160115 | 1205 | 76 | N | G | NT | 2
39 | 20 | 160114 | 1205 | 89 | P | G | NT | 2
40 | 21 | 160435 | 1219 | 34 | N | G | NT | 1
41 | 21 | 160436 | 1219 | 37 | N | G | NT | 1
42 | 21 | 160435 | 1219 | 48 | N | G | NT | 2
43 | 22 | 160067 | 1203 | 48 | N | G | NT | 2
44 | 22 | 160064 | 1203 | 61 | N | G | NT | 2
45 | 22 | 160067 | 1203 | 70 | N | G | NT | 2
46 | 23 | 160224 | 1210 | 40 | P | G | NT | 1
47 | 23 | 160216 | 1210 | 88 | O | G | NT | 2
48 | 25 | 160023 | 1201 | 43 | N | G | NT | 1
49 | 27 | 160544 | 1224 | 11 | N | G | NT | 1
50 | 27 | 160538 | 1224 | 20 | N | G | NT | 1
51 | 27 | 160547 | 1224 | 67 | N | G | NT | 2
52 | 29 | 160287 | 1213 | 54 | N | G | NT | 2
53 | 30 | 160182 | 1208 | 30 | N | G | NT | 1
54 | 31 | 160504 | 1222 | 10 | N | G | NT | 1
55 | 31 | 160500 | 1222 | 65 | N | G | NT | 2
56 | 32 | 160435 | 1219 | 7 | N | G | NT | 1
57 | 32 | 160089 | 1204 | 45 | N | G | NT | 1
58 | 32 | 160085 | 1204 | 87 | N | G | NT | 2
59 | 33 | 160226 | 1210 | 18 | N | G | NT | 1
60 | 33 | 160042 | 1202 | 60 | N | G | NT | 2
61 | 33 | 160226 | 1210 | 94 | N | G | ST | 2
62 | 34 | 160203 | 1209 | 19 | N | G | NT | 1
63 | 34 | 160320 | 1214 | 42 | N | G | NT | 1
64 | 34 | 160202 | 1209 | 47 | N | G | NT | 2
65 | 34 | 160322 | 1214 | 50 | N | G | NT | 2
66 | 34 | 160202 | 1209 | 55 | N | G | NT | 2
67 | 34 | 160322 | 1214 | 62 | N | G | NT | 2
68 | 35 | 160333 | 1215 | 85 | N | G | NT | 2
69 | 36 | 160063 | 1203 | 84 | N | G | NT | 2
70 | 37 | 160287 | 1213 | 39 | N | R | NT | 1
71 | 37 | 160476 | 1221 | 82 | N | R | NT | 2
72 | 38 | 160262 | 1212 | 75 | O | R | NT | 2
73 | 39 | 160321 | 1214 | 117 | N | R | ET | 2
74 | 40 | 160333 | 1215 | 2 | P | R | NT | 1
75 | 40 | 160160 | 1207 | 58 | N | R | NT | 2
76 | 40 | 160160 | 1207 | 61 | N | R | NT | 2
77 | 41 | 160165 | 1208 | 8 | N | R | NT | 1
78 | 41 | 160182 | 1208 | 43 | N | R | NT | 1
79 | 41 | 160173 | 1208 | 63 | N | R | NT | 2
80 | 42 | 160050 | 1203 | 10 | N | R | NT | 1
81 | 42 | 160065 | 1203 | 78 | N | R | NT | 2
82 | 42 | 160062 | 1203 | 80 | N | R | NT | 2
83 | 42 | 160058 | 1203 | 90 | N | R | NT | 2
84 | 43 | 160236 | 1211 | 33 | N | R | NT | 1
85 | 43 | 160252 | 1211 | 91 | N | R | ST | 2
86 | 44 | 160136 | 1206 | 4 | P | R | NT | 1
87 | 44 | 160219 | 1210 | 6 | N | R | NT | 1
88 | 44 | 160230 | 1210 | 18 | N | R | NT | 1
89 | 45 | 160297 | 1213 | 2 | N | Q | NT | 1
90 | 45 | 160316 | 1214 | 33 | N | Q | NT | 1
91 | 46 | 160063 | 1203 | 13 | N | Q | NT | 1
92 | 46 | 160539 | 1224 | 31 | N | Q | NT | 1
93 | 46 | 160550 | 1224 | 55 | N | Q | NT | 2
94 | 46 | 160551 | 1224 | 86 | N | Q | NT | 2
95 | 47 | 160177 | 1208 | 65 | N | Q | NT | 2
96 | 47 | 160235 | 1211 | 78 | P | Q | NT | 2
97 | 48 | 160159 | 1207 | 12 | N | Q | NT | 1
98 | 48 | 160155 | 1207 | 20 | N | Q | NT | 1
99 | 48 | 160154 | 1207 | 43 | N | Q | NT | 1
100 | 48 | 160160 | 1207 | 45 | N | Q | NT | 1
101 | 48 | 160230 | 1210 | 56 | N | Q | NT | 2
102 | 48 | 160159 | 1207 | 59 | N | Q | NT | 2
103 | 48 | 160221 | 1210 | 84 | N | Q | NT | 2
104 | 49 | 160322 | 1214 | 50 | N | S | NT | 2
105 | 49 | 160320 | 1214 | 53 | N | S | NT | 2
106 | 50 | 160160 | 1207 | 47 | P | S | ST | 1
107 | 50 | 160160 | 1207 | 72 | N | S | NT | 2
108 | 51 | 160319 | 1214 | 109 | N | F | ET | 2
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:
-- Counting the number of player_id occurrences
SELECT COUNT(player_id)
-- Filtering by team_id
FROM goal_details
-- Subquery to find country_id of Germany
WHERE team_id=(
-- Selecting country_id of Germany
SELECT country_id
FROM soccer_country
WHERE country_name='Germany'
);
Sample Output:
count
-------
7
(1 row)
Code Explanation:
The said query in SQL that retrieves the number of goals scored by the German team in the goal_details table.
1. The COUNT function counts the number of rows in the goal_details table.
2. The WHERE clause filters the results to only include rows where the team_id matches the country_id of Germany.
3. The country_id of Germany is obtained from a subquery that selects it from the soccer_country table, using a filter on the country_name column.
Alternative Solution:
Using JOIN and Subquery:
-- Counting the number of occurrences of player_id
SELECT COUNT(gd.player_id)
-- Joining goal_details and soccer_country tables based on team_id and country_id respectively
FROM goal_details gd
JOIN soccer_country sc ON gd.team_id = sc.country_id
-- Filtering by country_name 'Germany'
WHERE sc.country_name = 'Germany';
Explanation:
This query uses a JOIN operation to connect the goal_details table with the soccer_country table based on team_id. It then filters the results to only include entries for Germany and counts the number of player_id.
Using EXISTS with Subquery:
-- Counting the number of occurrences of player_id
SELECT COUNT(player_id)
-- Selecting from the goal_details table
FROM goal_details gd
-- Checking for existence of records in soccer_country table
WHERE EXISTS (
-- Subquery to check for existence of country_name 'Germany' and matching team_id
SELECT 1
FROM soccer_country sc
WHERE sc.country_name = 'Germany'
AND sc.country_id = gd.team_id
);
Explanation:
This query uses the EXISTS keyword along with a subquery to check if there exists a country with the name Germany in the soccer_country table, and if the team_id in goal_details matches that country's country_id.
Using Scalar Subquery:
-- Selecting player_count as a subquery result
SELECT (
-- Counting the number of player_id occurrences
SELECT COUNT(player_id)
-- Filtering by team_id of Germany
FROM goal_details
WHERE team_id = (
-- Selecting country_id of Germany
SELECT country_id
FROM soccer_country
WHERE country_name = 'Germany'
)
) AS player_count;
Explanation:
This query uses a scalar subquery to directly retrieve the count of player_id from goal_details for the specified country (Germany). The result is aliased as player_count.
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: Italy Goalkeeper in 2016 penalty shootout with Germany.
Next SQL Exercise: Goalkeepers for the England squad for 2016 EURO cup.
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-17.php
- Weekly Trends and Language Statistics
- Weekly Trends and Language Statistics