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.

Cet exercice fait partie du cours

Introduction to Databases in Python

Afficher le cours

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.

Exercice interactif pratique

Essayez cet exercice en complétant cet exemple de 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)

Modifier et exécuter le code