Microsoft 70-461 Exam Questions Instant Access

Question 1

You use Microsoft SQL Server to develop a database application.

Your application sends data to a VARCHAR(50) variable named @var.

You need to write a Transact-SQL statement that will return information on a successful or unsuccessful cast to an integer in a table.

Which Transact-SQL statement should you run?

AOption A

BOption B

COption C

DOption D

Answer : B

TRY_PARSE returns the result of an expression, translated to the requested data type, or null if the cast fails in SQL Server. Use TRY_PARSE only for converting from string to date/time and number types.

References: https://docs.microsoft.com/en-us/sql/t-sql/functions/try-parse-transact-sql?view=sql-server-2017

Question 2

You have a database that contains the tables shown in the exhibit. (Click the Exhibit button.)

You have an application named Appl. You have a parameter named @Count that uses the int data type. App1 is configured to pass @Count to a stored procedure. You need to create a stored procedure named usp_Customers for Appl. Usp_Customers must meet the following requirements:

* NOT use object delimiters.

* Minimize sorting and counting.

* Return only the last name of each customer in alphabetical order.

* Return only the number of rows specified by the @Count parameter.

* The solution must NOT use BEGIN and END statements.

Which code segment should you use?

To answer, type the correct code in the answer area.

ACREATE PROCEDURE usp_Customers @Count int
AS
SELECT TOP(@Count) Customers.LastName
FROM Customers
ORDER BY Customers.LastName

BCREATE PROCEDURE usp_Customers @Count int
AS
SELECT TOP(@Count) Customers.LastName
ORDER BY Customers.LastName

Answer : A

Question 3

Answer : G

Question 4

Your database contains tables named Products and ProductsPriceLog. The Products table contains columns named ProductCode and Price. The ProductsPriceLog table contains columns named ProductCode, OldPrice, and NewPrice.

The ProductsPriceLog table stores the previous price in the OldPrice column and the new price in the NewPrice column.

You need to increase the values in the Price column of all products in the Products table by 5 percent. You also need to log the changes to the ProductsPriceLog table.

Which Transact-SQL query should you use?

AUPDATE Products SET Price = Price * 1.05
OUTPUT inserted.ProductCode, deleted.Price, inserted.Price
INTO ProductsPriceLog(ProductCode, OldPrice, NewPrice)

BUPDATE Products SET Price = Price * 1.05
OUTPUT inserted.ProductCode, inserted.Price, deleted.Price
INTO ProductsPriceLog(ProductCode, OldPrice, NewPrice)

CUPDATE Products SET Price = Price * 1.05
OUTPUT inserted.ProductCode, deleted.Price, inserted.Price * 1.05
INTO ProductsPriceLog(ProductCode, OldPrice, NewPrice)

DUPDATE Products SET Price = Price * 1.05
INSERT INTO ProductsPriceLog(ProductCode, OldPrice, NewPrice)
SELECT ProductCode, Price, Price * 1.05 FROM Products

Answer : A

Question 5

You create a table by using the following Transact-SQL Statement:

You need to return a result set that has a single column named DisplayInformation. The result set must contain the Name value if the Name value is NOT NULL, otherwise the result set must contain the SubName value.

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.

See Below Explanation:

Update line 1 to get the following:

SELECT IIF (Name IS NOT NULL, Name, SubName)

FROM Products;

IIF returns one of two values, depending on whether the Boolean expression evaluates to true or false in SQL Server.

Syntax: IIF ( boolean_expression, true_value, false_value )

If the value of expression is NULL, IS NULL returns TRUE; otherwise, it returns FALSE.

If the value of expression is NULL,IS NOT NULL returns FALSE; otherwise, it returns TRUE.

To determine whether an expression is NULL, use IS NULL or IS NOT NULL instead of comparison operators (such as = or !=). Comparison operators return UNKNOWN when either or both arguments are NULL

References:https://msdn.microsoft.com/en-us/library/hh213574.aspx

https://msdn.microsoft.com/en-us/library/ms188795.aspx

Answer : A

Update line 1 to get the following:

SELECT IIF (Name IS NOT NULL, Name, SubName)

FROM Products;

IIF returns one of two values, depending on whether the Boolean expression evaluates to true or false in SQL Server.

Syntax: IIF ( boolean_expression, true_value, false_value )

If the value of expression is NULL, IS NULL returns TRUE; otherwise, it returns FALSE.

If the value of expression is NULL,IS NOT NULL returns FALSE; otherwise, it returns TRUE.

To determine whether an expression is NULL, use IS NULL or IS NOT NULL instead of comparison operators (such as = or !=). Comparison operators return UNKNOWN when either or both arguments are NULL

References:https://msdn.microsoft.com/en-us/library/hh213574.aspx

https://msdn.microsoft.com/en-us/library/ms188795.aspx

Question 6

You develop a Microsoft SQL Server 2012 database. You create a view that performs the following tasks:

* Joins 8 tables that contain up to 500,000 records each.

* Performs aggregations on 5 fields.

The view is frequently used in several reports.

You need to improve the performance of the reports.

What should you do?

AConvert the view into a table-valued function.

BConvert the view into a Common Table Expression (CTE).

CConvert the view into an indexed view.

DConvert the view into a stored procedure and retrieve the result from the stored procedure into a temporary table.

Answer : C

http://msdn.microsoft.com/en-us/library/ms191432.aspx

Question 7

You administer a Microsoft SQL Server database named Sales. The database is 3 terabytes in size. The Sales database is configured as shown in the following table.

You discover that Sales_2.ndf is corrupt. You need to recover the corrupted data in the minimum amount of time. What should you do?

APerform a file restore.

BPerform a transaction log restore.

CPerform a restore from a full backup.

DPerform a filegroup restore.

Answer : A

Microsoft Querying Microsoft SQL Server 2012/2014 70-461 Exam Questions