import pandas as pd

import sqlite3
connection = sqlite3.connect("example.sqlite")
countries = pd.read_sql_query("SELECT * FROM country", connection)
cities = pd.read_sql_query("SELECT * FROM city", connection)

students = pd.read_csv("students.csv")
students.to_sql('students', connection, if_exists='replace')

print(students)

res = pd.merge(countries, cities, left_on="capital", right_on="name")

res = res.rename(columns={'name_x': 'country'})

res['%pop in capital'] = res['population_y'] / res['population_x']

res.sort_values('%pop in capital', ascending=False, inplace=True)

print(res[['country', '%pop in capital']])
