Microsoft 70-461 Online Practice Questions and Exam Preparation

Microsoft 70-461 Online Questions & Answers

• Question 151:

  You administer a Microsoft SQL Server database that supports a shopping application. You need to retrieve a list of customers who live in territories that do not have a sales person. Which Transact- SQL query or queries should you use? (Each correct answer presents a complete solution. Choose all that apply.)

  A. SELECT CustomerID FROM Customer WHERE TerritoryID SOME(SELECT TerritoryID FROM Salesperson)
  B. SELECT CustomerID FROM Customer WHERE TerritoryID ALL(SELECT TerritoryID FROM Salesperson)
  C. SELECT CustomerID FROM Customer WHERE TerritoryID ANY(SELECT TerritoryID FROM Salesperson)
  D. SELECT CustomerID FROM Customer WHERE TerritoryID NOT IN(SELECT TerritoryID FROM Salesperson)
  B. SELECT CustomerID FROM Customer WHERE TerritoryID ALL(SELECT TerritoryID FROM Salesperson)
  D. SELECT CustomerID FROM Customer WHERE TerritoryID NOT IN(SELECT TerritoryID FROM Salesperson)

• Question 152:

  Note: This question is part of a series of questions that use the same set of answer choices. An answer choice may be correct for more than one question in the series.

  You develop a database for a travel application. You need to design tables and other database objects.

  You create the Historical_Events table.

  You need to store dates by using the smallest possible storage size.

  What should you do?

  A. Use the CAST function.
  B. Use the DATE data type.
  C. Use the FORMAT function.
  D. Use an appropriate collation.
  E. Use a user-defined table type.
  F. Use the VARBINARY data type.
  G. Use the DATETIME data type.
  H. Use the DATETIME2 data type.
  I. Use the DATETIMEOFFSET data type.
  J. Use the TODATETIMEOFFSET function.
  B. Use the DATE data type.

  ---

  The size of a column with the DATE format is 3 bytes fixed. Incorrect Answers:

  G: The DATETIME data type storage size is 8 bytes.

  H: 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.

  I: The 26 DATETIMEOFFSET data type storage size is 10 bytes.

  References: https://docs.microsoft.com/en-us/sql/t-sql/data-types/date-transact-sql?view=sql-server-2017

• Question 153:

  You administer a Microsoft SQL Server database that contains a table named Products.

  In a bit column named Discontinued, a value of 1 indicates a product is inactive, and a value of 0 indicates the product is active.

  You need to create a view that returns a column with a full product status description.

  Which expression should you use to define the column?

  A. IF(Discontinued = 0, `Active', `Inactive')
  B. CASE Discontinued WHEN 0 THEN `Active' WHEN 1 THEN `Inactive' END
  C. IF Discontinued = 0 `Active' ELSE `Inactive'
  D. DECODE (Discontinued, 0, `Active', 1, `Inactive', `Unknown')
  B. CASE Discontinued WHEN 0 THEN `Active' WHEN 1 THEN `Inactive' END

  ---

  The CASE statement evaluates a list of conditions and returns one of multiple possible result expressions.

  The CASE expression has two formats:

  The simple CASE expression compares an expression to a set of simple expressions to determine the result.

  The searched CASE expression evaluates a set of Boolean expressions to determine the result.

  Both formats support an optional ELSE argument.

  CASE can be used in any statement or clause that allows a valid expression.

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

• Question 154:

  You work as a SQL Server 2012 database developer at ABC.com. You are developing a stored procedure for ABC.com's e-Commerce application.

  Your stored procedure must display unique values from one column in multiple columns in the result set.

  How would you accomplish this?

  A. You should make use of the OUTER APPLY operator.
  B. You should make use of a dynamic cursor.
  C. You should make use of the PIVOT operator.
  D. You should make use of the CROSS APPLY operator.
  E. You should make use of the UNPIVOT operator.
  F. You should make use of a keyset cursor.
  C. You should make use of the PIVOT operator.

• Question 155:

  What are possible reasons that SQL Server does not reuse an existing cached plan? (Choose all that apply.)

  A. Because a SET option that influences the query result was changed
  B. Because the query is manually parameterized in a stored procedure, and no recompilation option is used
  C. Because SQL Server cannot determine the selectivity of the parameterized predicate
  D. Because a different data type was used for a parameter in the parameterized predicate
  A. Because a SET option that influences the query result was changed
  C. Because SQL Server cannot determine the selectivity of the parameterized predicate
  D. Because a different data type was used for a parameter in the parameterized predicate

  ---

  SQL Server does not reuse a cached plan if it cannot determine the selectivity of the parameterized predicate.

• Question 156:

  Where does SQL Server sort the data, if a sort is needed?

  A. In the current database
  B. In the master database
  C. In the msdb database
  D. SQL Server sorts data in memory, or spills the data to tempdb if it does not fit in memory
  D. SQL Server sorts data in memory, or spills the data to tempdb if it does not fit in memory

• Question 157:

  DRAG DROP

  You use Microsoft SQL Server client tool to develop a Microsoft Azure SQL Database instance to support an e-commerce application. The database consists of a Product table, a Store table, and a StoreProduct table as shown in the exhibit.

  (Click the Exhibit button.)

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

  Stores are not physically deleted, but are marked as deleted.

  When a store is deleted, the products that are sold in that store are marked as discontinued.

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

  

  Select and Place:

  

  

  ---

  Step 1: CREATE TRIGGER .. ON dbo.[Store]

  The trigger should be on the Store table.

  Step 2: INSTEAD OF DELETE

  Step 3: UPDATE dbo.Store..

  Stores are not physically deleted, but are marked as deleted.

  Step 4: UPDATE dbo.Product..

  When a store is deleted, the products that are sold in that store are marked as discontinued.

• Question 158:

  How many clustered indexes can you create on a table?

  A. 999
  B. 16
  C. 1
  D. 900
  C. 1

• Question 159:

  You develop a SQL Server database for a large multinational company.

  You must develop a stored procedure to loop through employee information and return data for one employee at a time.

  How should you complete the stored procedure? To answer, drag the appropriate Transact-SQL segment to the correct locations in the answer area. Each Transact-SQL segment 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: CURSOR VARYING OUTPUT Transact-SQL stored procedures can use the cursor data type only forOUTPUT parameters. If the cursor data type is specified for a parameter, both the VARYING and OUTPUT parameters are required. If the VARYING keyword is specified for a parameter, the data type must be cursor and the OUTPUT keyword must be specified. Box2: FORWARD_ONLY STATIC Box 3: CLOSE Box 4: DEALLOCATE References:https://technet.microsoft.com/en-us/library/ms175498(v=sql.105).aspx

• Question 160:

  You have a SQL Server database that contains all of the customer data for your company.

  You need to extract a random 1,000 row sample from a table Customers.

  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.

  TABLESAMPLE SYSTEM (1000 ROWS)

  Update line 3 to get the following:

  SELECT *

  FROM Customers

  TABLESAMPLE SYSTEM (1000 ROWS)

  The TABLESAMPLE clause limits the number of rows returned from a table in the FROM clause to a sample number orPERCENT of rows.

  Syntax: TABLESAMPLE [SYSTEM] (sample_number [ PERCENT | ROWS ] )

  ---

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