Apr 2016 updated: 70-457 exams

Exam Code: 70-457 (Practice Exam Latest Test Questions VCE PDF)
Exam Name: Transition Your MCTS on SQL Server 2008 to MCSA: SQL Server 2012, Part 1
Certification Provider: Microsoft
Free Today! Guaranteed Training- Pass 70-457 Exam.

2016 Apr 70-457 Study Guide Questions:

Q17. You have a database 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. 

Which code segment should you use? 

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

A. SELECT ProductCatalog.CatID, ProductCatalog.CatName, ProductCatalog.ProductID, ProductCatalog.ProdName, ProductCatalog.UnitPrice, 

RANK() OVER (ORDER BY ProductCatalog.UnitPrice DESC) AS PriceRank FROM Sales.ProductCatalog ORDER BY ProductCatalog.UnitPrice DESC 

B. SELECT ProductCatalog.CatID, ProductCatalog.CatName, ProductCatalog.ProductID, 

ProductCatalog.ProdName, ProductCatalog.UnitPrice, 

RANK() OVER (PARTITION BY ProductCatalog.UnitPrice ORDER BY ProductCatalog. 

UnitPrice DESC) AS PriceRank 

FROM Sales.ProductCatalog 

ORDER BY ProductCatalog.UnitPrice DESC 

Answer: A 


Q18. You are a database developer at an independent software vendor. You create stored procedures that contain proprietary code. You need to protect the code from being viewed by your customers. 

Which stored procedure option should you use? 

A. ENCRYPTBYKEY 

B. ENCRYPTION 

C. ENCRYPTBYPASSPHRASE 

D. ENCRYPTBYCERT 

Answer: B 

Reference: http://technet.microsoft.com/en-us/library/bb510663.aspx http://technet.microsoft.com/en-us/library/ms174361.aspx http://msdn.microsoft.com/en-us/library/ms187926.aspx http://technet.microsoft.com/en-us/library/ms190357.aspx http://technet.microsoft.com/en-us/library/ms188061.aspx 


Q19. 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? 

A. UPDATE Products SET Price = Price * 1.05 

OUTPUT inserted.ProductCode, deleted.Price, inserted.Price 

INTO ProductsPriceLog(ProductCode, OldPrice, NewPrice) 

B. UPDATE Products SET Price = Price * 1.05 

OUTPUT inserted.ProductCode, inserted.Price, deleted.Price 

INTO ProductsPriceLog(ProductCode, OldPrice, NewPrice) 

C. UPDATE Products SET Price = Price * 1.05 

OUTPUT inserted.ProductCode, deleted.Price, inserted.Price * 

INTO ProductsPriceLog(ProductCode, OldPrice, NewPrice) 

D. UPDATE Products SET Price = Price * 1.05 

INSERT INTO ProductsPriceLog (ProductCode, CldPnce, NewPrice; 

SELECT ProductCode, Price, Price * 1.05 FROM Products 

Answer: A 

Explanation: 

Http://msdn.microsoft.com/en-us/library/ms177564.aspx 


Q20. You administer a Microsoft SQL Server 2012 server that hosts a transactional database and a reporting database. The transactional database is updated through a web application and is operational throughout the day. The reporting database is only updated from the transactional database. 

The recovery model and backup schedule are configured as shown in the following table: 

At 16:20 hours, you discover that pages 17, 137, and 205 on one of the database files are corrupted on the transactional database. You need to ensure that the transactional database is restored. 

You also need to ensure that data loss is minimal. 

What should you do? 

A. Perform a partial restore. 

B. Restore the latest full backup, and restore the latest differential backup. Then, restore each log backup taken before the time of failure from the most recent differential backup. 

C. Perform a point-in-time restore. 

D. Restore the latest full backup. 

E. Restore the latest full backup, and restore the latest differential backup. Then, restore the latest log backup. 

F. Perform a page restore. 

G. Restore the latest full backup. Then, restore each differential backup taken before the time of failure from the most recent full backup. 

H. Restore the latest full backup. Then, restore the latest differential backup. 

Answer: F 


Q21. You administer a Microsoft SQL Server 2012 database named Contoso on a server named Server01. 

You need to prevent users from disabling server audits in Server01. 

What should you create? 

A. an Alert 

B. a Resource Pool 

C. an Extended Event session 

D. a Database Audit Specification 

E. a SQL Profiler Trace 

F. a Server Audit Specification 

G. a Policy 

Answer: G 


70-457 test engine

Down to date 70-457 prerequisites:

Q22. You develop a Microsoft SQL Server 2012 database. 

You need to create a batch process that meets the following requirements: 

. Status information must be logged to a status table. 

. If the status table does not exist at the beginning of the batch, it must be created. 

Which object should you use? 

A. Scalar user-defined function 

B. Inline user-defined function 

C. Table-valued user-defined function 

D. Stored procedure 

Answer: D 

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


Q23. You administer all the deployments of Microsoft SQL Server 2012 in your company. 

You need to ensure that an OLTP database that includes up-to-the-minute reporting requirements can be off-loaded from the primary database to another server. You also need to be able to add indexes to the secondary database. 

Which configuration should you use? 

A. Two servers configured in different data centers SQL Server Availability Group configured in Synchronous-Commit Availability Mode One server configured as an Active Secondary 

B. Two servers configured in the same data center SQL Server Availability Group configured in Asynchronous-Commit Availability Mode One server configured as an Active Secondary 

C. Two servers configured in the same data center A primary server configured to perform log-shipping every 10 minutes • A backup server configured as a warm standby 

D. Two servers configured in different data centers SQL Server Availability Group configured in Asynchronous-Commit Availability Mode 

E. Two servers configured on the same subnet SQL Server Availability Group configured in Synchronous-Commit Availability Mode 

F. SQL Server that includes an application database configured to perform transactional replication 

G. SQL Server that includes an application database configured to perform snapshot replication 

H. Two servers configured in a Windows Failover Cluster in the same data center SQL Server configured as a clustered instance 

Answer: F 

Reference: 

http://technet.microsoft.com/en-us/library/ms151738.aspx 


Q24. DRAG DROP 

You administer a Microsoft SQL Server 2012 clustered instance that has two nodes named Node 1 and Node 2. 

Node 1 fails and the cluster fails over to Node 2. You need to replace Node 1 and add it to the cluster. 

Which four actions should you perform in sequence? (To answer, move the appropriate actions from the list of actions to the answer area and arrange them in the correct order.) 

Answer: 


Q25. 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.) 

