70-461 Exam
Leading Microsoft 70-461 - An Overview 31 to 40
Act now and download your Microsoft 70-461 test today! Do not waste time for the worthless Microsoft 70-461 tutorials. Download Up to the minute Microsoft Querying Microsoft SQL Server 2012 exam with real questions and answers and begin to learn Microsoft 70-461 with a classic professional.
2021 Apr 70-461 practice test
Q31. You administer a Microsoft SQL Server 2012 database named ContosoDb. Tables are defined as shown in the exhibit. (Click the Exhibit button.)
You need to display rows from the Orders table for the Customers row having the CustomerId value set to 1 in the following XML format:
<row OrderId="1" OrderDate="2000-01-01T00:00:00" Amount="3400.00" Name="Customer
A" Country="Australia" />
<row OrderId="2" OrderDate="2001-01-01T00:00:00" Amount="4300.00" Name="Customer
A" Country="Australia" />
Which Transact-SQL query should you use?
A. SELECT OrderId, OrderDate, Amount, Name, Country FROM Orders INNER JOIN Customers ON Orders.CustomerId = Customers.CustomerId WHERE Customers.CustomerId = 1 FOR XML RAW
B. SELECT OrderId, OrderDate, Amount, Name, Country FROM Orders INNER JOIN Customers ON Orders.CustomerId = Customers.CustomerId WHERE Customers.CustomerId = 1 FOR XML RAW, ELEMENTS
C. SELECT OrderId, OrderDate, Amount, Name, Country FROM Orders INNER JOIN Customers ON Orders.CustomerId = Customers.CustomerId WHERE Customers.CustomerId = 1 FOR XML AUTO
D. SELECT OrderId, OrderDate, Amount, Name, Country FROM Orders INNER JOIN Customers ON Orders.CustomerId - Customers.CustomerId WHERE Customers.CustomerId= 1 FOR XML AUTO, ELEMENTS
E. SELECT Name, Country, OrderId, OrderDate, Amount FROM Orders INNER JOIN Customers ON Orders.CustomerId= Customers.CustomerId WHERE Customers.CustomerId- 1 FOR XML AUTO
F. SELECT Name, Country, Orderld, OrderDate, Amount FROM Orders INNER JOIN Customers ON Orders.CustomerId= Customers.CustomerId WHERE Customers.CustomerId= 1 FOR XML AUTO, ELEMENTS
G. SELECT Name AS '@Name', Country AS '@Country', OrderId, OrderDate, Amount FROM Orders INNER JOIN Customers ON Orders.CustomerId= Customers.CustomerId WHERE Customers.CustomerId= 1 FOR XML PATH ('Customers')
H. SELECT Name AS 'Customers/Name', Country AS 'Customers/Country', OrderId, OrderDate, Amount FROM Orders INNER JOIN Customers ON Orders.CustomerId= Customers.CustomerId WHERE Customers.CustomerId= 1 FOR XML PATH ('Customers')
Answer: A
Q32. You administer a Microsoft SQL Server 2012 database. The database contains a Product table created by using the following definition:
You need to ensure that the minimum amount of disk space is used to store the data in the Product table.
What should you do?
A. Convert all indexes to Column Store indexes.
B. Implement Unicode Compression.
C. Implement row-level compression.
D. Implement page-level compression.
Answer: D
Q33. DRAG DROP
You write the following SELECT statement to get the last order date for a particular customer.
You need to create the user-defined function to return the last order date for the specified customer.
Which five Transact-SQL statements should you use? (To answer, move the appropriate SQL statements from the list of statements to the answer area and arrange them in the correct order.)
Answer:
Q34. 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?
A. SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
B. SET XACT_ABORT OFF
C. SET TRANSACTION ISOLATION LEVEL SNAPSHOT
D. SET IMPLICIT_TRANSACTIONS ON
E. SET XACT_ABORT ON
F. SET IMPLICIT TRANSACTIONS OFF
Answer: E
Q35. You are developing a database application by using Microsoft SQL Server 2012. You have a query that runs slower than expected.
You need to capture execution plans that will include detailed information on missing indexes recommended by the query optimizer.
What should you do?
A. Add a HASH hint to the query.
B. Add a LOOP hint to the query.
C. Add a FORCESEEK hint to the query.
D. Add an INCLUDE clause to the index.
E. Add a FORCESCAN hint to the Attach query.
F. Add a columnstore index to cover the query.
G. Enable the optimize for ad hoc workloads option.
H. Cover the unique clustered index with a columnstore index.
I. Include a SET FORCEPLAN ON statement before you run the query.
J. Include a SET STATISTICS PROFILE ON statement before you run the query.
K. Include a SET STATISTICS SHOWPLAN_XML ON statement before you run the query.
L. Include a SET TRANSACTION ISOLATION LEVEL REPEATABLE READ statement before you run the query.
M. Include a SET TRANSACTION ISOLATION LEVEL SNAPSHOT statement before you run the query.
N. Include a SET TRANSACTION ISOLATION LEVEL SERIALIZABLE statement before you run the query.
Answer: K
Avant-garde 70-461 free practice test:
Q36. Your application contains a stored procedure for each country. Each stored procedure accepts an employee identification number through the @EmplD parameter.
You need to build a single process for each employee that will execute the appropriate stored procedure based on the country of residence.
Which approach should you use?
A. A SELECT statement that includes CASE
B. Cursor
C. BULK INSERT
D. View
E. A user-defined function
Answer: E
Explanation: SQL Server user-defined functions are routines that accept parameters, perform an action, such as a complex calculation, and return the result of that action as a value. The return value can either be a single scalar value or a result set.
Q37. You use a Microsoft SQL Server 2012 database.
You want to create a table to store Microsoft Word documents.
You need to ensure that the documents must only be accessible via Transact-SQL queries.
Which Transact-SQL statement should you use?
A. CREATE TABLE DocumentStore ( [Id] INT NOT NULL PRIMARY KEY, [Document] VARBINARY(MAX) NULL ) GO
B. CREATE TABLE DocumentStore ( [Id] hierarchyid, [Document] NVARCHAR NOT NULL ) GO
C. CREATE TABLE DocumentStore AS FileTable
D. CREATE TABLE DocumentStore ( [Id] [uniqueidentifier] ROWGUIDCOL NOT NULL UNIQUE, [Document] VARBINARY(MAX) FILESTREAM NULL ) GO
Answer: A
Q38. CORRECT TEXT
You need to create a view named uv_CustomerFullNames. The view must prevent the underlying structure of the customer table from being changed.
Part of the correct T-SQL statement has been provided in the answer area. Provide the complete code.
Answer:
Q39. You use Microsoft SQL Server 2012 to develop a database application.
You create a stored procedure named DeleteJobCandidate.
You need to ensure that if DeleteJobCandidate encounters an error, the execution of the stored procedure reports the error number.
Which Transact-SQL statement should you use?
A. DECLARE @ErrorVar INT;
DECLARE @RowCountVar INT;
EXEC DeleteJobCandidate
SELECT @ErrorVar = @@ERROR, @RowCountVar = @@ROWCOUNT;
IF (@ErrorVar <> 0)
PRINT N'Error = ' + CAST(@@ErrorVar AS NVARCHAR(8)) +
N', Rows Deleted = ' + CAST(@@RowCountVar AS NVARCHAR(8));
GO
B. DECLARE @ErrorVar INT;
DECLARE @RowCountVar INT;
EXEC DeleteJobCandidate
SELECT @ErrorVar = ERROR_STATE(), @RowCountVar = @@ROWCOUNT;
IF (@ErrorVar <> 0)
PRINT N'Error = ' + CAST(ERRORSTATE() AS NVARCHAR(8)) +
N', Rows Deleted = ' + CAST(@@RowCountVar AS NVARCHAR(8));
GO
C. EXEC DeleteJobCandidate
IF (ERROR_STATE() != 0)
PRINT N'Error = ' + CAST(@@ERROR AS NVARCHAR(8)) +
N', Rows Deleted = ' + CAST(@@ROWCOUNT AS NVARCHAR(8));
GO
D. EXEC DeleteJobCandidate
PRINT N'Error = ' + CAST(@@ERROR AS NVARCHAR(8)) +
N', Rows Deleted = ' + CAST(@@ROWCOUNT AS NVARCHAR(8));
GO
Answer: A
Q40. CORRECT TEXT
You have a database that contains the tables as shown in the exhibit. (Click the Exhibit button.)
You have the following query:
You need to recreate the query to meet the following requirements:
Reference columns by using one-part names only.
Sort aggregates by SalesTerritoryID, and then by ProductID.
Order the results in descending order from SalesTerritoryID to ProductID.
The solution must use the existing SELECT clause and FROM clause.
....
Which code segment should you use?
To answer, type the correct code in the answer area.
Answer: