w3resource

Soccer Database - Subquery: Exercises, Practice, Solution

SQL [33 exercises with solution]

[An editor is available at the bottom of the page to write and execute the scripts. Go to the editor]

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.

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.

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.

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.

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.

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.

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.

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.

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.

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.

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.

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.

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.

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.

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.

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.

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.

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

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.

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.

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.

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

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.

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.

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.

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.

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.

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.

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.

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.

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.

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.

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

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.



Follow us on Facebook and Twitter for latest update.

SQL: Tips of the Day

Difference between natural join and inner join

One significant difference between INNER JOIN and NATURAL JOIN is the number of columns returned-

Consider:

TableA                           TableB
+------------+----------+        +--------------------+    
|Column1     | Column2  |        |Column1  |  Column3 |
+-----------------------+        +--------------------+
| 1          |  2       |        | 1       |   3      |
+------------+----------+        +---------+----------+

The INNER JOIN of TableA and TableB on Column1 will return

SELECT * FROM TableA AS a INNER JOIN TableB AS b USING (Column1);
SELECT * FROM TableA AS a INNER JOIN TableB AS b ON a.Column1 = b.Column1;

+------------+-----------+---------------------+    
| a.Column1  | a.Column2 | b.Column1| b.Column3|
+------------------------+---------------------+
| 1          |  2        | 1        |   3      |
+------------+-----------+----------+----------+

The NATURAL JOIN of TableA and TableB on Column1 will return:

SELECT * FROM TableA NATURAL JOIN TableB
+------------+----------+----------+    
|Column1     | Column2  | Column3  |
+-----------------------+----------+
| 1          |  2       |   3      |
+------------+----------+----------+

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

 

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