Determine the percentage of population by gender and state

In this exercise, you will write a query to determine the percentage of the population in 2000 that comprised of women. You will group this query by state.

Cet exercice fait partie du cours

Introduction to Databases in Python

Afficher le cours

Instructions

Import case, cast and Float from sqlalchemy.
Define a statement to select state and the percentage of women in 2000.
- Inside func.sum(), use case() to select women (using the sex column) from pop2000. Remember to specify else_=0 if the sex is not 'F'.
- To get the percentage, divide the number of women in the year 2000 by the overall population in 2000. Cast the divisor - census.columns.pop2000 - to Float before multiplying by 100.
Group the query by state.
Execute the query and store it as results.
Print state and percent_female for each record. This has been done for you, so submit the answer to see the result.

Exercice interactif pratique

Essayez cet exercice en complétant cet exemple de code.

# import case, cast and Float from sqlalchemy
from sqlalchemy import ____, ____, ____

# Build a query to calculate the percentage of women in 2000: stmt
stmt = select([____,
    (func.sum(
        ____([
            (____ == 'F', ____)
        ], else_=0)) /
     cast(func.sum(____), ____) * 100).label('percent_female')
])

# Group By state
stmt = stmt.group_by(____)

# Execute the query and store the results: results
results = connection.execute(____).fetchall()

# Print the percentage
for result in results:
    print(result.state, result.percent_female)

Modifier et exécuter le code