Microsoft 70-457 Online Practice Questions and Exam Preparation

Microsoft 70-457 Online Questions & Answers

• Question 81:

  You develop a Microsoft SQL Server 2012 database that contains a table named Products. The Products table has the following definition:

  

  You need to create an audit record only when either the RetailPrice or WholeSalePrice column is updated.

  Which Transact-SQL query should you use?

  A. CREATE TRIGGER TrgPriceChange ON Products FOR UPDATE AS IF CCLUMNS_CHANGED (RetailPrice, WholesalePrice) - - Create Audit Records
  B. CREATE TRIGGER TrgPriceChange ON Products FOR UPDATE AS IF EXISTS(SELECT RetailPrice from inserted) OR EXISTS (SELECT WholeSalePnce FROM inserted) - - Create Audit Records
  C. CREATE TRIGGER TrgPriceChange ON Products FOR UPDATE AS IF COLUMNS_UPDATED (RetailPrice, WholesalePrice) - - Create Audit Records
  D. CREATE TRIGGER TrgPriceChange ON Products FOR UPDATE AS IF UPDATE(RetailPrice) OR UPDATE(WholeSalePrice) - - Create Audit Records
  B. CREATE TRIGGER TrgPriceChange ON Products FOR UPDATE AS IF EXISTS(SELECT RetailPrice from inserted) OR EXISTS (SELECT WholeSalePnce FROM inserted) - - Create Audit Records

• Question 82:

  You develop a Microsoft SQL Server 2012 database.

  You need to create and call a stored procedure that meets the following requirements:

  Accepts a single input parameter for CustomerID.

  Returns a single integer to the calling application.

  Which Transact-SQL statement or statements should you use? (Each correct answer presents part of the solution. Choose all that apply.)

  A. Option A
  B. Option B
  C. Option C
  D. Option D
  E. Option E
  F. Option F
  C. Option C
  F. Option F

• Question 83:

  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 following requirements are

  met:

  Students must be ranked based on their average marks.

  If one or more students have the same average, the same rank must be given to these students. Consecutive ranks must be skipped when the same rank is assigned.

  Which Transact-SQL query should you use?

  A. SELECT StudentCode as Code, RANK() OVER(ORDER BY AVG (Marks) DESC) AS Value FROM StudentMarks GROUP BY StudentCode
  B. SELECT Id, Name, Marks, DENSE_RANK() OVER(ORDER BY Marks DESC) AS Rank FROM StudentMarks
  C. SELECT StudentCode as Code, DENSE_RANK() OVER(ORDER BY AVG (Marks) DESC) AS Value FROM StudentMarks GROUP BY StudentCode
  D. SELECT StudentCode as Code, NTILE(2) OVER(ORDER BY AVG (Marks) DESC) AS Value FROM StudentMarks 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 StudentMarks) tmp WHERE Rank = 1
  F. SELECT StudentCode AS Code,Marks AS Value FROM ( SELECT StudentCode, Marks AS Marks, RANK() OVER(PARTITION BY SubjectCode ORDER BY Marks DESC) AS Rank FROM StudentMarks) 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 StudentMarks) tmp WHERE Rank = 1
  A. SELECT StudentCode as Code, RANK() OVER(ORDER BY AVG (Marks) DESC) AS Value FROM StudentMarks GROUP BY StudentCode

• Question 84:

  You administer a Microsoft SQL Server 2012 database.

  You configure Transparent Data Encryption (TDE) on the Orders database by using the following statements:

  

  You attempt to restore the Orders database and the restore fails. You copy the encryption file to the original location.

  A hardware failure occurs and so a new server must be installed and configured. After installing SQL Server to the new server, you restore the Orders database and copy the encryption files to their original location. However, you are unable

  to access the database.

  You need to be able to restore the database.

  Which Transact-SQL statement should you use before attempting the restore?

  

  A. B.
  C. D.
  C. D.

