w3resource
Soccer Database Exercises

SQL exercises on soccer Database: Display the list of players scored number of goals in every matches

SQL soccer Database: Subqueries Exercise-6 with Solution

6. Write a query in SQL to display the list of players scored number of goals in every matches.

Sample table: goal_details


Sample table: soccer_country


Sample table: player_mast


Sample Solution:

SQL Code:

SELECT match_no,country_name, player_name, COUNT(match_no)
FROM goal_details a, soccer_country b, player_mast c
WHERE a.team_id=b.country_id
AND a.player_id=c.player_id
GROUP BY match_no,country_name,player_name
ORDER BY match_no;

Sample Output:

 match_no |    country_name     |       player_name       | count
----------+---------------------+-------------------------+-------
        1 | France              | Dimitri Payet           |     1
        1 | France              | Olivier Giroud          |     1
        1 | Romania             | Bogdan Stancu           |     1
        2 | Switzerland         | Fabian Schar            |     1
        3 | Slovakia            | Ondrej Duda             |     1
        3 | Wales               | Gareth Bale             |     1
        3 | Wales               | Hal Robson-Kanu         |     1
        4 | England             | Eric Dier               |     1
        4 | Russia              | Vasili Berezutski       |     1
        5 | Croatia             | Luka Modric             |     1
        6 | Poland              | Arkadiusz Milik         |     1
        7 | Germany             | Bastian Schweinsteiger  |     1
        7 | Germany             | Thomas Muller           |     1
        8 | Spain               | Gerard Pique            |     1
        9 | Republic of Ireland | Ciaran Clark            |     1
        9 | Republic of Ireland | Wes Hoolahan            |     1
       10 | Italy               | Emanuele Giaccherini    |     1
       10 | Italy               | Graziano Pelle          |     1
       11 | Hungary             | Adam Szalai             |     1
       11 | Hungary             | Zoltan Stieber          |     1
       12 | Iceland             | Birkir Bjarnason        |     1
       12 | Portugal            | Nani                    |     1
       13 | Russia              | Denis Glushakov         |     1
       13 | Slovakia            | Marek Hamsik            |     1
       13 | Slovakia            | Vladimir Weiss          |     1
       14 | Romania             | Bogdan Stancu           |     1
       14 | Switzerland         | Admir Mehmedi           |     1
       15 | France              | Antoine Griezmann       |     1
       15 | France              | Dimitri Payet           |     1
       16 | England             | Daniel Sturridge        |     1
       16 | England             | Jamie Vardy             |     1
       16 | Wales               | Gareth Bale             |     1
       17 | Northern Ireland    | Gareth McAuley          |     1
       17 | Northern Ireland    | Niall McGinn            |     1
       19 | Italy               | Eder                    |     1
       20 | Croatia             | Ivan PeriSic            |     1
       20 | Croatia             | Ivan Rakitic            |     1
       20 | Czech Republic      | Milan Skoda             |     1
       20 | Czech Republic      | TomasNecid              |     1
       21 | Spain               | Alvaro Morata           |     2
       21 | Spain               | Nolito                  |     1
       22 | Belgium             | Axel Witsel             |     1
       22 | Belgium             | Romelu Lukaku           |     2
       23 | Iceland             | Birkir Saevarsson       |     1
       23 | Iceland             | Gylfi Sigurdsson        |     1
       25 | Albania             | Armando Sadiku          |     1
       27 | Wales               | Aaron Ramsey            |     1
       27 | Wales               | Gareth Bale             |     1
       27 | Wales               | Neil Taylor             |     1
       29 | Poland              | Jakub Blaszczykowski    |     1
       30 | Germany             | Mario Gomez             |     1
       31 | Turkey              | Burak Yilmaz            |     1
       31 | Turkey              | Ozan Tufan              |     1
       32 | Croatia             | Ivan PeriSic            |     1
       32 | Croatia             | Nikola Kalinic          |     1
       32 | Spain               | Alvaro Morata           |     1
       33 | Austria             | Alessandro Schopf       |     1
       33 | Iceland             | Jon Dadi Bodvarsson     |     2
       34 | Hungary             | Balazs Dzsudzsak        |     2
       34 | Hungary             | Zoltan Gera             |     1
       34 | Portugal            | Cristiano Ronaldo       |     2
       34 | Portugal            | Nani                    |     1
       35 | Republic of Ireland | Robbie Brady            |     1
       36 | Belgium             | Radja Nainggolan        |     1
       37 | Poland              | Jakub Blaszczykowski    |     1
       37 | Switzerland         | Xherdan Shaqiri         |     1
       38 | Northern Ireland    | Gareth McAuley          |     1
       39 | Portugal            | Ricardo Quaresma        |     1
       40 | France              | Antoine Griezmann       |     2
       40 | Republic of Ireland | Robbie Brady            |     1
       41 | Germany             | Jerome Boateng          |     1
       41 | Germany             | Julian Draxler          |     1
       41 | Germany             | Mario Gomez             |     1
       42 | Belgium             | Eden Hazard             |     1
       42 | Belgium             | Michy Batshuayi         |     1
       42 | Belgium             | Toby Alderweireld       |     1
       42 | Belgium             | Yannick Carrasco        |     1
       43 | Italy               | Giorgio Chiellini       |     1
       43 | Italy               | Graziano Pelle          |     1
       44 | England             | Wayne Rooney            |     1
       44 | Iceland             | Arnor Ingvi Traustason  |     1
       44 | Iceland             | Kolbeinn Sigthorsson    |     1
       45 | Poland              | Robert Lewandowski      |     1
       45 | Portugal            | Renato Sanches          |     1
       46 | Belgium             | Radja Nainggolan        |     1
       46 | Wales               | Ashley Williams         |     1
       46 | Wales               | Hal Robson-Kanu         |     1
       46 | Wales               | Sam Vokes               |     1
       47 | Germany             | Mesut ozil              |     1
       47 | Italy               | Leonardo Bonucci        |     1
       48 | France              | Antoine Griezmann       |     1
       48 | France              | Dimitri Payet           |     1
       48 | France              | Olivier Giroud          |     2
       48 | France              | Paul Pogba              |     1
       48 | Iceland             | Birkir Bjarnason        |     1
       48 | Iceland             | Kolbeinn Sigthorsson    |     1
       49 | Portugal            | Cristiano Ronaldo       |     1
       49 | Portugal            | Nani                    |     1
       50 | France              | Antoine Griezmann       |     2
       51 | Portugal            | Eder                    |     1
(100 rows)

Query Visualization:

Duration:

Query visualization of Display the list of players scored number of goals in every matches - Duration

Rows:

Query visualization of Display the list of players scored number of goals in every matches - Rows

Cost:

Query visualization of Display the list of players scored number of goals in every matches - 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 match no, play stage, date of match, number of gole scored, and the result of the match where Portugal played against Hungary.
Next: Write a query in SQL to find the teams who played the heighest audence match.

What is the difficulty level of this exercise?



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