Microsoft 70-461 Exam Practice Test Instant Access

Question 1

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 2

Answer : M

Question 3

You are a database developer of a Microsoft SQL Server database.

You are designing a table that will store Customer data from different sources. The table will include a column that contains the CustomerID from the source system and a column that contains the SourceID.

A sample of this data is as shown in the following table.

You need to ensure that the table has no duplicate CustomerID within a SourceID. You also need to ensure that the data in the table is in the order of SourceID and then CustomerID.

Which Transact- SQL statement should you use?

ACREATE TABLE Customer(SourceID int NOT NULL,CustomerID int NOT NULL,CustomerName varchar(255) NOT NULLCONSTRAINT UQ_Customer UNIQUE(SourceID, CustomerID));

BCREATE TABLE Customer(SourceID int NOT NULL UNIQUE,CustomerID int NOT NULL UNIQUE,CustomerName varchar(255) NOT NULL);

CCREATE TABLE Customer(SourceID int NOT NULL PRIMARY KEY CLUSTERED,CustomerID int NOT NULL UNIQUE,CustomerName varchar(255) NOT NULL);

DCREATE TABLE Customer(SourceID int NOT NULL,CustomerID int NOT NULL,CustomerName varchar(255) NOT NULL,CONSTRAINT PK_Customer PRIMARY KEY CLUSTERED(SourceID, CustomerID));

Answer : D

A PRIMARY KEY is a constraint that enforces entity integrity for a specified column or columns by using a unique index. Only one PRIMARY KEY constraint can be created for each table.

We need to use both SourceID and CustomerID, in that order, in the PRIMARY KEY constraint.

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

Question 4

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

You need to create a query for a report. The query must meet the following requirements:

* Return the last name of the customer who placed the order.

* Return the most recent order date for each customer.

* Group the results by CustomerID.

* Display the most recent OrderDate first.

The solution must support the ANSI SQL-99 standard and must not use table or column aliases.

You need to create a query for a report. The query must meet the following requirements:

* Return the last name of the customer who placed the order.

* Return the most recent order date for each customer.

* Group the results by CustomerID.

* Display the most recent OrderDate first.

The solution must support the ANSI SQL-99 standard and must not use table or column aliases.

Part of the correct Transact-SQL has been provided in the answer area below. Enter the Transact-SQL in the answer area that resolves the problem and meets the stated goals or requirements. You can add Transact-SQL within the Transact-SQL segment 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.

A1. SELECT LastName,
2 MAX(OrderDate)
3 FROM Customers INNER JOIN Orders ON Customers.CustomerID = Orders.CustomerID
4 GROUP BY CustomerID
5 ORDER BY OrderDate DESC
On line 3 add Customers INNER JOIN Orders ON
On line 4 add CustomerID
On line 5 add OrderDate DESC

B1. SELECT LastName,
2 MAX(OrderDate) AS MostRecentOrderDate
3 FROM Customers INNER JOIN Orders ON Customers.CustomerID = Orders.CustomerID
4 GROUP BY CustomerID
5 ORDER BY OrderDate DESC
On line 3 add Customers INNER JOIN Orders ON Customers.CustomerID = Orders.CustomerID
On line 4 add CustomerID
On line 5 add OrderDate DESC

Answer : B

References: https://technet.microsoft.com/en-us/library/ms190014(v=sql.105).aspx

Question 5

You are writing a set of queries against a FILESTREAM-enabled database.

You create a stored procedure that will update multiple tables within a transaction.

You need to ensure that if the stored procedure raises a runtime error, the entire transaction is terminated and rolled back.

Which Transact-SQL statement should you include at the beginning of the stored procedure?

ASET TRANSACTION ISOLATION LEVEL SERIALIZABLE

BSET XACT_ABORT OFF

CSET TRANSACTION ISOLATION LEVEL SNAPSHOT

DSET IMPLICIT_TRANSACTIONS ON

ESET XACT_ABORT ON

FSET IMPLICIT TRANSACTIONS OFF

Answer : E

Question 6

You administer a Microsoft SQL Server instance that will support several databases.

You need to ensure that the organization serial numbers can be stored in every new database created. You

also need to ensure that the serial numbers are six alphanumeric characters and have the same structure

across every database.

What should you do?

ACreate a user-defined data type on the master database.

BCreate a user-defined type on the master database.

CCreate a user-defined data type on the model database.

DCreate a user-defined type on the model database.

Answer : D

A user defined data type in SQL Server is only accessible in a single database, but one trick with new

databases is to create the objects in the model database, so as new databases are created the user defined

data types, rules, defaults, etc. will automatically be available.

References:

https://www.mssqltips.com/sqlservertip/1628/sql-server-user-defined-data-types-rules-and-defaults/

Question 7

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

Microsoft 70-461 Querying Microsoft SQL Server 2012/2014 Exam Practice Test