2018 February New Microsoft 70-761 Exam Dumps with PDF and VCE Free Updated Today! Following are some new 70-761 Real Exam Questions:
1.|2018 New 70-761 Exam Dumps (PDF & VCE) 135Q&As Download:
https://www.braindump2go.com/70-761.html
2.|2018 New 70-761 Exam Questions & Answers Download:
https://drive.google.com/drive/folders/0B75b5xYLjSSNZG9yTW9reVdkZG8?usp=sharing
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
Explanation:
Example of GROUP BY CUBE result set:
In the following example, the CUBE operator returns a result set that has one grouping for all possible combinations of columns in the CUBE list and a grand total grouping.
References: https://technet.microsoft.com/en-us/library/bb522495(v=sql.105).aspx
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
Explanation:In the result sets that are generated by the GROUP BY operators, NULL has the following uses:
If a grouping column contains NULL, all null values are considered equal, and they are put into one NULL group.
When a column is aggregated in a row, the value of the column is shown as NULL.
Example of GROUP BY ROLLUP result set:
References: https://technet.microsoft.com/en-us/library/bb522495(v=sql.105).aspx
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
!!!RECOMMEND!!!
1.|2018 New 70-761 Exam Dumps (PDF & VCE) 135Q&As Download:
https://www.braindump2go.com/70-761.html
2.|2018 New 70-761 Study Guide Video:
https://youtu.be/7Q-ah51thwA