HOTSPOT
You have the following subqueries: Subquery1, Subquery2, and Subquery3.
You need to replace the three subqueries with named result sets or temporary tables. The following
requirements must be met:

Which replacement techniques should you use? To answer, select the appropriate options in the answer area.
Hot Area:

Answer:

Explanation:
Subquery1: common table expression (CTE)
A common table expression (CTE) can be thought of as a temporary result set that is defined within the
execution scope of a single SELECT, INSERT, UPDATE, DELETE, or CREATE VIEW statement. A CTE is
similar to a derived table in that it is not stored as an object and lasts only for the duration of the query. Unlike a
derived table, a CTE can be self-referencing and can be referenced multiple times in the same query.
Subquery2: global temporary table
Global temporary tables are visible to any user and any connection after they are created, and are deleted
when all users that are referencing the table disconnect from the instance of SQL Server.
Subquery3: local temporary table
Local temporary tables are visible only to their creators during the same connection to an instance of SQL
Server as when the tables were first created or referenced. Local temporary tables are deleted after the user
disconnects from the instance of SQL Server.

https://technet.microsoft.com/en-us/library/ms190766(v=sql.105).aspx
https://technet.microsoft.com/en-us/library/ms186986.aspx