right side when there is no match. A customer might have no orders so the right table must be allowed have a NULL value. Box 3: ON c.custid = o.custid We JOIN on the custID column, which is available in both tables. Box 4: GROUP BY c.custid
References: (v=sql.110).aspx QUESTION: 57 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.
Answer: Exhibit Explanation: Exhibit
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. References: (v=sql.105).aspx - us/library/ms186986.aspx QUESTION: 58 You are building a stored procedure that will update data in a table named Table1 by using a complex query as the data source. You need to ensure that the SELECT statement in the stored procedure meets the following requirements: ? Data being processed must be usable in several statements in the stored procedure.
You've reached the end of your free preview.
Want to read all 14 pages?
- Fall '20