Microsoft 70-461 Online Practice Questions and Exam Preparation

Microsoft 70-461 Online Questions & Answers

• Question 291:

  How do you modify a column value in a target row and collect the result of the modification in one visit to the row?

  A. By using an UPDATE based on a join
  B. By using an UPDATE based on a table expression
  C. By using an UPDATE with a variable
  D. The task cannot be achieved with only one visit to the row.
  C. By using an UPDATE with a variable

• Question 292:

  DRAG DROP

  You administer a Microsoft Azure SQL Database instance.

  You are troubleshooting a number of stored procedures that use transactions.

  1.

  p_ModifyCustomer modifies customer records in the database. While this transaction is running, no other transaction should be allowed to insert data into the range of orders being modified.

  2.

  p_GetOrders is used to retrieve order information for a customer. While this transaction is running, all other processes should be able to read and modify data being used by this procedure, and it is acceptable to have this procedure return stale values.

  3.

  p_ShipOrders is run once per day to batch orders into shipping criteria. Processes that read these records must receive a copy of the record as it exists at the beginning of the transaction, but should not utilize the TempDB to facilitate concurrency.

  You need to choose the appropriate transaction isolation level for each stored procedure. The transaction must meet the need while providing the highest level of concurrency and performance.

  Which isolation levels should you use? To answer, drag the appropriate isolation level or levels to correct stored procedure or procedures. Each isolation level may be used once, more than once, or not at all. You may need to drag the split bar between panes or scroll to view content.

  Select and Place:

  

  

  ---

  Box 1: READ_COMMITTED

  READ COMMITTED specifies that statements cannot read data that has been modified but not committed by other transactions. This prevents dirty reads. Data can be changed by other transactions between individual statements within the

  current transaction, resulting in nonrepeatable reads or phantom data. This option is the SQL Server default.

  The behavior of READ COMMITTED depends on the setting of the READ_COMMITTED_SNAPSHOT database option

  Box 2: REPEATABLE_READ:

  REPEATABLE_READ specifies that statements cannot read data that has been modified but not yet committed by other transactions and that no other transactions can modify data that has been read by the current transaction until the

  current transaction completes.

  Shared locks are placed on all data read by each statement in the transaction and are held until the transaction completes. This prevents other transactions from modifying any rows that have been read by the current transaction.

  Box 3: SERIALIZABLE

  SERIALIZABLE Specifies the following:

  Statements cannot read data that has been modified but not yet committed by other transactions.

  No other transactions can modify data that has been read by the current transaction until the current transaction completes.

  Other transactions cannot insert new rows with key values that would fall in the range of keys read by any statements in the current transaction until the current transaction completes.

  Incorrect Answers:

  Not READ:UNCOMMITTED:

  Transactions running at the READ UNCOMMITTED level do not issue shared locks to prevent other transactions from modifying data read by the current transaction. READ UNCOMMITTED transactions are also not blocked by exclusive

  locks that would prevent the current transaction from reading rows that have been modified but not committed by other transactions. When this option is set, it is possible to read uncommitted modifications, which are called dirty reads.

  Reference: https://docs.microsoft.com/en-us/sql/t-sql/statements/set-transaction-isolation-level-transact-sql?view=sql-server-2017

• Question 293:

  You support a database structure shown in the exhibit.

  

  You need to write a query that displays the following details:

  Total sales made by sales people, year, city, and country Sub totals only at the city level and country level A grand total of the sales amount

  Which Transact-SQL query should you use?

  A. SELECT SalesPerson.Name, Country, City, DatePart(yyyy, SaleDate) AS Year, Sum(Amount) AS Total FROM Sale INNER JOIN SalesPerson ON Sale.SalesPersonID = SalesPerson.SalesPersonID GROUP BY GROUPING SETS((SalesPerson.Name, Country, City, DatePart(yyyy, SaleDate)), (Country, City), (Country), ())
  B. SELECT SalesPerson.Name, Country, City, DatePart(yyyy, SaleDate) AS Year, Sum(Amount) AS Total FROM Sale INNER JOIN SalesPerson ON Sale.SalesPersonID = SalesPerson.SalesPersonID GROUP BY CUBE(SalesPerson.Name, Country, City, DatePart(yyyy, SaleDate))
  C. SELECT SalesPerson.Name, Country, City, DatePart(yyyy, SaleDate) AS Year, Sum(Amount) AS Total FROM Sale INNER JOIN SalesPerson ON Sale.SalesPersonID = SalesPerson.SalesPersonID GROUP BY CUBE(SalesPerson.Name, DatePart(yyyy, SaleDate), City, Country)
  D. SELECT SalesPerson.Name, Country, City, DatePart(yyyy, SaleDate) AS Year, Sum(Amount) AS Total FROM Sale INNER JOIN SalesPerson ON Sale.SalesPersonID = SalesPerson.SalesPersonID GROUP BY ROLLUP(SalesPerson.Name, DatePart(yyyy, SaleDate), City, Country)
  A. SELECT SalesPerson.Name, Country, City, DatePart(yyyy, SaleDate) AS Year, Sum(Amount) AS Total FROM Sale INNER JOIN SalesPerson ON Sale.SalesPersonID = SalesPerson.SalesPersonID GROUP BY GROUPING SETS((SalesPerson.Name, Country, City, DatePart(yyyy, SaleDate)), (Country, City), (Country), ())

  ---

  Be careful with this question, because on exam can be different options for answer. Reference: http://www.grapefruitmoon.net/diving-into-t-sql-grouping-sets/ Reference: http://msdn.microsoft.com/en-us/library/ms177673.aspx

