# 数据库代写 | CEGE0052 – Preparation for SQL Quiz 2

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

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.
Use WITH statement with temporary tables as follows
• two_weeks with column two_weeks_ago
• building_id_two_weeks with column building_id (no need for an alias)

5. Use a WITH 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. You should have temporary tables as follows:
• max_report_date with column max_date
• window_max_report_date with column window_id (no alias needed)
• room_max_report_date with column room_id (no alias needed) – use IN just in case
there is more than one window
• building_max_report_date with column building_id (no alias needed) – use IN just in
case there is more than one room
then a select statement with all the columns of the buildings table – use IN just in case there
is more than one building

E-mail: itcsdx@outlook.com  微信:itcsdx