w3resource

Soccer Database - Subquery: Exercises, Practice, Solution

SQL [33 exercises with solution]

Sample Database: soccer

soccer database relationship structure

1. From the following table, write a SQL query to find out which teams played the first match of the 2016 Euro Cup. Return match number, country name.  Go to the editor

Sample table: match_details


Sample table: soccer_country


Sample Output:

 match_no | country_name
----------+--------------
        1 | France
        1 | Romania
(2 rows)

Click me to see the solution

2. From the following tables, write a SQL query to find the winner of EURO cup 2016. Return country name.  Go to the editor

Sample table: soccer_country


Sample table: match_details


Sample Output:

   team
----------
 Portugal
(1 row)

Click me to see the solution

3. From the following table, write a SQL query to find the highest audience match. Return match_no, play_stage, goal_score, audience.  Go to the editor

Sample table: match_mast


Sample Output:

 match_no | play_stage | goal_score | audence
----------+------------+------------+---------
       48 | Q          | 5-2        |   76833
(1 row)

Click me to see the solution

4. From the following tables, write a SQL query to find the match number in which Germany played against Poland. Group the result set on match number. Return match number.  Go to the editor

Sample table: match_details


Sample table: soccer_country


Sample Output:

 match_no
----------
       18
(1 row)

Click me to see the solution

5. From the following tables, write a SQL query to find the result of the match where Portugal played against Hungary. Return match_no, play_stage, play_date, results, goal_score.  Go to the editor

Sample table: match_mast


Sample table: match_details


Sample table: soccer_country


Sample Output:

 match_no | play_stage | play_date  | results | goal_score
----------+------------+------------+---------+------------
       34 | G          | 2016-06-22 | DRAW    | 3-3
(1 row)

Click me to see the solution

6. From the following table, write a SQL query to find the players who scored the most goals in each match. Group the result set on match number, country name and player name. Sort the result-set in ascending order by match number. Return match number, country name, player name and number of matches.  Go to the editor

Sample table: goal_details


Sample table: soccer_country


Sample table: player_mast


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
.....

Click me to see the solution

7. From the following tables, write a SQL query to find the highest audience match. Return country name of the teams.  Go to the editor

Sample table: soccer_country


Sample table: goal_details


Sample table: match_mast


Sample Output:

 country_name
--------------
 France
 Iceland
(2 rows)

Click me to see the solution

8. From the following tables, write a SQL query to find the player who scored the last goal for Portugal against Hungary. Return player name.  Go to the editor

Sample table: player_mast


Sample table: goal_details


Sample table: match_details


Sample table: soccer_country


Sample Output:

    player_name
--------------------
 Cristiano Ronaldo
(1 row)

Click me to see the solution

9. From the following table, write a SQL query to find the second-highest stoppage time in the second half.  Go to the editor

Sample table: match_mast


Sample Output:

 max
-----
 374
(1 row)

Click me to see the solution

10. From the following table, write a SQL query to find the teams played the match where the second highest stoppage time had been added in the second half of play. Return country name of the teams.  Go to the editor

Sample table: soccer_country


Sample table: match_details


Sample Output:

 country_name
--------------
 Albania
 France
(2 rows)

Sample table: match_mast


Click me to see the solution

11. From the following table, write a SQL query to find the teams played the match where second highest stoppage time had been added in second half of play. Return match_no, play_date, stop2_sec.  Go to the editor

Sample table: match_mast


Sample Output:

 match_no | play_date  | stop2_sec
----------+------------+-----------
       15 | 2016-06-16 |       374
(1 row)

Click me to see the solution

12. From the following tables, write a SQL query to find the team, which was defeated by Portugal in EURO cup 2016 final. Return the country name of the team.  Go to the editor

Sample table: soccer_country


Sample table: match_details


Sample Output:

 country_name
--------------
 France
(1 row)

Click me to see the solution

13. From the following table, write a SQL query to find the club, which supplied the most number of players to the 2016-EURO cup. Return club name, number of players.  Go to the editor

Sample table: player_mast


Sample Output:

 playing_club | count
