Microsoft 70-457 Online Practice Questions and Exam Preparation

Microsoft 70-457 Online Questions & Answers

• Question 111:

  You support a database structure shown in the exhibit. (Click the Exhibit button.)

  

  You need to write a query that displays the following details:

  Total sales made by sales people, year, city, and country Sub totals only at the city level and country level A grand total of the sales amount

  Which Transact-SQL query should you use?

  A. SELECT SalesPerson.Name, Country, City, DatePart(yyyy, SaleDate) AS Year, Sum(Amount) AS Total FROM Sale INNER JOIN SalesPerson ON Sale.SalesPersonID = SalesPerson.SalesPersonID GROUP BY GROUPING SETS((SalesPerson.Name, Country, City, DatePart(yyyy, SaleDate)), (Country, City), (Country), ())
  B. SELECT SalesPerson.Name, Country, City, DatePart(yyyy, SaleDate) AS Year, Sum(Amount) AS Total FROM Sale INNER JOIN SalesPerson ON Sale.SalesPersonID = SalesPerson.SalesPersonID GROUP BY CUBE(SalesPerson.Name, Country, City, DatePart(yyyy, SaleDate))
  C. SELECT SalesPerson.Name, Country, City, DatePart(yyyy, SaleDate) AS Year, Sum(Amount) AS Total FROM Sale INNER JOIN SalesPerson ON Sale.SalesPersonID = SalesPerson.SalesPersonID GROUP BY CUBE(SalesPerson.Name, DatePart(yyyy, SaleDate), City, Country)
  D. SELECT SalesPerson.Name, Country, City, DatePart(yyyy, SaleDate) AS Year, Sum(Amount) AS Total FROM Sale INNER JOIN SalesPerson ON Sale.SalesPersonID = SalesPerson.SalesPersonID GROUP BY ROLLUP(SalesPerson.Name, DatePart(yyyy, SaleDate), City, Country)
  A. SELECT SalesPerson.Name, Country, City, DatePart(yyyy, SaleDate) AS Year, Sum(Amount) AS Total FROM Sale INNER JOIN SalesPerson ON Sale.SalesPersonID = SalesPerson.SalesPersonID GROUP BY GROUPING SETS((SalesPerson.Name, Country, City, DatePart(yyyy, SaleDate)), (Country, City), (Country), ())

• Question 112:

  You use a Microsoft SQL Server 2012 database.

  You need to create an indexed view within the database for a report that displays Customer Name and the total revenue for that customer.

  Which four T-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:

  

  

• Question 113:

  You want to add a new GUID column named BookGUID to a table named dbo.Book that already contains data.

  BookGUID will have a constraint to ensure that it always has a value when new rows are inserted into dbo.Book.

  You need to ensure that the new column is assigned a GUID for existing rows.

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

  Select and Place:

  

  

• Question 114:

  You administer three Microsoft SQL Server 2012 servers named ServerA, ServerB, and ServerC. ServerA is the acting principal and ServerB is the mirror.

  You need to add ServerC as a witness to the existing mirroring session between ServerA and ServerB.

  You need to achieve this goal without delaying synchronization.

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

  Select and Place:

  

  

• Question 115:

  You use Microsoft SQL Server 2012 database to develop a shopping cart application. You need to rotate the unique values of the ProductName field of a table-valued expression into multiple columns in the output.

  Which Transact-SQL operator should you use?

  A. CROSS JOIN
  B. CROSS APPLY
  C. PIVOT
  D. UNPIVOT
  C. PIVOT

• Question 116:

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

  You create a table by using the following definition:

  CREATE TA3LE Prices

  (

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

  Select and Place:

  

  

• Question 117:

  You administer a Microsoft SQL Server 2012 database.

  The database contains a Product 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 Product table.

  What should you do?

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

• Question 118:

  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.

  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);
  A. ALTER TABLE Inventory ADD TotalItems AS ItemsInStore + ItemsInWarehouse

• Question 119:

  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 ensure that users are able to modify data by using the view. What should you do?

  A. Create an AFTER trigger on the view.
  B. Modify the view to use the WITH VIEW_METADATA clause.
  C. Create an INSTEAD OF trigger on the view.
  D. Modify the view to an indexed view.
  C. Create an INSTEAD OF trigger on the view.

• Question 120:

  You are developing a database application by using Microsoft SQL Server 2012. An application that uses a database begins to run slowly. Your investigation shows the root cause is a query against a read-only table that has a clustered

  index.

  The query returns the following six columns:

  One column in its WHERE clause contained in a non-clustered index Four additional columns One COUNT (*) column based on a grouping of the four additional columns

  You need to optimize the statement.

  What should you do?

  A. Add a HASH hint to the query.
  B. Add a LOOP hint to the query.
  C. Add a FORCESEEK hint to the query.
  D. Add an INCLUDE clause to the index.
  E. Add a FORCESCAN hint to the Attach query.
  F. Add a columnstore index to cover the query.
  G. Enable the optimize for ad hoc workloads option.
  H. Cover the unique clustered index with a columnstore index.
  I. Include a SET FORCEPLAN ON statement before you run the query.
  J. Include a SET STATISTICS PROFILE ON statement before you run the query.
  F. Add a columnstore index to cover the query.