70-457 Guide

70-457 Exam Royal Pack (In Stock.)

 
  • Microsoft
  • Exam Number/Code 70-457
  • Product Name Transition Your MCTS on SQL Server 2008 to MCSA: SQL Server 2012, Part 1
  • Questions and Answers
  • 184 Q&As
  • Last Updated
  • March 14,2017
  • List Price
  • $128.99
  • Price
  • Today 59.99 USD

Free TrialVersion: demo Buy Now 50% OFF

Today Big Q: 70-457 prerequisites?

Acquiring Microsoft Microsoft certification can be a hard process by oneself. The Microsoft 70-457 exam true exam will possess some adjustments, and the Microsoft 70-457 practice questions and answers may make some modifications accordingly. Your Microsoft 70-457 test is actually an important section of Microsoft Microsoft certification exam. Exambible offers prosperous resources to suit your needs to make the Microsoft 70-457 exam. Your Microsoft 70-457 certificate is a threshold to the IT area.

2017 Jan pass 70-457:

Q101. You administer a Microsoft SQL Server 2012 instance that contains a financial database hosted on a storage area network (SAN). 

The financial database has the following characteristics: 

A data file of 2 terabytes is located on a dedicated LUN (drive D). 

A transaction log of 10 GB is located on a dedicated LUN (drive E). 

Drive D has 1 terabyte of free disk space. 

Drive E has 5 GB of free disk space. 

The database is continually modified by users during business hours from Monday through Friday between 09:00 hours and 17:00 hours. Five percent of the existing data is modified each day. 

The Finance department loads large CSV files into a number of tables each business day at 11:15 hours and 15:15 hours by using the BCP or BULK INSERT commands. Each data load adds 3 GB of data to the database. 

These data load operations must occur in the minimum amount of time. 

A full database backup is performed every Sunday at 10:00 hours. Backup operations will be performed every two hours (11:00, 13:00, 15:00, and 17:00) during business hours. You need to ensure that the minimum amount of data is lost. 

Which recovery model should the database use? 

A. FULL 

B. DBO_ONLY 

C. CONTINUE_AFTER_ERROR 

D. CHECKSUM 

E. NO_CHECKSUM 

F. SIMPLE 

G. Transaction log 

H. SKIP 

I. RESTART 

J. COPY_ONLY 

K. NORECOVERY 

L. BULK_LOGGED 

M. Differential 

N. STANDBY 

Answer:

Explanation: 

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


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

Answer:

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

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


Q103. DRAG DROP 

You use Microsoft SQL Server 2012 to develop a database that has two tables named Div1Cust and Div2Cust. 

Each table has columns named DivisionID and CustomerId . None of the rows in Div1Cust exist in Div2Cust. 

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

The rows in Div1Cust must be combined with the rows in Div2Cust. The result set must have columns named Division and Customer. Duplicates must be retained. 

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

Answer: 


Q104. 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:

Reference: 

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


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

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

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


Renewal sql server certification 70-457:

Q106. 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.CustomerXd - 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:


Q107. You develop a database for a travel application. 

You need to design tables and other database objects. 

You create a stored procedure. 

You need to supply the stored procedure with multiple event names and their dates as parameters. 

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:


Q108. CORRECT TEXT 

You have a view that was created by using the following code: 

You need to create an inline table-valued function named Sales.fn_OrdersByTerritory. 

Sales.fn_OrdersByTerritory must meet the following requirements: 

*Accept the @T integer parameter. 

*Use one-part names to reference columns. 

*Filter the query results sorted by SalesTerritoryID. 

*Return the columns in the same order as the order used in OrdersByTerritoryView. 

Which code segment should you use? 

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

Answer: CREATE FUNCTION Sales.fn_OrdersByTerritory (@T int) RETURNS TABLE AS RETURN ( SELECT OrderID,OrderDate,SalesTerrirotyID,TotalDue FROM Sales.OrdersByTerritory WHERE SalesTerritoryID = @T ) 


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

Answer:

Reference: http://www.grapefruitmoon.net/diving-into-t-sql-grouping-sets/ 

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


Q110. 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:

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


To know more about the 70-457, click here.

Tagged as : Microsoft 70-457 Dumps, Download 70-457 pdf, 70-457 VCE, 70-457 pass4sure, examcollection 70-457