Microsoft 70-461 Online Practice Questions and Exam Preparation

Microsoft 70-461 Online Questions & Answers

• Question 241:

  You develop a Microsoft SQL Server 2012 database. The database is used by two web applications that access a table named Products. You want to create an object that will prevent the applications from accessing the table directly while still providing access to the required data. You need to ensure that the following requirements are met:

  -

  Future modifications to the table definition will not affect the applications' ability to access data.

  -

  The new object can accommodate data retrieval and data modification.

  -

  You need to achieve this goal by using the minimum amount of changes to the existing applications. What should you create for each application?

  A. views
  B. table partitions
  C. table-valued functions
  D. stored procedures
  A. views

• Question 242:

  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. Which Transact-SQL statement should you use?

  A. ALTER TABLE Inventory ADD TotalItems AS ItemsInStore + ItemsInWarehouse
  B. ALTER TABLE Inventory ADD ItemsInStore - ItemsInWarehouse = TotalItems
  C. ALTER TABLE Inventory ADD TotalItems = ItemsInStore + ItemsInWarehouse
  D. ALTER TABLE Inventory ADD TotalItems AS SUM(ItemsInStore, ItemslnWarehouse);
  A. ALTER TABLE Inventory ADD TotalItems AS ItemsInStore + ItemsInWarehouse

  ---

  http://technet.microsoft.com/en-us/library/ms190273.aspx

• Question 243:

  You use Microsoft SQL Server 2012 to create a stored procedure as shown in the following code segment. (Line numbers are included for reference only.) The procedure can be called within other transactions. You need to ensure that when the DELETE statement from the HumanResourcesJobCandidate table succeeds, the modification is retained even if the insert into the Audit.Log table fails. Which code segment should you add to line 14?

  

  A. IF @@TRANCOUNT = 0
  B. IF (XACT_STATE ( ) ) = 0
  C. IF (XACT_STATE ( ) ) = 1
  D. IF @@TRANCOUNT = l
  C. IF (XACT_STATE ( ) ) = 1

  ---

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

• Question 244:

  You use Microsoft Azure SQL Database to develop a database application. You create a stored procedure named dbo.InsertData.

  dbo.InsertData uses multiple batch statements to calculate a column named RateofChange and insert data one row at a time. You need to ensure that when the transaction fails, dbo.InsertData meets the following requirements:

  1.

  If one row in the batch has a duplicate key error, all other rows will be inserted.

  2.

  If the value of the RateofChange throws an error, the row will not be inserted. Which set of Transact-SQL statements should you add to dbo.InsertData?

  

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

  ---

  SET XACT_ABORT OFF

  When SET XACT_ABORT is ON, if a Transact-SQL statement raises a run-time error, the entire transaction is terminated and rolled back.

  ARITHABORT ON

  ARITHABORT ends a query when an overflow or divide-by-zero error occurs during query execution.

  When SET ARITHABORT and SET ANSI WARNINGS are ON, these error conditions cause the query to end.

  When SET ARITHABORT is ON and SET ANSI WARNINGS is OFF, these error conditions cause the batch to end.

  References:

  https://docs.microsoft.com/en-us/sql/t-sql/statements/set-arithabort-transact-sql

• Question 245:

  You develop a Microsoft SQL Server 2012 database.

  You need to create and call a stored procedure that meets the following requirements:

  1.

  Accepts a single input parameter for CustomerID.

  2.

  Returns a single integer to the calling application.

  Which Transact-SQL statement or statements should you use? (Each correct answer presents part of the solution. Choose all that apply.)

  A. CREATE PROCEDURE dbo.GetCustomerRating @CustomerID INT, @CustomerRating INT OUTPUT AS SET NOCOUNT ON SELECT @CustomerRating = CustomerOrders/CustomerValue FROM Customers WHERE CustomerID = @CustomerID RETURN GO
  B. EXECUTE dbo.GetCustomerRating 1745
  C. DECLARE @CustomerRatingByCustomer INT DECLARE @Result INT EXECUTE @Result = dbo.GetCustomerRating 1745, @CustomerRatingByCustomer
  D. CREATE PROCEDURE dbo.GetCustomerRating @CustomerID INT, @CustomerRating INT OUTPUT AS SET NOCOUNT ON SELECT @Result = CustomerOrders/CustomerValue FROM Customers WHERE CustomerID = @CustomerID RETURN @Result GO
  E. DECLARE @CustomerRatingByCustomer INT EXECUTE dbo.GetCustomerRating @CustomerID = 1745, @CustomerRating = @CustomerRatingByCustomer OUTPUT
  F. CREATE PROCEDURE dbo.GetCustomerRating @CustomerID INT AS DECLARE @Result INT SET NOCOUNT ON SELECT @Result = CustomerOrders/CustomerValue FROM Customers WHERE CustomerID = @CustomerID RETURNS @Result GO
  A. CREATE PROCEDURE dbo.GetCustomerRating @CustomerID INT, @CustomerRating INT OUTPUT AS SET NOCOUNT ON SELECT @CustomerRating = CustomerOrders/CustomerValue FROM Customers WHERE CustomerID = @CustomerID RETURN GO
  E. DECLARE @CustomerRatingByCustomer INT EXECUTE dbo.GetCustomerRating @CustomerID = 1745, @CustomerRating = @CustomerRatingByCustomer OUTPUT

• Question 246:

  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));
  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));

• Question 247:

  You develop a Microsoft SQL Server database named ContosoDb. ContosoDb contains a table named Employess that was created by using the following Transact-SQL statement:

  

  You need to create a view that allows the insertion of new records into the Employees table by using the view.

  

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

• Question 248:

  You are developing a SQL Server 2012 database for ABC.com. You need to create a computed column that returns the data by referencing another table using an INNER JOIN. How would you accomplish this?

  A. You should make use of the FORMAT function.
  B. You should make use of a scalar user-defined function.
  C. You should make use of an inline function.
  D. You should make use of a table-valued user-defined function.
  E. You should make use of a stored procedure.
  D. You should make use of a table-valued user-defined function.

• Question 249:

  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
  D. Numeric

  ---

  Numeric is the only one in the list that can give a fixed precision and scale.

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

• Question 250:

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

  

  You need to create a query for a report. The query must meet the following requirements:

  1. NOT use object delimiters.

  2. Return the most recent orders first.

  3. Use the first initial of the table as an alias.

  4. Return the most recent order date for each customer.

  5. Retrieve the last name of the person who placed the order.

  6. Return the order date in a column named MostRecentOrderDate that appears as the last column in the report.

  The solution must support the ANSI SQL-99 standard.

  Which code segment should you use?

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

  Check the answer below

  SELECT C.LastName, MAX(O.OrderDate) AS MostRecentOrderDate FROM Customers AS C INNER JOIN Orders AS O ON C.CustomerID = O.CustomerID GROUP BY C.LastName ORDER BY MostRecentOrderDate DESC