1. Create a SQLite database by exporting the itp sensor_data from PostgreSQL. Follow the instructions to get started. Document the steps you took to create the database. A screenshot of the terminal or cmd prompt is fine. Make sure your database includes the person and device tables. Optionally create some views.

SQlite File:

devices.db

To create an sqlite database for the itp sensor data I used the following steps:

Some queries

  1. Write some queries using your new SQLite database of ITP sensor data. Refer to the SQLite and PostgreSQL queries we went over in class. Experiment. Spend some time trying to use queries to answer questions with data.

Check to make sure that the data is correctly imported

sqlite> SELECT * FROM sensor_data LIMIT 10; 

device      measurement  reading  recorded_at        
----------  -----------  -------  -------------------
rootcellar  humidity     67       2019-11-28 07:46:28
rootcellar  temperature  44.1     2019-11-28 07:46:28
basement    humidity     53       2019-11-28 07:46:34
basement    temperature  53.8     2019-11-28 07:46:34
loft        humidity     70.8     2019-11-28 07:47:14
loft        temperature  41.72    2019-11-28 07:47:14
basement    humidity     53       2019-11-28 07:47:45
basement    temperature  53.8     2019-11-28 07:47:45
basement    humidity     53       2019-11-28 07:48:36
basement    temperature  53.8     2019-11-28 07:48:36

Create a view and check the weekly moving average temperature of kitchen.

sqlite> CREATE VIEW kitchen_temps AS SELECT * FROM sensor_data WHERE measurement LIKE 'temperature' AND device LIKE 'kitchen';
sqlite> SELECT *, AVG(reading) OVER(ORDER BY recorded_at ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) as weekly_avg from kitchen_temps LIMIT 10;

device   measurement  reading  recorded_at          weekly_avg      
-------  -----------  -------  -------------------  ----------------
kitchen  temperature  73.22    2015-12-26 20:58:28  73.22           
kitchen  temperature  73.04    2015-12-26 20:59:28  73.13           
kitchen  temperature  73.04    2015-12-26 21:00:29  73.1            
kitchen  temperature  73.76    2015-12-26 21:02:31  73.265          
kitchen  temperature  71.6     2015-12-26 21:03:32  72.932          
kitchen  temperature  70.16    2015-12-26 21:04:32  72.47           
kitchen  temperature  69.26    2015-12-26 21:05:33  72.0114285714286
kitchen  temperature  68.72    2015-12-26 21:06:33  71.3685714285714
kitchen  temperature  68.18    2015-12-26 21:07:34  70.6742857142857
kitchen  temperature  67.82    2015-12-26 21:08:34  69.9285714285714

Relational Database Reflection