w3resource
Soccer Database Exercises

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. Write a query in SQL to find the total number of goals scored by each player within normal play schedule and arrange the result set according to the heighest to lowest scorer.

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
GROUP BY player_name,country_name
ORDER BY count(*) DESC;

Sample Output:

       player_name       | count |    country_name
-------------------------+-------+--------------------
 Antoine Griezmann       |     6 | France
 Nani                    |     3 | Portugal
 Alvaro Morata           |     3 | Spain
 Dimitri Payet           |     3 | France
 Olivier Giroud          |     3 | France
 Gareth Bale             |     3 | Wales
 Cristiano Ronaldo       |     3 | Portugal
 Jon Dadi Bodvarsson     |     2 | Iceland
 Romelu Lukaku           |     2 | Belgium
 Bogdan Stancu           |     2 | Romania
 Kolbeinn Sigthorsson    |     2 | Iceland
 Robbie Brady            |     2 | Republic of Ireland
 Hal Robson-Kanu         |     2 | Wales
 Gareth McAuley          |     2 | Northern Ireland
 Balazs Dzsudzsak        |     2 | Hungary
 Mario Gomez             |     2 | Germany
 Jakub Blaszczykowski    |     2 | Poland
 Graziano Pelle          |     2 | Italy
 Ivan PeriSic            |     2 | Croatia
 Birkir Bjarnason        |     2 | Iceland
 Radja Nainggolan        |     2 | Belgium
 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
 Ricardo Quaresma        |     1 | Portugal
 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
 Ivan Rakitic            |     1 | Croatia
 Bastian Schweinsteiger  |     1 | Germany
 Wayne Rooney            |     1 | England
 Eder                    |     1 | Portugal
 Eric Dier               |     1 | England
 Fabian Schar            |     1 | Switzerland
 Niall McGinn            |     1 | Northern Ireland
 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
 Vasili Berezutski       |     1 | Russia
 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
 Daniel Sturridge        |     1 | England
(77 rows)

Query Visualization:

Duration:

Query visualization of Find the total number of goals scored by each player within normal play schedule and arrange the result set in descending order of goal - Duration

Rows:

Query visualization of Find the total number of goals scored by each player within normal play schedule and arrange the result set in descending order of goal - Rows

Cost:

Query visualization of Find the total number of goals scored by each player within normal play schedule and arrange the result set in descending order of goal - Cost

Practice Online


Sample Database: soccer

soccer database relationship structure

Have another way to solve this solution? Contribute your code (and comments) through Disqus.

Previous: Write a query in SQL to find the number of goal scored by each team in every match within normal play schedule.
Next: Write a query in SQL to find the highest individual scorer in EURO cup 2016.

What is the difficulty level of this exercise?



New Content: Composer: Dependency manager for PHP, R Programming