100% Valid Lead2pass Microsoft 70-761 New Questions Free Version.v.2018-2-7.135q:
https://www.lead2pass.com/70-761.html
QUESTION 106
Hotspot Question
You have a table named HumanResources.Department that was created with the query shown in the exhibit. (Click the Exhibit button.)
You need to query temporal data in the table.
In the table below, identify the Transact-SQL segments that must be used to retrieve the appropriate data. NOTE: Make only one selection in each column.
Answer:
Explanation:
AS OF: Returns a table with a rows containing the values that were actual (current) at the specified point in time in the past.
CONTAINED IN: If you search for non-current row versions only, we recommend you to use CONTAINED IN as it works only with the history table and will yield the best query performance.
Incorrect Answers:
Not ALL: Returns the union of rows that belong to the current and the history table.
QUESTION 107
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.
– Data being processed must contain statistics.
What should you do?
A. Update Table1 by using a common table expression (CTE).
B. Insert the data into a temporary table, and then update Table1 from the temporary table.
C. Place the SELECT statement in a derived table, and then update Table1 by using a JOIN to the derived table.
D. Insert the data into a table variable, and then update Table1 from the table variable.
Answer: B
Explanation:
Temp Tables…
Are real materialized tables that exist in tempdb
Have dedicated stats generated by the engine
Can be indexed
Can have constraints
Persist for the life of the current CONNECTION
Can be referenced by other queries or subproce
Incorrect Answers:
A: CTEs do not have dedicated stats. They rely on stats on the underlying objects
C: Unlike a derived table, a CTE can be self-referencing and can be referenced multiple times in the same query.
References:
https://technet.microsoft.com/en-us/library/ms190766(v=sql.105).aspx https://dba.stackexchange.com/questions/13112/whats-the-difference-between-a-cte-and-a-temp-table
QUESTION 108
You have a database that includes the tables shown in the exhibit. (Click the exhibit button.)
You need to create a list of all customers and the date that the customer placed their last order. For customers who have not placed orders, you must substitute a zero for the order ID and 01/01/1990 for the date.
Which Transact-SQL statement should you run?
A. 
B. 
C. 
D. 
Answer: A
Explanation:
COALESCE evaluates the arguments in order and returns the current value of the first expression that initially does not evaluate to NULL.
References: https://docs.microsoft.com/en-us/sql/t-sql/language-elements/coalesce-transact-sql
QUESTION 109
You have a disk-based table that contains 15 columns.
You query the table for the number of new rows created during the current day.
You need to create an index for the query. The solution must generate the smallest possible index.
Which type of index should you create?
A. clustered
B. filtered nonclustered with a getdate() predicate in the WHERE statement clause
C. hash
D. nonclustered with compression enabled
Answer: B
Explanation:
A filtered index is an optimized nonclustered index especially suited to cover queries that select from a well-defined subset of data. It uses a filter predicate to index a portion of rows in the table. A well-designed filtered index can improve query performance as well as reduce index maintenance and storage costs compared with full-table indexes.
Creating a filtered index can reduce disk storage for nonclustered indexes when a full-table index is not necessary.
References: https://docs.microsoft.com/en-us/sql/relational-databases/indexes/create-filtered-indexes
QUESTION 110
Note: This question is part of a series of questions that use the same or similar answer choices. An answer choice may be correct for more than one question in the series. Each question is independent of the other questions in this series. Information and details provided in a question apply only to that question.
You have a database that contains several connected tables. The tables contain sales data for customers in the United States only.
You have the following partial query for the database. (Line numbers are included for reference only.)
You need to complete the query to generate the output shown in the following table.
Which statement clause should you add at line 3?
A. GROUP BY
B. MERGE
C. GROUP BY ROLLUP
D. LEFT JOIN
E. GROUP BY CUBE
F. CROSS JOIN
G. PIVOT
H. UNPIVOT
Answer: E
QUESTION 111
Note: This question is part of a series of questions that use the same or similar answer choices. An answer choice may be correct for more than one question in the series. Each question is independent of the other questions in this series. Information and details provided in a question apply only to that question.
You have a database that contains several connected tables. The tables contain sales data for customers in the United States only.
You have the following partial query for the database. (Line numbers are included for reference only.)
You need to complete the query to generate the output shown in the following table.
Which statement clause should you add at line 3?
A. GROUP BY
B. MERGE
C. GROUP BY ROLLUP
D. LEFT JOIN
E. GROUP BY CUBE
F. CROSS JOIN
G. PIVOT
H. UNPIVOT
Answer: C
QUESTION 112
Note: This question is part of a series of questions that use the same or similar answer choices. An answer choice may be correct for more than one question in the series. Each question is independent of the other questions in this series. Information and details provided in a question apply only to that question.
You have a database that contains several connected tables. The tables contain sales data for customers in the United States only.
You have the following partial query for the database. (Line numbers are included for reference only.)
You need to complete the query to generate the output shown in the following table.
Which statement clause should you add at line 3?
A. GROUP BY
B. MERGE
C. GROUP BY ROLLUP
D. LEFT JOIN
E. GROUP BY CUBE
F. CROSS JOIN
G. PIVOT
H. UNPIVOT
Answer: F
Explanation:A cross join that does not have a WHERE clause produces the Cartesian product of the tables involved in the join. The size of a Cartesian product result set is the number of rows in the first table multiplied by the number of rows in the second table.
References: https://technet.microsoft.com/en-us/library/ms190690(v=sql.105).aspx
QUESTION 113
You create a table by running the following Transact-SQL statement:
You need to view all customer data.
Which Transact-SQL statement should you run?
A. 
B. 
C. 
D. 
E. 
F. 
G. 
H. 
Answer: B
Explanation:
The FOR SYSTEM_TIME ALL clause returns all the row versions from both the Temporal and History table.
References: https://msdn.microsoft.com/en-us/library/dn935015.aspx
QUESTION 114
SIMULATION
You have a table named Cities that has the following two columns: CityID and CityName. The CityID column uses the int data type, and CityName uses nvarchar(max).
You have a table named RawSurvey. Each row includes an identifier for a question and the number of persons that responded to that question from each of four cities. The table contains the following representative data:
A reporting table named SurveyReport has the following columns: CityID, QuestionID, and RawCount, where RawCount is the value from the RawSurvey table.
You need to write a Transact-SQL query to meet the following requirements:
Retrieve data from the RawSurvey table in the format of the SurveyReport table.
The CityID must contain the CityID of the city that was surveyed.
The order of cities in all SELECT queries must match the order in the RawSurvey table.
The order of cities in all IN statements must match the order in the RawSurvey table.
Construct the query using the following guidelines:
Use one-part names to reference tables and columns, except where not possible.
ALL SELECT statements must specify columns.
Do not use column or table aliases, except those provided.
Do not surround object names with square brackets.
Part of the correct Transact-SQL has been provided in the answer area below. Enter the code in the answer area that resolves the problem and meets the stated goals or requirements. You can add code within the code that has been provided as well as below it.
Use the Check Syntax button to verify your work. Any syntax or spelling errors will be reported by line and character position.
Answer:
1 SELECT Rawcount
2 from (select cityid,questioned,rawcount) AS t1
3 unpivot
4 (rawcount for questioned in (QuestionID)) AS t2
5 JOIN t2
6. ON t1.CityName = t2.cityName
UNPIVOT must be used to rotate columns of the Rawsurvey table into column values.
References: https://technet.microsoft.com/en-us/library/ms177410(v=sql.105).aspx
QUESTION 115
SIMULATION
You create a table named Sales.Categories by running the following Transact-SQL statement:
You add the following data to the table.
You need to create a query that uses a common table expression (CTE) to show the parent category of each category. The query must meet the following requirements:
Return all columns from the Categories table in the order shown.
Exclude all categories that do not have a parent category.
Construct the query using the following guidelines:
Name the expression ParentCategories.
Use PC as the alias for the expression.
Use C as the alias for the Categories table.
Use the AS keyword for all table aliases.
Use individual column names for each column that the query returns.
Do not use a prefix for any column name.
Do not surround object names with square brackets.
Part of the correct Transact-SQL has been provided in the answer area below. Enter the code in the answer area that resolves the problem and meets the stated goals or requirements. You can add code within the code that has been provided as well as below it.
Use the Check Syntax button to verify your work. Any syntax or spelling errors will be reported by line and character position. You may check syntax as many times as needed.
Answer:
1 WITH ParentCategories pc (CategoryID, Name, PatentCategoryID) AS (SELECT
A. categoryID,c.name,c.parentcategoryid
2 FROM sales.categories c
3 WHERE parentcategoryid is not null
4 )
5 SELECT * FROM parentcategories
Note: On Line 1 replace c with WITH ParentCategories pc (CategoryID, Name, PatentCategoryID) AS Note: The basic syntax structure for a CTE is:
WITH expression_name [ ( column_name [,…n] ) ]
AS
( CTE_query_definition )
References: https://technet.microsoft.com/en-us/library/ms190766(v=sql.105).aspx
70-761 dumps full version (PDF&VCE): https://www.lead2pass.com/70-761.html
Large amount of free 70-761 exam questions on Google Drive: https://drive.google.com/open?id=0B3Syig5i8gpDU2RSQnhzX2pIZVE
You may also need:
70-762 exam dumps: https://drive.google.com/open?id=0B3Syig5i8gpDMW9NcjJrQXlsMGs
70-764 exam dumps: https://drive.google.com/open?id=0B3Syig5i8gpDUjBoM0pVQnlUTlU
70-765 exam dumps: https://drive.google.com/open?id=0B3Syig5i8gpDejczeWp0aURaSnM
70-767 exam dumps: https://drive.google.com/open?id=0B3Syig5i8gpDdTF0R0taLWgxSmc
70-768 exam dumps: https://drive.google.com/open?id=0B3Syig5i8gpDZ2pRQkV6Vnc4dHc