--------------+-------
 Juventus     |    12
 Liverpool    |    12
(2 rows)

Click me to see the solution

14. From the following tables, write a SQL query to find the player who scored the first penalty of the tournament. Return player name and Jersey number.  Go to the editor

Sample table: player_mast


Sample table: goal_details


Sample Output:

  player_name   | jersey_no
----------------+-----------
 Bogdan Stancu  |        19
(1 row)

Click me to see the solution

15. From the following tables, write a SQL query to find the player who scored the first penalty in the tournament. Return player name, Jersey number and country name.  Go to the editor

Sample table: player_mast


Sample table: goal_details


Sample table: soccer_country


Sample Output:

  player_name   | jersey_no | country_name
----------------+-----------+--------------
 Bogdan Stancu  |        19 | Romania
(1 row)

Click me to see the solution

16. From the following tables, write a SQL query to find the goalkeeper for Italy in penalty shootout against Germany in Football EURO cup 2016. Return goalkeeper name.  Go to the editor

Sample table: player_mast


Sample table: penalty_gk


Sample table: soccer_country


Sample Output:

    player_name
-------------------
 Gianluigi Buffon
(1 row)

Click me to see the solution

17. From the following tables, write a SQL query to find the number of goals Germany scored at the tournament.  Go to the editor

Sample table: goal_details


Sample table: soccer_country


Sample Output:

 count
-------
     7
(1 row)

Click me to see the solution

18. From the following table, write a SQL query to find the players who were the goalkeepers of the England team that played in the 2016-EURO. Return player name, jersey number, club name  Go to the editor

Sample table: player_mast


Sample table: soccer_country


Sample Output:

   player_name   | jersey_no | playing_club
-----------------+-----------+--------------
 Joe Hart        |         1 | Man. City
 Fraser Forster  |        13 | Southampton
 Tom Heaton      |        23 | Burnley
(3 rows)

Click me to see the solution

19. From the following table, write a SQL query to find the Liverpool players who were part of England's squad at the 2016 Euro Cup. Return player name, jersey number, and position to play, age.  Go to the editor

Sample table: player_mast


Sample table: soccer_country


Sample Output:

    player_name    | jersey_no | posi_to_play | age
-------------------+-----------+--------------+-----
 James Milner      |         4 | MF           |  30
 Adam Lallana      |         8 | MF           |  28
 Nathaniel Clyne   |        12 | DF           |  25
 Jordan Henderson  |        14 | MF           |  26
 Daniel Sturridge  |        15 | FD           |  26
(5 rows)

Click me to see the solution

20. From the following table, write a SQL query to find the players who scored the last goal in the second semi-final, i.e., the 50th match of the 2016-EURO Cup. Return player name, goal time, goal half, country name.  Go to the editor

Sample table: player_mast


Sample table: goal_details


Sample table: soccer_country


Sample Output:

    player_name     | goal_time | goal_half | country_name
--------------------+-----------+-----------+--------------
 Antoine Griezmann  |        72 |         2 | France
(1 row)

Click me to see the solution

21. From the following table, write a SQL query to find out who was the captain of Portugal's winning EURO cup 2016 team. Return the captain name.  Go to the editor

Sample table: player_mast


Sample table: match_captain


Sample table: match_details


Sample Output:

    player_name
--------------------
 Cristiano Ronaldo
(1 row)

Click me to see the solution

22. From the following tables, write a SQL query to count the number of players played for 'France’ in the final. Return 'Number of players shared fields'.  Go to the editor

Sample table: player_in_out


Sample table: match_mast


Sample table: soccer_country


Sample Output:

 Number of players shared fields
---------------------------------
                              14
(1 row)

Click me to see the solution

23. From the following table, write a SQL query to find the Germany goalkeeper who did not concede any goals in their group stage matches. Return goalkeeper name, jersey number.  Go to the editor

Sample table: player_mast


Sample table: match_details


Sample table: soccer_country


Sample Output:

  player_name  | jersey_no
---------------+-----------
 Manuel Neuer  |         1
(1 row)

Click me to see the solution

