Microsoft 70-457 Online Practice Questions and Exam Preparation

Microsoft 70-457 Online Questions & Answers

• Question 41:

  You administer a Microsoft SQL Server 2012 server. The MSSQLSERVER service uses a domain account named CONTOSO\SQLService.

  You plan to configure Instant File Initialization.

  You need to ensure that Data File Autogrow operations use Instant File Initialization.

  What should you do? Choose all that apply.

  A. Restart the SQL Server Agent Service.
  B. Disable snapshot isolation.
  C. Restart the SQL Server Service.
  D. Add the CONTOSO\SQLService account to the Perform Volume Maintenance Tasks local security policy.
  E. Add the CONTOSO\SQLService account to the Server Operators fixed server role.
  F. Enable snapshot isolation.
  C. Restart the SQL Server Service.
  D. Add the CONTOSO\SQLService account to the Perform Volume Maintenance Tasks local security policy.

• Question 42:

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

  

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

  Which Transact-SQL query should you use?

  A. CREATE TRIGGER TrgPhoneNumberChange ON Customers FOR UPDATE AS IF COLUMNS_UPDATED (HomeNumber, MobileNumber) - - Create Audit Records
  B. CREATE TRIGGER TrgPhoneNumberChange ON Customers FOR UPDATE AS IF EXISTS( SELECT HomeNumber FROM inserted) OR EXISTS (SELECT MobileNumber FROM inserted) - - Create Audit Records
  C. CREATE TRIGGER TrgPhoneNumberChange ON Customers FOR UPDATE AS IF COLUMNS_CHANGED (HomeNumber, MobileNumber) - - Create Audit Records
  D. CREATE TRIGGER TrgPhoneNumberChange ON Customers FOR UPDATE AS IF UPDATE (HomeNumber) OR UPDATE (MobileNumber) - - Create Audit Records
  D. CREATE TRIGGER TrgPhoneNumberChange ON Customers FOR UPDATE AS IF UPDATE (HomeNumber) OR UPDATE (MobileNumber) - - Create Audit Records

• Question 43:

  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
  A. UPDATE Products SET Price = Price * 1.05 OUTPUT inserted.ProductCode, deleted.Price, inserted.Price INTO ProductsPriceLog(ProductCode, OldPrice, NewPrice)

• Question 44:

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

  

  You need to create a query for a report. The query must meet the following requirements:

  *NOT use object delimiters.

  *Return the most recent orders first.

  *Use the first initial of the table as an alias.

  *Return the most recent order date for each customer.

  *Retrieve the last name of the person who placed the order. *Return the order date in a column named MostRecentOrderDate that appears as the last column in the report.

  The solution must support the ANSI SQL-99 standard.

  Which code segment should you use?

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

  A. SELECT c.CustomerID -- optional
  B. LastName, max(o.OrderDate) 'MostRecentOrderDate' FROM Customer c LEFT OUTER JOIN Orders o ON o.CustomerID = c.CustomerID GROUP BY
  C. CustomerID, c.LastName ORDER BY 3 DESC
  D. select C.Lastname, P.MostRecentOrderDate from customers AS C inner join ( select customID, MostRecentOrderDate=max(orderDate) from orders group by customID ) P on C.customerID=P.CustomerID order by P.MostRecentOrderDate desc
  E. SELECT C.LastName, O.OrderDate AS MostRecentOrderDate FROM Customers AS C INNER JOIN Orders AS O ON C.CustomerID = O.CustomerID ORDER BY O.OrderDate DESC
  A. SELECT c.CustomerID -- optional

• Question 45:

  You administer a Microsoft SQL Server 2012 database.

  The database contains a customer 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 customer table. What should you do?

  A. Implement row-level compression.
  B. Implement page-level compression.
  C. Convert all indexes to Column Store indexes.
  D. Implement Unicode compression.
  B. Implement page-level compression.

• Question 46:

  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.
  A. Reference a user-defined function within the computed column.

