Ideas to 70 461 practice test

Proper study guides for Updated Microsoft Querying Microsoft SQL Server 2012 certified begins with Microsoft 70 461 vce preparation products which designed to deliver the Exact 70 461 exam questions pdf questions by making you pass the exam 70 461 pdf test at your first time. Try the free 70 461 exam dumps demo right now.


♥♥ 2021 NEW RECOMMEND ♥♥

Free VCE & PDF File for Microsoft 70-461 Real Exam (Full Version!)

★ Pass on Your First TRY ★ 100% Money Back Guarantee ★ Realistic Practice Exam Questions

Free Instant Download NEW 70-461 Exam Dumps (PDF & VCE):
Available on: http://www.surepassexam.com/70-461-exam-dumps.html

Q61. You have three tables that contain data for dentists, psychiatrists, and physicians. You create a view that is used to look up their email addresses and phone numbers. 

The view has the following definition: 

You need to ensure that users can update only the phone numbers and email addresses by using this view. 

What should you do? 

A. Alter the view. Use the EXPAND VIEWS query hint along with each SELECT statement. 

B. Create an INSTEAD OF UPDATE trigger on the view. 

C. Drop the view. Re-create the view by using the SCHEMABINDING clause, and then create an index on the view. 

D. Create an AFTER UPDATE trigger on the view. 

Answer:


Q62. DRAG DROP 

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: 


Q63. 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 FX_Orders_CustomerID FOREIGN KEY (CustomerId) REFERENCES 

Customer (CustomerId) 

B. ALTER TABLE Customer 

ADD CONSTRAINT FK_Customer_CustomerID FOREIGN KEY {CustomerID) 

REFERENCES 

Orders (CustomerId) 

C. ALTER TABLE Orders 

ADD CONSTRAINT CK_Crders_CustomerID 

CHECK (CustomerId IN (SELECT CustomerId FROM Customer)) 

D. ALTER TABLE Customer 

ADD OrderId INT NOT NULL; 

ALTER TABLE Customer 

ADD CONSTRAINT FK_Customer_OrderID FOREIGN KEY (CrderlD) REFERENCES 

Orders 

(CrderlD); 

E. ALTER TABLE Orders 

ADD CONSTRAINT PK Orders CustomerId PRIMARY KEY (CustomerID) 

Answer:

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


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


Q65. Your application contains a stored procedure for each country. Each stored procedure accepts an employee identification number through the @EmplD parameter. 

You need to build a single process for each employee that will execute the appropriate stored procedure based on the country of residence. 

Which approach should you use? 

A. A SELECT statement that includes CASE 

B. Cursor 

C. BULK INSERT 

D. View 

E. A user-defined function 

Answer:

Explanation: SQL Server user-defined functions are routines that accept parameters, perform an action, such as a complex calculation, and return the result of that action as a value. The return value can either be a single scalar value or a result set. 


Q66. You use a contained database named ContosoDb within a domain. You need to create a user who can log on to the ContosoDb database. You also need to ensure that you can port the database to different database servers within the domain without additional user account configurations. 

Which type of user should you create? 

A. SQL user without login 

B. SQL user with a custom SID 

C. SQL user with login 

D. Domain user 

Answer:


Q67. You develop a Microsoft SQL Server 2012 database. 

You need to create a batch process that meets the following requirements: 

. Returns a result set based on supplied parameters. 

. Enables the returned result set to perform a join with a table. 

Which object should you use? 

A. Inline user-defined function 

B. Stored procedure 

C. Table-valued user-defined function 

D. Scalar user-defined function 

Answer:


Q68. You are a database developer at an independent software vendor. You create stored 

procedures that contain proprietary code. 

You need to protect the code from being viewed by your customers. 

Which stored procedure option should you use? 

A. ENCRYPTBYKEY 

B. ENCRYPTION 

C. ENCRYPTBYPASSPHRASE 

D. ENCRYPTBYCERT 

Answer:


Q69. 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 AccountHolder WHERE CityID NOT IN (SELECT CityID FROM BranchMaster) 

B. SELECT AccountHolderID FROM AccountHolder WHERE CityID <> ALL (SELECT CityID FROM BranchMaster) 

C. SELECT AccountHolderlD FROM AccountHolder WHERE CityID <> SOME (SELECT CityID FROM BranchMaster) 

D. SELECT AccountHolderID FROM AccountHolder WHERE CityID <> ANY (SELECT CityID FROM BranchMaster) 

Answer: A,B 

Explanation: 

Verified the answers as correct. 

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

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

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


Q70. You are a database developer of a Microsoft SQL Server 2012 database. 

You are designing a table that will store Customer data from different sources. The table will include a column that contains the CustomerID from the source system and a column that contains the SourceID. 

A sample of this data is as shown in the following table. 

You need to ensure that the table has no duplicate CustomerID within a SourceID. You also need to ensure that the data in the table is in the order of SourceID and then CustomerID. 

Which Transact- SQL statement should you use? 

A. CREATE TABLE Customer 

(SourceID int NOT NULL IDENTITY, 

CustomerID int NOT NULL IDENTITY, 

CustomerName varchar(255) NOT NULL); 

B. CREATE TABLE Customer 

(SourceID int NOT NULL, 

CustomerID int NOT NULL PRIMARY KEY CLUSTERED, 

CustomerName varchar(255) NOT NULL); 

C. CREATE TABLE Customer 

(SourceID int NOT NULL PRIMARY KEY CLUSTERED, 

CustomerID int NOT NULL UNIQUE, 

CustomerName varchar(255) NOT NULL); 

D. CREATE TABLE Customer 

(SourceID int NOT NULL, 

CustomerID int NOT NULL, 

CustomerName varchar(255) NOT NULL, 

CONSTRAINT PK_Customer PRIMARY KEY CLUSTERED 

(SourceID, CustomerID)); 

Answer: