Database - Assignment 3

Assignment

Complete the first pages of PostgreSQL instructions
Connect a Postgresql Client to pg.dev2db.com database
Create a table
Insert records into your table
Query your table
Ensure that mqtt_message table contains atleast 3 records
Connect to the itp database and run all example queries in the document

Questions

Select all the data your device sent
Count the number of records your device sent
Get the maximum temperature reading your device sent

Question 1 - All Data from Device_12

SELECT * FROM sensor_data WHERE device = 'device_12';

Excerpt of Results

image

2 - Count the number of records your device sent

SELECT count(*) FROM sensor_data WHERE device = 'device_12';
image

3 - Maximum temperature reading your device sent

SELECT device, max(reading)
FROM sensor_data
WHERE measurement = 'temperature' AND device = 'device_12'
GROUP BY device
image

Notes - Running the Queries

Database after creating the 4 entries.

image

Database entries for device_12

image

Running the Queries

image