Get startedGet started for free

Determine the difference by state from the 2000 and 2008 censuses

In this final exercise, you will write a query to calculate the states that changed the most in population. You will limit your query to display only the top 10 states.

This exercise is part of the course

Introduction to Databases in Python

View Course

Exercise instructions

  • Build a statement to:
    • Select state.
    • Calculate the difference in population between 2008 (pop2008) and 2000 (pop2000).
  • Group the query by census.columns.state using the .group_by() method on stmt.
  • Order by 'pop_change' in descending order using the .order_by() method with the desc() function on 'pop_change'.
  • Limit the query to the top 10 states using the .limit() method.
  • Execute the query and store it as results.
  • Print the state and the population change for each result. 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.

# Build query to return state name and population difference from 2008 to 2000
stmt = select([____,
     (____-____).label('pop_change')
])

# Group by State
stmt = stmt.____(____)

# Order by Population Change
stmt = stmt.____(____)

# Limit to top 10
stmt = stmt.____(____)

# Use connection to execute the statement and fetch all results
results = connection.execute(____).fetchall()

# Print the state and population change for each record
for result in results:
    print('{}:{}'.format(result.state, result.pop_change))
Edit and Run Code