Microsoft Querying Data with Transact-SQL (70-761) Certification Sample Questions

The purpose of this Sample Question Set is to provide you with information about the Microsoft Querying Data with Transact-SQL exam. These sample questions will make you very familiar with both the type and the difficulty level of the questions on the 70-761 certification test. To get familiar with real exam environment, we suggest you try our Sample Microsoft Querying Data with Transact-SQL Certification Practice Exam. This sample practice exam gives you the feeling of reality and is a clue to the questions asked in the actual Microsoft Certified Solutions Associate (MCSA) - SQL 2016 Database Development certification exam.

These sample questions are simple and basic questions that represent likeness to the real Microsoft 70-761 exam questions. To assess your readiness and performance with real time scenario based questions, we suggest you prepare with our Premium Microsoft Querying Data with Transact-SQL Certification Practice Exam. When you solve real time scenario based questions practically, you come across many difficulties that give you an opportunity to improve.

Microsoft 70-761 Sample Questions:

01. You have a database that stores sales and order information. Users must be able to extract information from the tables on an ad hoc basis. They must also be able to reference the extracted information as a single table.

You need to implement a solution that allows users to retrieve the data required, based on variables defined at the time of the query. What should you implement?

a) the COALESCE function

b) a view

c) a table-valued function

d) the TRY_PARSE function

e) a stored procedure

f) the ISNULL function

g) a scalar function

h) the TRY_CONVERT function

 

02. You have a table named Products that contains information about the products that your company sells. The table contains many columns that do not always contain values. You need to implement an ANSI standard method to convert the NULL values in the query output to the phrase “Not Applicable”.

What should you implement?

a) the COALESCE function

b) a view

c) a table-valued function

d) the TRY_PARSE function

e) a stored procedure

f) the ISNULL function

g) a scalar function

h) the TRY_CONVERT function

 

03. 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.

 

04. You need to create an indexed view that requires logic statements to manipulate the data that the view displays. Which two database objects should you use?

Each correct answer presents a complete solution.

a) a user-defined table-valued function

b) a CRL function

c) a stored procedure

d) a user-defined scalar function

 

05. Multiple processes use the data from a table named Sales and place it in other databases across the organization. Some of the processes are not completely aware of the data types in the Sales table. This leads to data type conversion errors.

You need to implement a method that returns a NULL value id data conversion fails instead of throwing an error. What should you implement?

a) the COALESCE function

b) a view

c) a table-valued function

d) the TRY_PARSE function

e) a stored procedure

f) the ISNULL function

g) a scalar function

h) the TRY_CONVERT function

 

06. You create a table by running the following Transact-SQL statement:

You are developing a report that displays customer information. The report must contain a grand total column. You need to write a query that returns the data for the report. Which Transact-SQL statement should you run?

a) Option A

b) Option B

c) Option C

d) Option D

e) Option E

f) Option F

g) Option G

h) Option H

 

07. You have a table named AuditTrail that tracks modifications to data in other tables. The AuditTrail table is updated by many processes. Data input into AuditTrail may contain improperly formatted date time values.

You implement a process that retrieves data from the various columns in AuditTrail, but sometimes the process throws an error when it is unable to convert the data into valid date time values. You need to convert the data into a valid date time value using the en-US format culture code. If the conversion fails, a null value must be returned in the column output. The conversion process must not throw an error.

What should you implement?

a) the COALESCE function

b) a view

c) a table-valued function

d) the TRY_PARSE function

e) a stored procedure

f) the ISNULL function

g) a scalar function

h) the TRY_CONVERT function

 

08. You have a database that is denormalized. Users make frequent changes to data in a primary table. You need to ensure that users cannot change the tables directly, and that changes made to the primary table also update any related tables.

What should you implement?

a) the COALESCE function

b) a view

c) a table-valued function

d) the TRY_PARSE function

e) a stored procedure

f) the ISNULL function

g) a scalar function

h) the TRY_CONVERT function

 

09. 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

 

10. You need to create a database object that meets the following requirements:

- accepts a product identifies as input

- calculates the total quantity of a specific product, including quantity on hand and quantity on order

- caches and reuses execution plan

- returns a value

- can be called from within a SELECT statement

- can be used in a JOIN clause

What should you create?

a) a user-defined table-valued function

b) a temporary table that has a columnstore index

c) a memory-optimized table that has updated statistics

d) a natively-complied stored procedure that has an OUTPUT parameter

Answers:

Note: For any error in Microsoft Certified Solutions Associate (MCSA) - SQL 2016 Database Development (70-761) certification exam sample questions, please update us by writing an email on feedback@edusum.com.