Hello, dear friend, you can consult us at any time if you have any questions, add WeChat: daixieit

CEGE0052 – Preparation for SQL Quiz 2

(Assignment 4)

Introduction

Submission Method:

Work through this worksheet and use your answers to help you to answer the quiz questions on Moodle.

The quiz is worth 10% of the marks for the module.

- If you have questions about the quiz or this practice sheet, please post them on Moodle

- That way everyone is given the same information

- That way I remember what I’ve said to you and don’t mark you down for doing something that I wasn’t expecting

This worksheet and SQL Quiz 2 will cover the SQL you learned in Week 7/8 of this module. For the quiz, the questions could be similar to, those in this worksheet. Everyone will get a different set of questions overall.

NB: You should create a txt file for all the SQL that you write, so that you can refer to it during the quiz.

Plagiarism

You should work on this worksheet and complete the quiz on your own, using your own user account (username =userXXX, password = userXXXpasswordXXX).

The database has a log which records everything you do – every time you log on, every query you run, every error you make. If necessary, we can use this log to find out who is doing what and when – in particular when you are logged on and what mistakes you make. We can also tell if you just copy/paste someone else’s work rather than do the work yourself.

** Read the instructions a few times before starting to make sure you are clear on what you need to do **

Before You Start - Centennial University Case Study

** You should make sure you are very familiar with the Centennial Case Study before starting this worksheet **

This worksheet and SQL Quiz 2 make use of the centennial database which you will see on the databases list in PGAdmin. Within this database there is a ucfscde schema that contains the data created by the SQL scripts for the Centennial case study. You can see the data in all the tables, add condition reports and delete the condition reports that you add (you can't delete reports that others have added and you can't delete temperature values once inserted)

As you did for Week 6, for this worksheet you should once again connect to the shared ucfscde database and insert the following:

1. A building_condition report

2. A room_condition report

3. A window_condition report

4. A temperature_value reading

You can find the required SQL for this in the Week 5 Case Study example scripts. You should adapt the condition status so that we have a variety of data in the database

- Be creative - change the condition status values to those for your own building, room or window

- Be creative - change the geometry that the report refers to (pick a room, window or building at random)

- Be creative – change the temperature values and the sensor that the temperature value refers to

Note: There are triggers on the condition tables so that even though you will insert condition records for 'user1' these will be changed to refer to your own user number.

Note: where it is not specified, the query should return ALL the columns from the tables.

Note: in some cases you will need to make use of some of the analytical and/or filtering query expressions from the previous week

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

Part 3 –Set Queries and cartesian Joins

1. Write a query to list the 3D distance of all the rooms from all the ethernet cables. Your answer should have three columns – the ethernet_id, the room_id and the distance with alias distance. The rooms table should be listed first with alias a and the ethernet_cables table second with alias b.

2. Write a query to list the 2D distance between all the buildings and all the sensors. Your answer should have three columns – the building_id, the sensor_id and the distance with alias distance. The buildings table should be listed first with alias a, the sensors table second with alias b

3. Write a query that lists the room uses that are in the building with ID = 2 but not in the building with ID =1. Your answer should have one column called room_use. Use DISTINCT in both select statements so that each room_use is listed only once.

4. Write a query that lists the room uses that are in both the building with ID = 2 and the building with ID = 1. Your answer should have one column called room_use. Use DISTINCT in both select statements so that each room_use is listed only once. List the rooms from building with ID 2 first.

5. Write a query that creates a list of all the room_ids without windows, using an EXCEPT operation. Your answer should have one column room_id. Use DISTINCT on both select statements so that each room_id is listed only once.

Part 4 –INNER JOINS using primary and foreign keys

1. Write a query that combines all the information from the university and the buildings. Select the university table first as table a and the buildings second as table b. Select all columns from both tables

2. Write a query that combines all the information from the rooms and temperature sensors. Use an INNER JOIN with the rooms table first as table a, and the temperature sensors second as table b. Select all columns from both tables.

3. Write a query that joins the window_condition information to the asset_health information to put text in for each condition value. List all columns from both tables and have the window_condition listed first as table a and the asset_health_indicator table listed second as table b. Use an INNER JOIN

