70-457 | You create a stored procedure that will update multiple

Question: 32

You create a stored procedure that will update multiple tables within a transaction. You need to ensure that if the stored procedure raises a run-time 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 XACT_A30RT ON
B. SET ARITHABORT ON
C. TRY
D. BEGIN
E. SET ARITHABORT OFF
F. SET XACT ABORT OFF

Answer: E

70-457|Which isolation level should you use?

Question: 31

You use Microsoft SQL Server 2012 to write code for a transaction that contains several statements. There is high contention between readers and writers on several tables used by your transaction. You need to minimize the use of the tempdb space. You also need to prevent reading queries from blocking writing queries. Which isolation level should you use?

A. SERIALIZABLE
B. SNAPSHOT
C. READ COMMITTED SNAPSHOT
D. REPEATABLE READ

Answer: C

70-457 | You use Microsoft SQL Server 2012 to develop a database application.

Question : 29

You use Microsoft SQL Server 2012 to develop a database application.You need to implement a computed column that references a lookup table by using an INNER JOIN against another table.What should you do?

A. Reference a user-defined function within the computed column.
B. Create a BEFORE trigger that maintains the state of the computed column.
C. Add a default constraint to the computed column that implements hard-coded values.
D. Add a default constraint to the computed column that implements hard-coded CASE statements.

Answer : A

70-457 Sample Question : 18

Question : 18

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

Answer: D

70-457 Sample Question : 17

Question : 17

You have an XML schema collection named Sales.InvoiceSchema.You need to declare a variable of the XML type named XML1. The solution must ensure that XML1 is validated by using Sales.InvoiceSchema.Which code segment should you use?To answer, type the correct code in the answer area.

A. Declare @XML1=XML(Sales.InvoiceSchema)
B. DECLARE @XML1 XML @XML1 = Sales.InvoiceSchema CREATE XML SCHEMA COLLECTION XML1 AS @XML1

Answer : A

70-457 Sample Question : 16

Question : 16

You develop a Microsoft SQL Server 2012 database.You create a view that performs the following tasks:The view is frequently used in several reports. You need to improve the performance of the reports.What should you do?

A. Convert the view into a table-valued function.
B. Convert the view into a Common Table Expression (CTE).
C. Convert the view into an indexed view.
D. Convert the view into a stored procedure and retrieve the result from the stored procedure into a temporary table.

Answer : C

70-457 Sample Question : 15

Question : 15

You develop a Microsoft SQL Server 2012 server database that supports an application.The application contains a table that has the following definition:
CREATE TABLE Inventory (ItemID int NOT NULL PRIMARY KEY,ItemsInStore int NOT NULL,ItemsInWarehouse int NOT NULL)You need to create a computed column that returns the sum total of the ItemsInStore and ItemsInWarehouse values for each row.The new column is expected to be queried heavily, and you need to be able to index the column.Which Transact-SQL statement should you use?

A. ALTER TABLE Inventory ADD TotalItems AS ItemslnStore + ItemsInWarehouse
B. ALTER TABLE Inventory ADD TotalItems AS ItemsInStore + ItemsInWarehouse PERSISTED
C. ALTER TABLE Inventory ADD TotalItems AS SUM(ItemsInStore, ItemsInWarehouse) PERSISTED
D. ALTER TABLE Inventory ADD TotalItems AS SUM(ItemsInStore, ItemsInWarehouse)

Answer : B

70-457 Sample Question : 14

Question : 14

You create a table that has the StudentCode, SubjectCode, and Marks columns to record mid-year marks for students. The table has marks obtained by 50 students for various subjects.You need to ensure that the top half of the students arranged by their average marks must be given a rank of 1 and the remaining students must be given a rank of 2.Which Transact-SQL query should you use?

