Microsoft 70-461 Online Practice Questions and Exam Preparation

Microsoft 70-461 Online Questions & Answers

• Question 141:

  DRAG DROP

  You need to create a cursor that meets the following requirements:

  Executes as quickly as possible.

  Reflects all data changes made to the table while scrolling.

  Which five Transact-SQL statements should you use to develop the solution? To answer, move the appropriate Transact-SQL segments from the list of Transact-SQL segments to the answer are and arrange them in the correct order.

  Select and Place:

  

  

  ---

  Step 1: Declare variables. Step 2: Open the cursor Step 3: Fetch the first instance. Step 4: Loop Step 5: Close and deallocate the cursor

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

• Question 142:

  DRAG DROP

  You develop an application that uses data from a Microsoft SQL Server database.

  Your application experiences blocking problems.

  You need to enable row versioning and you want connections to have row versioning enabled by default.

  How should you complete the Transact-SQL statement? To answer, drag the appropriate command to the correct positions. Each command 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:

  

  

  ---

  You can use a row versioning-based isolation level.

  Set READ_COMMITTED_SNAPSHOT database option ON to enable read-committed transactions to use row versioning, and use snapshot isolation.

  References: https://docs.microsoft.com/en-us/sql/relational-databases/sql-server-transaction-locking-and-row-versioning-guide?view=sql-server-2017

• Question 143:

  SIMULATION

  The following is a series of questions in which you are required to input one or more lines of code.

  To input your response

  Type your response into the text entry field in the Answer Area. You may input one or more lines of code. More than one solution may be correct. You will receive credit if your solution matches any of the correct solutions.

  To validate code syntax

  After entering your code, click the Check Syntax button. This validates code syntax (such as SQL commands) and values (such as table names and variable names) used in your solution. If there are any errors, they will appear in the window

  next to the Check Syntax button. You may change your code and re-validate the syntax as many times as you want.

  Note that Check Syntax does NOT validate whether you have answered the question correctly. It simply validates the accuracy of your syntax.

  To view available command keywords

  Click the Keywords button to view a list of command keywords. This is a general list provided for reference and is not limited to commands used in the question.

  

  The Sales schema of a database contains the tables shown in the exhibit.

  

  You need to create a stored procedure named RecentOrders that meets the following requirements:

  1. Declares an input variable named @EndDate of type varchar(255).

  2. Compare the value of @EndDate with the OrderDate for records from the Sales.Orders table.

  3. Return only the OrderID column for rows where the OrderDate is more recent than @EndDate.

  Construct the Transact-SQL for the stored procedure by using the following guidelines:

  1. Do not use aliases.

  2. Do not use object delimiters.

  3. Ensure that any objects called in RecentOrders can be resolved by all users.

  4. Convert @endDate to a datetime data type in the WHERE clause of the SELECT statement before comparing the value to an OrderDate.

  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.

  

  Check the answer below

  Line 1: CREATE PROCECURE RecentOrders @EndDate varchar(255) Line 2: AS Line 3: SELECT OrderID Line 4: FROM Sales.Orders Line 5: WHERE Sales.Orders.OrderDate > @Endate

  ---

  https://docs.microsoft.com/en-us/sql/t-sql/statements/create-procedure-transact-sql

• Question 144:

  Which of the following correctly represents the logical query processing order of the various query clauses?

  A. SELECT > FROM > WHERE > GROUP BY > HAVING > ORDER BY
  B. FROM > WHERE > GROUP BY > HAVING > SELECT > ORDER BY
  C. FROM > WHERE > GROUP BY > HAVING > ORDER BY > SELECT
  D. SELECT > ORDER BY > FROM > WHERE > GROUP BY > HAVING
  B. FROM > WHERE > GROUP BY > HAVING > SELECT > ORDER BY

• Question 145:

  What is the difference between the simple CASE expression and the searched CASE expression?

  A. The simple CASE expression is used when the database recovery model is simple, and the searched CASE expression is used when it's full or bulk logged.
  B. The simple CASE expression compares an input expression to multiple possible expressions in the WHEN clauses, and the searched CASE expression uses independent predicates in the WHEN clauses.
  C. The simple CASE expression can be used anywhere in a query, and the searched CASE expression can be used only in the WHERE clause.
  D. The simple CASE expression can be used anywhere in a query, and the searched CASE expression can be used only in query filters (ON, WHERE, HAVING).
  B. The simple CASE expression compares an input expression to multiple possible expressions in the WHEN clauses, and the searched CASE expression uses independent predicates in the WHEN clauses.

