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.

Questo esercizio fa parte del corso

Introduction to Databases in Python

Visualizza il corso

Istruzioni dell'esercizio

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.

Esercizio pratico interattivo

Prova a risolvere questo esercizio completando il codice di esempio.

# 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)

Modifica ed esegui il codice