Device to Database
Week 3
Using the sensor_data table in the itp database:
a. Select all the data your device sent
b. Count the number of records your device sent
c. Get the maximum temperature reading your device sent
Use the farm PostgreSQL database. Write queries to answer the following questions:
a. When did the outside sensor break and stop sending data?
b. Show the min and max temperature in the root cellar by year
c. What was the lowest temperature recorded in 2019?
Challenge: Which sensor recorded the lowest temperature in 2019 and when? Hint: you need a subquery.
Week 4
Your SQLite database file
Your best 2 queries and description of what they're doing
I was able to easily understand queries that I did in Week 3 however I had a bit more trouble trying to understand the sample queries for this week. More specifically, it took me a while to understand the “where” part of the code such as the lines below.
WHERE s.device = d.name
AND d.person_id = p.id
AND p.name = 'Gracy'
After going back and forth comparing week 3 queries I was able to understand the concept. I think I was confused since we jumped from something concrete such as measurement = “temperature"' to something like d.person_id = p.id. I now understand that this “combines” the two databases together.
Notes:
ON Clause can be used to join columns that have different names. We use ON clause to specify a join condition. This lets us specify join conditions separate from any search or filter conditions in the WHERE clause.
Week 5
Use the farm database on InfluxDB. Write queries to answer the following questions:
When did the outside sensor break and stop sending data?
What was the lowest temperature recorded in 2018? Which sensor recorded this data?
Find the min and max temperatures by week for the root cellar for the last 3 months of 2018.
Explain the differences between the InfluxDB and TimescaleDB and query results.
OPTIONAL: Write two queries that use data from the itp database on InfluxDB.
Week 6
Three workflows using Node-Red
