Exercise

Determining the overall percentage of women

It's possible to combine functions and operators in a single select statement as well. These combinations can be exceptionally handy when we want to calculate percentages or averages, and we can also use the case() expression to operate on data that meets specific criteria while not affecting the query as a whole. The case() expression accepts a list of conditions to match and the column to return if the condition matches, followed by an else_ if none of the conditions match. We can wrap this entire expression in any function or math operation we like.

Often when performing integer division, we want to get a float back. While some databases will do this automatically, you can use the cast() function to convert an expression to a particular type.

Instructions

100 XP
  • Import case, cast, and Float from sqlalchemy.
  • Build an expression female_pop2000to calculate female population in 2000. To achieve this:
    • Use case() inside func.sum().
    • The first argument of case() is a list containing a tuple of
      • i) A boolean checking that census.columns.sex is equal to 'F'.
      • ii) The column census.columns.pop2000.
    • The second argument is the else_ condition, which should be set to 0.
  • Calculate the total population in 2000 and use cast() to convert it to Float.
  • Build a query to calculate the percentage of women in 2000. To do this, divide female_pop2000 by total_pop2000 and multiply by 100.
  • Execute the query and print percent_female.