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

Question 1

You administer a Microsoft SQL Server database named ContosoDb. The database has the following schema collection:

The database has a table named ReceivedPurchaseOrders that includes an XML column named PurchaseOrder by using the above schema.
You need to set the requiresApproval attribute of the XML documents to false if they contain more than 50 items.
Which Transact-SQL query should you run?

A. Option D

B. Option B

C. Option A

D. Option C

Correct Answer: A

Explanation: Only visible for Actualtests4sure members. You can sign-up / login (it's free).

Question 2

You administer a SQL Server database that tracks sales that are made by sales persons. The database contains a table that is defined by the following Transact-SQL statements:

You have the following requirements:
* accept a datetime value for the query month
* return a list of salespeople IDs who have sales in the query month or before the query month
* compare sales with sales quota for salespeople who have a sales quota
* display year-to-date sales for salespeople that do not have a sales quota How should you complete the stored procedure? To answer, drag the appropriate Transact-SQL segments to the correct locations. Each Transact-SQL segment may be used once, more than once, or not at all. You may need to drag the split bar between panes or scroll to view content.

Correct Answer:

Explanation:
Box 1: IIF ..
The IIF logical function 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 )
Box 2: [LastSzale] < EOMONTH(@queryMonth)
The EOMONTH function returns the last day of the month containing a specified date, with an optional offset.
References:
https://docs.microsoft.com/en-us/sql/t-sql/functions/logical-functions-iif-transact-sql?view=sql-server-2017
https://docs.microsoft.com/en-us/sql/t-sql/functions/eomonth-transact-sql?view=sql-server-2017

Question 3

You administer a Microsoft SQL Server database named ContosoDb. ContosoDb contains a table named Suppliers and an indexed view named VWLocalSuppliers, both of which were created by using the following Transact-SQL statement:

You need to change the data type of the Code column in the Suppliers table to nvarchar(50).
Which four Transact-SQL segments should you use to develop the solution? To answer, move the appropriate Transact-SQL segments from the list of Transact-SQL segments to the answer area and arrange them in the correct order.

Correct Answer:

Question 4

You administer a database that includes a table named Customers that contains more than 750 rows. You create a new column named PartitionNumber of the int type in the table.
You need to assign a PartitionNumber for each record in the Customers table. You also need to ensure that the PartitionNumber satisfies the following conditions:
* Always starts with 1.
* Starts again from 1 after it reaches 100.
Which Transact-SQL statement should you use?

A. CREATE SEQUENCE CustomerSequence AS int
START WITH 0
INCREMENT BY 1
MINVALUE 1
MAXVALUE 100
UPDATE Customers SET PartitionNumber = NEXT VALUE FOR CustomerSequence
DROP SEQUENCE CustomerSequence

B. CREATE SEQUENCE CustomerSequence AS int
START WITH 1
INCREMENT BY 1
MINVALUE 0
MAXVALUE 100
CYCLE
UPTATE Customers SET PartitionNumber = NEXT VALUE FOR CustomerSequence
DROP SEQUENCE CustomerSequence

C. CREATE SEQUENCE CustomerSequence AS int
START WITH 1
INCREMENT BY 1
MINVALUE 1
MAXVALUE 100
UPDATE Customers SET PartitionNumber = NEXT VALUE FOR CustomerSequence + 1 DROP SEQUENCE CustomerSequence

D. CREATE SEQUENCE CustomerSequence AS int
START WITH 1
INCREMENT BY 1
MINVALUE 1
MAXVALUE 100
CYCLE
UPDATE Customers SET PartitionNumber = NEXT VALUE FOR CustomerSequence
DROP SEQUENCE CustomerSequence

Correct Answer: D

