数据库代写 | CEGE0052 – Assignment 4

这个作业是完成数据库查询相关的问题

CEGE0052 – Assignment 4

Part 1 – Nested Queries
1. Get details of the first building condition report(s) and work out how many days ago was the
first building condition report captured? Your answer should first find the earliest building
report and then nest this result to calculate how long ago it was captured. Your answer
should have a column called days_ago_captured for the calculation. You should calculate
the date difference as follows: now()::timestamp – report_date::timestamp) as
days_ago_captured
2. Which temperature sensor reported the lowest value overall? Select all the columns from
the temperature_sensors table and use IN to make sure that all the sensors with the
minimum value are returned.
3. List the details of the room with the lowest temperature value. Select all the columns from
the rooms table and use IN twice to make sure that if multiple sensors have the same value
they are picked up and then the multiple rooms for these sensors are found.
4. Use a nested query to list the details of any buildings that have a condition report in the last
2 weeks. You should use where report_date > (select date_trunc(‘day’, NOW() – interval ‘2
weeks’)) to find the reports and then DISTINCT so that the building details are not repeated
5. Use a nested query to find details of the building(s) with the window that has the latest
condition report. Start by finding the maximum report date for the window_condition
table, then from there find the window with that report then the room with that window
then the building with that room. Use IN for all the nested statements except the report
date / max report date
Part 2 – The WITH operator
1. Get details of the first building conditoin report(s) and work out how many days ago was the
first building condition report captured? Your answer should first find the earliest building
report and store this as a temporary table using WITH and table name bld_condition_days
with column min_date. Use this result to calculate how long ago it was captured. Your
answer should have a column called days_ago_captured for the calculation. You should
calculate the date difference as follows: now()::timestamp – report_date::timestamp) as
days_ago_captured
2. Which temperature sensor reported the lowest value overall? Select all the columns from
the temperature_sensors table and use IN to make sure that all the sensors with the
minimum value are returned. Use a WITH statement to create a temporary table called
min_temp_value with column min_temp and then use this to find the temperature sensor ID
in a second table called temp_sensor then use this in the final select statement to get the
details for the sensor.
3. List the details of the room with the lowest temperature value. Select all the columns from
the rooms table and use IN twice to make sure that if multiple sensors have the same value
they are picked up and then the multiple rooms for these sensors are found. You will need
three temporary tables as follows:
• min_temp_value with column min_value
• min_temp_sensor_id with column temperature_sensor_id (no need for an alias)
• min_temp_sensor with column room_id
and then all the details of the room