• Question 47:

  You use Microsoft SQL Server 2012 to develop a database application. You create a stored procedure named dbo.ModifyData that can modify rows.

  You need to ensure that when the transaction fails, dbo.ModifyData meets the following requirements:

  Does not return an error

  Closes all opened transactions

  Which Transact-SQL statement should you use?

  A. BEGIN TRANSACTION BEGIN TRY EXEC dbo.ModifyData COMMIT TRANSACTION END TRY BEGIN CATCH IF @@ TRANCOUNT = 0 ROLLBACK TRANSACTION; END CATCH
  B. BEGIN TRANSACTION BEGIN TRY EXEC dbo.ModifyData COMMIT TRANSACTION END TRY BEGIN CATCH IF @@ERROR != 0 ROLLBACK TRANSACTION; THROW; END CATCH
  C. BEGIN TRANSACTION BEGIN TRY EXEC dbo.ModifyData COMMIT TRANSACTION END TRY BEGIN CATCH IF @@TRANCOUNT = 0 ROLLBACK TRANSACTION; THROW; END CATCH
  D. BEGIN TRANSACTION BEGIN TRY EXEC dbo.ModifyData COMMIT TRANSACTION END TRY BEGIN CATCH IF @@ERROR != 0 ROLLBACK TRANSACTION; END CATCH
  D. BEGIN TRANSACTION BEGIN TRY EXEC dbo.ModifyData COMMIT TRANSACTION END TRY BEGIN CATCH IF @@ERROR != 0 ROLLBACK TRANSACTION; END CATCH

• Question 48:

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

  

  You need to create a query that calculates the total sales of each OrderId from the Sales.Details table.

  The solution must meet the following requirements:

  *Use one-part names to reference columns.

  *Start the order of the results from OrderId.

  *NOT depend on the default schema of a user.

  *Use an alias of TotalSales for the calculated ExtendedAmount. *Display only the OrderId column and the calculated TotalSales column.

  Which code segment should you use?

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

  A. SELECT OrderID, SUM(ExtendedAmount) AS TotalSales FROM Sales.Details GROUP BY OrderID ORDER BY OrderID
  B. SELECT OrderID, SUM(ExtendedAmount) AS TotalSales FROM Sales.Details ORDER BY OrderID
  A. SELECT OrderID, SUM(ExtendedAmount) AS TotalSales FROM Sales.Details GROUP BY OrderID ORDER BY OrderID

• Question 49:

  You administer a Microsoft SQL Server 2012 server that has a database named Contoso. The Contoso database has a table named ProductPrices in a schema named Sales.

  You need to create a script that writes audit events into the application log whenever data in the ProductPrices table is updated.

  Which four Transact-SQL statements should you use? (To answer, move the appropriate statements from the list of statements to the answer area and arrange them in the correct order.)

  Select and Place:

  

  

• Question 50:

  You are a database developer of a Microsoft SQL Server 2012 database. The database contains a table named Customers that has the following definition:

  

  You need to ensure that the CustomerId column in the Orders table contains only values that exist in the CustomerId column of the Customer table.

  Which Transact-SQL statement should you use?

  A. ALTER TABLE Orders ADD CONSTRAINT FX_Orders_CustomerID FOREIGN KEY (CustomerId) REFERENCES Customer (CustomerId)
  B. ALTER TABLE Customer ADD CONSTRAINT FK_Customer_CustomerID FOREIGN KEY {CustomerID) REFERENCES Orders (CustomerId)
  C. ALTER TABLE Orders ADD CONSTRAINT CK_Crders_CustomerID CHECK (CustomerId IN (SELECT CustomerId FROM Customer))
  D. ALTER TABLE Customer ADD OrderId INT NOT NULL; ALTER TABLE Customer ADD CONSTRAINT FK_Customer_OrderID FOREIGN KEY (CrderlD) REFERENCES Orders (CrderlD);
  E. ALTER TABLE Orders ADD CONSTRAINT PK Orders CustomerId PRIMARY KEY (CustomerID)
  A. ALTER TABLE Orders ADD CONSTRAINT FX_Orders_CustomerID FOREIGN KEY (CustomerId) REFERENCES Customer (CustomerId)