24. From the following tables, write a SQL query to find the runners-up in Football EURO cup 2016. Return country name.  Go to the editor

Sample table: match_details


Sample table: soccer_country


Sample Output:

 country_name
--------------
 France
(1 row)

Click me to see the solution

25. From the following tables, write a SQL query to find the maximum penalty shots taken by the teams. Return country name, maximum penalty shots.  Go to the editor

Sample table: soccer_country


Sample table: penalty_shootout


Sample Output:

 country_name | shots
--------------+-------
 Poland       |     9
 Italy        |     9
 Germany      |     9
(3 rows)

Click me to see the solution

26. From the following tables, write a SQL query to find the maximum number of penalty shots taken by the players. Return country name, player name, jersey number and number of penalty shots.  Go to the editor

Sample table : player_mast


Sample table : penalty_shootout


Sample table : soccer_country


Sample Output:

 country_name |      player_name      | jersey_no | shots
--------------+-----------------------+-----------+-------
 Poland       | Jakub Blaszczykowski  |        16 |     2
 Poland       | Arkadiusz Milik       |         7 |     2
 Poland       | Robert Lewandowski    |         9 |     2
 Poland       | Kamil Glik            |        15 |     2
(4 rows)

Click me to see the solution

27. From the following table, write a SQL query to find the matches with the most penalty shots.  Go to the editor

Sample table : penalty_shootout


Sample Output:

 match_no | shots
----------+-------
       47 |    18
(1 row)

Click me to see the solution

28. From the following table, write a SQL query to determine the match number where the most penalty shots were taken. Return match number, country name.  Go to the editor

Sample table: penalty_shootout


Sample Output:

 match_no | country_name
----------+--------------
       47 | Germany
       47 | Italy
(2 rows)

Click me to see the solution

29. From the following table, write a SQL query to find the player of ‘Portugal’ who took the seventh kick against ‘Poland’. Return match number, player name and kick number.  Go to the editor

Sample table: penalty_shootout


Sample table: soccer_country


Sample Output:

 match_no | player_name | kick_no
----------+-------------+---------
       45 | Nani        |       7
(1 row)

Click me to see the solution

30. From the following table, write a SQL query to find the stage of the match where penalty kick number 23 was taken. Return match number, play_stage.  Go to the editor

Sample table: match_mast


Sample table: penalty_shootout


Sample Output:

 match_no | play_stage
----------+------------
       47 | Q
(1 row)

Click me to see the solution

31. From the following table, write a SQL query to find the venues where penalty shoot-out matches were played. Return venue name.  Go to the editor

Sample table: soccer_venue


Sample table: match_mast


Sample table: penalty_shootout


Sample Output:

       venue_name
-------------------------
 Stade VElodrome
 Stade de Bordeaux
 Stade Geoffroy Guichard
(3 rows)

Click me to see the solution

32. From the following table, write a SQL query to find out when the penalty shootout matches were played. Return playing date.  Go to the editor

Sample table: match_mast


Sample table: penalty_shootout


Sample Output:

 play_date
------------
 2016-07-03
 2016-07-01
 2016-06-25
(3 rows)

Click me to see the solution

33. From the following table, write a SQL query to find the fastest goal at the EURO cup 2016, after 5 minutes. Return 'Quickest goal after 5 minutes'.  Go to the editor

Sample table: goal_details


Sample Output:

 Most quickest goal after 5 minutes
------------------------------------
                                  6
(1 row)

Click me to see the solution

More to come .......

Practice Online


Query visualizations are generated using Postgres Explain Visualizer (pev)

Do not submit any solution of the above exercises at here, if you want to contribute go to the appropriate exercise page.



Share this Tutorial / Exercise on : Facebook and Twitter

SQL: Tips of the Day

Count the occurrences of DISTINCT values?

example db
id         name
-----      ------
1          Mark
2          Mike
3          Paul
4          Mike
5          Mike
6          John
7          Mark
SELECT name,COUNT(*) as count 
FROM tablename 
GROUP BY name 
ORDER BY count DESC;

expected result

name       count
-----      -----
Mike       3
Mark       2
Paul       1
John       1

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