import pandas as pd
import sqlite3
Dataset¶
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:
- Country.csv
- League.csv
- players_20.csv
- Team.csv
- teams_and_leagues.csv
Acknowledgements¶
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.
SQL joins are one of the important aspects of extracting information from multiple tables. We only provide a summary of joins in the forms of the following diagram. For a detailed note, please see this link.
Picture Source: codeproject.com
Inner JOIN¶
SELECT A.Column1, A.Column2, ...
FROM
TableA A
INNER JOIN TableB B
ON A.key = B.key;
[into New_Table]
This querying data returns a joining (on defined keys) of TableA and TableB using the records that are available in both Table A and TableB.
mySQL("""
Select
B.url,
A.team_long_name,
B.league_name
from Team A
inner join teams_and_leagues B
on
A.team_fifa_api_id = B.url
""")
Left JOIN¶
SELECT A.Column1, A.Column2, ...
FROM
TableA A
Left JOIN TableB B
ON A.key = B.key;
[into New_Table]
This querying data returns a joining (on defined keys) of TableA and TableB using all records that are available in Table A and the records in TableB that are available in TableA as well.
mySQL("""
Select
B.url,
A.team_long_name,
B.league_name
from Team A
left join teams_and_leagues B
on
A.team_fifa_api_id = B.url
""")
Right JOIN¶
SELECT A.Column1, A.Column2, ...
FROM
TableA A
Right JOIN TableB B
ON A.key = B.key;
[into New_Table]
This querying data returns a joining (on defined keys) of TableA and TableB using all records that are available in Table B and the records in TableA that are available in TableB as well.
Note: RIGHT and FULL OUTER JOINs are not currently supported in SQLite
FULL OUTER JOIN¶
SELECT A.Column1, A.Column2, ...
FROM
TableA A
FULL OUTER JOIN TableB B
ON A.key = B.key;
[into New_Table]
This querying data returns a joining (on defined keys) of TableA and TableB using the records that are available in Table A and TableB.
Note: RIGHT and FULL OUTER JOINs are not currently supported in SQLite
Left Excluding JOIN¶
SELECT A.Column1, A.Column2, ...
FROM
TableA A
Left JOIN TableB B
ON A.key = B.key;
Where B.key is Null
This querying data returns a joining (on defined keys) of TableA and TableB using all records that are available in Table A but not in Table B.
mySQL("""
Select
B.url,
A.team_long_name,
B.league_name
from Team A
Left Join teams_and_leagues B
on
A.team_fifa_api_id = B.url
where
B.url is Null
""")
Right Excluding JOIN¶
SELECT A.Column1, A.Column2, ...
FROM
TableA A
Left JOIN TableB B
ON A.key = B.key;
Where A.key is Null
This querying data returns a joining (on defined keys) of TableA and TableB using all records that are available in Table B but not in Table A.
mySQL("""
Select
B.url,
A.team_long_name,
B.league_name
from Team A
Left Join teams_and_leagues B
on
A.team_fifa_api_id = B.url
where
A.team_fifa_api_id is Null
""")
Outer Excluding JOIN¶
SELECT A.Column1, A.Column2, ...
FROM
TableA A
FULL OUTER JOIN TableB B
ON A.key = B.key;
Where
A.key is Null or B.key is Null
This querying data returns a joining (on defined keys) of TableA and TableB using all records that are available in Table A and Table B but not in the mutual records.
Note: RIGHT and FULL OUTER JOINs are not currently supported in SQLite