4. Write a query that joins the room_condition information to the asset_health information to put text for each condition value. This requires a number of separate INNER JOIN queries, once for each piece of condition information. List the room_condition first as table a, then the asset_healith_indicator as table b, c, d, e, f etc.

The first line of the query should be as follows:

select a.room_condition_id, a.room_id, a.report_date,

b.asset_health_indicator_description as ceiling_condition,

c.asset_health_indicator_description as wall_condition,

d.asset_health_indicator_description as doors_condition,

e.asset_health_indicator_description as windows_condition,

f.asset_health_indicator_description as furniture_equipment_condition,

g.asset_health_indicator_description as heating_system_condition,

h.asset_health_indicator_description as air_conditioning_condition,

i.asset_health_indicator_description as sockets_condition,

j.asset_health_indicator_description as lighting_and_switches_condition

and the pattern for the join should be as follows:

inner join ucfscde.asset_health_indicator b on a.ceiling_condition =

b.asset_health_indicator_id

inner join ucfscde.asset_health_indicator c on a.wall_condition =

c.asset_health_indicator_id

inner join ucfscde.asset_health_indicator d on a.doors_condition =

d.asset_health_indicator_id

5. Write a query that joins the window information to the criticality information to see the criticality of each window. List the window table first as a and the criticality table second as b. Select all columns from both tables. Use an INNER JOIN

Part 5 – LEFT JOINS using primary and foreign keys

1. Write a query that joins all the room information with sensor information where it exists. Use a LEFT join with room information as table a and sensor information as table b. Select all the columns from both tables.

2. Write a query that joins all the window information with any window_condition information where it exists. Use a LEFT JOIN and select all columns from both tables. Windows should be table a, the first in the join, and the window_condition table should be table b.

3. Write a query that joins all the building information with any building_condition information inserted before 25th June 2020 information. Use a LEFT JOIN with the building table first as a and then the building condition table as table b. Select all columns from both tables. Your date in the where clause should be formatted as '25-Jun-2020'

4. Write a query that joins all the building_condition information to the asset_health information to put text in for each condition value. This is a multi-join query. Use LEFT JOIN in each case. Table a should be the buildings table, and b, c, d, e, f etc should be the asset_health_indicator table. You should select the following columns:

select a.building_id, a.user_id, a.report_date, b.asset_health_indicator_description as roof_condition,

c.asset_health_indicator_description as opaque_facade_condition,

d.asset_health_indicator_description as transparent_facade_condition,

e.asset_health_indicator_description as doors_gates_condition,

f.asset_health_indicator_description as elevators_condition,

g.asset_health_indicator_description as stairs_condition,

h.asset_health_indicator_description as disabled_access_condition

5. Write a query that joins the room table to the temperature_sensors table and then takes the result of that join and joins it to the temperature values to find out all the temperature values measured for a particular room. Use a LEFT JOIN in each caes. Select all the columns from all the tables. Table a is the rooms, b is the sensors and c is the temperature values

Part 6 – FULL OUTER JOINS using primary and foreign keys

1. Write a query that joins the window information to the criticality information to see the criticality of each window to see if we have windows at all levels of criticality and any windows without a criticality rating. Use a FULL OUTER JOIN with the windows as the second table. Select all columns from both tables. Do not use a WITH statement

2. Use a FULL OUTER JOIN to write a query that joins the buildings and building_condition tables to double check if we have buildings without a condition rating captured in the last month. Use a WITH statement and crreate a temporary table called reports_last_month using DISTINCT ON first, and then use a full outer join with the buildings table first, selecting all columns from both tables.

The first line of the reports_last_month query should be as follows:

select distinct on (building_id) building_id, roof_condition, opaque_facade_condition, transparent_facade_condition, doors_gates_condition, elevators_condition, stairs_condition, disabled_access_condition, user_id, report_date

and you should use a WHERE clause as follows:

where report_date > (select date_trunc('day', NOW() - interval '1 months'))

3. Write a query that joins the users and room condition data to double check if we have users who haven't submitted a room condition value and if we have room condition reports without an associated user. Use a FULL OUTER JOIN and select all the columns from each table. The first table should be users with alias a and the second room_condition with alias b

4. Write a query that joins the university and buildings and rooms information together. Use a full outer join in each case with the university as table a, the buildings as table b and the rooms as table c. Your first line should be: select a.*, b.*, c.*

