World Nations Database
👋 Welcome to your workspace! Here, you can run SQL queries, write Python code, and add text in Markdown. This workspace is automatically connected to a MariaDB database with a schema that contains tables about countries in the world (source).
You can click the "Browse tables" button in the upper righthand corner of the cell below to view the available tables.
There is a short query and a visualization of the number of languages spoken and the population of each country, rendered in Plotly to get you started.
WITH world_languages AS (
SELECT
countries.name AS country,
continents.name AS continent,
year,
population,
COUNT(*) AS number_of_languages,
ROW_NUMBER() OVER(PARTITION BY country ORDER BY year DESC) AS year_index
FROM countries
INNER JOIN country_languages USING(country_id)
INNER JOIN languages USING(language_id)
INNER JOIN country_stats USING(country_id)
INNER JOIN regions USING(region_id)
INNER JOIN continents USING(continent_id)
GROUP BY 1, 2, 3, 4)
SELECT * FROM world_languages
WHERE year_index = 1# Import libraries
import pandas as pd
import plotly.express as px
# Create scatter plot
fig = px.scatter(
country_languages,
x="population",
y="number_of_languages",
color="continent",
hover_name="country",
log_x=True,
)
# Create labels and show plot
fig.update_layout(
title="Number of Languages Spoken in a Country by Population<br><sup>Interactive Chart</sup>",
title_x=0.5,
xaxis_title="Population (Log)",
yaxis_title="Number of Languages",
legend_title="Continent",
template="plotly_dark",
)
fig.show()This is an interactive plot! Hover over different countries to view country details such as population.
💪 Now it's your turn to construct your own queries and analyze the data! Remember, you can review the tables in the database at any point using the "Browse tables" button.