• Question 146:

  You have a database named Sales that contains the tables as 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. Return the last name of the customer who placed the order.

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

  3. Group the results by CustomerID.

  4. Order the results by the most recent OrderDate.

  5. Use the database name and table name for any table reference.

  6. Use the first initial of the table as an alias when referencing columns in a table.

  The solution must support the ANSI SQL-99 standard and must NOT use object identifiers.

  Part of the correct T-SQL statement has been provided in the answer area. Complete the SQL statement.

  

  Check the answer below

  SELECT o.LastName, MAX (o.OrderData) AS MostRecentOrderData FROM Sales.Orders AS o GROUP BY o.CustomerID ORDER BY o.OrderDate DESC

• Question 147:

  You use Microsoft SQL Server 2012 to develop a database that has two tables named Div1Cust and Div2Cust.

  Each table has columns named DivisionID and CustomerId. None of the rows in Div1Cust exist in Div2Cust.

  You need to write a query that meets the following requirements:

  The rows in Div1Cust must be combined with the rows in Div2Cust.

  The result set must have columns named Division and Customer.

  Duplicates must be retained.

  Which three Transact-SQL statements should you use? (To answer, move the appropriate statements from the list of statements to the answer area and arrange them in the correct order.)

  Select and Place:

  

  

• Question 148:

  You administer a Microsoft SQL Server 2012 database. The database contains a table named Employee. Part of the Employee table is shown in the exhibit.

  

  Confidential information about the employees is stored in a separate table named EmployeeData. One record exists within EmployeeData for each record in the Employee table. You need to assign the appropriate constraints and table properties to ensure data integrity and visibility. On which column in the Employee table should you a create a unique constraint?

  A. DateHired
  B. DepartmentID
  C. EmployeelD
  D. EmployeeNum
  E. FirstName
  F. JobTitle
  G. LastName
  H. MiddleName
  I. ReportsToID
  D. EmployeeNum

• Question 149:

  SIMULATION You need to create a view named Sales.uv_CustomerFullNames. The view must display the FirstName and LastName fields from a table named Sales.Customers. The view must also prevent the underlying structure of the customer table from being changed.

  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

  WITH SCHEMABINDING

  Add WITH SCHEMABINDING to the first line to get:

  CREATE VIEWsales.uv_CustomerFullNames WITH SCHEMABINDING AS SELECT FirstName, LastName FROM Sales.Customers

  When you use the SchemaBinding keyword while creating a view or function you bind the structure of any underlying tables or views. It means that as long as that schemabound object exists as a schemabound object (ie you don't remove schemabinding) you are limited in changes that can be made to the tables or views that it refers to.

  ---

  https://sqlstudies.com/2014/08/06/schemabinding-what-why/

• Question 150:

  You develop a Microsoft SQL Server 2012 database that contains a table named Customers. The Customers table has the following definition. You need to create an audit record only when either the MobileNumber or HomeNumber column is updated. Which Transact-SQL query should you use?

  

  A. CREATE TRIGGER TrgPhoneNumberChange ON Customers FOR UPDATE AS IF COLUMNS_UPDATED (HomeNumber, MobileNumber) - - Create Audit Records
  B. CREATE TRIGGER TrgPhoneNumberChange ON Customers FOR UPDATE AS IF EXISTS( SELECT HomeNumber FROM inserted) OR EXISTS (SELECT MobileNumber FROM inserted) - - Create Audit Records
  C. CREATE TRIGGER TrgPhoneNumberChange ON Customers FOR UPDATE AS IF COLUMNS_CHANGED (HomeNumber, MobileNumber) - - Create Audit Records
  D. CREATE TRIGGER TrgPhoneNumberChange ON Customers FOR UPDATE AS IF UPDATE (HomeNumber) OR UPDATE (MobileNumber) - - Create Audit Records
  D. CREATE TRIGGER TrgPhoneNumberChange ON Customers FOR UPDATE AS IF UPDATE (HomeNumber) OR UPDATE (MobileNumber) - - Create Audit Records

  ---

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