5. Write a query that joins the window information to the window health information and the asset health indicator information. Your query should list all columns from all tables, use FULL OUTER JOINs and use the tables as follows - windows as table a, window_condition as table b and asset_health_indicator as table c

Part 7 – JOINS using spatial relationships

1. Write a query that joins the buildings and ethernet cables to see if we have any cables totally outside buildings and any buildings without cables. We can't use a FULL OUTER join with a spatial condition so use two left joins and UNION ALL the results. The first left join should have the buildings as table a and the ethernet_cables as table b, the second should have the ethernet cables as table c and the buildings as table d. Select all columns from both tables. Use st_3dintersects. To make sure that the UNION operation works, the second query should be select d.*, c.* so that the columns are in the same order.

Finally, use select DISINCT around the UNION query to make sure that each row is only repeated once

2. Write a query that checks whether all the rooms are within buildings. Use a LEFT JOIN with the rooms as table a and the buildings as table b. Use st_3dintersects Select all columns from both tables

3. Write a query to find out which windows are associated with each building. Use st_3dintersects and use a left join with the windows first as table a and the buildings second as table b. Select all columns from both tables. Use an INNER JOIN

4. Write a query to combine the details of the temperature sensors and the university campus. Use a LEFT JOIN with the temperature_sensors first as table a and the university as table b. Select all columns from both tables. Use st_intersects

5. Write a query to list all the details of the rooms and university, checking if the rooms are on campus. Use a LEFT JOIN with the rooms first as table a and the university second as table b. Select all columns from both tables. Use st_intersects

Part 8 – Views

As we are all working in the same database and schema, we can't create views individually – as we would probably try and create views with the same name and these would overwrite each other.

If you would like to practice creating views to see how they work, you can do so in your own database, using the data created for Assignment 2 (or of course using the data you're creating for Assignment 5).

Part 9 –Combining Operators

1. Write a query that lists all the windows and their latest condition reports taken within the last 2 months. You should use a WITH statement using ORDER BY and DISTINCT ON to get the latest reports, calling the temporary table latest_window_reports, and then use this result as input to a JOIN. Remember that some windows might not have condition reports within the last 2 months so use a LEFT JOIN with the windows first as a and the latest_window_reports second as b. Select all columns from both tables.

2. USE a cartesian join, order by and distinct on to find the sensor that is furthest away from each room. The rooms should be table a and the temperature_sensors table b. Your answer should have the following columns:

room_id

sensor_id

st_distance(a.location, b.location) as distance

3. Write a query to find out if it is cheaper to refurbish the all the classrooms or all the computer labs. Use WITH. First calculate the cost of refurbishing the classrooms, giving the temporary table the name of classroom_cost with a column classroom_cost_in_£.

Secondly, calculate the cost of refurbishing the labs, giving it the name lab_cost with a column lab_cost_in_£.

For both of these cost calculations you can use a cartesian join between the rooms table and the parameters table as there is only one matching parameter value so there won't be a double calculation. You should also use sum(st_area () * parameter_value) to calculate the cost.

Then do a select query (cartesian join) that subtracts the two. As there is only one row in each of the temporary tables you will get a one-row result. The result should have three columns.

● classroom_cost_in_£

● lab_cost_in_£

● classroom_cheaper_than_lab

Table aliases are as follows:

● classroom_cost uses rooms as table a and parameters as table b

● lab_cost uses rooms as table c and parameters as table d

● the final select uses classroom_cost as table e and lab_cost as table f

4. Write a query that lists the room uses that are in Building 2 but not in Building 1. Do not use WITH, use a nested query for each building. Use DISTINCT

5. What is the average condition of room 1.01 in each building? Use a WITH statement to find the ID of any room that has number 1.01, selecting all the columns from the rooms table and calling the temporary table room_101

Then create a second temporary table called latest_condition_reports_average with columns room_id, report_date, user_id and the average condition value with alias average_condition. You will need to use order by and DISTINCT ON for this

Then join the two using an INNER JOIN with room_101 as table a and latest_condition_reports_average as table b. The first line of this final select statement should be:

select a.*, b.average_condition, b.user_id, b.report_date