w3resource

SQL exercises on soccer Database: Basic - Exercises, Practice, Solution

SQL [29 exercises with solution]

Sample Database: soccer

soccer database relationship structure

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)

Click me to see the solution

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)

Click me to see the solution

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)

Click me to see the solution

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)

Click me to see the solution

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)

Click me to see the solution

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)

Click me to see the solution

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)

Click me to see the solution

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)

Click me to see the solution

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)

Click me to see the solution

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)

Click me to see the solution

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

Click me to see the solution

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)

Click me to see the solution

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)

Click me to see the solution

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)

Click me to see the solution

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)

Click me to see the solution

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)

Click me to see the solution

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)

Click me to see the solution

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)

Click me to see the solution

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)

Click me to see the solution

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)

Click me to see the solution

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


Click me to see the solution

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)

Click me to see the solution

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)

Click me to see the solution

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)

Click me to see the solution

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

Click me to see the solution

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)

Click me to see the solution

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)

Click me to see the solution

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)

Click me to see the solution

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)

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.



Share this Tutorial / Exercise on : Facebook and Twitter

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