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.

本练习是课程的一部分

Introduction to Databases in Python

查看课程

练习说明

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.

交互式实操练习

通过完成这段示例代码来试试这个练习。

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

编辑并运行代码