Microsoft 70-461 Free Practice Questions

Q1. 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 CustomerIdvalue set to 1 in the following XML format. 

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') 

View Answer

Q2. CORRECT TEXT 

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

You need to create a query that returns a list of products from Sales.ProductCatalog. The solution must meet the following requirements: 

UnitPrice must be returned in descending order. 

The query must use two-part names to reference the table. 

The query must use the RANK function to calculate the results. 

The query must return the ranking of rows in a column named PriceRank. 

The list must display the columns in the order that they are defined in the table. 

PriceRank must appear last. 

Part of the correct T-SQL statement has been provided in the answer area. Provide the complete code. 

View Answer

Q3. CORRECT TEXT 

You need to create a table named OrderDetails on a new server. OrderDetails must meet the following requirements: 

. Contain a new column named LineltemTotal that stores the product of ListPrice 

and Quantity for each row. 

. The calculation for a line item total must not be run every time the table is queried. 

. The code must NOT use any object delimiters. 

The solution must ensure that LineItemTotal is stored as the last column in the table. 

Part of the correct T-SQL statement has been provided in the answer area. Provide the complete code. 

View Answer

Q4. You administer a Microsoft SQL Server database that supports a shopping application. 

You need to retrieve a list of customers who live in territories that do not have a sales person. 

Which Transact- SQL query or queries should you use? (Each correct answer presents a complete solution. Choose all that apply.) 

A. SELECT CustomerID FROM Customer 

WHERE TerritoryID <> SOME(SELECT TerritoryID FROM Salesperson) 

B. SELECT CustomerID FROM Customer 

WHERE TerritoryID <> ALL(SELECT TerritoryID FROM Salesperson) 

C. SELECT CustomerID FROM Customer 

WHERE TerritoryID <> ANY(SELECT TerritoryID FROM Salesperson) 

D. SELECT CustomerID FROM Customer 

WHERE TerritoryID NOT IN(SELECT TerritoryID FROM Salesperson) 

View Answer

Q5. CORRECT TEXT 

You have a database that contains the tables as shown below: 

You have a stored procedure named Procedure1. Procedure1 retrieves all order ids after a specific date. The rows for Procedure1 are not sorted. Procedure1 has a single parameter named Parameter1. Parameter1 uses the varchar type and is configured to pass the specific date to Procedure1. A database administrator discovers that OrderDate is not being compared correctly to Parameter1 after the data type of the column is changed to datetime. You need to update the SELECT statement to meet the following requirements: 

The code must NOT use aliases. 

The code must NOT use object delimiters. 

The objects called in Procedure1 must be able to be resolved by all users. 

OrderDate must be compared to Parameter1 after the data type of Parameter1 is 

changed to datetime. 

Which SELECT statement should you use? 

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

View Answer

Q6. You are developing a database that will contain price information. 

You need to store the prices that include a fixed precision and a scale of six digits. 

Which data type should you use? 

A. Float 

B. Money 

C. Smallmoney 

D. Decimal 

View Answer

Q7. DRAG DROP 

You use Microsoft SQL Server 2012 to develop a database application. 

You create a table by using the following definition: 

CREATE TABLE Prices ( 

PriceId int IDENTITY(1,1) PRIMARY KEY, 

ActualPrice NUMERIC(16,9), 

PredictedPrice NUMERIC(16,9) 

) 

You need to create a computed column based on a user-defined function named udf_price_index. You also need to ensure that the column supports an index. 

Which three 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.) 

View Answer

Q8. You administer several Microsoft SQL Server 2012 database servers. Merge replication has been configured for an application that is distributed across offices throughout a wide area network (WAN). Many of the tables involved in replication use the XML and varchar (max) data types. Occasionally, merge replication fails due to timeout errors. You need to reduce the occurrence of these timeout errors. What should you do? 

A. Set the Merge agent on the problem subscribers to use the slow link agent profile. 

B. Create a snapshot publication, and reconfigure the problem subscribers to use the snapshot publication. 

C. Change the Merge agent on the problem subscribers to run continuously. 

D. Set the Remote Connection Timeout on the Publisher to 0. 

View Answer

Q9. You develop a Microsoft SQL Server 2012 database that contains a heap named OrdersHistoncal. 

You write the following Transact-SQL query: 

. INSERT INTO OrdersHistorical 

. SELECT * FROM CompletedOrders 

You need to optimize transaction logging and locking for the statement. Which table hint should you use? 

A. HOLDLOCK 

B. ROWLOCK 

C. XLOCK 

D. UPDLOCK 

E. TABLOCK 

View Answer

Q10. DRAG DROP 

You develop a database application for a university. You need to create a view that will be indexed that meets the following requirements: 

. Displays the details of only students from Canada. 

. Allows insertion of details of only students from Canada. 

Which four 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.) 

View Answer

Q11. You develop a Microsoft SQL Server 2012 database. The database is used by two web applications that access a table named Products. 

You want to create an object that will prevent the applications from accessing the table directly while still providing access to the required data. 

You need to ensure that the following requirements are met: 

. Future modifications to the table definition will not affect the applications' ability to access data. . The new object can accommodate data retrieval and data modification. 

You need to achieve this goal by using the minimum amount of changes to the existing applications. 

What should you create for each application? 

A. table partitions 

B. views 

C. table-valued functions 

D. stored procedures 

View Answer

Q12. You develop a database for a travel application. You need to design tables and other database objects. You create a stored procedure. You need to supply the stored procedure with multiple event names and their dates as parameters. What should you do? 

A. Use the CAST function. 

B. Use the DATE data type. 

C. Use the FORMAT function. 

D. Use an appropriate collation. 

E. Use a user-defined table type. 

F. Use the VARBINARY data type. 

G. Use the DATETIME data type. 

H. Use the DATETIME2 data type. 

I. Use the DATETIMEOFFSET data type. 

J. Use the TODATETIMEOFFSET function. 

View Answer

Q13. You administer a Microsoft SQL Server 2012 database. The database contains a table named Employee. Part of the Employee table is shown in the exhibit. (Click the Exhibit button.) 

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. On which column in the Employee table should you a create a unique constraint? 

A. DateHired 

B. DepartmentID 

C. EmployeelD 

D. EmployeeNum 

E. FirstName 

F. JobTitle 

G. LastName 

H. MiddleName 

I. ReportsToID 

View Answer

Q14. You are developing a database that will contain price information. 

You need to store the prices that include a fixed precision and a scale of six digits. 

Which data type should you use? 

A. Real 

B. Small money 

C. Money 

D. Decimal 

View Answer

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

View Answer

Q16. 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 JOIN 

B. UNPIVOT 

C. PIVOT 

D. CROSS APPLY 

View Answer

Q17. Your database contains a table named Customer that has columns named CustomerID 

and Name. 

You want to write a query that retrieves data from the Customer table sorted by Name 

listing 20 rows at a time. 

You need to view rows 41 through 60. 

Which Transact-SQL query should you create? 

A. Option A 

B. Option B 

C. Option C 

D. Option D 

View Answer