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.

Diese Übung ist Teil des Kurses

Introduction to Databases in Python

Kurs anzeigen

Anleitung zur Übung

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.

Interaktive Übung

Versuche dich an dieser Übung, indem du diesen Beispielcode vervollständigst.

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

Code bearbeiten und ausführen