Explanation: Only visible for Actualtests4sure members. You can sign-up / login (it's free).

Question 5

Note: This question is part of 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 the series. Information and details provided in a question apply only to that question.
You administer a Microsoft SQL Server database.
The database contains a table named Employee. Part of the Employee table is shown in the exhibit. (Click the Exhibit button.)

Unless stated above, no columns in the Employee table reference other tables.
Confidential information about the employees is stored in a separate table named EmployeeData. One record exists within EmployeeData for each record in the Employee table.
You need to assign the appropriate constraints and table properties to ensure data integrity and visibility.
Which column in the Employee table should be referenced in a one-to-one relationship by the EmployeeData table?

A. LastName

B. EmployeeID

C. MiddleName

D. DateHired

E. DepartmentID

F. JobTitle

G. ReportsToID

H. EmployeeNum

I. FirstName

Correct Answer: B

Question 6

You are designing a Microsoft SQL Server database named Orders.
You create a table by running the following Transact-SQL statement:

You create a stored procedure to be used by an ASP.NET application that runs the following statement:

You need to ensure that the query runs as efficiently as possible.
Which Transact-SQL statement should you run?

A. Option D

B. Option B

C. Option A

D. Option C

Correct Answer: B

Explanation: Only visible for Actualtests4sure members. You can sign-up / login (it's free).

Question 7

You maintain a SQL Server database that is used by a retailer to track sales and salespeople. The database includes two tables and two triggers that is defined by the following Transact-SQL statements:

During days with a large sales volume, some new sales transaction fail and report the following error: Arithmetic overflow error converting expression to data type int.
You need to ensure that the two triggers are applied once per sale, and that they do not interfere with each other.
How should you complete the relevant Transact-SQL statement? To answer, drag the appropriate Transact-SQL segments to the correct location or locations. Each Transact-SQL segment may be used once, more than once, or not at all. You may need to drag the split bar between panes or scroll to view content.

Correct Answer:

Explanation:
Box 1: OFF
Box 2: 0
Only direct recursion of AFTER triggers is prevented when the RECURSIVE_TRIGGERS database option is set to OFF. To disable indirect recursion of AFTER triggers, also set the nested triggers server option to 0.
Note: Both DML and DDL triggers are nested whena trigger performs an action that initiates another trigger. These actions can initiate other triggers, and so on. DML and DDL triggers can be nested up to 32 levels. You can control whether AFTER triggers can be nested through the nested triggers server configuration option.
If nested triggers are allowed and a trigger in the chain starts an infinite loop, the nesting level is exceeded and the trigger terminates.
References:https://msdn.microsoft.com/en-us/library/ms190739.aspx

Question 8

Your database contains a table named Purchases. The table includes a DATETIME column named PurchaseTime that stores the date and time each purchase is made. There is a non-clustered index on the PurchaseTime column.
The business team wants a report that displays the total number of purchases made on the current day.
You need to write a query that will return the correct results in the most efficient manner.
Which Transact-SQL query should you use?

A. SELECT COUNT(*)
FROM Purchases
WHERE PurchaseTime = CONVERT(DATE, GETDATE())

B. SELECT COUNT(*)
FROM Purchases
WHERE PurchaseTime = GETDATE()

C. SELECT COUNT(*)
FROM Purchases
WHERE CONVERT(VARCHAR, PurchaseTime, 112) = CONVERT(VARCHAR, GETDATE(), 112)

D. SELECT COUNT(*)
FROM Purchases
WHERE PurchaseTime >= CONVERT(DATE, GETDATE())
AND PurchaseTime < DATEADD(DAY, 1, CONVERT(DATE, GETDATE()))

Correct Answer: D

Explanation: Only visible for Actualtests4sure members. You can sign-up / login (it's free).

Question 9

You use Microsoft SQL Server 2012 database to develop a shopping cart application.
You need to invoke a table-valued function for each row returned by a query.
Which Transact-SQL operator should you use?

A. CROSS APPLY

B. CROSS JOIN

C. PIVOT

D. UNPIVOT

Correct Answer: A

Explanation: Only visible for Actualtests4sure members. You can sign-up / login (it's free).

Question 10

You use a Microsoft Azure SQL Database instance. The instance contains a table named Products that has columns named Code, Name, and IsPerishable.
You need to create a view named VwPerishableProducts that:
* returns rows from Products that have a value of True in the IsPerishable column;
* does not allow columns to be altered or dropped in the underlying table.
Which Transact-SQL statement should you run?

A. Option

B. Option

C. Option

D. Option

Correct Answer: C

Explanation: Only visible for Actualtests4sure members. You can sign-up / login (it's free).

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

Latest Update

Useful Links

Contact Us