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.

This exercise is part of the course

Introduction to Databases in Python

View Course

Exercise 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.

Hands-on interactive exercise

Have a go at this exercise by completing this sample 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)

Edit and Run Code