Please note, this is a STATIC archive of website www.w3resource.com from 19 Jul 2022, cach3.com does not collect or store any user information, there is no "phishing" involved.
w3resource

SQL Exercises, Practice, Solution - SUBQUERIES exercises on soccer Database

SQL [33 exercises with solution]

Sample Database: soccer

soccer database relationship structure

1. From the following tables, write a SQL query to find the teams played the first match of EURO cup 2016. 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 most watched match in the world. 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 tables, write a SQL query to find those players who scored number of goals in every 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, which had been added, in the second half of play.  Go to the editor

Sample table: match_mast


Sample Output:

 max
-----
 374
(1 row)

Click me to see the solution

10. From the following tables, 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 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 tables, write a SQL query to find the players who were the goalkeepers of England squad in 2016-EURO cup. 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 tables, write a SQL query to find the players under contract to Liverpool were in the Squad of England in 2016-EURO cup. Return player name, jersey number, 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 tables, write a SQL query to find the players who scored the last goal in the 2nd semi-final, i.e., 50th match in EURO cup 2016. 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 tables, write a SQL query to find the captain of the EURO cup 2016 winning team from Portugal. 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 tables, write a SQL query to find the Germany goalkeeper who didn't concede any goal 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 those match where the highest number of penalty shots taken.  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 find the match number where highest number of penalty shots had been 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 tables, write a SQL query to find the player of 'Portugal' who taken 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 tables, write a SQL query to find the stage of match where penalty kick number 23 had been 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 tables, write a SQL query to find the venues where penalty shoot-out matches 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 tables, write a SQL query to find the date when penalty shootout matches 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 quickest 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.



SQL: Tips of the Day

SQL Server SELECT into existing table. 

INSERT INTO dbo.TABLETWO
SELECT col1, col2
  FROM dbo.TABLEONE
 WHERE col3 LIKE @search_key

This assumes there's only two columns in dbo.TABLETWO - you need to specify the columns otherwise:

INSERT INTO dbo.TABLETWO
  (col1, col2)
SELECT col1, col2
  FROM dbo.TABLEONE
 WHERE col3 LIKE @search_key

Database: SQL Server

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