06-building-and-querying

%23%20%2F%2F%2F%20script%0A%23%20requires-python%20%3D%20%22%3E%3D3.12%2C%3C3.15%22%0A%23%20dependencies%20%3D%20%5B%22marimo%5Bsql%5D%22%2C%20%22duckdb%3D%3D1.5.1%22%2C%20%22polars%3D%3D1.33.1%22%2C%20%22pyarrow%3D%3D22.0.0%22%2C%20%22requests%3D%3D2.33.1%22%5D%0A%23%20%0A%23%20%5Btool.marimo.export%5D%0A%23%20lock_kind%20%3D%20%22observed%22%0A%23%20%2F%2F%2F%0A%22%22%22Building%20and%20querying%20a%20database.%0A%22%22%22%0A%0Aimport%20marimo%0A%0A__generated_with%20%3D%20%220.25.2%22%0Aapp%20%3D%20marimo.App(width%3D%22medium%22%2C%20sql_output%3D%22polars%22)%0A%0A%0A%40app.cell%0Adef%20_()%3A%0A%20%20%20%20import%20marimo%20as%20mo%0A%0A%20%20%20%20return%20(mo%2C)%0A%0A%0A%40app.cell%0Adef%20_()%3A%0A%20%20%20%20import%20duckdb%0A%20%20%20%20import%20polars%20as%20pl%0A%20%20%20%20import%20requests%0A%0A%20%20%20%20return%20duckdb%2C%20pl%2C%20requests%0A%0A%0A%40app.cell(hide_code%3DTrue)%0Adef%20_(mo)%3A%0A%20%20%20%20mo.md(r%22%22%22%0A%20%20%20%20%23%20Building%20and%20Querying%20a%20Database%0A%0A%20%20%20%20In%20this%20notebook%20you%20turn%20the%20design%20from%20the%20topic%20slides%20into%20a%20database%3A%20two%20tables%20with%20declared%20keys%2C%20filled%20from%20the%20towns%20file%20you%20saved%20in%20notebook%205.%20Then%20you%20ask%20it%20questions%2C%20the%20first%20few%20of%20them%20ones%20you%20already%20know%20the%20answer%20to.%0A%0A%20%20%20%20%7C%20%7C%20%7C%0A%20%20%20%20%7C---%7C---%7C%0A%20%20%20%20%7C%20%E2%9C%8F%EF%B8%8F%20%7C%20Your%20turn.%20Add%20cells%20with%20the%20**%2B**%20button%20%7C%0A%20%20%20%20%7C%20%F0%9F%9A%80%20%7C%20This%20week's%20work%20%7C%0A%0A%20%20%20%20For%20a%20written%20answer%2C%20add%20a%20cell%20under%20the%20question%2C%20open%20the%20cell's%20**%E2%8B%AF**%20menu%20and%20choose%20**Convert%20to%20Markdown**.%0A%0A%20%20%20%20Every%20%E2%9C%8F%EF%B8%8F%20is%20part%20of%20this%20week's%20work.%20The%20**Going%20further**%20at%20the%20end%20of%20an%20exercise%20is%20optional%3B%20try%20it%20once%20the%20rest%20is%20done.%0A%20%20%20%20%22%22%22)%0A%20%20%20%20return%0A%0A%0A%40app.cell(hide_code%3DTrue)%0Adef%20_(mo)%3A%0A%20%20%20%20mo.md(r%22%22%22%0A%20%20%20%20**Work%20with%20your%20agent%20the%20same%20way%20as%20in%20notebook%205.**%20Try%20it%20yourself%20first%2C%20or%20write%20the%20steps%20in%20words.%20Then%20ask%20your%20agent%2C%20ask%20it%20to%20explain%20any%20line%20you%20could%20not%20have%20written%2C%20and%20ask%20which%20concepts%20its%20answer%20used.%20Do%20exercise%20A%20by%20hand.%0A%20%20%20%20%22%22%22)%0A%20%20%20%20return%0A%0A%0A%40app.cell(hide_code%3DTrue)%0Adef%20_(mo)%3A%0A%20%20%20%20mo.md(r%22%22%22%0A%20%20%20%20%23%201.%20From%20the%20Diagram%20to%20Tables%0A%0A%20%20%20%20The%20topic%20slides%20split%20the%20towns%20file%20into%20two%20tables%2C%20%60counties%60%20and%20%60towns%60%2C%20joined%20by%20the%20county's%20name.%20This%20section%20builds%20them.%0A%0A%20%20%20%20**The%20data.**%20The%20counties%20come%20from%20the%20course%20API%2C%20and%20the%20towns%20from%20%60data%2Fma-towns.csv%60%2C%20the%20file%20notebook%205%20saved.%20If%20that%20file%20is%20not%20there%2C%20the%20cell%20fetches%20the%20towns%20again.%0A%20%20%20%20%22%22%22)%0A%20%20%20%20return%0A%0A%0A%40app.cell%0Adef%20_(mo%2C%20pl%2C%20requests)%3A%0A%20%20%20%20county_rows%20%3D%20pl.DataFrame(%0A%20%20%20%20%20%20%20%20requests.get(%22https%3A%2F%2Foim.zhili.dev%2Fma%2Fcounties%22%2C%20timeout%3D10).json()%0A%20%20%20%20)%0A%0A%20%20%20%20town_columns%20%3D%20%5B%0A%20%20%20%20%20%20%20%20%22name%22%2C%0A%20%20%20%20%20%20%20%20%22county%22%2C%0A%20%20%20%20%20%20%20%20%22population%22%2C%0A%20%20%20%20%20%20%20%20%22income_per_capita_dollars%22%2C%0A%20%20%20%20%20%20%20%20%22average_single_family_bill_dollars%22%2C%0A%20%20%20%20%5D%0A%20%20%20%20_towns_file%20%3D%20mo.notebook_dir().parent%20%2F%20%22data%22%20%2F%20%22ma-towns.csv%22%0A%20%20%20%20if%20_towns_file.exists()%3A%0A%20%20%20%20%20%20%20%20town_rows%20%3D%20pl.read_csv(_towns_file).select(town_columns)%0A%20%20%20%20else%3A%0A%20%20%20%20%20%20%20%20_records%20%3D%20%5B%5D%0A%20%20%20%20%20%20%20%20for%20_county%20in%20county_rows%5B%22name%22%5D%3A%0A%20%20%20%20%20%20%20%20%20%20%20%20_reply%20%3D%20requests.get(%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%22https%3A%2F%2Foim.zhili.dev%2Fma%2Ftowns%22%2C%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20params%3D%7B%22county%22%3A%20_county%2C%20%22per_page%22%3A%20100%7D%2C%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20timeout%3D10%2C%0A%20%20%20%20%20%20%20%20%20%20%20%20)%0A%20%20%20%20%20%20%20%20%20%20%20%20_records%20%2B%3D%20_reply.json()%5B%22items%22%5D%0A%20%20%20%20%20%20%20%20town_rows%20%3D%20pl.DataFrame(_records).select(town_columns)%0A%0A%20%20%20%20county_rows.shape%2C%20town_rows.shape%0A%20%20%20%20return%0A%0A%0A%40app.cell(hide_code%3DTrue)%0Adef%20_(mo)%3A%0A%20%20%20%20mo.md(r%22%22%22%0A%20%20%20%20**The%20database%20is%20a%20file**%2C%20%60data%2Fma-towns.duckdb%60.%20It%20keeps%20the%20tables%20after%20this%20notebook%20stops%2C%20and%20any%20program%20that%20can%20read%20DuckDB%20can%20open%20it.%0A%0A%20%20%20%20Before%20you%20run%20the%20next%20cell%2C%20open%20%60.gitignore%60%20in%20the%20root%20folder%20of%20your%20repository%20and%20add%20this%20line%2C%20then%20commit%20it%3A%0A%0A%20%20%20%20%60%60%60%0A%20%20%20%20*.duckdb*%0A%20%20%20%20%60%60%60%0A%0A%20%20%20%20This%20notebook%20rebuilds%20the%20database%20from%20the%20data%20every%20time%20it%20runs%2C%20so%20the%20file%20itself%20does%20not%20belong%20in%20your%20repository.%0A%0A%20%20%20%20**One%20cell%20builds%20it.**%20It%20deletes%20the%20old%20file%2C%20opens%20a%20new%20one%2C%20creates%20the%20tables%20and%20loads%20them%2C%20and%20closes%20the%20file%20when%20the%20%60with%60%20block%20ends.%20Read%20the%20two%20%60CREATE%20TABLE%60%20statements%20against%20the%20diagram%3A%0A%0A%20%20%20%20-%20%60name%60%20is%20the%20**primary%20key**%20of%20each%20table.%20A%20county's%20name%20or%20a%20town's%20name%20names%20exactly%20one%20row%2C%20and%20the%20database%20refuses%20a%20second%20row%20with%20the%20same%20name.%0A%20%20%20%20-%20%60county%60%20in%20%60towns%60%20is%20a%20**foreign%20key**.%20%60REFERENCES%20counties(name)%60%20makes%20the%20database%20refuse%20a%20town%20whose%20county%20is%20not%20in%20%60counties%60%2C%20so%20%60counties%60%20has%20to%20be%20filled%20first.%20This%20rule%20is%20called%20**referential%20integrity**.%0A%20%20%20%20-%20%60NOT%20NULL%60%20and%20%60CHECK%60%20are%20rules%20about%20one%20column.%20A%20population%20has%20to%20be%20there%20and%20has%20to%20be%20more%20than%20zero.%0A%20%20%20%20-%20%60fips_code%60%20is%20%60UNIQUE%60%3A%20it%20is%20not%20the%20key%2C%20and%20still%20no%20two%20counties%20can%20share%20one.%0A%20%20%20%20-%20%60government_abolished_year%60%20has%20no%20%60NOT%20NULL%60.%20For%20the%20six%20counties%20that%20still%20have%20a%20county%20government%2C%20the%20year%20is%20empty%2C%20and%20that%20is%20a%20real%20answer.%0A%0A%20%20%20%20The%20last%20statement%2C%20%60CREATE%20VIEW%60%2C%20saves%20a%20query%20for%20later.%20Section%205%20explains%20it.%0A%20%20%20%20%22%22%22)%0A%20%20%20%20return%0A%0A%0A%40app.cell%0Adef%20_(duckdb%2C%20mo)%3A%0A%20%20%20%20_file%20%3D%20mo.notebook_dir().parent%20%2F%20%22data%22%20%2F%20%22ma-towns.duckdb%22%0A%20%20%20%20_file.parent.mkdir(exist_ok%3DTrue)%0A%20%20%20%20_file.unlink(missing_ok%3DTrue)%0A%0A%20%20%20%20with%20duckdb.connect(_file)%20as%20_con%3A%0A%20%20%20%20%20%20%20%20_con.execute(%22%22%22%0A%20%20%20%20%20%20%20%20%20%20%20%20CREATE%20TABLE%20counties%20(%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20name%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20VARCHAR%20PRIMARY%20KEY%2C%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20fips_code%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20VARCHAR%20NOT%20NULL%20UNIQUE%2C%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20towns%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20INTEGER%20NOT%20NULL%2C%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20population%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20INTEGER%20NOT%20NULL%20CHECK%20(population%20%3E%200)%2C%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20founded_year%20%20%20%20%20%20%20%20%20%20%20%20%20%20INTEGER%20NOT%20NULL%2C%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20government_abolished_year%20INTEGER%0A%20%20%20%20%20%20%20%20%20%20%20%20)%0A%20%20%20%20%20%20%20%20%22%22%22)%0A%20%20%20%20%20%20%20%20_con.execute(%22%22%22%0A%20%20%20%20%20%20%20%20%20%20%20%20CREATE%20TABLE%20towns%20(%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20name%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20VARCHAR%20PRIMARY%20KEY%2C%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20county%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20VARCHAR%20NOT%20NULL%20REFERENCES%20counties(name)%2C%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20population%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20INTEGER%20NOT%20NULL%20CHECK%20(population%20%3E%200)%2C%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20income_per_capita_dollars%20%20%20%20%20%20%20%20%20%20INTEGER%2C%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20average_single_family_bill_dollars%20INTEGER%20CHECK%20(average_single_family_bill_dollars%20%3E%3D%200)%0A%20%20%20%20%20%20%20%20%20%20%20%20)%0A%20%20%20%20%20%20%20%20%22%22%22)%0A%20%20%20%20%20%20%20%20_con.execute(%22%22%22%0A%20%20%20%20%20%20%20%20%20%20%20%20INSERT%20INTO%20counties%0A%20%20%20%20%20%20%20%20%20%20%20%20SELECT%20name%2C%20fips_code%2C%20towns%2C%20population%2C%20founded_year%2C%20government_abolished_year%0A%20%20%20%20%20%20%20%20%20%20%20%20FROM%20county_rows%0A%20%20%20%20%20%20%20%20%22%22%22)%0A%20%20%20%20%20%20%20%20_con.execute(%22INSERT%20INTO%20towns%20SELECT%20*%20FROM%20town_rows%22)%0A%20%20%20%20%20%20%20%20_con.execute(%22%22%22%0A%20%20%20%20%20%20%20%20%20%20%20%20CREATE%20VIEW%20high_bill_towns_by_county%20AS%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20SELECT%20county%2C%20count(*)%20AS%20towns%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20FROM%20towns%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20WHERE%20average_single_family_bill_dollars%20%3E%2010000%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20GROUP%20BY%20county%0A%20%20%20%20%20%20%20%20%22%22%22)%0A%0A%20%20%20%20database%20%3D%20_file%0A%20%20%20%20database.name%0A%20%20%20%20return%20(database%2C)%0A%0A%0A%40app.cell(hide_code%3DTrue)%0Adef%20_(mo)%3A%0A%20%20%20%20mo.md(r%22%22%22%0A%20%20%20%20**Every%20other%20cell%20asks%20through%20%60ask%60.**%20It%20opens%20the%20file%20only%20to%20read%2C%20runs%20one%20query%2C%20hands%20back%20the%20result%20as%20a%20table%2C%20and%20closes%20the%20file%20again.%20Nothing%20keeps%20the%20database%20open%2C%20so%20you%20can%20rebuild%20it%20at%20any%20time.%20Your%20database%20project's%20notebooks%20use%20the%20same%20function.%0A%20%20%20%20%22%22%22)%0A%20%20%20%20return%0A%0A%0A%40app.cell%0Adef%20_(database%2C%20duckdb)%3A%0A%20%20%20%20def%20ask(sql)%3A%0A%20%20%20%20%20%20%20%20with%20duckdb.connect(database%2C%20read_only%3DTrue)%20as%20_con%3A%0A%20%20%20%20%20%20%20%20%20%20%20%20return%20_con.sql(sql).pl()%0A%0A%20%20%20%20return%20(ask%2C)%0A%0A%0A%40app.cell%0Adef%20_(ask)%3A%0A%20%20%20%20ask(%22SELECT%20(SELECT%20count(*)%20FROM%20counties)%20AS%20counties%2C%20(SELECT%20count(*)%20FROM%20towns)%20AS%20towns%22)%0A%20%20%20%20return%0A%0A%0A%40app.cell(hide_code%3DTrue)%0Adef%20_(mo)%3A%0A%20%20%20%20mo.md(r%22%22%22%0A%20%20%20%20%23%23%20%E2%9C%8F%EF%B8%8F%20A%20%C2%B7%20A%20Table%20of%20Your%20Own%0A%0A%20%20%20%20**No%20agent%20for%20this%20one**%2C%20and%20no%20code%20completion%20either.%20When%20the%20editor%20suggests%20code%20in%20grey%20as%20you%20type%2C%20press%20%60Esc%60%20and%20keep%20typing%20your%20own.%0A%0A%20%20%20%20The%20course%20API%20keeps%20each%20town's%20figures%20for%20every%20year.%20Open%20%5Boim.zhili.dev%2Fma%2Ftowns%2F317%2Fhistory%5D(https%3A%2F%2Foim.zhili.dev%2Fma%2Ftowns%2F317%2Fhistory)%2C%20Wellesley's%2C%20in%20your%20browser%20and%20read%20what%20comes%20back.%0A%0A%20%20%20%201.%20Write%20a%20%60CREATE%20TABLE%60%20for%20a%20table%20%60town_bills%60%3A%20one%20row%20for%20one%20town%20in%20one%20fiscal%20year%2C%20with%20the%20town's%20name%2C%20the%20year%20and%20the%20average%20single-family%20bill.%20Make%20the%20town's%20name%20a%20foreign%20key%20to%20%60towns%60.%0A%20%20%20%202.%20Choose%20the%20primary%20key%2C%20which%20takes%20two%20columns.%20A%20key%20of%20two%20columns%20goes%20on%20a%20line%20of%20its%20own%20at%20the%20end%20of%20the%20table%2C%20in%20the%20form%20%60PRIMARY%20KEY%20(order_id%2C%20product_id)%60.%0A%20%20%20%203.%20Insert%20two%20of%20Wellesley's%20years%20by%20hand.%0A%20%20%20%204.%20Run%20all%20of%20it%20inside%20%60with%20duckdb.connect(database)%20as%20_con%3A%60%2C%20in%20a%20cell%20of%20its%20own%2C%20after%20the%20cell%20that%20builds%20the%20database.%0A%0A%20%20%20%20*Check%20yourself%3A%20inserting%20the%20same%20town%20and%20year%20a%20second%20time%20is%20refused%20with%20%60Duplicate%20key%60%2C%20and%20a%20town%20that%20is%20not%20in%20%60towns%60%20is%20refused%20with%20%60foreign%20key%60.*%0A%0A%20%20%20%20**Going%20further.**%20Load%20every%20year%20for%20one%20county's%20towns%20from%20the%20API%2C%20and%20find%20the%20town%20whose%20bill%20grew%20the%20most%20over%2010%20years.%0A%20%20%20%20%22%22%22)%0A%20%20%20%20return%0A%0A%0A%40app.cell(hide_code%3DTrue)%0Adef%20_(mo)%3A%0A%20%20%20%20mo.md(r%22%22%22%0A%20%20%20%20%23%202.%20What%20the%20Database%20Refuses%0A%0A%20%20%20%20A%20constraint%20checks%20every%20row%20that%20arrives%2C%20without%20anybody%20remembering%20to.%20Here%20is%20a%20town%20in%20a%20county%20that%20does%20not%20exist.%20%60try%60%20and%20%60except%60%20catch%20the%20refusal%2C%20so%20the%20rest%20of%20the%20notebook%20keeps%20running%2C%20and%20the%20cell%20shows%20the%20message.%0A%20%20%20%20%22%22%22)%0A%20%20%20%20return%0A%0A%0A%40app.cell%0Adef%20_(database%2C%20duckdb)%3A%0A%20%20%20%20try%3A%0A%20%20%20%20%20%20%20%20with%20duckdb.connect(database)%20as%20_con%3A%0A%20%20%20%20%20%20%20%20%20%20%20%20_con.execute(%22INSERT%20INTO%20towns%20VALUES%20('Nowhere'%2C%20'Atlantis'%2C%2010%2C%200%2C%200)%22)%0A%20%20%20%20%20%20%20%20refused%20%3D%20%22accepted%22%0A%20%20%20%20except%20duckdb.ConstraintException%20as%20_error%3A%0A%20%20%20%20%20%20%20%20refused%20%3D%20str(_error).splitlines()%5B0%5D%0A%20%20%20%20refused%0A%20%20%20%20return%0A%0A%0A%40app.cell(hide_code%3DTrue)%0Adef%20_(mo)%3A%0A%20%20%20%20mo.md(r%22%22%22%0A%20%20%20%20%23%23%20%E2%9C%8F%EF%B8%8F%20B%20%C2%B7%20Breaking%20the%20Rules%0A%0A%20%20%20%20Write%20three%20inserts%20into%20%60towns%60%2C%20each%20breaking%20a%20different%20rule%3A%20a%20name%20that%20is%20already%20there%2C%20a%20population%20of%20zero%2C%20and%20a%20county%20that%20does%20not%20exist.%20Show%20the%20message%20each%20one%20gets.%0A%0A%20%20%20%20*Check%20yourself%3A%20the%20three%20messages%20contain%20%60Duplicate%20key%60%2C%20%60CHECK%20constraint%20failed%60%20and%20%60foreign%20key%60.*%0A%0A%20%20%20%20**Going%20further.**%20Find%20a%20rule%20the%20scenario%20of%20your%20database%20project%20states%20that%20a%20constraint%20cannot%20hold%2C%20and%20write%20the%20query%20that%20checks%20it%20instead.%0A%20%20%20%20%22%22%22)%0A%20%20%20%20return%0A%0A%0A%40app.cell(hide_code%3DTrue)%0Adef%20_(mo)%3A%0A%20%20%20%20mo.md(r%22%22%22%0A%20%20%20%20%23%203.%20Asking%20One%20Table%0A%0A%20%20%20%20%60SELECT%60%20names%20the%20columns%2C%20%60FROM%60%20the%20table%2C%20%60WHERE%60%20keeps%20the%20rows%20that%20pass%20a%20condition%2C%20%60ORDER%20BY%60%20sorts%20and%20%60LIMIT%60%20cuts%20the%20list%20short.%20Read%20it%20as%20a%20sentence%3A%20the%20towns%20in%20Norfolk%20County%2C%20the%20highest%20bill%20first.%0A%20%20%20%20%22%22%22)%0A%20%20%20%20return%0A%0A%0A%40app.cell%0Adef%20_(ask)%3A%0A%20%20%20%20ask(%22%22%22%0A%20%20%20%20%20%20%20%20SELECT%20name%2C%20population%2C%20average_single_family_bill_dollars%0A%20%20%20%20%20%20%20%20FROM%20towns%0A%20%20%20%20%20%20%20%20WHERE%20county%20%3D%20'Norfolk'%0A%20%20%20%20%20%20%20%20ORDER%20BY%20average_single_family_bill_dollars%20DESC%0A%20%20%20%20%20%20%20%20LIMIT%205%0A%20%20%20%20%22%22%22)%0A%20%20%20%20return%0A%0A%0A%40app.cell(hide_code%3DTrue)%0Adef%20_(mo)%3A%0A%20%20%20%20mo.md(r%22%22%22%0A%20%20%20%20%23%23%20%E2%9C%8F%EF%B8%8F%20C%20%C2%B7%20An%20Answer%20You%20Already%20Know%0A%0A%20%20%20%20In%20notebook%205%2C%20exercise%20B%20found%20the%20five%20towns%20of%20more%20than%2050%2C000%20people%20with%20the%20highest%20average%20bill%2C%20in%20polars.%20Ask%20the%20database%20the%20same%20question.%0A%0A%20%20%20%20*Check%20yourself%3A%20Brookline%2C%20Newton%2C%20Cambridge%2C%20Somerville%2C%20Quincy.*%0A%0A%20%20%20%20**Going%20further.**%20Ask%20the%20same%20question%20of%20%60counties%60%20and%20%60towns%60%20together%3A%20which%20county%20is%20each%20of%20those%20five%20in%2C%20and%20what%20share%20of%20its%20county's%20people%20does%20each%20hold%3F%20That%20needs%20both%20tables%20at%20once%2C%20which%20the%20topic%20slides%20call%20a%20join.%0A%20%20%20%20%22%22%22)%0A%20%20%20%20return%0A%0A%0A%40app.cell(hide_code%3DTrue)%0Adef%20_(mo)%3A%0A%20%20%20%20mo.md(r%22%22%22%0A%20%20%20%20%23%204.%20%60GROUP%20BY%60%0A%0A%20%20%20%20Notebook%205%20counted%20the%20million-dollar%20towns%20in%20each%20county%20in%20polars%2C%20with%20%60.filter()%60%2C%20%60.group_by()%60%20and%20%60.agg()%60.%20The%20same%20kind%20of%20question%20in%20SQL%20asks%20how%20many%20towns%20in%20each%20county%20have%20an%20average%20bill%20over%20%2410%2C000.%20%60WHERE%60%20is%20the%20filter%2C%20%60GROUP%20BY%60%20makes%20one%20group%20per%20county%2C%20and%20%60count(*)%60%20counts%20the%20rows%20in%20each.%0A%20%20%20%20%22%22%22)%0A%20%20%20%20return%0A%0A%0A%40app.cell%0Adef%20_(ask)%3A%0A%20%20%20%20ask(%22%22%22%0A%20%20%20%20%20%20%20%20SELECT%20county%2C%20count(*)%20AS%20towns%0A%20%20%20%20%20%20%20%20FROM%20towns%0A%20%20%20%20%20%20%20%20WHERE%20average_single_family_bill_dollars%20%3E%2010000%0A%20%20%20%20%20%20%20%20GROUP%20BY%20county%0A%20%20%20%20%20%20%20%20ORDER%20BY%20towns%20DESC%0A%20%20%20%20%22%22%22)%0A%20%20%20%20return%0A%0A%0A%40app.cell(hide_code%3DTrue)%0Adef%20_(mo)%3A%0A%20%20%20%20mo.md(r%22%22%22%0A%20%20%20%20%60WHERE%60%20keeps%20rows%20before%20they%20are%20grouped.%20To%20keep%20**groups**%20after%20they%20are%20counted%2C%20use%20%60HAVING%60%3A%20the%20counties%20with%20more%20than%20five%20such%20towns.%0A%20%20%20%20%22%22%22)%0A%20%20%20%20return%0A%0A%0A%40app.cell%0Adef%20_(ask)%3A%0A%20%20%20%20ask(%22%22%22%0A%20%20%20%20%20%20%20%20SELECT%20county%2C%20count(*)%20AS%20towns%0A%20%20%20%20%20%20%20%20FROM%20towns%0A%20%20%20%20%20%20%20%20WHERE%20average_single_family_bill_dollars%20%3E%2010000%0A%20%20%20%20%20%20%20%20GROUP%20BY%20county%0A%20%20%20%20%20%20%20%20HAVING%20count(*)%20%3E%205%0A%20%20%20%20%20%20%20%20ORDER%20BY%20towns%20DESC%0A%20%20%20%20%22%22%22)%0A%20%20%20%20return%0A%0A%0A%40app.cell(hide_code%3DTrue)%0Adef%20_(mo)%3A%0A%20%20%20%20mo.md(r%22%22%22%0A%20%20%20%20%23%23%20%E2%9C%8F%EF%B8%8F%20D%20%C2%B7%20A%20Count%20You%20Can%20Check%0A%0A%20%20%20%201.%20Count%20the%20towns%20in%20each%20county%20with%20%60GROUP%20BY%60%2C%20and%20compare%20each%20count%20with%20the%20%60towns%60%20column%20of%20%60counties%60.%20*Check%20yourself%3A%20Worcester%2060%2C%20Middlesex%2054%2C%20Essex%2034%2C%20and%20all%2014%20agree.*%0A%20%20%20%202.%20Add%20the%20counts%20up.%20Write%20in%20a%20markdown%20cell%20why%20the%20sum%20has%20to%20equal%20the%20number%20of%20rows%20in%20%60towns%60.%20*Check%20yourself%3A%20351.*%0A%20%20%20%203.%20For%20each%20county%2C%20the%20total%20population%20of%20its%20towns%2C%20and%20the%20share%20of%20those%20people%20living%20in%20towns%20with%20a%20bill%20over%20%2410%2C000.%20Use%20%60sum()%60%20and%20a%20%60CASE%20WHEN%60%20inside%20it%2C%20or%20two%20queries.%20*Check%20yourself%3A%20the%20totals%20add%20up%20to%207%2C136%2C171.*%0A%0A%20%20%20%20**Going%20further.**%20Answer%20one%20question%20of%20your%20own%20about%20the%20towns%20with%20%60GROUP%20BY%60%2C%20and%20check%20it%20in%20polars%20on%20%60town_rows%60.%20Or%20download%20the%20TV%20show%20database%20from%20%5BDownloads%5D(%2Fdownloads%2F)%2C%20keep%20it%20out%20of%20your%20repository%2C%20and%20answer%20the%20two%20TV%20show%20questions%20from%20the%20topic%20slides.%0A%20%20%20%20%22%22%22)%0A%20%20%20%20return%0A%0A%0A%40app.cell(hide_code%3DTrue)%0Adef%20_(mo)%3A%0A%20%20%20%20mo.md(r%22%22%22%0A%20%20%20%20%23%205.%20Views%0A%0A%20%20%20%20A%20**view**%20is%20a%20query%20with%20a%20name.%20The%20cell%20that%20builds%20the%20database%20saved%20the%20first%20query%20of%20section%204%20as%20%60high_bill_towns_by_county%60.%20Asking%20for%20it%20by%20name%20runs%20that%20query%20again%2C%20on%20the%20rows%20as%20they%20are%20at%20that%20moment%2C%20so%20a%20view%20stores%20no%20rows%20of%20its%20own%20and%20is%20never%20out%20of%20date.%20Section%204's%20query%20listed%20the%20counties%20in%20order%3B%20a%20view%20keeps%20no%20order%2C%20so%20put%20the%20%60ORDER%20BY%60%20in%20the%20question%20you%20ask%20of%20it.%0A%20%20%20%20%22%22%22)%0A%20%20%20%20return%0A%0A%0A%40app.cell%0Adef%20_(ask)%3A%0A%20%20%20%20ask(%22SELECT%20*%20FROM%20high_bill_towns_by_county%20ORDER%20BY%20towns%20DESC%22)%0A%20%20%20%20return%0A%0A%0A%40app.cell(hide_code%3DTrue)%0Adef%20_(mo)%3A%0A%20%20%20%20mo.md(r%22%22%22%0A%20%20%20%20It%20returns%20the%20same%20rows%20as%20section%204's%20first%20query.%20In%20your%20database%20project%2C%20save%20the%20question%20the%20business%20asks%20every%20week%20the%20same%20way%2C%20in%20the%20cell%20that%20builds%20your%20database.%0A%0A%20%20%20%20Full%20reference%3A%20%5BSQL%20%C2%A728%5D(%2Fhandbooks%2Fsql%2F04-building-and-loading%2F%2328-views)%0A%20%20%20%20%22%22%22)%0A%20%20%20%20return%0A%0A%0A%40app.cell(hide_code%3DTrue)%0Adef%20_(mo)%3A%0A%20%20%20%20mo.md(r%22%22%22%0A%20%20%20%20%23%23%20%E2%9C%8F%EF%B8%8F%20E%20%C2%B7%20Northwind's%20Orders%0A%0A%20%20%20%20Northwind%2C%20the%20company%20in%20the%20topic%20slides%20and%20the%20SQL%20handbook%2C%20keeps%20its%2016%2C282%20orders%20in%20one%20file%20on%20the%20course%20site.%20The%20cell%20below%20downloads%20the%20file%20to%20your%20%60data%60%20folder%2C%20once%2C%20and%20counts%20the%20orders.%20A%20query%20can%20read%20a%20file%20directly%2C%20with%20the%20file's%20path%20in%20place%20of%20a%20table%20name%3B%20write%20each%20query%20below%20the%20same%20way%2C%20with%20%60orders_file%60.%0A%20%20%20%20%22%22%22)%0A%20%20%20%20return%0A%0A%0A%40app.cell%0Adef%20_(ask%2C%20mo%2C%20requests)%3A%0A%20%20%20%20_path%20%3D%20mo.notebook_dir().parent%20%2F%20%22data%22%20%2F%20%22northwind-orders.parquet%22%0A%20%20%20%20if%20not%20_path.exists()%3A%0A%20%20%20%20%20%20%20%20_reply%20%3D%20requests.get(%0A%20%20%20%20%20%20%20%20%20%20%20%20%22https%3A%2F%2Foim7510.github.io%2Fdata%2Fnorthwind%2Forders.parquet%22%2C%0A%20%20%20%20%20%20%20%20%20%20%20%20timeout%3D30%2C%0A%20%20%20%20%20%20%20%20)%0A%20%20%20%20%20%20%20%20_reply.raise_for_status()%0A%20%20%20%20%20%20%20%20_path.write_bytes(_reply.content)%0A%20%20%20%20orders_file%20%3D%20_path.as_posix()%0A%20%20%20%20ask(f%22SELECT%20count(*)%20AS%20orders%20FROM%20'%7Borders_file%7D'%22)%0A%20%20%20%20return%0A%0A%0A%40app.cell(hide_code%3DTrue)%0Adef%20_(mo)%3A%0A%20%20%20%20mo.md(r%22%22%22%0A%20%20%20%201.%20How%20many%20different%20countries%20did%20Northwind%20ship%20orders%20to%3F%20Use%20the%20column%20%60ShipCountry%60.%20*Check%20yourself%3A%2021.*%0A%20%20%20%202.%20For%20each%20country%2C%20the%20number%20of%20orders%20and%20the%20total%20of%20%60Freight%60%2C%20the%20most%20freight%20first.%20*Check%20yourself%3A%20USA%20with%202%2C328%20orders%20and%20573%2C251%20in%20freight%2C%20then%20Germany%20with%202%2C193%20and%20537%2C189.75%2C%20then%20France%20with%201%2C778%20and%20442%2C055.75.*%0A%0A%20%20%20%20**Going%20further.**%20Rank%20the%20countries%20by%20freight%20per%20order%20instead%2C%20and%20see%20which%20countries%20move.%0A%20%20%20%20%22%22%22)%0A%20%20%20%20return%0A%0A%0A%40app.cell(hide_code%3DTrue)%0Adef%20_(mo)%3A%0A%20%20%20%20mo.md(r%22%22%22%0A%20%20%20%20%23%20%F0%9F%9A%80%20This%20Week's%20Work%0A%0A%20%20%20%201.%20**What%20is%20due%20is%20listed%20under%20*Before%20next%20session*%20in%20the%20post-class%20notes.**%20I%20post%20them%20on%20the%20session%20page%20after%20each%20class%2C%20with%20the%20sections%20we%20reached%2C%20and%20the%20checkpoint%20on%20Canvas%20lists%20the%20same%20items.%0A%20%20%20%202.%20**%5BThe%20database%20project%5D(%2Fassignments%2Fdatabase-project%2F)**%2C%20Part%20A%2C%20due%20Sunday%2011%2F08%0A%20%20%20%20%22%22%22)%0A%20%20%20%20return%0A%0A%0A%40app.cell(hide_code%3DTrue)%0Adef%20_(mo)%3A%0A%20%20%20%20mo.md(r%22%22%22%0A%20%20%20%20**Self-check%3A**%0A%0A%20%20%20%20-%20%5B%20%5D%20I%20can%20turn%20a%20diagram%20into%20%60CREATE%20TABLE%60%20statements%20with%20the%20keys%20declared%0A%20%20%20%20-%20%5B%20%5D%20I%20can%20explain%20what%20a%20primary%20key%2C%20a%20foreign%20key%2C%20%60NOT%20NULL%60%20and%20%60CHECK%60%20each%20refuse%0A%20%20%20%20-%20%5B%20%5D%20I%20can%20build%20a%20database%20file%20from%20data%20and%20read%20it%20with%20a%20function%20that%20closes%20it%20again%0A%20%20%20%20-%20%5B%20%5D%20I%20can%20ask%20one%20table%20a%20question%20with%20%60SELECT%60%2C%20%60WHERE%60%2C%20%60ORDER%20BY%60%20and%20%60LIMIT%60%0A%20%20%20%20-%20%5B%20%5D%20I%20can%20count%20groups%20with%20%60GROUP%20BY%60%2C%20keep%20some%20of%20them%20with%20%60HAVING%60%2C%20and%20check%20the%20counts%20against%20a%20total%20I%20know%0A%20%20%20%20-%20%5B%20%5D%20I%20can%20save%20a%20query%20as%20a%20view%20and%20ask%20for%20it%20by%20name%0A%20%20%20%20%22%22%22)%0A%20%20%20%20return%0A%0A%0Aif%20__name__%20%3D%3D%20%22__main__%22%3A%0A%20%20%20%20app.run()%0A