SQL Exercise: Goals scored by each player during normal play
3. From the following table, write a SQL query to count the number of goals scored by each player within a normal play schedule. Group the result set on player name and country name and sorts the result-set according to the highest to the lowest scorer. Return player name, number of goals and country name.
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
..........
108 | 51 | 160319 | 1214 | 109 | N | F | ET | 2
Sample table: player_mast
player_id | team_id | jersey_no | player_name | posi_to_play | dt_of_bir | age | playing_club
-----------+---------+-----------+-------------------------+--------------+------------+-----+---------------------
160001 | 1201 | 1 | Etrit Berisha | GK | 1989-03-10 | 27 | Lazio
160008 | 1201 | 2 | Andi Lila | DF | 1986-02-12 | 30 | Giannina
160016 | 1201 | 3 | Ermir Lenjani | MF | 1989-08-05 | 26 | Nantes
160007 | 1201 | 4 | Elseid Hysaj | DF | 1994-02-20 | 22 | Napoli
160013 | 1201 | 5 | Lorik Cana | MF | 1983-07-27 | 32 | Nantes
160010 | 1201 | 6 | Frederic Veseli | DF | 1992-11-20 | 23 | Lugano
160004 | 1201 | 7 | Ansi Agolli | DF | 1982-10-11 | 33 | Qarabag
160012 | 1201 | 8 | Migjen Basha | MF | 1987-01-05 | 29 | Como
160017 | 1201 | 9 | Ledian Memushaj | MF | 1986-12-17 | 29 | Pescara
..........
160548 | 1224 | 23 | Simon Church | FD | 1988-12-10 | 27 | MK Dons
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 player_name, the count of occurrences, and country_name
SELECT player_name, count(*) AS goal_count, country_name
-- Joining goal_details with player_mast using player_id
FROM goal_details a
JOIN player_mast b ON a.player_id = b.player_id
-- Joining the result with soccer_country using team_id and country_id
JOIN soccer_country c ON a.team_id = c.country_id
-- Filtering the results to include only goals with goal_schedule 'NT'
WHERE goal_schedule = 'NT'
-- Grouping the results by player_name and country_name
GROUP BY player_name, country_name
-- Sorting the results based on the count of goals in descending order
ORDER BY goal_count DESC;
Sample Output:
player_name | count | country_name
-------------------------+-------+---------------------
Antoine Griezmann | 5 | France
Cristiano Ronaldo | 3 | Portugal
Gareth Bale | 3 | Wales
Olivier Giroud | 3 | France
Alvaro Morata | 3 | Spain
Nani | 3 | Portugal
Ivan PeriSic | 2 | Croatia
Radja Nainggolan | 2 | Belgium
Birkir Bjarnason | 2 | Iceland
Gareth McAuley | 2 | Northern Ireland
Romelu Lukaku | 2 | Belgium
Hal Robson-Kanu | 2 | Wales
Balazs Dzsudzsak | 2 | Hungary
Kolbeinn Sigthorsson | 2 | Iceland
Robbie Brady | 2 | Republic of Ireland
Bogdan Stancu | 2 | Romania
Mario Gomez | 2 | Germany
Jakub Blaszczykowski | 2 | Poland
Dimitri Payet | 2 | France
Adam Szalai | 1 | Hungary
Ozan Tufan | 1 | Turkey
Aaron Ramsey | 1 | Wales
Gerard Pique | 1 | Spain
Jamie Vardy | 1 | England
Arnor Ingvi Traustason | 1 | Iceland
Robert Lewandowski | 1 | Poland
Zoltan Gera | 1 | Hungary
Birkir Saevarsson | 1 | Iceland
Arkadiusz Milik | 1 | Poland
Michy Batshuayi | 1 | Belgium
Eder | 1 | Italy
Julian Draxler | 1 | Germany
Neil Taylor | 1 | Wales
Paul Pogba | 1 | France
Eden Hazard | 1 | Belgium
Nolito | 1 | Spain
Giorgio Chiellini | 1 | Italy
Leonardo Bonucci | 1 | Italy
Nikola Kalinic | 1 | Croatia
Toby Alderweireld | 1 | Belgium
Sam Vokes | 1 | Wales
Zoltan Stieber | 1 | Hungary
Admir Mehmedi | 1 | Switzerland
Armando Sadiku | 1 | Albania
Jon Dadi Bodvarsson | 1 | Iceland
Ivan Rakitic | 1 | Croatia
Wayne Rooney | 1 | England
Eric Dier | 1 | England
Fabian Schar | 1 | Switzerland
Ciaran Clark | 1 | Republic of Ireland
Ondrej Duda | 1 | Slovakia
TomasNecid | 1 | Czech Republic
Mesut ozil | 1 | Germany
Wes Hoolahan | 1 | Republic of Ireland
Alessandro Schopf | 1 | Austria
Luka Modric | 1 | Croatia
Thomas Muller | 1 | Germany
Burak Yilmaz | 1 | Turkey
Gylfi Sigurdsson | 1 | Iceland
Xherdan Shaqiri | 1 | Switzerland
Renato Sanches | 1 | Portugal
Vladimir Weiss | 1 | Slovakia
Ashley Williams | 1 | Wales
Marek Hamsik | 1 | Slovakia
Yannick Carrasco | 1 | Belgium
Milan Skoda | 1 | Czech Republic
Jerome Boateng | 1 | Germany
Axel Witsel | 1 | Belgium
Denis Glushakov | 1 | Russia
Emanuele Giaccherini | 1 | Italy
(70 rows)
Code Explanation:
The given query in SQL that returns a list of player names, the corresponding country names, and the number of goals scored by each player for their country during normal time
The JOIN operations joins the goal_details table with the player_mast and soccer_country tables based on the common player_id and country_id columns, respectively. The selected columns are player_name, country_name, and count(), where count() is an aggregate function that counts the number of rows in the grouped result set.
The WHERE clause filters the result set to only include rows where the goal_schedule column equals 'NT', which likely stands for "normal time".
The GROUP BY clause groups the result set by player_name and country_name, meaning that each unique combination of player and country is treated as a separate group.
The ORDER BY clause sorts the result set in descending order based on the count(*) column, which represents the number of goals scored by each player for their country during normal time.
Alternative Solution:
Using EXISTS Subquery:
-- Selecting player_name, goal_count, and country_name
SELECT player_name, goal_count, country_name
-- Subquery to calculate the goal count for each player and country
FROM (
-- Selecting player_name, the count of occurrences, and country_name
SELECT pm.player_name, COUNT(*) as goal_count, sc.country_name
-- Joining goal_details with player_mast using player_id
FROM goal_details gd
JOIN player_mast pm ON gd.player_id = pm.player_id
-- Joining the result with soccer_country using team_id and country_id
JOIN soccer_country sc ON gd.team_id = sc.country_id
-- Filtering the results to include only goals with goal_schedule 'NT'
WHERE gd.goal_schedule = 'NT'
-- Grouping the results by player_name and country_name
GROUP BY pm.player_name, sc.country_name
) as inner_result
-- Sorting the results based on the goal_count in descending order
ORDER BY goal_count DESC;
Explanation:
This query uses an EXISTS subquery to ensure that the inner query returns results. The inner query retrieves player names, the count of goals, and the corresponding country name where goal_schedule is 'NT'. The outer query then orders the result by goal count in descending order.
Relational Algebra Expression:
Relational Algebra Tree:
Go to:
PREV : Number of goals scored by each team in each match.
NEXT : Find the highest individual scorer in EURO cup 2016.
Practice Online
Sample Database: soccer
Query Visualization:
Duration:
Rows:
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.