Microsoft 70-461 Online Practice Questions and Exam Preparation

Microsoft 70-461 Online Questions & Answers

• Question 411:

  You have a SQL database that contains a table named Products.

  You are implementing a stored procedure that retrieves the list of products, performs custom business logic and then retrieve the list of products again.

  The custom business logic in the stored procedure does not modify data from the Products table.

  The stored procedure contains the following:

  

  You need to complete line 01 of the stored procedure to ensure that when the transaction occurs, the data read from the SELECT * FROM Products statement on line 05 is identical to the data read from the SELECT * FROM Products

  statement on line 10. The solution must maximize concurrency.

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

  REPEATABLE READ

  Add REPEATABLE READ to line 1 to get:

  SET TRANSACTIONISOLATION LEVEL 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.

  Incorrect Answers:

  READ UNCOMMITTED specifies that statements can read rows that have been modified by other transactions but not yet 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.

  ---

  https://msdn.microsoft.com/en-us/library/ms173763.aspx

• Question 412:

  You develop a stored procedure for a wholesale glass manufacturing company. The stored procedure uses a cursor to read all row-based combinations of the following tables: You observe that the stored procedure returns results very slowly. You need to improve the execution speed of the stored procedure. Which cursor type should you use?

  

  A. GLOBAL FORWARD_ONLY
  B. LOCAL FAST_FORWARD
  C. LOCAL STATIC READ_ONLY FORWARD_ONLY
  D. GLOBAL STATIC
  B. LOCAL FAST_FORWARD

  ---

  FAST_FORWARD specifies a FORWARD_ONLY, READ_ONLY cursor with performance optimizations enabled. References: https://msdn.microsoft.com/en-us/library/ms180169.aspx

• Question 413:

  You have a Microsoft SQL Server 2012 database that contains tables named Customers and Orders. The tables are related by a column named CustomerID. You need to create a query that meets the following requirements:

  -

  Returns the CustomerName for all customers and the OrderDate for any orders that they have placed.

  -

  Results must include customers who have not placed any orders. Which Transact-SQL query should you use?

  A. SELECT CustomerName, OrderDate FROM Customers RIGHT OUTER JOIN Orders ON Customers.CustomerID = Orders.CustomerID
  B. SELECT CustomerName, CrderDate FROM Customers JOIN Orders ON Customers.CustomerID = Orders.CustomerID
  C. SELECT CustomerName, OrderDate FROM Customers CROSS JOIN Orders ON Customers.CustomerID = Orders.CustomerID
  D. SELECT CustomerName, OrderDate FROM Customers LEFT OUTER JOIN Orders ON Customers.CustomerID = Orders.CustomerID
  D. SELECT CustomerName, OrderDate FROM Customers LEFT OUTER JOIN Orders ON Customers.CustomerID = Orders.CustomerID

  ---

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

• Question 414:

  You develop a database application for Microsoft SQL Server and Microsoft Azure SQL Database.

  You need to raise an exception and transfer execution to a CATCH block.

  You need to ensure that the exception returns output in the following format:

  Msg 51000, Level 16, State 1, Line 1 The record does not exist.

  Which Transact-SQL statement should you run?

  A. DECLARE @Message NVARCHAR(2048); SELECT @Message = FORMATMESSAGE('The record does not exist.'); THROW 51000 , 1 , @Message
  B. THROW 51000 , 'The record does not exist.' , 1
  C. THROW ERROR_MESSAGE ('The record does not exist.'), 1
  D. THROW 51000 , FORMATMESSAGE('The record does not exist.') , 1
  B. THROW 51000 , 'The record does not exist.' , 1

  ---

  The following example shows how to use the THROW statement to raise an exception.

  THROW 51000, 'The record does not exist.', 1;

  Here is the result set.

  Msg 51000, Level 16, State 1, Line 1

  The record does not exist.

  References: https://docs.microsoft.com/en-us/sql/t-sql/language-elements/throw-transact-sql?view=sql-server-2017

• Question 415:

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

  

  

  

  You need to create a query that returns a list of products from Sales.ProductCatalog. The solution must meet the following requirements:

  1. UnitPrice must be returned in descending order.

  2. The query must use two-part names to reference the table.

  3. The query must use the RANK function to calculate the results.

  4. The query must return the ranking of rows in a column named PriceRank.

  5. The list must display the columns in the order that they are defined in the table.

  6. PriceRank must appear last.

  Which code segment should you use?

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

  Check the answer below

  SELECT ProductCatalog.CatID, ProductCatalog.CatName, ProductCatalog.ProductID, ProductCatalog.ProdName, ProductCatalog.UnitPrice, RANK() OVER (ORDER BY ProductCatalog.UnitPrice DESC) AS PriceRank FROM Sales.ProductCatalog ORDER BY ProductCatalog.UnitPrice DESC

