w3resource

SQL Exercise: Goals scored by each player during normal play

SQL soccer Database: Joins Exercise-3 with Solution

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


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)

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.

Relational Algebra Expression:

Relational Algebra Expression: Find the total number of goals scored by each player within normal play schedule and arrange the result set in descending order of goal.

Relational Algebra Tree:

Relational Algebra Tree: Find the total number of goals scored by each player within normal play schedule and arrange the result set in descending order of goal.

Practice Online


Sample Database: soccer

soccer database relationship structure

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

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

Previous SQL Exercise: Number of goals scored by each team in each match.
Next SQL Exercise: Find the highest individual scorer in EURO cup 2016.

What is the difficulty level of this exercise?

Test your Programming skills with w3resource's quiz.



Follow us on Facebook and Twitter for latest update.

SQL: Tips of the Day

What is the best way to paginate results in SQL Server?

SELECT  *
FROM    ( SELECT    ROW_NUMBER() OVER ( ORDER BY OrderDate ) AS RowNum, *
          FROM      Orders
          WHERE     OrderDate >= '1980-01-01'
        ) AS RowConstrainedResult
WHERE   RowNum >= 1
    AND RowNum < 20
ORDER BY RowNum

Database: SQL Server

Ref: https://bit.ly/3MGrNlk

 





We are closing our Disqus commenting system for some maintenanace issues. You may write to us at reach[at]yahoo[dot]com or visit us at Facebook