import pandas as pd
import sqlite3
In addition, here, we use FIFA 20 Complete Player Datasetet and European Soccer Database from Kaggle.com.
The datasets that we use from the above likes are:
The datasets are also publicly available website sofifa.com.
In this article, we provide a summary of basic SQL Server commands and syntaxes. For sake of demonstrations SQL Server syntaxes, we use SQLite3. Moreover, the following function can help us to mimic SQL Server Environment in this Jupyter notebook.
connection = sqlite3.connect('FIFA.db')
cursor = connection.cursor()
def mySQL(Syntax): return pd.read_sql_query(Syntax, connection)
Here we provide a summary of popular SQL Server command with examples. For full notes, please see the official SQL Server documents at docs.microsoft.com.
AVG here stands for Average value. For example, to get the average Age of each club, we can try:
mySQL("""
Select
Avg(A.age) as Ave_Age,
B.team_long_name,
C.league_name
from players_20 A
inner join Team B
on A.club = B.team_long_name
inner join teams_and_leagues C
on B.team_fifa_api_id = C.url
Group by
B.team_long_name,
C.league_name
Order by
C.league_name,
B.team_long_name
""")
We demonstrate this using an example, let's count the number of players in each Club.
mySQL("""
Select
Count(short_name) as Number_of_Players,
B.team_long_name,
C.league_name
from players_20 A
inner join Team B
on A.club = B.team_long_name
inner join teams_and_leagues C
on B.team_fifa_api_id = C.url
Group by
B.team_long_name,
C.league_name
Order by
C.league_name,
B.team_long_name
""")
This returns a summation of values.
This returns the minimum value.
mySQL("""
Select
Min(age) as Min_Age,
B.team_long_name,
C.league_name
from players_20 A
inner join Team B
on A.club = B.team_long_name
inner join teams_and_leagues C
on B.team_fifa_api_id = C.url
Group by
B.team_long_name,
C.league_name
Order by
C.league_name,
B.team_long_name
""")
This returns the maximum value.
mySQL("""
Select
Max(age) as Max_Age,
B.team_long_name,
C.league_name
from players_20 A
inner join Team B
on A.club = B.team_long_name
inner join teams_and_leagues C
on B.team_fifa_api_id = C.url
Group by
B.team_long_name,
C.league_name
Order by
C.league_name,
B.team_long_name
""")
This is useful for converting values. Consider the following example.
mySQL("""
Select
Sum(age)/Count(age) as Average_Age,
B.team_long_name,
C.league_name
from players_20 A
inner join Team B
on A.club = B.team_long_name
inner join teams_and_leagues C
on B.team_fifa_api_id = C.url
where
B.team_long_name = 'Manchester United'
Group by
B.team_long_name,
C.league_name
Order by
C.league_name,
B.team_long_name
""")
Note that we have calculated a summation of some values and divided by some number. Since Age is an int value, the result after dividing the summation is also an int value. However, if we use cast and covert the summation first to float and then the result will be also a float value. Thus,
mySQL("""
Select
cast(Sum(age) as float)/Count(age) as Average_Age,
B.team_long_name,
C.league_name
from players_20 A
inner join Team B
on A.club = B.team_long_name
inner join teams_and_leagues C
on B.team_fifa_api_id = C.url
where
B.team_long_name = 'Manchester United'
Group by
B.team_long_name,
C.league_name
Order by
C.league_name,
B.team_long_name
""")