P.s.: These 70-457 Exam Questions Were Updated After 2014/11/1 With The Change Of New Microsoft Exam. 100 Percent Vaild And 100 Percent Pass Ensure. Visit Our PassLeader Website And Get All Valid Exam Questions With PDF And VCE. 100 Percent Guaranteed Pass or 100 Percent Money Back Guarantee!

Vendor: Microsoft
Exam Code: 70-457
Exam Name: Transition Your MCTS on SQL Server 2008 to MCSA: SQL Server 2012, Part 1 Exam

QUESTION 1
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.

Answer: C

QUESTION 2
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)
AS
SELECT OrderID
,OrderDate
,SalesTerrirotyID
,TotalDue
FROM Sales.OrdersByTerritory
SORTED BY SalesTerritoryID

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

You deploy a new server that has SQL Server 2012 installed. You need to create a table named Sales.OrderDetails on the new server. Sales.OrderDetails must meet the following requirements:
* Write the results to a disk.
* Contain a new column named LineItemTotal that stores the product of ListPrice and Quantity for each row.
* The code must NOT use any object delimiters.
The solution must ensure that LineItemTotal is stored as the last column in the table. Which code segment should you use? To answer, type the correct code in the answer area.

Answer:
CREATE TABLE Sales.OrderDetails (
ListPrice as money,
Quantity as int,
LineItemTotal as ListPrice * Quantity)

QUESTION 4
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: D

QUESTION 5
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: AB

QUESTION 6
You administer a Microsoft SQL Server 2012 database. The database contains a table named Employee. Part of the Employee table is shown in the exhibit. (Click the Exhibit button.)


Confidential information about the employees is stored in a separate table named EmployeeData. One record exists within EmployeeData for each record in the Employee table. You need to assign the appropriate constraints and table properties to ensure data integrity and visibility. On which column in the Employee table should you a create a unique constraint?

A.    DateHired
B.    DepartmentID
C.    EmployeelD
D.    EmployeeNum
E.    FirstName
F.    JobTitle
G.    LastName
H.    MiddleName
I.    ReportsToID

Answer: D

QUESTION 7
You administer a Microsoft SQL Server 2012 database. The database contains a table named Employee. Part of the Employee table is shown in the exhibit. (Click the Exhibit button.)


Unless stated above, no columns in the Employee table reference other tables. Confidential information about the employees is stored in a separate table named EmployeeData. One record exists within EmployeeData for each record in the Employee table.

A.    DateHired
B.    DepartmentID
C.    EmployeeID
D.    EmployeeNum
E.    FirstName
F.    JobTitle
G.    LastName
H.    MiddleName
I.    ReportsToID

Answer: I

http://www.passleader.com/70-457.html

QUESTION 8
You administer a Microsoft SQL Server 2012 database. The database contains a table named Employee. Part of the Employee table is shown in the exhibit. (Click the Exhibit button.)


Confidential information about the employees is stored in a separate table named EmployeeData. One record exists within EmployeeData for each record in the Employee table. You need to assign the appropriate constraints and table properties to ensure data integrity and visibility. On which column in the Employee table should you use an identity specification to include a seed of 1,000 and an increment of 1?

A.    DateHired
B.    DepartmentID
C.    EmployeeID
D.    EmployeeNum
E.    FirstName
F.    JobTitle
G.    LastName
H.    MiddleName
I.    ReportsToID

Answer: C

QUESTION 9
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.    Option A
B.    Option B
C.    Option C
D.    Option D

Answer: A

QUESTION 10
Drag and Drop Question
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:

QUESTION 11
You develop three Microsoft SQL Server 2012 databases named Database1, Database2, and Database3. You have permissions on both Database1 and Database2. You plan to write and deploy a stored procedure named dbo.usp_InsertEvent in Database3. dbo.usp_InsertEvent must execute other stored procedures in the other databases. You need to ensure that callers that do not have permissions on Database1 or Database2 can execute the stored procedure. Which Transact-SQL statement should you use?

A.    USE Database2
B.    EXECUTE AS OWNER
C.    USE Database1
D.    EXECUTE AS CALLER

Answer: B

QUESTION 12
You develop 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 not include customers who have not placed any orders.
Which Transact-SQL query should you use?

A.    SELECT CustomerName, OrderDate
FROM Customers
LEFT OUTER JOIN Orders
ON Customers.CuscomerlD = Orders.CustomerId
B.    SELECT CustomerName, OrderDate
FROM Customers
RIGHT OUTER JOIN Orders
ON Customers.CustomerID = Orders.CustomerId
C.    SELECT CustomerName, OrderDate
FROM Customers
CROSS JOIN Orders
ON Customers.CustomerId = Orders.CustomerId
D.    SELECT CustomerName, OrderDate
FROM Customers
JOIN Orders
ON Customers.CustomerId = Orders.CustomerId

Answer: D

QUESTION 13
You develop a Microsoft SQL Server 2012 database. You need to create a batch process that meets the following requirements:
* Status information must be logged to a status table.
* If the status table does not exist at the beginning of the batch, it must be created.
Which object should you use?

A.    Scalar user-defined function
B.    Inline user-defined function
C.    Table-valued user-defined function
D.    Stored procedure

Answer: D

QUESTION 14
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

QUESTION 15
Drag and Drop Question
You use Microsoft SQL Server 2012 to develop a database application. You create two tables by using the following table definitions.

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

Answer:

Thank You for Choosing PassLeader’s Products, We PassLeader Guarantee:
1) More than 99,900 Satisfied Customers Worldwide.
2) Average 99.9% Success Rate.
3) Free Update to match latest and real exam scenarios.
4) Instant Download Access! No Setup required.
5) Questions & Answers are downloadable in PDF format and VCE test engine format.
6) Multi-Platform capabilities – Windows, Laptop, Mac, Android, iPhone, iPod, iPad.
7) 100% Guaranteed Success or 100% Money Back Guarantee.
8) Fast, helpful support 24×7.

http://www.passleader.com/70-457.html