<jupyter_start><jupyter_text>Loading a csv into database<jupyter_code>import pandas as pd
with open('international_debt.csv', 'r') as file:
    data_df = pd.read_csv(file)
data_df.to_sql('international_debt', con=engine, index=True, index_label='id', if_exists='replace')
%load_ext sql
%sql mysql://shukur:*******@localhost/datacamp<jupyter_output><empty_output><jupyter_text>1. The World Bank's international debt data<jupyter_code>%%sql

SELECT * 
FROM international_debt
LIMIT 10;<jupyter_output>* mysql://shukur:***@localhost/datacamp
10 rows affected.<jupyter_text>2. Finding the number of distinct countries<jupyter_code>%%sql
SELECT DISTINCT(COUNT(country_name)) AS total_distinct_countries
FROM international_debt;<jupyter_output>* mysql://shukur:***@localhost/datacamp
1 rows affected.<jupyter_text>3. Finding out the distinct debt indicators<jupyter_code>%%sql
SELECT DISTINCT(indicator_code) AS distinct_debt_indicators 
FROM international_debt
ORDER BY distinct_debt_indicators;<jupyter_output>* mysql://shukur:***@localhost/datacamp
25 rows affected.<jupyter_text>4. Totaling the amount of debt owed by the countries<jupyter_code>%%sql
SELECT 
    ROUND(SUM(debt)/1000000, 2) AS total_debt
FROM international_debt;<jupyter_output>* mysql://shukur:***@localhost/datacamp
1 rows affected.<jupyter_text>5. Country with the highest debt<jupyter_code>%%sql
SELECT 
    country_name, 
    SUM(debt) AS total_debt
FROM international_debt
GROUP BY country_name
ORDER BY total_debt DESC
LIMIT 1;<jupyter_output>* mysql://shukur:***@localhost/datacamp
1 rows affected.<jupyter_text>6. Average amount of debt across indicators¶<jupyter_code>%%sql
SELECT 
    indicator_code AS debt_indicator,
    indicator_name,
    AVG(debt) AS average_debt
FROM international_debt
GROUP BY debt_indicator, indicator_name
ORDER BY average_debt
LIMIT 10;<jupyter_output>* mysql://shukur:***@localhost/datacamp
10 rows affected.<jupyter_text>7. The highest amount of principal repayments¶<jupyter_code>%%sql
SELECT 
    country_name, 
    indicator_name
FROM international_debt
WHERE debt = (SELECT 
                 MAX(debt)
             FROM international_debt
             WHERE indicator_code ='DT.AMT.DLXF.CD');<jupyter_output>* mysql://shukur:***@localhost/datacamp
1 rows affected.<jupyter_text>8. The most common debt indicator<jupyter_code>%%sql
SELECT indicator_code, COUNT(*) AS indicator_count
FROM international_debt
GROUP BY indicator_code
ORDER BY indicator_count DESC
LIMIT 20;<jupyter_output>* mysql://shukur:***@localhost/datacamp
20 rows affected.<jupyter_text>9. Other viable debt issues and conclusion<jupyter_code>%%sql
SELECT country_name, MAX(debt) AS maximum_debt
FROM international_debt
GROUP BY country_name
ORDER BY maximum_debt DESC
LIMIT 10;<jupyter_output>* mysql://shukur:***@localhost/datacamp
10 rows affected.
{'fraction_non_alphanumeric': 0.10141342756183745, 'fraction_numerical': 0.014134275618374558, 'mean_word_length': 6.651351351351352, 'pattern_counts': {'":': 0, '<': 32, '<?xml version=': 0, '>': 32, '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': '10', 'dir': 'jupyter-structured-clean-dedup', 'id': '513895', 'license': 'Unlicense', 'n_tokens_mistral': 1002, 'n_tokens_neox': 931, 'n_words': 223, 'path': 'notebook.ipynb', 'repo_name': 'shukkkur/International-Debt-Statistics', 'size': '2830'}