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.
SELECT A.Column1, A.Column2, ...
FROM
TableA
UNION ALL
SELECT A.Column1, A.Column2, ...
FROM
TableA
In the set theory, let $A$ represent TableA and $B$ represents TableB. Then, this querying data is equivalent to:
mySQL("""
Select
short_name,
club
from
players_20
Union All
Select
short_name,
club
from
players_19
""")
SELECT A.Column1, A.Column2, ...
FROM
TableA
INTERSECT
SELECT A.Column1, A.Column2, ...
FROM
TableA
In the set theory, let $A$ represent TableA and $B$ represents TableB. Then, this querying data is equivalent to:
mySQL("""
Select
short_name,
club
from
players_20
Intersect
Select
short_name,
club
from
players_19
""")
SELECT A.Column1, A.Column2, ... FROM TableA
MINUS
SELECT A.Column1, A.Column2, ... FROM TableA
In the set theory, let $A$ represent TableA and $B$ represents TableB. Then, this querying data is equivalent to:
SELECT
column_list
FROM Table
Where Column1 [Not] Like defined_pattern
mySQL("""
Select short_name, Club from players_20 where club like '%Juve%'
""")
SELECT
column_list
FROM Table
Where Column1 [Not] in Value_List
mySQL("""
Select
short_name,
Club
from
players_20
where
club in ('Juventus')
""")
SELECT
column_list
FROM Table
Where Column1 between Low and High
mySQL("""
Select
short_name,
Club,
Age
from
players_20
where
club in ('Juventus')
and
Age between 15 and 25
""")
SELECT
column_list
FROM Table
Where Column1 is [Not] Null