Answer: 


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

Which Transact-SQL query should you use? 

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: E 


70-457 answers

Real 70-457 pdf download:

Q27. You develop a Microsoft SQL Server 2012 database. You create a view from the Orders and OrderDetails tables by using the following definition. 

You need to improve the performance of the view by persisting data to disk. 

What should you do? 

A. Create an INSTEAD OF trigger on the view. 

B. Create an AFTER trigger on the view. 

C. Modify the view to use the WITH VIEW_METADATA clause. 

D. Create a clustered index on the view. 

Answer: D 

Explanation: 

Http://msdn.microsoft.com/en-us/library/ms188783.aspx 


Q28. 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 all files except Sales_2.ndf are corrupt. 

You need to recover the corrupted data in the minimum amount of time. 

What should you do? 

A. Perform a restore from a full backup. 

B. Perform a transaction log restore. 

C. Perform a file restore. 

D. Perform a filegroup restore. 

Answer: A 


Q29. You administer a Microsoft SQL Server 2012 database. The database has a table named Customers owned by UserA and another table named Orders owned by UserB. You also have a stored procedure named GetCustomerOrderInfo owned by UserB. GetCustomerOrderInfo selects data from both tables. 

You create a new user named UserC. 

You need to ensure that UserC can call the GetCustomerOrderInfo stored procedure. 

You also need to assign only the minimum required permissions to UserC. 

Which permission or permissions should you assign to UserC? Choose all that apply. 

A. The Select permission on Customers 

B. The Execute permission on GetCustomerOrderInfo 

C. The Take Ownership permission on Customers 

D. The Control permission on GetCustomerOrderInfo 

E. The Take Ownership permission on Orders 

F. The Select permission on Orders 

Answer: A,B 

Reference: 

The question seems to be missing something. Or the original answer is incorrect. I've changed it to what I believe to be the correct answer. The original answer included "The Select permission on Orders.", but due to ownership chaining, you would only need to give Execute permissions to UserC to access the Orders table since UserB is the owner. 

Reference: 

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

http://stackoverflow.com/questions/2212044/sql-server-how-to-permission-schemas 

http://sqlservercentral.com/blogs/steve_jones/2012/03/14/ownership-chains-in-sql-server 


Q30. You have a Microsoft SQL Server 2012 database that contains tables named Customers and Orders. 

The tables are related by a column named CustomerID. 

You need to create a query that meets the following requirements: 

. Returns the CustomerName for all customers and the OrderDate for any orders that they have placed. . Results must include customers who have not placed any orders. 

Which Transact-SQL query should you use? 

A. SELECT CustomerName, OrderDate FROM Customers RIGHT OUTER JOIN Orders ON Customers.CustomerID = Orders.CustomerID 

B. SELECT CustomerName, CrderDate FROM Customers JOIN Orders ON Customers.CustomerID = Orders.CustomerID 

C. SELECT CustomerName, OrderDate FROM Customers CROSS JOIN Orders ON Customers.CustomerID = Orders.CustomerID 

D. SELECT CustomerName, CrderDate FROM Customers LEFT OUTER JOIN Orders ON Customers.CustomerID = Orders.CustomerID 

Answer: D 

Reference: 

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


Q31. You administer a Microsoft SQL Server 2012 server that has SQL Server Integration Services (SSIS) installed. 

You plan to deploy new SSIS packages to the server. The SSIS packages use the Project Deployment Model together with parameters and Integration Services environment variables. 

You need to configure the SQL Server environment to support these packages. 

What should you do? 

A. Create SSIS configuration files for the packages. 

B. Create an Integration Services catalog. 

C. Install Data Quality Services. 

D. Install Master Data services. 

Answer: B 

Reference: 

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


Q32. You administer a Microsoft SQL Server 2012 server that hosts a transactional database and a reporting database. The transactional database is updated through a web application and is operational throughout the day. The reporting database is only updated from the transactional database. 

The recovery model and backup schedule are configured as shown in the following table: 

One of the hard disk drives that stores the reporting database fails at 16:40 hours. 

You need to ensure that the reporting database is restored. You also need to ensure that data loss is minimal. 

What should you do? 

A. Restore the latest full backup. Then, restore each differential backup taken before the time of failure from the most recent full backup. 

B. Perform a partial restore. 

C. Restore the latest full backup, and restore the latest differential backup. Then, restore the latest log backup. 

D. Restore the latest full backup. 

E. Perform a page restore. 

F. Restore the latest full backup, and restore the latest differential backup. Then, restore each log backup taken before the time of failure from the most recent differential backup. 

G. Restore the latest full backup. Then, restore the latest differential backup. 

H. Perform a point-in-time restore. 

Answer: G 



see more 70-457 - Transition Your MCTS on SQL Server 2008 to MCSA: SQL Server 2012, Part 1

Microsoft 70-457 Certification Sample Questions and Answers: http://www.braindumpsall.net/70-457-dumps/

P.S. New 70-457 dumps PDF: http://www.4easydumps.com/70-457-dumps-download.html