Please note, this is a STATIC archive of website www.w3resource.com from 19 Jul 2022, cach3.com does not collect or store any user information, there is no "phishing" involved.
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 participated in the EURO cup 2016.  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 in EURO cup 2016 within normal play schedule.  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 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 ended with 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 the date when Football EURO cup 2016 begins.  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 in EURO cup 2016.  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 got a result by 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 the 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 goal scored in every match within 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 those matches where no stoppage time added in 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 ending with a goalless draw in-group stage of play. 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 count the number of matches ending with only one goal win, except those matches, which was decided by penalty shoot-out. 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 within normal time of play. 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 replaced in 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 total number of players replaced in the first half of play. 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 have there 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 count the total number of players replaced in the extra time of play.  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 substitute happened in various stage of play for the entire 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 shootout 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 scored goal in penalty shootout 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 tables, write a SQL query to find the players with shot number they taken 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 tables, write a SQL query to count the number of penalty shots taken by the teams. 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 booking happened in each half of play within 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 booking happened in 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 booking 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.



SQL: Tips of the Day

SQL Server SELECT into existing table. 

INSERT INTO dbo.TABLETWO
SELECT col1, col2
  FROM dbo.TABLEONE
 WHERE col3 LIKE @search_key

This assumes there's only two columns in dbo.TABLETWO - you need to specify the columns otherwise:

INSERT INTO dbo.TABLETWO
  (col1, col2)
SELECT col1, col2
  FROM dbo.TABLEONE
 WHERE col3 LIKE @search_key

Database: SQL Server

Ref: https://bit.ly/3y6tpA3