SQL Queries

Assignment

Practice writing SQL queries against the itp and farm databases.

Issues

Since I am using Windows, it was a little challenging to follow the tutorials. I tried using psql on my VPS Ubuntu and whenever there were issues with queries it would just quit. Same thing happened in command prompt/powershell. Constant restarting and poor visualization of data made it hard for me to focus on extracting data, so I used pgAdmin 4 to view the data instead. I still had to use command lines, but the clear organization of data helped a lot. In addition, there is no need to login each time.

Answers

When did the outside sensor break and stop sending data?

I am not sure if I am running correct queries. First I ran a query to see if there was a day when sensor didn’t record by check with NULL. There were none.

SELECT *
    FROM sensor_data 
    WHERE device = 'outside'
	AND recorded_at IS NULL;

Then I checked if NOT NULL command worked. It did.

Finally it turned out that I have been answering the question wrong. Jake pointed me into the right direction and the day it has stopped working last is January 20th at 03:38

SELECT device, min(recorded_at), max(recorded_at)
    FROM sensor_data
    GROUP BY device;

Show the min and max temperature in the root cellar by year

SELECT extract(YEAR from recorded_at) as year, device, 
    max(reading), min(reading), round(avg(reading), 2) as avg
    FROM sensor_data
    WHERE measurement = 'temperature'
	AND device = 'rootcellar'
    AND recorded_at BETWEEN '2016-12-01' and '2020-02-25'
    GROUP BY year, device
    ORDER BY device, year;

What was the lowest temperature recorded 2018?

SELECT extract(YEAR from recorded_at) as year, device, 
    min(reading)
    FROM sensor_data
    WHERE measurement = 'temperature'
	AND recorded_at BETWEEN '2018-01-01' and '2018-12-31'
    GROUP BY year, device
    ORDER BY device, year;

It’s not the best query because it’s by year and to make sure that I am getting the most minimum from any device and not an average value of any kind (by some bizarre mistake), I ran this query:

SELECT recorded_at, device, 
    min(reading)
    FROM sensor_data
    WHERE measurement = 'temperature'
	AND recorded_at BETWEEN '2018-01-01' and '2018-12-31'
	AND reading BETWEEN '-100' and '-7'
    GROUP BY recorded_at, device;

It seems that the above query was correct and the coldest it has been is -7.42. At first I thought that my results were completely wrong because I interpreted the numbers in Celcius and thought to my self that there is no way that the coldest day in Wisconsin is only -7.42 Celcius. Then, I realized that it’s Fahrenheit and felt a bit silly.

It was useful to find out the values anyway for the challenge question.

My sensor

First query I ran is just to see when device started sending data and when it stopped.

SELECT min(recorded_at), max(recorded_at)
    FROM sensor_data
    WHERE device = 'device_16';

Results are

“2020-02-13 12:49:32.804098-05” “2020-02-25 19:59:57.307063-05”

Then I checked the lowest and highest temperature recorded by device.

SELECT min(reading), max(reading)
    FROM sensor_data
    WHERE device = 'device_16'
	AND measurement = 'temperature';

Results are

70.55 lowest and 86.49 highest

Conclusion

I realize that I probably made many inefficient queries, but I am pretty happy that I got to familiarize myself with the UI. I think its visualization helped me better understand the database and queries themselves.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s