SQL exercises on soccer Database: Basic - Exercises, Practice, Solution
SQL [29 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
1. From the following table, write a SQL query to count the number of venues for EURO cup 2016. Return number of venues.
Sample table: soccer_venue
Sample Output:
count
-------
10
(1 row)
2. From the following table, write a SQL query to count the number of countries that participated in the 2016-EURO Cup.
Sample table: player_mast
Sample Output:
count
-------
24
(1 row)
3. From the following table, write a SQL query to find the number of goals scored within normal play during the EURO cup 2016.
Sample table: goal_details
Sample Output:
count ------- 108 (1 row)
4. From the following table, write a SQL query to find the number of matches that ended with a result.
Sample table: match_mast
Sample Output:
count
-------
40
(1 row)
5. From the following table, write a SQL query to find the number of matches that ended in draws.
Sample table: match_mast
Sample Output:
count
-------
11
(1 row)
6. From the following table, write a SQL query to find out when the Football EURO cup 2016 will begin.
Sample table: match_mast
Sample Output:
Beginning Date ---------------- 2016-06-11 (1 row)
7. From the following table, write a SQL query to find the number of self-goals scored during the 2016 European Championship.
Sample table: goal_details
Sample Output:
count
-------
3
(1 row)
8. From the following table, write a SQL query to count the number of matches ended with a results in-group stage.
Sample table: match_mast
Sample Output:
count
-------
25
(1 row)
9. From the following table, write a SQL query to find the number of matches that resulted in a penalty shootout.
Sample table: penalty_shootout
Sample Output:
count
-------
3
(1 row)
10. From the following table, write a SQL query to find number of matches decided by penalties in the Round 16.
Sample table: match_mast
Sample Output:
count
-------
1
(1 row)
11. From the following table, write a SQL query to find the number of goals scored in every match within a normal play schedule. Sort the result-set on match number. Return match number, number of goal scored.
Sample table: goal_details
Sample Output:
match_no | count
----------+-------
1 | 3
2 | 1
3 | 3
4 | 2
12. From the following table, write a SQL query to find the matches in which no stoppage time was added during the first half of play. Return match no, date of play, and goal scored.
Sample table: match_mast
Sample Output:
match_no | play_date | goal_score
----------+------------+------------
4 | 2016-06-12 | 1-1
(1 row)
13. From the following table, write a SQL query to count the number of matches that ended in a goalless draw at the group stage. Return number of matches.
Sample table: match_details
Sample Output:
count
-------
4
(1 row)
14. From the following table, write a SQL query to calculate the number of matches that ended in a single goal win, excluding matches decided by penalty shootouts. Return number of matches.
Sample table: match_details
Sample Output:
count
-------
13
(1 row)
15. From the following table, write a SQL query to count the number of players replaced in the tournament. Return number of players as "Player Replaced".
Sample table: player_in_out
Sample Output:
Player Replaced
-----------------
293
(1 row)
16. From the following table, write a SQL query to count the total number of players replaced during normal playtime. Return number of players as "Player Replaced".
Sample table: player_in_out
Sample Output:
Player Replaced
-----------------
275
(1 row)
17. From the following table, write a SQL query to count the number of players who were replaced during the stoppage time. Return number of players as "Player Replaced".
Sample table: player_in_out
Sample Output:
Player Replaced
-----------------
9
(1 row)
18. From the following table, write a SQL query to count the number of players who were replaced during the first half. Return number of players as "Player Replaced".
Sample table: player_in_out
Sample Output:
Player Replaced
-----------------
3
(1 row)
19. From the following table, write a SQL query to count the total number of goalless draws played in the entire tournament. Return number of goalless draws.
Sample table: match_details
Sample Output:
count
-------
4
(1 row)
20. From the following table, write a SQL query to calculate the total number of players who were replaced during the extra time.
Sample table: player_in_out
Sample Output:
count
-------
9
(1 row)
21. From the following table, write a SQL query to count the number of substitutes during various stages of the tournament. Sort the result-set in ascending order by play-half, play-schedule and number of substitute happened. Return play-half, play-schedule, number of substitute happened.
Sample table: player_in_out
22. From the following table, write a SQL query to count the number of shots taken in penalty shootouts matches. Number of shots as "Number of Penalty Kicks".
Sample table: penalty_shootout
Sample Output:
Number of Penalty Kicks
-------------------------
37
(1 row)
23. From the following table, write a SQL query to count the number of shots that were scored in penalty shootouts matches. Return number of shots scored goal as "Goal Scored by Penalty Kicks".
Sample table: penalty_shootout
Sample Output:
Goal Scored by Penalty Kicks
------------------------------
28
(1 row)
24. From the following table, write a SQL query to count the number of shots missed or saved in penalty shootout matches. Return number of shots missed as "Goal missed or saved by Penalty Kicks".
Sample table: penalty_shootout
Sample Output:
Goal missed or saved by Penalty Kicks
---------------------------------------
9
(1 row)
25. From the following table, write a SQL query to find the players with shot numbers they took in penalty shootout matches. Return match_no, Team, player_name, jersey_no, score_goal, kick_no.
Sample table: soccer_country
Sample table: penalty_shootout
Sample table: player_mast
Sample Output:
match_no | Team | player_name | jersey_no | score_goal | kick_no
----------+-------------+-------------------------+-----------+------------+---------
37 | Switzerland | Stephan Lichtsteiner | 2 | Y | 1
37 | Poland | Robert Lewandowski | 9 | Y | 2
37 | Switzerland | Granit Xhaka | 10 | N | 3
37 | Poland | Arkadiusz Milik | 7 | Y | 4
26. From the following table, write a SQL query to count the number of penalty shots taken by each team. Return country name, number of shots as "Number of Shots".
Sample table: soccer_country
Sample table: penalty_shootout
Sample Output:
country_name | Number of Shots --------------+----------------- Poland | 9 Italy | 9 Germany | 9 Portugal | 5 Switzerland | 5 (5 rows)
27. From the following table, write a SQL query to count the number of bookings in each half of play within the normal play schedule. Return play_half, play_schedule, number of booking happened.
Sample table: player_booked
Sample Output:
play_half | play_schedule | count
-----------+---------------+-------
1 | NT | 61
2 | NT | 123
(2 rows)
28. From the following table, write a SQL query to count the number of bookings during stoppage time.
Sample table: player_booked
Sample Output:
count
-------
10
(1 row)
29. From the following table, write a SQL query to count the number of bookings that happened in extra time.
Sample table: player_booked
Sample Output:
count
-------
7
(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
What is the most efficient/elegant way to parse a flat table into a tree?
WITH RECURSIVE MyTree AS (
SELECT * FROM MyTable WHERE ParentId IS NULL
UNION ALL
SELECT m.* FROM MyTABLE AS m JOIN MyTree AS t ON m.ParentId = t.Id
)
SELECT * FROM MyTree;
Ref: https://bit.ly/3FPYFFF
- Weekly Trends
- Java Basic Programming Exercises
- SQL Subqueries
- Adventureworks Database Exercises
- C# Sharp Basic Exercises
- SQL COUNT() with distinct
- JavaScript String Exercises
- JavaScript HTML Form Validation
- Java Collection Exercises
- SQL COUNT() function
- SQL Inner Join
- JavaScript functions Exercises
- Python Tutorial
- Python Array Exercises
- SQL Cross Join
- C# Sharp Array Exercises
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