Microsoft 70-461 Online Practice Questions and Exam Preparation

Microsoft 70-461 Online Questions & Answers

• Question 211:

  You are developing an application that uses data residing in two databases named Inventory and Accounting on a single Microsoft SQL Server 2012 server. You plan to provide a record set from tables in the Inventory database. You write the following query:

  

  You need to create this query as a permanent database object that can be joined to other queries. The other queries will be run from the Accounting database. Neither database allows cross-database ownership. Which Transact-SQL statement should you use?

  

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

  ---

  A table-valued function returns a single rowset (unlike stored procedures, which can return multiple result shapes). Because the return type of a table-valued function is Table, you can use a table-valued function anywhere in SQL that you can use a table. You can also treat the table-valued function just as you would a table.

  References: https://docs.microsoft.com/en-us/dotnet/framework/data/adonet/sql/linq/how-to-use-table-valued-user-defined-functions

• Question 212:

  You are employed as a SQL Server 2012 database developer at ABC.com. ABC.com has a database named SalesDB with tables named Customer and Orders. The Customer and Orders tables were created using the following Transact-

  SQL code:

  CREATE TABLE SalesDB.Customers

  (

  CustomerID int NOT NULL PRIMARY KEY,

  FirstName varchar (150) NOT NULL,

  LastName varchar (150) NOT NULL,

  Address1 varchar (200) NOT NULL,

  Address2 varchar (200) NULL,

  City varchar (100) NOT NULL,

  StateCode varchar (2) NOT NULL,

  Zip varchar (5) NOT NULL,

  Phone varchar (10) NOT NULL

  )

  GO

  CREATE TABLE SalesDB.Orders

  (

  OrderID int NOT NULL PRIMARY KEY,

  CustomerID int NOT NULL,

  OrderDate datetime NOT NULL,

  ShipDate datetime NOT NULL,

  Exam CustomerID int NOT NULL,

  SalesRepID int NOT NULL

  )

  GO

  You must now create an OrderDetails table as shown in the following database diagram:

  

  The TotalPrice column must be a computed column based on the product of the UnitPrice and Quantity columns and the data must be stored in the table.

  How would you create this table?

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

  A. Check the answer below.
  A. Check the answer below.

  ---

  CREATE TABLE SalesDB.OrderDetails

  (

  OrderID int NOT NULL,

  ProductID int NOT NULL,

  Quantity int NOT NULL,

  UnitPrice money NOT NULL,

  TotalPrice AS (Quantity * UnitPrice) PERSISTED

  )

• Question 213:

  What are the things that the SELECT INTO statement doesn't copy from the source? (Choose all that apply.)

  A. Indexes
  B. Constraints
  C. The IDENTITY property
  D. Triggers
  A. Indexes
  B. Constraints
  D. Triggers

• Question 214:

  You develop a Microsoft SQL Server 2012 database. You need to create and call a stored procedure that meets the following requirements:

  -Accepts a single input parameter for CustomerID.

  -

  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. Option A
  B. Option B
  C. Option C
  D. Option D
  E. Option E
  F. Option F
  A. Option A
  E. Option E

• Question 215:

  Which of the following is only possible when using the MERGE statement in regard to the OUTPUT clause?

  A. Referring to columns from the source table
  B. Referring to both the keywords deleted and inserted
  C. Assigning aliases to output columns
  D. Using composable DML
  A. Referring to columns from the source table

• Question 216:

  HOTSPOT

  You develop a SQL Server database for an online theater sales company. The database includes the following four tables:

  

  The following diagram shows the tables and their relationships:

  

  Hot Area:

  

  

• Question 217:

  Which of the following techniques can be used to inject unwanted code into dynamic SQL when user input is concatenated with valid SQL commands?

  A. Insert a comment string of two dashes, then the malicious code, and then a single quotation mark.
  B. Insert a single quotation mark, then the malicious code, and then a comment string of two dashes.
  C. Insert the malicious code followed by a single quotation mark and a comment string of two dashes.
  B. Insert a single quotation mark, then the malicious code, and then a comment string of two dashes.

  ---

  The initial single quotation mark terminates the input string, and the final comment removes the effect of the terminating single quotation mark. Then the malicious code can be inserted in between them.

• Question 218:

  You are developing an SQL Server database for an automobile manufacturer. The manufacturer maintains the list of vehicles sold and vehicles that have been recalled. The tables are shown below:

  

  You have the following Transact-SQL code. Line numbers are included for reference only.

  

  You must update the VehiclesToRecall table with the list of vehicles that were recalled in 2014. You must maximize the performance of the operation. For each of the following statements, select Yes if the statement is true. Otherwise, select No.

  Hot Area:

  

  

• Question 219:

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

  

  You need to create a view named uv_CustomerFullName to meet the following requirements:

  1. The code must NOT include object delimiters.

  2. The view must be created in the Sales schema.

  3. Columns must only be referenced by using one-part names.

  4. The view must return the first name and the last name of all customers.

  5. The view must prevent the underlying structure of the customer table from being changed.

  6. The view must be able to resolve all referenced objects, regardless of the user's default schema.

  Which code segment should you use?

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

  Check the answer below

  CREATE VIEW Sales.uv_CustomerFullName WITH SCHEMABINDING AS SELECT FirstName, LastName FROM Sales.Customers

• Question 220:

  A local bank uses a SQL Server database to manage accounts. You are developing a stored procedure that contains multiple Transact-SQL INSERT statements.

  The stored procedure must use transaction management to handle errors.

  You need to ensure that the stored procedure rolls back the entire transaction if a run-time occurs.

  Which Transact-SQL statement should you add to the stored procedure?

  A. SET ARITHABORT ON
  B. SET NOEXEC ON
  C. SET TRANSACTION ISOLATION LEVEL ON
  D. SET XACT_ABORT ON
  D. SET XACT_ABORT ON

  ---

  SET XACT_ABORT specifies whether SQL Server automatically rolls back the current transaction when a Transact-SQL statement raises a run-time error. When SET XACT_ABORT is ON, if a Transact-SQL statement raises a run-time error, the entire transaction is terminated and rolled back.

  References: https://docs.microsoft.com/en-us/sql/t-sql/statements/set-xact-abort-transact-sql?view=sql-server-2017