• Question 294:

  Your manager has asked you to design a new table and enforce rules for the type of information that can be stored in a particular column. The information must match a particular pattern e.g. a number between 1 and 7. This checking must be

  performed during insert or update operations.

  Which parameter should you use when you create the column?

  A. CHECK
  B. DEFAULT
  C. Foreign Key
  D. Primary Key
  A. CHECK

• Question 295:

  SIMULATION

  You need to create a table named OrderDetails that contains the following columns: LineItemTotal, ListPrice, and Quantity. LineItemTotal stores the product of ListPrice and Quantity for each row.

  Construct the Transact-SQL for creating the table by using the following guidelines:

  1. Ensure that the calculation for a line item total is not run every time the table is queried.

  2. Do not use any object delimiters.

  3. Store LineItemTotal as the last column in the table.

  Part of the correct Transact-SQL has been provided in the answer area below. Enter the Transact-SQL in the answer area that resolves the problem and meets the stated goals or requirements. You can add Transact-SQL within the Transact-SQL segment that has been provided as well as below it.

  

  

  Use the ‘Check Syntax’ button to verify your work. Any syntax or spelling errors will be reported by line and character position.

  Check the answer below

  1. CREATE TABLE OrderDetails

  2. (

  3. ListPrice money NOT NULL,

  4. Quantity int NOT NULL,

  5. LineItemTotal AS (ListPrice * Quantity) PERSISTED

  6. )

  To line 5 add: LineItemTotal AS (ListPrice * Quantity) PERSISTED

• Question 296:

  What types of database objects can have synonyms? (Choose all that apply.)

  A. Stored procedures
  B. Indexes
  C. Temporary tables
  D. Database users
  A. Stored procedures
  C. Temporary tables

• Question 297:

  You write the following SELECT statement to get the last order date for a particular customer.

  

  You need to create the user-defined function to return the last order date for the specified customer.

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

  Select and Place:

  

  

  ---

  * First function header

  * Then declare that the function returns a datetime

  * Thirdly begin the function body.

  * Fourthly declare the return variable

  * At last include the code that retrieves the required date.

• Question 298:

  You are designing a Microsoft SQL Server database named Orders.

  You create a table according to the following schema:

  

  You create a stored procedure to be used by an ASP.NET application that runs the following statement:

  

  You need to ensure that the query runs as efficiently as possible. What Transact-SQL statement should you run?

  

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

  ---

  Explanation:

  By including nonkey columns, you can create nonclustered indexes that cover more queries. This is because the nonkey columns have the following benefits:

  1.

  They can be data types not allowed as index key columns.

  2.

  They are not considered by the Database Engine when calculating the number of index key columns or index key size.

  An index with nonkey columns can significantly improve query performance when all columns in the query are included in the index either as key or nonkey columns. Performance gains are achieved because the query optimizer can locate all the column values within the index; table or clustered index data is not accessed resulting in fewer disk I/O operations.

  References: https://docs.microsoft.com/en-us/sql/relational-databases/indexes/create-indexes-with-included-columns

• Question 299:

  SIMULATION

  You have a data warehouse that contains the data for all the customers of your company.

  You need to create a stored procedure that generates a list of customer addresses. The list must meet the following requirements:

  1. The procedure must accept a string value.

  2. The procedure should return all customers who reside in a city whose name begins with the provided value.

  3. If a user provides a blank value, the procedure must execute, and then return a blank result set.

  Part of the correct Transact-SQL has been provided in the answer area below. Enter the code in the answer area that resolves the problem and meets the stated goals or requirements. You can add code within code that has been provided as well as below it.

  

  

  Use the ‘Check Syntax’ button to verify your work. Any syntax or spelling errors will be reported by line and character position.

  Check the answer below

  1 CREATE PROCEDURE uspGetAddress @City nvarchar(50) = ""

  2 AS

  3 SELECT *

  4 FROM Person.Address

  5 WHERE City LIKE @City *

  6 GO

  On line 1 add "" (set the default value)

  On line 5 add LIKE (use string comparison)

• Question 300:

  You work as a database developer at ABC.com. ABC.com has a SQL Server 2012 database named SalesDB as illustrated in the following database diagram:

  

  ABC.com has retail stores in a few major cities across the country. The company wants to ascertain whether it would be advantageous to open a store in other cities based on feedback from its customers.

  You are required to provide the company's CEO with a list of Customers who live in a city that does not have a ABC.com store, along with the customer's Phone Number and the customer's City, and arranged alphabetically by City name.

  Which of the following Transact-SQL statements would return the required information?

  A. SELECT CustomerName, CustomerCity, CustomerPhone FROM Customers WHERE CustomerCity NOT EXISTS (SELECT StoreCity FROM Stores) ORDER BY CustomerCity
  B. SELECT CustomerName, CustomerCity, CustomerPhone FROM Customers WHERE CustomerCity < > ALL (SELECT StoreCity FROM Stores) ORDER BY StoreCity
  C. SELECT CustomerName, CustomerCity, CustomerPhone FROM Customers WHERE CustomerCity < > ANY (SELECT StoreCity FROM Stores) ORDER BY CustomerCity
  D. SELECT CustomerName, CustomerCity, CustomerPhone FROM Customers WHERE CustomerCity NOT IN (SELECT StoreCity FROM Stores) ORDER BY StoreCity
  C. SELECT CustomerName, CustomerCity, CustomerPhone FROM Customers WHERE CustomerCity < > ANY (SELECT StoreCity FROM Stores) ORDER BY CustomerCity