数据库代写｜SQL – Advanced Queries 1 Nested Queries

This Week

– And run the queries as you did last week

SELECT * FROM << TABLE NAME >>

SELECT * FROM (<< SELECT * FROM << TABLE NAME >>)

SELECT <FIELDNAME1, FIELDNAME2>

FROM <TABLENAME1>

WHERE <FIELDNAME1> IN

(SELECT <FIELDNAME2> FROM <TABLENAME2>)

TABLENAME1 and TABLENAME 2 can refer to the same table or different tables

– ER Diagram shows that the room table has a primary/foreign key relationship with the buildings table

– First write a sub query to find the ID of the Building 2 select building_id from ucfscde.buildings where building_name = ‘Building 2’;

– Find the maximum temperature

– Then find the date when that occurred

select reading_timestamp from ucfscde.temperature_values where value_degrees_c = (select max(value_degrees_c) from ucfscde.temperature_values);

– First find the maximum (latest) installation date select max(window_installation_date) from ucfscde.windows;

– Then find the location of that window select location from ucfscde.windows where window_installation_date = (select max(window_installation_date) from ucfscde.windows);Nested Queries

– Then find the building that intersects that location – we use st_3dintersects as the building and windows are 3D select * from ucfscde.buildings a where st_3dintersects(a.location,(select location from ucfscde.windows where window_installation_date = (select max(window_installation_date) from ucfscde.windows)));

– At this point it is useful to look at the ER diagram to see how buildings and temperature values are linked

– Option 1 -> temperature values -> temperature_sensors -> rooms -> buildings

– Option 2 -> take advantage of the fact that the sensors have a location and do an st_contains query so: temperature_values -> temperature_sensors -> (st_contains) buildings Option 1

– Find the max temperature

– Find out which sensor it was measured by

– Find out which room that sensor is in

– Find out which building that room is in

– Get the details of the buildingOption 1 select * from ucfscde.buildings where building_id = (select building_id from ucfscde.rooms where room_id = (select room_id from ucfscde.temperature_sensors where sensor_id = (select temperature_sensor_id from ucfscde.temperature_values where value_degrees_c = (select min(value_degrees_c) from ucfscde.temperature_values))));

– Find the max temperature

– Find out which sensor it was measured by

– We don’t have a 3D contains function (yet!) so we measure the distance from the sensor to the containing building – it should be 0

– Get details of the buildingOption 2 select * from ucfscde.buildings b where st_distance(b.location, (select location from ucfscde.temperature_sensors where sensor_id = (select temperature_sensor_id from ucfscde.temperature_values where value_degrees_c = (select min(value_degrees_c) from ucfscde.temperature_values)))) =0;