<jupyter_start><jupyter_text>FIFA ANALYTICS<jupyter_code># Installing SQL module in the notebook
#!pip install ipython-sql
import sqlalchemy
sqlalchemy.__version__
#Loading the SQL module
%load_ext sql
# connect to a SQLite database
%sql sqlite://
# importing the database

%sql sqlite:///D://fifaDB.db<jupyter_output><empty_output><jupyter_text>PART A: Let's Get a General Overview of the database    The database is named **fifaDB** and has only one table called **soccer**.<jupyter_code>#show the table structure

%sql PRAGMA table_info('soccer'); 
# check the first 10 rows of the table

%sql SELECT * FROM soccer LIMIT 10;
# Let's get the next 20 rows after the first 10 rows

%sql SELECT * FROM soccer LIMIT 20 OFFSET 10;<jupyter_output>sqlite://
 * sqlite:///D://fifaDB.db
Done.<jupyter_text>1. How many distinct players are in this table?<jupyter_code>%%sql

SELECT COUNT(*) FROM soccer;<jupyter_output>sqlite://
 * sqlite:///D://fifaDB.db
Done.<jupyter_text>2. How many distinct nationalities are there?<jupyter_code>%%sql

SELECT COUNT (DISTINCT nationality) 
AS 'Number of Nationalities' 
FROM soccer;<jupyter_output>sqlite://
 * sqlite:///D://fifaDB.db
Done.<jupyter_text>3. How many players come from each nationality? Top 10 nationalities based on the number of palyers<jupyter_code>%%sql

SELECT nationality, COUNT(nationality)
FROM soccer
GROUP BY nationality
ORDER BY COUNT(nationality)
DESC
LIMIT 10;<jupyter_output>sqlite://
 * sqlite:///D://fifaDB.db
Done.<jupyter_text>The last 10 nationalities based on the number of players<jupyter_code>%%sql

SELECT nationality, COUNT(nationality)
FROM soccer
GROUP BY nationality
ORDER BY COUNT(nationality)
ASC
LIMIT 10;<jupyter_output>sqlite://
 * sqlite:///D://fifaDB.db
Done.<jupyter_text>4. How many distinct club_teams are there? And How many players come form each club? Number of club teams<jupyter_code>%%sql

SELECT COUNT (DISTINCT club_team) 
AS 'Number of Club Teams' 
FROM soccer;<jupyter_output>sqlite://
 * sqlite:///D://fifaDB.db
Done.<jupyter_text>Top ten club teams based on the number of players<jupyter_code>%%sql

SELECT club_team, COUNT(club_team)
FROM soccer
GROUP BY club_team
ORDER BY COUNT(club_team)
DESC
LIMIT 10;<jupyter_output>sqlite://
 * sqlite:///D://fifaDB.db
Done.<jupyter_text>Bottom 10 club teams based on the number of players<jupyter_code>%%sql

SELECT club_team, COUNT(club_team)
FROM soccer
GROUP BY club_team
ORDER BY COUNT(club_team)
ASC
LIMIT 10;<jupyter_output>sqlite://
 * sqlite:///D://fifaDB.db
Done.<jupyter_text>5. Find out the rank of club_teams based on the average overall_rating of players whose overall_rating is greater than 80<jupyter_code>%%sql

SELECT club_team, AVG(overall_rating)
FROM soccer
WHERE overall_rating > 80
GROUP BY club_team
ORDER BY AVG(overall_rating)
DESC;<jupyter_output>sqlite://
 * sqlite:///D://fifaDB.db
Done.<jupyter_text>6. Find out overall_rating, wage and value of players in Manchester United ranked based on their overall_rating<jupyter_code>%%sql

SELECT name, overall_rating, wage_euro, value_euro
FROM soccer
WHERE club_team = 'Manchester United'
ORDER BY overall_rating DESC, value_euro;<jupyter_output>sqlite://
 * sqlite:///D://fifaDB.db
Done.<jupyter_text>7. Find out overall_rating, wage and value of players in Arsenal ranked based on their overall_rating<jupyter_code>%%sql

SELECT name, overall_rating, wage_euro, value_euro
FROM soccer
WHERE club_team = 'Arsenal'
ORDER BY overall_rating DESC, value_euro;<jupyter_output>sqlite://
 * sqlite:///D://fifaDB.db
Done.<jupyter_text>8. What are the club_team, overall_rating, club_position and dribling score of players whose prefered foot is left<jupyter_code>%%sql

SELECT name, club_team, overall_rating, club_position, dribbling
FROM soccer
WHERE preferred_foot = 'Left'
ORDER BY overall_rating DESC
LIMIT 25;<jupyter_output>sqlite://
 * sqlite:///D://fifaDB.db
Done.
{'fraction_non_alphanumeric': 0.10196179659266907, 'fraction_numerical': 0.010325245224574084, 'mean_word_length': 5.6239316239316235, 'pattern_counts': {'":': 0, '<': 41, '<?xml version=': 0, '>': 42, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 0, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '13', 'dir': 'jupyter-structured-clean-dedup', 'id': '180902', 'license': 'MIT', 'n_tokens_mistral': 1391, 'n_tokens_neox': 1302, 'n_words': 401, 'path': 'fifa-analytics/notebook-01.ipynb', 'repo_name': 'fonyango/SQL', 'size': '3874'}