• Question 85:

  You administer a Microsoft SQL Server 2012 database that includes a table named Products. The Products table has columns named Productld, ProductName, and CreatedDateTime. The table contains a unique constraint on the

  combination of ProductName and CreatedDateTime. You need to modify the Products table to meet the following requirements:

  Remove all duplicates of the Products table based on the ProductName column.

  Retain only the newest Products row.

  Which Transact-SQL query should you use?

  A. WITH CTEDupRecords AS ( SELECT MAX(CreatedDateTime) AS CreatedDateTime, ProductName FROM Products GROUP BY ProductName HAVING COUNT(*) > 1 ) DELETE p FROM Products p JOIN CTEDupRecords cte ON
  B. ProductName = cte.ProductName AND p.CreatedDateTime > cte.CreatedDateTime
  C. WITH CTEDupRecords AS ( SELECT MAX(CreatedDateTime) AS CreatedDateTime, ProductName FROM Products GROUP BY ProductName HAVING COUNT(*) > 1 ) DELETE p FROM Products p JOIN CTEDupRecords cte ON cte.ProductName = p.ProductName AND cte.CreatedDateTime > p.CreatedDateTime
  D. WITH CTEDupRecords AS ( SELECT MIN(CreatedDateTime) AS CreatedDateTime, ProductName FROM Products GROUP BY ProductName ) DELETE p FROM Products p JOIN CTEDupRecords cte ON
  E. ProductName = cte.ProductName
  F. WITH CTEDupRecords AS ( SELECT MAX(CreatedDateTime) AS CreatedDateTime, ProductName FROM Products GROUP BY ProductName HAVING COUNT(*) > 1 ) DELETE p FROM Products p JOIN CTEDupRecords cte ON
  G. ProductName = cte.ProductName
  B. ProductName = cte.ProductName AND p.CreatedDateTime > cte.CreatedDateTime

• Question 86:

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

  

  You have an application named Appl. You have a parameter named @Count that uses the int data type.

  App1 is configured to pass @Count to a stored procedure.

  You need to create a stored procedure named usp_Customers for App1. Usp_Customers must meet the

  following requirements:

  *NOT use object delimiters.

  *Minimize sorting and counting.

  *Return only the last name of each customer in alphabetical order. *Return only the number of rows

  specified by the @Count parameter.

  The solution must NOT use BEGIN and END statements.

  Which code segment should you use? To answer, type the correct code in the answer area.

  Check the answer in explanation.

  CREATE PROCEDURE usp_Customers @Count int AS SELECT TOP(@Count) Customers.LastName FROM Customers ORDER BY Customers.LastName

• Question 87:

  You administer a Microsoft SQL Server 2012 instance.

  You need to stop a blocking process that has an SPID of 64 without stopping other processes What should you do?

  A. Execute the following Transact-SQL statement: EXECUTE sp_KillSPID 64
  B. Restart the SQL Server service.
  C. Execute the following Transact-SQL statement: KILL 64
  D. Execute the following Transact-SQL statement: ALTER SESSION KILL '64'
  C. Execute the following Transact-SQL statement: KILL 64

• Question 88:

  Your database contains a table named SalesOrders. The table includes a DATETIME column named OrderTime that stores the date and time each order is placed. There is a non-clustered index on the OrderTime column.

  The business team wants a report that displays the total number of orders placed 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 SalesOrders WHERE OrderTime = CONVERT(DATE, GETDATE())
  B. SELECT COUNT(*) FROM SalesOrders WHERE OrderTime = GETDATE()
  C. SELECT COUNT(*) FROM SalesOrders WHERE CONVERT(VARCHAR, OrderTime, 112) = CONVERT(VARCHAR, GETDATE(I, 112))
  D. SELECT COUNT(*) FROM SalesOrders WHERE OrderTime >= CONVERT(DATE, GETDATE()) AND OrderTime < DATEADD(DAY, CONVERT(DATE, GETDATE()))
  D. SELECT COUNT(*) FROM SalesOrders WHERE OrderTime >= CONVERT(DATE, GETDATE()) AND OrderTime < DATEADD(DAY, CONVERT(DATE, GETDATE()))

• Question 89:

  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?

  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')
  A. SELECT OrderId, OrderDate, Amount, Name, Country FROM Orders INNER JOIN Customers ON Orders.CustomerId = Customers.CustomerId WHERE Customers.CustomerId = 1 FOR XML RAW

• Question 90:

  You create a view based on the following statement:

  

  You grant the Select permission to User1 for this view. You need to change the view so that it displays only the records that were processed in the month prior to the current month. You need to ensure that after the changes, the view functions correctly for User1.

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

  Select and Place: