SQL database

Responses for fallowing questions, using the farm PostgreSQL database. 

a. When did the outside sensor break and stop sending data?

SELECT device, min(recorded_at), max(recorded_at)
FROM sensor_data
GROUP by device
Screen Shot 2020-02-27 at 10.35.16 AM.png
 

Outside sensor break down and stop sending data

b. Show the min and max temperature in the root cellar by year;

SELECT extract(YEAR FROM recorded_at)as day, device, 
max(reading),  min(reading), avg(reading)
FROM sensor_data
Screen Shot 2020-02-24 at 1.52.10 PM.png
 

The minimum temperature in the root cellar; 27.80

and maximum temperature in the root cellar; 69.00

c. What was the lowest temperature recorded 2018?

SELECT extract(YEAR FROM recorded_at) ad day, device
max(reading), min(reading), avg(reading)
FROM sensor_data
WHERE mesurement=’temperature’
AND recorded_at BETWEEN’2018-01-01’ and ‘2018-12-31‘
GROUP BY day, device;
Screen Shot 2020-02-24 at 2.16.51 PM.png
 

Outside temperature was the lowest one in 2018.

Write two queries that use data from your sensor.

SOIL

Caption of SOIL data from device_17

Caption of SOIL data from device_17

TEMPERATURE

Caption of TEMPERATURE data from device_17

Caption of TEMPERATURE data from device_17