Part 1
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

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:

QUESTION 16
You administer a database that includes a table named Customers that contains more than 750 rows. You create a new column named PartitionNumber of the int type in the table. You need to assign a PartitionNumber for each record in the Customers table. You also need to ensure that the PartitionNumber satisfies the following conditions:
* Always starts with 1.
* Starts again from 1 after it reaches 100.
Which Transact-SQL statement should you use?

A.    CREATE SEQUENCE CustomerSequence AS int
START WITH 0
INCREMENT BY 1
MINVALUE 1
MAXVALUE 100
UPDATE Customers SET PartitionNumber = NEXT VALOE FOR CustomerSequence DROP SEQUENCE
CustomerSequence
B.    CREATE SEQUENCE CustomerSequence AS int
START WITH 1
INCREMENT BY 1
MINVALUE 1 MAXVALUE 100
CYCLE
UPDATE Customers SET PartitionNumber = NEXT VALUE FOR CustomerSequence DROP SEQUENCE
CustomerSequence
C.    CREATE SEQUENCE CustomerSequence AS int
START WITH 1
INCREMENT BY 1
MINVALUE 1
MAXVALUE 100
UPDATE Customers SET PartitionNumber = NEXT VALUE FOR CustomerSequence + 1 DROP SEQUENCE
CustomerSequence
D.    CREATE SEQUENCE CustomerSequence AS int
START WITH 1
INCREMENT BY 1
MINVALUE 0
MAXVALUE 100
CYCLE
UPTATE Customers SET PartitionNumber = NEXT VALUE FOR CustomerSequence DROP SEQUENCE
CustomerSequence

Answer: B

QUESTION 17
You use Microsoft SQL Server 2012 to develop a database application. You need to create an object that meets the following requirements:
* Takes an input variable
* Returns a table of values
* Cannot be referenced within a view
Which object should you use?

A.    Scalar-valued function
B.    Inline function
C.    User-defined data type
D.    Stored procedure

Answer: D

QUESTION 18
Drag and Drop Question
You create the following stored procedure. (Line numbers are included for reference only.)

You need to ensure that the stored procedure performs the following tasks:
* If a record exists, update the record.
* If no record exists, insert a new record.
Which four Transact-SQL statements should you insert at line 07? (To answer, move the appropriate statements from the list of statements to the answer area and arrange them in the correct order.)

Answer:

QUESTION 19
You use a Microsoft SQL Server 2012 database that contains a table named BlogEntry that has the following columns:

Id is the Primary Key.
You need to append the "This is in a draft stage" string to the Summary column of the recent 10 entries based on the values in EntryDateTime. Which Transact-SQL statement should you use?

A.    UPDATE TOP (10) BlogEntry SET Summary.WRITE(N’ This is in a draft stage’, NULL, 0)
B.    UPDATE BlogEntry
SET Summary – CAST(N’ This is in a draft stage’ as nvarchar(max)) WHERE Id IN (SELECT TOP (10) Id
FROM B1ogEntry ORDER BY EntryDateTime DESC)
C.    UPDATE BlogEntry SET Summary.WRITE(N’ This is in a draft stage’, NULL, 0) FROM (SELECT TOP (10)
Id FROM BlogEntry ORDER BY EntryDateTime DESC) AS s WHERE BlogEntry.Id = s.ID
D.    UPDATE BlogEntry SET Summary.WRITE(N’ This is in a draft stage’, 0, 0) WHERE Id IN (SELECT TOP (10)
Id FROM BlogEntry ORDER BY EntryDateTime DESC)

Answer: B

QUESTION 20
You use Microsoft SQL Server 2012 to develop a database application. You create a stored procedure named DeleteJobCandidate. You need to ensure that if DeleteJobCandidate encounters an error, the execution of the stored procedure reports the error number. Which Transact-SQL statement should you use?

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

Answer: A

---

Get Passleader 70-457 Braindumps With Cheap Price And Pass Guaranteed With Money Back

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