# 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 best way to paginate results in SQL Server**?

SELECT * FROM ( SELECT ROW_NUMBER() OVER ( ORDER BY OrderDate ) AS RowNum, * FROM Orders WHERE OrderDate >= '1980-01-01' ) AS RowConstrainedResult WHERE RowNum >= 1 AND RowNum < 20 ORDER BY RowNum

**Database: SQL Server**

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

**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