Microsoft 70-461 Online Practice Questions and Exam Preparation

Microsoft 70-461 Online Questions & Answers

• Question 281:

  DRAG DROP

  You administer a SQL Server database that tracks sales that are made by sales persons. The database contains a table that is defined by the following Transact-SQL statements:

  

  You have the following requirements: accept a datetime value for the query month return a list of salespeople IDs who have sales in the query month or before the query month compare sales with sales quota for salespeople who have a sales quota display year-to-date sales for salespeople that do not have a sales quota How should you complete the stored procedure? To answer, drag the appropriate Transact-SQL segments to the correct locations. 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: IIF ..

  The IIF logical function returns one of two values, depending on whether the Boolean expression evaluates to true or false in SQL Server.

  Syntax: IIF ( boolean_expression, true_value, false_value )

  Box 2: [LastSzale] < EOMONTH(@queryMonth)

  The EOMONTH function returns the last day of the month containing a specified date, with an optional offset.

  References:

  https://docs.microsoft.com/en-us/sql/t-sql/functions/logical-functions-iif-transact-sql?view=sql-server-2017

  https://docs.microsoft.com/en-us/sql/t-sql/functions/eomonth-transact-sql?view=sql-server-2017

• Question 282:

  Which of the following is not a reason to update statistics manually?

  A. You just rebuilt an index.
  B. You bulk-inserted a large amount of data to a table and want to query this table immediately after the insert.
  C. You upgraded the database.
  D. Query execution times are slow; however, you know that the queries are written correctly and supported with appropriate indexes.
  A. You just rebuilt an index.

• Question 283:

  You are employed as a SQL Server 2012 database developer at ABC.com. ABC.com has a SalesDB database with a view named SalesV.

  The SalesV view was created using the following Transact-SQL code:

  CREATE VIEW SalesDB.ProductsSalesV

  AS

  SELECT OrderID, ProductID, ShipDate, OrderDate, Amount

  FROM SalesDB.Orders;

  You want to create an inline table-valued function named fn_ABC that accepts a @ProductID parameter of the integer data type. The inline table-valued function should also allow for sales orders for each product to be listed by the latest sale.

  How would you create this inline table-valued function?

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

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

  ---

  CREATE FUNCTION SalesDB.fn_ABC ( @ProductID int ) RETURNS TABLE AS RETURN ( SELECT OrderID, ProductID, ShipDate, OrderDate, Amount FROM Sales. ProductsSalesV WHERE ProductID = @ProductID ORDER BY OrderDate DESC );

• Question 284:

  You develop a Microsoft SQL Server database that contains a table named Employee, defined as follows: You need to create a view that contains two computed columns representing the month and the year of the [HireDate] of each Employee. Which function should you use?

  

  A. SUBSTRING()
  B. TRYDATEDIFF()
  C. DATEPART()
  D. CAST()
  C. DATEPART()

  ---

  The DATEPART() function was returns specified parts of a date.

  References: https://database.guide/6-functions-to-get-the-day-month-and-year-from-a-date-in-sql-server/

• Question 285:

  You have a database that contains the tables as shown below:

  

  You have a stored procedure named Procedure1. Procedure1 retrieves all order ids after a specific date. The rows for Procedure1 are not sorted. Procedure1 has a single parameter named Parameter1. Parameter1 uses the varchar type and is configured to pass the specific date to Procedure1. A database administrator discovers that OrderDate is not being compared correctly to Parameter1 after the data type of the column is changed to datetime. You need to update the SELECT statement to meet the following requirements:

  1. The code must NOT use aliases.

  2. The code must NOT use object delimiters.

  3. The objects called in Procedure1 must be able to be resolved by all users.

  4. OrderDate must be compared to Parameter1 after the data type of Parameter1 is changed to datetime.

  Which SELECT statement should you use?

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

  Check the answer below

  SELECT Orders.OrderID FROM Orders WHERE Orders.OrderDate>CONVERT(datetime,@Parameter1)

• Question 286:

  You develop a Microsoft SQL Server 2012 database. You create a view from the Orders and OrderDetails tables by using the following definition. You need to ensure that users are able to modify data by using the view. What should you do?

  

  A. Create an AFTER trigger on the view.
  B. Modify the view to use the WITH VIEW_METADATA clause.
  C. Create an INSTEAD OF trigger on the view.
  D. Modify the view to an indexed view.
  C. Create an INSTEAD OF trigger on the view.

  ---

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

• Question 287:

  You administer a Microsoft SQL Server database. The database contains a Product table created by using the following definition:

  

  You need to ensure that the minimum amount of disk space is used to store the data in the Product table. What should you do?

  A. Convert all indexes to Column Store indexes.
  B. Implement Unicode Compression.
  C. Implement row-level compression.
  D. Implement page-level compression.
  D. Implement page-level compression.

  ---

  Reference: http://msdn.microsoft.com/en-us/library/cc280449.aspx Reference: http://msdn.microsoft.com/en-us/library/cc280464.aspx

  Reference: http://msdn.microsoft.com/en-us/library/cc280576.aspx Reference: http://msdn.microsoft.com/en-us/library/ee240835.aspx

• Question 288:

  You administer a Microsoft SQL Server database that supports a banking transaction management application. You need to retrieve a list of account holders who live in cities that do not have a branch location. Which Transact-SQL query or queries should you use? (Each correct answer presents a complete solution. Choose all that apply.)

  A. SELECT AccountHolderID FROM AccountHolder WHERE CityID NOT IN (SELECT CityID FROM BranchMaster)
  B. SELECT AccountHolderID FROM AccountHolder WHERE CityID ALL (SELECT CityID FROM BranchMaster)
  C. SELECT AccountHolderlD FROM AccountHolder WHERE CityID SOME (SELECT CityID FROM BranchMaster)
  D. SELECT AccountHolderID FROM AccountHolder WHERE CityID ANY (SELECT CityID FROM BranchMaster)
  A. SELECT AccountHolderID FROM AccountHolder WHERE CityID NOT IN (SELECT CityID FROM BranchMaster)
  B. SELECT AccountHolderID FROM AccountHolder WHERE CityID ALL (SELECT CityID FROM BranchMaster)

  ---

  http://msdn.microsoft.com/en-us/library/ms188047.aspx http://msdn.microsoft.com/en-us/library/ms177682.aspx http://msdn.microsoft.com/en-us/library/ms173545.aspx

• Question 289:

  SIMULATION

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

  You need to create a query that returns the customers who represent the top five percent of the total actual sales.

  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.

  

  

  Check the answer below

  Explanation:

  1 SELECT CustomerID, N,

  2 Top5= TOP 5 PERCENT MaX (0.95)

  3 RANK() (ORDER BY N) OVER (PARTITION BY Sales)

  4

  On line 2 add TOP 5 PERCENT

  On line 3 add: RANK()

  ---

  https://docs.microsoft.com/en-us/sql/t-sql/queries/select-over-clause-transact-sql

• Question 290:

  You have a Microsoft SQL Server database. The database contains a table that is defined by the following Transact-SQL statement:

  

  

  Employee names must always start with a capital letter.

  You need to define a constraint to enforce the employee name requirement.

  Which Transact-SQL statement should you use?

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