首页 » 数据库辅导 » 数据库代写｜SQL – Advanced Queries 1 Nested Queries

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

这是一篇来自美国的关于使用一个SQL查询的输出作为对另一个SQL查询输入的数据库代写

 

This Week

  • Last week we looked at queries that used data from one table
  • However in real life you’ll probably need data from multiple tables to provide evidence for decision making
  • This week we’ll look at a number of ways to combine data from multiple tablesThis Lesson
  • We’ll learn about nested queries, which use the output of one SQL query as the input into another SQL queryNested Queries
  • As you go through this lesson make sure you have the Centennial ER diagram next to you so that you can stop at each example and look at how the tables are related in the diagram!

– And run the queries as you did last week

  • As we’ve mentioned, the result of an SQL query statement is a TABLE
  • As the input into an SQL query statement is also a table, we can use the result of one query as input into the next one
  • Also known as SUB QUERIES
  • We do this by nesting the queries
  • This means writing one query and putting it inside another one
  • NB: You always end up with ONE SQL statement (not two separate queries)
  • A nested query is a query within a query
  • The output of any query can be used like a temporary/virtual table as input to another query
  • So instead of:

SELECT * FROM << TABLE NAME >>

  • You can have (note the brackets)

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

  • Find the rooms in Building 2

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

  • Using the building_id columnNested Queries
  • Find the rooms in Building 2

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

  • Then nest this inside a query to find the windows (use brackets) select * from ucfscde.rooms where building_id = (select building_id from ucfscde.buildings where building_name = ‘Building 2’);
  • When did the maximum temperature reading occur?
  • This is a nested query using values from the SAME table

– Find the maximum temperature

– Then find the date when that occurred

  • When did the maximum temperature reading occur?

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

  • Which building is the newest window in?

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

  • Which building is the newest window in?

– 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

  • Which building is the newest window in?

– 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)));

  • Find the details of the building that recorded the lowest temperature

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

  • Find the details of the building that recorded the lowest temperature
  • Find the details of the building that recorded the lowest temperature

– 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

  • Follow the ER Diagram

– 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))));

  • Using a spatial function

– 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;

程序辅导定制C/C++/JAVA/安卓/PYTHON/留学生/PHP/APP开发/MATLAB

本网站支持 Alipay WeChatPay PayPal等支付方式

E-mail: vipdue@outlook.com  微信号:vipnxx

如果您使用手机请先保存二维码，微信识别。如果用电脑，直接掏出手机果断扫描。

标签: