Microsoft 70-761 Exam Questions Instant Access

Question 1

You have a table named Table1 that contains 200 million rows. Table1 contains a column named SaleDate that has a data type of DateTime2(3).

Users report that the following query runs slowly.

You need to reduce the amount of time it takes to run the query.

What should you use to replace the WHERE statement?

AWHERE SaleDate >= '2017-01-01' AND SaleDate < '2018-01-01'

BWHERE cast(SaleDate as varchar(10)) BETWEEN '2017-01-01'
AND '2017-12-31'

CWHERE cast(SaleDate as date) BETWEEN '2017-01-01'
AND '2017-12-31'

DWHERE 2017 = year(SaleDate)

Answer : C

References:

https://docs.microsoft.com/en-us/sql/t-sql/queries/select-transact-sql?view=sql-server-2017

Question 2

Note: This question is part of a series of questions that present the same scenario. Each question in the series contains a unique solution that might meet the stated goals. Some question sets might have more than one correct solution, while others might not have a correct solution.

After you answer a question in this section, you will NOT be able to return to it. As a result, these questions will not appear in the review screen.

You are building a stored procedure that will be used by hundreds of users concurrently.

You need to store rows that will be processed later by the stored procedure. The object that stores the rows must meet the following requirements:

* Be indexable

* Contain up-to-date statistics

* Be able to scale between 10 and 100,000 rows

The solution must prevent users from accessing one another's data.

Solution: You create a global temporary table in the stored procedure.

Does this meet the goal?

AYes

BNo

Answer : A

Question 3

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 tables named Customer_CRMSystem and Customer_HRSystem. Both tables use the following structure:

The tables include the following records:

Customer_CRMSystem

Customer_HRSystem

Records that contain null values for CustomerCode can be uniquely identified by CustomerName.

You need to display a Cartesian product, combining both tables.

Which Transact-SQL statement should you run?

AOption A

BOption B

COption C

DOption D

EOption E

FOption F

GOption G

HOption H

Answer : G

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 4

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?

AGROUP BY

BMERGE

CGROUP BY ROLLUP

DLEFT JOIN

EGROUP BY CUBE

FCROSS JOIN

GPIVOT

HUNPIVOT

Answer : C

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 5

You have a database that contains tables named Customer_CRMSystem and Customer_HRSystem. Both tables use the following structure:

The tables include the following records:

Customer_CRMSystem

Customer_HRSystem

Records that contain null values for CustomerCode can be uniquely identified by CustomerName.

You need to create a list of all unique customers that appear in either table.

Which Transact-SQL statement should you run?

AOption A

BOption B

COption C

DOption D

EOption E

FOption F

GOption G

HOption H

Answer : E

UNION combines the results of two or more queries into a single result set that includes all the rows that belong to all queries in the union. The UNION operation is different from using joins that combine columns from two tables.

Question 6

After you answer a question in this section, you will NOT be able to return to it. As a result, these questions will not appear in the review screen.

You are creating indexes in a data warehouse.

You have a dimension table named Table1 that has 10,000 rows. The rows are used to generate several reports.

The reports join a column that is the primary key.

The execution plan contains bookmark lookups for Table1.

You discover that the reports run slower than expected.

You need to reduce the amount of time it takes to run the reports.

Solution: You create a nonclustered index on the primary key column that includes the bookmark lookup columns.

Does this meet the goal?

AYes

BNo

Answer : B

Question 7

After you answer a question in this section, you will NOT be able to return to it. As a result, these questions will not appear in the review screen.

You have a database named DB1 that contains two tables named Sales.Customers and Sales.Orders. Sales.Customers has a foreign key relationship to a column named CustomerID in Sales.Orders.

You need to recommend a query that returns all the customers. The query must also return the number of orders that each customer placed in 2016.

Solution: You recommend the following query:

Does this meet the goal?

AYes

BNo

Answer : A

Microsoft Querying Data with Transact-SQL 70-761 Exam Questions