More practice with joins

You can use the same select statement you built in the last exercise, however, let's add a twist and only return a few columns and use the other table in a group_by() clause.

This exercise is part of the course

Introduction to Databases in Python

View Course

Exercise instructions

Build a statement to select:
- The state column from the census table.
- The sum of the pop2008 column from the census table.
- The census_division_name column from the state_fact table.
Append a .select_from() to stmt in order to join the census and state_fact tables by the state and name columns.
Group the statement by the name column of the state_fact table.
Execute the statement stmt_grouped to get all the records and save it as results.
Submit the answer to loop over the results object and print each record.

Hands-on interactive exercise

Have a go at this exercise by completing this sample code.

# Build a statement to select the state, sum of 2008 population and census
# division name: stmt
stmt = select([
    ____,
    func.sum(____),
    ____
])

# Append select_from to join the census and state_fact tables by the census state and state_fact name columns
stmt_joined = stmt.select_from(
    census.join(____, census.columns.____ == state_fact.columns.____)
)

# Append a group by for the state_fact name column
stmt_grouped = stmt_joined.group_by(____)

# Execute the statement and get the results: results
results = connection.execute(____).fetchall()

# Loop over the results object and print each record.
for record in results:
    print(record)

Edit and Run Code