Icon

02_​DB_​InDB_​Processing

02 DB InDB Processing Exercise Solution
Missing Values Strategy: 02_DB_InDB_Processing This workflow implements some in-database manipulation operations. 1. It connects to a database (SQLite) newCensus.sqlite to read tables ss13hme (house data) and ss13pme (person data) containing data for the US state of Maine 2. joins ss13hme and ss13pme on SERIALNO 3. filters all rows from ss13pme where COW is NULL 4. filters all rows from ss13pme where COW is NOT NULL 5. calculate average AGEP for the different SEX groups 6. Optional. Sort the data rows by descending AGEP and extract top 10 only.. Optional connect to 1_Data/newCensus.sqliteselect * from ss13hmeselect * from ss13pmerm PUMA*PWGTP* inner join on SERIAL NOCOW is NOT NULLCOW is NULLaverage AGEPfor each SEX groupimport all data forperson & housefilesimport all rows where COW is NOT NULLimport all rows whereCOW is NULLsex groups = 1, 2descendingby AGEPtop 10 onlywith LIMIT commandoldest 10 peopleby AGEPSQLite Connector DB Table Selector DB Table Selector DB Column Filter DB Joiner DB Row Filter DB Row Filter DB GroupBy DB Reader DB Reader DB Reader DB Reader DB Sorter DB Query DB Reader Missing Values Strategy: 02_DB_InDB_Processing This workflow implements some in-database manipulation operations. 1. It connects to a database (SQLite) newCensus.sqlite to read tables ss13hme (house data) and ss13pme (person data) containing data for the US state of Maine 2. joins ss13hme and ss13pme on SERIALNO 3. filters all rows from ss13pme where COW is NULL 4. filters all rows from ss13pme where COW is NOT NULL 5. calculate average AGEP for the different SEX groups 6. Optional. Sort the data rows by descending AGEP and extract top 10 only.. Optional connect to 1_Data/newCensus.sqliteselect * from ss13hmeselect * from ss13pmerm PUMA*PWGTP*inner join on SERIAL NOCOW is NOT NULLCOW is NULLaverage AGEPfor each SEX groupimport all data forperson & housefilesimport all rows where COW is NOT NULLimport all rows whereCOW is NULLsex groups = 1, 2descendingby AGEPtop 10 onlywith LIMIT commandoldest 10 peopleby AGEPSQLite Connector DB Table Selector DB Table Selector DB Column Filter DB Joiner DB Row Filter DB Row Filter DB GroupBy DB Reader DB Reader DB Reader DB Reader DB Sorter DB Query DB Reader

Nodes

Extensions

Links