A. SELECT StudentCode as Code,RANK ( ) OVER (ORDER BY AVG (Marks) DESC) AS Value FROM Student Marks GROUP BY StudentCode
B. SELECT Id, Name, Marks, DENSE_RANK () OVER (ORDER BY Marks DESC) AS Rank FROM Student Marks
C. SELECT StudentCode as Code,DENSE_RANK () OVER (ORDER BY AVG (Marks) DESC) AS Value FROM Student Marks GROUP BY StudentCode
D. SELECT StudentCode as Code,NTILE (2) OVER (ORDER BY AVG (Marks) DESC) AS Value FROM Student Marks GROUP BY StudentCode
E. SELECT StudentCode AS Code,Marks AS Value FROM (SELECT StudentCode, Marks AS Marks,RANK () OVER (PARTITION BY SubjectCode ORDER BY Marks ASC) AS Rank FROM Student Marks)tmp WHERE Rank = 1
F. SELECT StudentCode AS Code,Marks AS Value FRCM (SELECT StudentCode, Marks AS Marks,RANK() OVER (PARTITION BY SubjectCode ORDER 3Y Marks DESC) AS Rank FRCM Student Marks)tmp WHERE Rank = 1
G. SELECT StudentCode AS Code,Marks AS Value FROM(SELECT StudentCode, Marks AS Marks,RANK () OVER (PARTITION BY StudentCode ORDER BY Marks ASC) AS Rank FROM
StudentMarks)tmp WHERE Rank = 1
H. SELECT StudentCode AS Code,Marks AS Value FROM(SELECT StudentCode, Marks AS Marks,RANXO OVER (PARTITION BY StudentCode ORDER BY Marks DESC) AS Rank FROM
Student Marks)tmp WHERE Rank = 1

Answer : D

70-457 Sample Question : 13

Question : 13

You administer a Microsoft SQL Server 2012 database named ContosoDb. Tables are defined as shown in the exhibit. (Click the Exhibit button.)

Untitled

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 = 1FOR 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

70-457 Sample Question : 12

Question : 12

You develop a Microsoft SQL Server 2012 server database that supports an application.The application contains a table that has the following definition:CREATE TABLE Inventory (ItemID int NOT NULL PRIMARY KEY,ItemsInStore int NOT NULL,ItemsInWarehouse int NOT NULL)You need to create a computed column that returns the sum total of the ItemsInStore andItemsInWarehouse values for each row.Which Transact-SQL statement should you use?

A. ALTER TABLE Inventory ADD TotalItems AS ItemsInStore + ItemsInWarehouse
B. ALTER TABLE Inventory ADD ItemsInStore – ItemsInWarehouse = TotalItems
C. ALTER TABLE Inventory ADD TotalItems = ItemsInStore + ItemsInWarehouse
D. ALTER TABLE Inventory ADD TotalItems AS SUM(ItemsInStore, ItemslnWarehouse);

Answer : A

Explanation:

Http://technet.microsoft.com/en-us/library/ms190273.aspx

70-457 Sample Question : 11

Question : 11

You develop a database for a travel application.You need to design tables and other database objects.You create a view that displays the dates and times of the airline schedules on a report.You need to display dates and times in several international formats.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.

Answer: C

70-457 Sample Question : 10

Question : 10

You administer a Microsoft SQL Server database that supports a banking transaction management application.You need to retrieve a list of account holders who live in cities that do not have a branch location. Which Transact-SQL query or queries should you use? (Each correct answer presents a complete solution.Choose all that apply.)

A. SELECT AccountHolderID FROM AccouritHolder WHERE CityID NOT IN (SELECT CityID FROM BranchMaster)
B. SELECT AccountHolderID FROM AccountHolder WHERE CityID <> ALL (SELECT CityID FROM BranchMaster)
C. SELECT AccountHolderlD FROM AccouncHolder WHERE CityID <> SOME (SELECT CityID FROM BranchMaster]
D. SELECT AccountHolderID FROM AccountHolder WHERE CityID <> ANY (SELECT CityID FROM BranchMaster)

Answer : CD

70-457 Sample Question : 9

 Question : 9

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

Answer: B

70-457 Sample Question : 8

Question : 8

You use Microsoft SQL Server 2012 to develop a database application. You need to implement a computed column that references a lookup table by using an INNER JOIN against another table. What should you do?

A. Reference a user-defined function within the computed column.
B. Create a BEFORE trigger that maintains the state of the computed column.
C. Add a default constraint to the computed column that implements hard-coded values.
D. Add a default constraint to the computed column that implements hard-coded CASE statements.

Answer: A

Search Words: 70-457 Course real exam questions answers practice test braindumps, free Q&A online dumps download and free 70-457 Course discount coupon code available.

Leave a Comment.

ten + 6 =