SQL exercises on soccer Database: Find the total number of goals scored by each player within normal play schedule and arrange the result set in descending order of goal

SQL soccer Database: Joins Exercise-3 with Solution

3. From the following tables, write a SQL query to count the number of goals scored by each player within 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

Sample table: player_mast

Sample table: soccer_country

Sample Solution:

SQL Code:

SELECT player_name,count(*),country_name
FROM goal_details a
JOIN player_mast b ON a.player_id=b.player_id
JOIN soccer_country c ON a.team_id=c.country_id
WHERE goal_schedule = 'NT'
GROUP BY player_name,country_name
ORDER BY 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)

Relational Algebra Expression:

Relational Algebra Tree:

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: From the following tables, write a SQL query to find the number of goal scored by each team in every match within normal play schedule. Return match number, country name and goal score.
Next: From the following tables, write a SQL query to find the highest individual scorer in EURO cup 2016. Return player name, country name and highest individual scorer.