Microsoft 70-761 Online Practice Questions and Exam Preparation

Microsoft 70-761 Online Questions & Answers

• Question 31:

  SIMULATION

  You have a database that contains the following tables.

  

  You need to create a query that lists the highest-performing salespersons based on the current year-to-date sales period. The query must meet the following requirements:

  

  Return the LastName and SalesYTD for the three salespersons with the highest year-to-date sales values.

  

  Exclude salespersons that have no value for TerritoryID.

  Construct the query using the following guidelines:

  

  Use the first letter of a table name as the table alias.

  

  Use two-part column names.

  

  Do not surround object names with square brackets.

  

  Do not use implicit joins.

  

  Use only single quotes for literal text.

  

  Use aliases only if required.

  

  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.

  1 SELECT top 3 lastname,salesYTD 2 FROM Person AS p INNER JOIN SalesPerson AS s 3 ON p.PersonID = s.SalesPersonID 4 WHERE territoryid is null 5 order by salesytd dsec

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

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

  ---

  Correct Answer:

  1 SELECT top 3 lastname,salesYTD

  2 FROM Person AS p INNER JOIN SalesPerson AS s

  3 ON p.PersonID = s.SalesPersonID

  4 WHERE territoryid is not null

  5 order by salesytd desc

  Note:

  On line 4 add a not before null.

  On line 5 change dsec to desc.

• Question 32:

  Note: This question is part of a series of questions that present the same scenario. Each question in the series contains a unique solution that might meet the stated goals. Some question sets might have more than one correct solution, while

  others might not have a correct solution.

  After you answer a question in this section. You will NOT be able to return to it. As a result, these questions will not appear in the review screen.

  You have a table that was created by running the following Transact-SQL statement:

  

  The Products table includes the data shown in the following table:

  

  TotalUnitPrice is calculated by using the following formula:

  TotalUnitPrice = UnitPrice * (UnitsInStock + UnitsOnOrder)

  You need to ensure that the value returned for TotalUnitPrice for ProductB is equal to 600.00.

  Solution: You run the following Transact-SQL statement:

  

  Does the solution meet the goal?

  A. Yes
  B. No
  A. Yes

  ---

  ISNULL ( check_expression , replacement_value )

  Arguments:

  check_expression

  Is the expression to be checked for NULL. check_expression can be of any type.

  replacement_value

  Is the expression to be returned if check_expression is NULL. replacement_value must be of a type that is implicitly convertible to the type of check_expresssion.

  References: https://docs.microsoft.com/en-us/sql/t-sql/functions/isnull-transact-sql

• Question 33:

  DRAG DROP

  You have a database that includes the following tables:

  

  You need to create a list of all customer IDs and the date of the last order that each customer placed. If the customer has not placed any orders, you must return the date January 1, 1900. The column names must be CustomerID and LastOrderDate.

  Which four Transact-SQL segments 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 area and arrange them in the correct order.

  Select and Place:

  

  

  ---

  Box 1: SELECT..COALESCE...

  The COALESCE function evaluates the arguments in order and returns the current value of the first expression that initially does not evaluate to NULL.

  Box 2: ..LEFT OUTER JOIN..

  The LEFT JOIN (LEFT OUTER JOIN) keyword returns all rows from the left table (table1), with the matching rows in the right table (table2). The result is NULL in the right side when there is no match. A customer might have no orders so the

  right table must be allowed have a NULL value.

  Box 3: ON c.custid = o.custid

  We JOIN on the custID column, which is available in both tables.

  Box 4: GROUP BY c.custid

  References:

  https://technet.microsoft.com/en-us/library/ms189499(v=sql.110).aspx

  http://www.w3schools.com/sql/sql_join_left.asp

• Question 34:

  Note: This question is part of a series of questions that use the same or similar answer choices. An answer choice may be correct for more than one question in the series. Each question is independent of the other questions in this series. Information and details provided in a question apply only to that question.

  You have a database that contains tables named Customer_CRMSystem and Customer_HRSystem. Both tables use the following structure:

  

  The tables include the following records: Customer_CRMSystem

  

  Customer_HRSystem

  

  Records that contain null values for CustomerCode can be uniquely identified by CustomerName. You need to display a Cartesian product, combining both tables.

  Which Transact-SQL statement should you run?

  

  A. B. C.
  D. E. F. G. H.
  G

  ---

  A cross join that does not have a WHERE clause produces the Cartesian product of the tables involved in the join. The size of a Cartesian product result set is the number of rows in the first table multiplied by the number of rows in the second table.

  References: https://technet.microsoft.com/en-us/library/ms190690(v=sql.105).aspx

• Question 35:

  Note: This question is part of a series of questions that present the same scenario. Each question in the series contains a unique solution that might meet the stated goals. Some question sets might have more than one correct solution, while others might not have a correct solution.

  After you answer a question in this section. You will NOT be able to return to it. As a result, these questions will not appear in the review screen.

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

  

  You have the following stored procedure:

  

  You need to modify the stored procedure to meet the following new requirements:

  Insert product records as a single unit of work.

  Return error number 51000 when a product fails to insert into the database.

  If a product record insert operation fails, the product information must not be permanently written to the database.

  Solution: You run the following Transact-SQL statement:

  

  Does this meet the goal?

  A. Yes
  B. No
  B. No

  ---

  XACT_STATE is a scalar function that reports the user transaction state of a current running request. XACT_STATE indicates whether the request has an active user transaction, and whether the transaction is capable of being committed.

  The states of XACT_STATE are:

  0 There is no active user transaction for the current request.

  1 The current request has an active user transaction. The request can perform any actions, including writing data and committing the transaction.

  2 The current request has an active user transaction, but an error has occurred that has caused the transaction to be classified as an uncommittable transaction.

  Example of correct use:

  BEGIN CATCH

  -- Test XACT_STATE for 0, 1, or -1.

  -- If 1, the transaction is committable.

  -- If -1, the transaction is uncommittable and should

  -- be rolled back.

  -- XACT_STATE = 0 means there is no transaction and

  -- a commit or rollback operation would generate an error.

  -- Test whether the transaction is uncommittable.

  IF (XACT_STATE()) = -1

  BEGIN

  PRINT 'The transaction is in an uncommittable state.' +

  ' Rolling back transaction.'

  ROLLBACK TRANSACTION;

  END;

  -- Test whether the transaction is active and valid.

  IF (XACT_STATE()) = 1

  BEGIN

  PRINT 'The transaction is committable.' +

  ' Committing transaction.'

  COMMIT TRANSACTION;

  END;

  END CATCH;

  References:

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

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

