Soccer Database - Subquery: Exercises, Practice, Solution
SQL [33 exercises with solution]
Sample Database: soccer
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)
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 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)
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 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
.....
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 in the second half. Go to the editor
Sample table: match_mast
Sample Output:
max ----- 374 (1 row)
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
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 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)
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)
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)
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)
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 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)
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 the matches with the most penalty shots. 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 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)
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)
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)
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)
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)
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)
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
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
- Exercises: Weekly Top 16 Most Popular Topics
- SQL Exercises, Practice, Solution - JOINS
- SQL Exercises, Practice, Solution - SUBQUERIES
- JavaScript basic - Exercises, Practice, Solution
- Java Array: Exercises, Practice, Solution
- C Programming Exercises, Practice, Solution : Conditional Statement
- HR Database - SORT FILTER: Exercises, Practice, Solution
- C Programming Exercises, Practice, Solution : String
- Python Data Types: Dictionary - Exercises, Practice, Solution
- Python Programming Puzzles - Exercises, Practice, Solution
- C++ Array: Exercises, Practice, Solution
- JavaScript conditional statements and loops - Exercises, Practice, Solution
- C# Sharp Basic Algorithm: Exercises, Practice, Solution
- Python Lambda - Exercises, Practice, Solution
- Python Pandas DataFrame: Exercises, Practice, Solution
- Conversion Tools
- JavaScript: HTML Form Validation