• Question 416:

  How can you support the SELECT clause of a query by using a nonclustered index that is already used for the WHERE clause?

  A. You could use SELECT *.
  B. You could modify the index that is already used to include the columns from the select list that are not part of the key.
  C. You could add column aliases.
  D. There is no way to support the SELECT clause with indexes.
  B. You could modify the index that is already used to include the columns from the select list that are not part of the key.

• Question 417:

  Which function do you use to return the last identity value generated in a specific table?

  A. MAX
  B. SCOPE_IDENTITY
  C. @@IDENTITY
  D. IDENT_CURRENT
  D. IDENT_CURRENT

• Question 418:

  You administer a Microsoft SQL Server database named ContosoDb. Tables are defined as shown in the exhibit. (Click the Exhibit button.)

  

  You need to display rows from the Orders table for the Customers row having the CustomerId value set to 1 in the following XML format:

  Which Transact-SQL query should you use?

  A. SELECT OrderId, OrderDate, Amount, Name, CountryFROM Orders INNER JOIN Customers ON Orders.CustomerId = Customers.CustomerId WHERE Customers.CustomerId = 1 FOR XML RAW
  B. SELECT OrderId, OrderDate, Amount, Name, CountryFROM Orders INNER JOIN Customers ON Orders.CustomerId = Customers.CustomerId WHERE Customers.CustomerId = 1 FOR XML RAW, ELEMENTS
  C. SELECT OrderId, OrderDate, Amount, Name, CountryFROM Orders INNER JOIN Customers ON Orders.CustomerId = Customers.CustomerId WHERE Customers.CustomerId = 1 FOR XML AUTO
  D. SELECT OrderId, OrderDate, Amount, Name, CountryFROM Orders INNER JOIN Customers ON Orders.CustomerId = Customers.CustomerId WHERE Customers.CustomerId = 1 FOR XML AUTO, ELEMENTS
  E. SELECT Name, Country, OrderId, OrderDate, AmountFROM Orders INNER JOIN Customers ON Orders.CustomerId = Customers.CustomerId WHERE Customers.CustomerId = 1 FOR XML AUTO
  F. SELECT Name, Country, OrderId, OrderDate, AmountFROM Orders INNER JOIN Customers ON Orders.CustomerId = Customers.CustomerId WHERE Customers.CustomerId = 1 FOR XML AUTO, ELEMENTS
  G. SELECT Name AS '@Name', Country AS '@Country', OrderId, OrderDate, AmountFROM Orders INNER JOIN Customers ON Orders.CustomerId= Customers.CustomerId WHERE Customers.CustomerId = 1 FOR XML PATH ('Customers')
  H. SELECT Name AS 'Customers/Name', CountryAS 'Customers/Country', OrderId, OrderDate, AmountFROM Orders INNER JOIN Customers ON Orders.CustomerId = Customers.CustomerId WHERE Customers.CustomerId = 1 FOR XML PATH ('Customers')
  G. SELECT Name AS '@Name', Country AS '@Country', OrderId, OrderDate, AmountFROM Orders INNER JOIN Customers ON Orders.CustomerId= Customers.CustomerId WHERE Customers.CustomerId = 1 FOR XML PATH ('Customers')

  ---

  Explanation:

  Need to use FOR XML PATH ('Customers') to get the XML output oriented towards the Customers Element.

  Example: Specifying XPath-like column names

  In the following query the ProductModelID column name specified starts with '@'. Therefore, an attribute of the element that has the corresponding column value is created in the resulting XML.

  SELECT ProductModelID AS "@id",

  Name FROM Production.ProductModel WHERE ProductModelID=122 OR ProductModelID=119 FOR XML PATH ('ProductModelData');

  This is the result:

  All-Purpose Bike Stand

  Bike Wash

  References: https://docs.microsoft.com/en-us/sql/relational-databases/xml/examples-using-path-mode

• Question 419:

  You are designing a Microsoft SQL Server database named Orders.

  You create a table by running the following Transact-SQL statement:

  

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

  

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

  ---

  Creating a primary key automatically creates a corresponding unique clustered index, or a nonclustered index if specified as such.

  Example: To create a primary key in an existing table

  The following example creates a primary key on the column TransactionID in the AdventureWorks database.

  ALTER TABLE Production.TransactionHistoryArchive

  ADD CONSTRAINT PK_TransactionHistoryArchive_TransactionID PRIMARY KEY CLUSTERED (TransactionID);

  References:

  https://docs.microsoft.com/en-us/sql/relational-databases/tables/create-primary-keys

• Question 420:

  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 applications. What should you create for each application?

  A. Synonyms
  B. Common table expressions
  C. Views
  D. Temporary tables
  C. Views

  ---

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