• Question 36:

  You have a disk-based table that contains 15 columns.

  You query the table for the number of new rows created during the current day.

  You need to create an index for the query. The solution must generate the smallest possible index.

  Which type of index should you create?

  A. clustered
  B. filtered nonclustered with a getdate() predicate in the WHERE statement clause
  C. hash
  D. nonclustered with compression enabled
  B. filtered nonclustered with a getdate() predicate in the WHERE statement clause

  ---

  A filtered index is an optimized nonclustered index especially suited to cover queries that select from a well-defined subset of data. It uses a filter predicate to index a portion of rows in the table. A well-designed filtered index can improve

  query performance as well as reduce index maintenance and storage costs compared with full-table indexes.

  Creating a filtered index can reduce disk storage for nonclustered indexes when a full-table index is not necessary.

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

• Question 37:

  You have a database that contains the following tables:

  

  You need to write a query that returns a list of all customers who have not placed orders. Which Transact-SQL statement should you run?

  A. SELECT c.custid FROM Sales.Customers c INNER JOIN Sales.Order oON c.custid = o.custid
  B. SELECT custid FROM Sales.Customers INTERSECTSELECT custid FROM Sales.Orders
  C. SELECTc.custid FROM Sales.Customers c LEFT OUTER Sales.Order oON c.custid = o.custid
  D. SELECT c.custid FROM Sales.Customers c LEFT OUTER JOIN Sales.Order oON c.custid = o.custid WHERE orderid IS NULL
  E. SELECT custid FROM Sales.Customers UNION ALL SELECT custid FROM Sales.Orders
  F. SELECT custid FROM Sales.Customers UNION SELECT custid FROM Sales.Orders
  G. SELECT c.custid FROM Sales.Customers c RIGHT OUTER JOIN Sales.Orders o ON c.custid = o.custid
  D. SELECT c.custid FROM Sales.Customers c LEFT OUTER JOIN Sales.Order oON c.custid = o.custid WHERE orderid IS NULL

  ---

  Inner joins return rows only when there is at least one row from both tables that matches the join condition. Inner joins eliminate the rows that do not match with a row from the other table. Outer joins, however, return all rows from at least one of the tables or views mentioned in the FROM clause, as long as those rows meet any WHERE or HAVING search conditions. All rows are retrieved from the left table referenced with a left outer join, and all rows from the right table referenced in a right outer join. All rows from both tables are returned in a full outer join.

  References: https://technet.microsoft.com/en-us/library/ms187518(v=sql.105).aspx

• Question 38:

  You need to create a table named Sales that meets the following requirements:

  

  Which Transact-SQL statement should you run?

  

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

  ---

  datetime2 Defines a date that is combined with a time of day that is based on 24-hour clock. datetime2 can be considered as an extension of the existing datetime type that has a larger date range, a larger default fractional precision, and

  optional user-specified precision.

  Incorrect Answers:

  B, C: NEWQSEQUENTIALID creates a GUID that is greater than any GUID previously generated by this function on a specified computer since Windows was started. A GUID uses more space then IDENTITY value.

  References:

  https://docs.microsoft.com/en-us/sql/t-sql/data-types/datetime2-transact-sql

  https://docs.microsoft.com/en-us/sql/t-sql/functions/newsequentialid-transact-sql

• Question 39:

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

  

  You need to view all customer data.

  Which Transact-SQL statement should you run?

  

  A. Option A
  B. Option B
  C. Option C
  D. Option D
  E. Option E
  F. Option F
  G. Option G
  H. Option H
  B. Option B

  ---

  The FOR SYSTEM_TIME ALL clause returns all the row versions from both the Temporal and History table. References: https://msdn.microsoft.com/en-us/library/dn935015.aspx

• Question 40:

  HOTSPOT

  You need to develop a Transact-SQL statement that meets the following requirements:

  The statement must return a custom error when there are problems updating a table.

  The error number must be the value 50555.

  The error severity level must be 14.

  A Microsoft SQL Server alert must be triggered when the error condition occurs.

  Which Transact-SQL segment should you use for each requirement? To answer, select the appropriate Transact-SQL segments in the answer area.

  Hot Area:

  

  

  ---

  RAISERROR generates an error message and initiates error processing for the session. RAISERROR can either reference a user-defined message stored in the sys.messages catalog view or build a message dynamically. The message is returned as a server error message to the calling application or to an associated CATCH block of a TRY...CATCH construct. New applications should use THROW instead.

  Note: RAISERROR syntax:

  RAISERROR( { msg_id | msg_str | @local_variable }

  { ,severity ,state }

  [ ,argument [ ,...n ] ] )

  [ WITH option [ ,...n ] ]

  The LOG option logs the error in the error log and the application log for the instance of the Microsoft SQL Server Database Engine.

  References:

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