SQL Exercises, Practice, Solution - SUBQUERIES exercises on soccer Database
SQL [33 exercises with solution]
Sample Database: soccer
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)
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)
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)
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)
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)
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
.....
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)
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)
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)
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
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)
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)
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)
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)
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)
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)
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)
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)
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)
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)
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)
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)
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)
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)
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)
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)
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)
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)
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)
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)
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)
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)
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)
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.
- New Content published on w3resource:
- Scala Programming Exercises, Practice, Solution
- Python Itertools exercises
- Python Numpy exercises
- Python GeoPy Package exercises
- Python Pandas exercises
- Python nltk exercises
- Python BeautifulSoup exercises
- Form Template
- Composer - PHP Package Manager
- PHPUnit - PHP Testing
- Laravel - PHP Framework
- Angular - JavaScript Framework
- Vue - JavaScript Framework
- Jest - JavaScript Testing Framework