SQL exercises on soccer Database: Basic - Exercises, Practice, Solution
SQL [29 exercises with solution]
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. Go to the editor
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. Go to the editor
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. Go to the editor
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. Go to the editor
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. Go to the editor
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. Go to the editor
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. Go to the editor
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. Go to the editor
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. Go to the editor
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. Go to the editor
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. Go to the editor
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. Go to the editor
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. Go to the editor
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. Go to the editor
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". Go to the editor
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". Go to the editor
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". Go to the editor
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". Go to the editor
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. Go to the editor
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. Go to the editor
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. Go to the editor
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". Go to the editor
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". Go to the editor
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". Go to the editor
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. Go to the editor
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". Go to the editor
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. Go to the editor
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. Go to the editor
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. Go to the editor
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
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