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
Exercise instructions
- Build a statement to select:
- The
state
column from thecensus
table. - The sum of the
pop2008
column from thecensus
table. - The
census_division_name
column from thestate_fact
table.
- The
- Append a
.select_from()
tostmt
in order to join thecensus
andstate_fact
tables by thestate
andname
columns. - Group the statement by the
name
column of thestate_fact
table. - Execute the statement
stmt_grouped
to get all the records and save it asresults
. - 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)