Get real 070-457 exam questions for better preparation

Microsoft 070-457 Exam Overview:

Microsoft 070-457 Exam Syllabus Topics:

Microsoft Transition Your MCTS on SQL Server 2008 to MCSA: SQL Server 2012, Part 1 Sample Questions:

Question 1

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. EXECUTE AS OWNER
B. USE Database1
C. USE Database2
D. EXECUTE AS CALLER

Question 2

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 CK_Orders_CustomerID CHECK (CustomerID IN (SELECT CustomerId FROM Customer))
B. ALTER TABLE Customer ADD CONSTRAINT FK_Customer_CustomerID FOREIGN KEY (CustomerID) REFERENCES
Orders (CustomerID)
C. ALTER TABLE Customer ADD OrderID INT NOT NULL;
ALTER TABLE Customer ADD CONSTRAINT FK_Customer_OrderID FOREIGN KEY (OrderID) REFERENCES Orders (OrderID);
D. ALTER TABLE Orders ADD CONSTRAINT PK_Orders_CustomerID PRIMARY KEY (CustomerID)
E. ALTER TABLE Orders ADD CONSTRAINT FK_Orders_CustomerID FOREIGN KEY (CustomerID) REFERENCES Customer (CustomerID)

Question 3

You administer a Microsoft SQL Server 2012 database named Contoso on a server named Server01. You need to prevent users from disabling server audits in Server01. What should you create?

A. a Database Audit Specification
B. a Server Audit Specification
C. a Policy
D. an Extended Event session
E. a Resource Pool
F. an Alert
G. a SQL Profiler Trace

Question 4

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.

A. CREATE TABLE Sales.OrderDetails ( ListPrice money not null, Quantity int not null, LineItemTotal as (ListPrice * Quantity))
B. CREATE TABLE Sales.OrderDetails ( ListPrice money not null, Quantity int not null, LineItemTotal as (ListPrice * Quantity) PERSISTED)

Question 5

You develop a Microsoft SQL Server 2012 database that has two tables named SavingAccounts and LoanAccounts. Both tables have a column named AccountNumber of the nvarchar data type. You use a third table named Transactions that has columns named TransactionId, AccountNumber, Amount, and TransactionDate. You need to ensure that when multiple records are inserted in the Transactions table, only the records that have a valid AccountNumber in the SavingAccounts or LoanAccounts are inserted. Which Transact-SQL statement should you use?

A. CREATE TRIGGER TrgValidateAccountNumber ON Transactions FOR INSERT AS BEGIN
INSERT INTO Transactions SELECT TransactionID,AccountNumber,Amount,TransactionDate FROM inserted WHERE AccountNumber IN (SELECT AccountNumber FROM LoanAccounts UNION SELECT AccountNumber FROM SavingAccounts)) END
B. CREATE TRIGGER TrgValidateAccountNumber ON Transactions INSTEAD OF INSERT AS BEGIN
IF EXISTS (
SELECT AccountNumber FROM inserted EXCEPT
(SELECT AccountNumber FROM LoanAccounts
UNION SELECT AccountNumber FROM SavingAccounts))
BEGIN
ROLLBACK TRAN
END
END
C. CREATE TRIGGER TrgValidateAccountNumber ON Transactions INSTEAD OF INSERT AS BEGIN
INSERT INTO Transactions SELECT TransactionID,AccountNumber,Amount,TransactionDate FROM inserted WHERE AccountNumber IN (SELECT AccountNumber FROM LoanAccounts UNION SELECT AccountNumber FROM SavingAccounts)) END
D. CREATE TRIGGER TrgValidateAccountNumber ON Transactions FOR INSERT AS BEGIN
IF EXISTS (
SELECT AccountNumber FROM inserted EXCEPT
(SELECT AccountNumber FROM LoanAccounts
UNION SELECT AccountNumber FROM SavingAccounts))
BEGIN
ROLLBACK TRAN
END
END

Solutions: