Assignment 4

Assignment

Practice Writing SQL Queries against the itp and farm databases
Use the farm PostgreSQL database. Write queries to answer the following
a. When did the outside sensor break and stop sending data?
b. Show the min and max temperature in the root cellar by year
What was the lowest temperature recorded in 2018
Write to querries that use data from your sensor

Question 1 - When did outside sensor break and stop sending data?

SELECT * FROM sensor_data
WHERE device = 'outside'
ORDER BY id desc
LIMIT 10;

As per the above query and the results below I'd say it stopped sending around 8:38:45 on the 20th of January 2021.

image

Question 2 - Show the min and max temperature in the root cellar by year

SELECT
        extract(YEAR FROM recorded_at) as year,
        device,
    min(reading) FILTER (WHERE measurement = 'temperature')::integer AS min,
    max(reading) FILTER (WHERE measurement = 'temperature')::integer AS max

    FROM sensor_data
    WHERE measurement IN ('temperature') and device IN ('rootcellar')
    GROUP BY year, device
    ORDER BY device, year;
image

Question 3 - What was the lowest temperature recorded in 2018

SELECT
        extract(YEAR FROM recorded_at) as year,
        device,
    min(reading) FILTER (WHERE measurement = 'temperature')::integer AS min
    FROM sensor_data
    WHERE measurement IN ('temperature') and extract(YEAR FROM recorded_at) = 2018
    GROUP BY year, device
    ORDER BY device, year;

As per this the lowest temperature recorded was -7 from the outside device. Here's a list of lowest temperatures for each place otherwise.

image