Microsoft 70-461 Online Practice Questions and Exam Preparation

Microsoft 70-461 Online Questions & Answers

• Question 131:

  You create a table that has the StudentCode, SubjectCode, and Marks columns to record mid-year marks for students. The table has marks obtained by 50 students for various subjects.

  You need to ensure that the following requirements are met:

  Students must be ranked based on their average marks.

  If one or more students have the same average, incremental ranks must be given based on the order they are created.

  Ranks must be sequential without gaps in between.

  Which Transact-SQL query should you use?

  

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

  ---

  The ROW_NUMBER numbers the output of a result set. More specifically, returns the sequential number of a row within a partition of a result set, starting at 1 for the first row in each partition.

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

• Question 132:

  You are developing a database that will contain price information.

  You need to store the prices that include a fixed precision and a scale of six digits.

  Which data type should you use?

  A. Real
  B. Small money
  C. Money
  D. Decimal
  D. Decimal

• Question 133:

  DRAG DROP

  You use Microsoft SQL Server to develop a database application.

  You execute a stored procedure named PriceIncreasePct that has multiple statements.

  You need to ensure that the calling application can find out the exact error message if an exception occurs.

  Which five 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:

  

  

  ---

  @@Error returns the error number for the last Transact-SQL statement executed.

  References: https://docs.microsoft.com/en-us/sql/t-sql/language-elements/try-catch-transact-sql https://docs.microsoft.com/en-us/sql/t-sql/functions/error-transact-sql

• Question 134:

  You develop a Microsoft SQL Server 2012 server database that supports an application. The application contains a table that has the following definition:

  CREATE TABLE Inventory (

  ItemID int NOT NULL PRIMARY KEY,

  ItemsInStore int NOT NULL,

  ItemsInWarehouse int NOT NULL)

  You need to create a computed column that returns the sum total of the ItemsInStore and ItemsInWarehouse values for each row. The new column is expected to be queried heavily, and you need to be able to index the column.

  Which Transact-SQL statement should you use?

  A. ALTER TABLE Inventory ADD TotalItems AS ItemslnStore + ItemsInWarehouse
  B. ALTER TABLE Inventory ADD TotalItems AS ItemsInStore + ItemsInWarehouse PERSISTED
  C. ALTER TABLE Inventory ADD TotalItems AS SUM(ItemsInStore,ItemsInWarehouse) PERSISTED
  D. ALTER TABLE Inventory ADD TotalItems AS SUM(ItemsInStore, ItemsInWarehouse)
  B. ALTER TABLE Inventory ADD TotalItems AS ItemsInStore + ItemsInWarehouse PERSISTED

  ---

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

• Question 135:

  SIMULATION

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

  1. The query must return a list of salespeople ranked by amount of sales and organized by postal code.

  2. The salesperson who has the highest amount of sales must be ranked first.

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

  Check the answer below

  1 SELECT RowNumber() OVER(PARTITION BY PostalCode ORDER BY SalesYTd DESC) AS "Ranking", 2 p.LastName, s.SalesYTD, a.PostalCode 3 FROM Sales.SalesPerson AS a etc

  On line 1 add: RowNumber One line 1 add: PARTITION BY

  ROW_NUMBER() numbers the output of a result set. More specifically, returns the sequential number of a row within a partition of a result set, starting at 1 for the first row in each partition.

  SYNTAX for OVER:

  OVER ( [ ] [ ] [ ]

  )

  Example: Using the OVER clause with the ROW_NUMBER function

  The following example returns the ROW_NUMBER for sales representatives based on their assigned sales quota.

  SELECT ROW_NUMBER() OVER(ORDER BY SUM(SalesAmountQuota) DESC) AS RowNumber,

  FirstName, LastName, CONVERT(varchar(13), SUM(SalesAmountQuota),1) AS SalesQuota FROM dbo.DimEmployee AS e INNER JOIN dbo.FactSalesQuota AS sq

  ON e.EmployeeKey = sq.EmployeeKey WHERE e.SalesPersonFlag = 1 GROUP BY LastName, FirstName; Here is a partial result set.

  RowNumber FirstName LastName SalesQuota

  1

  Jillian

  Carson

  12,198,000.00

  2

  Linda

  Mitchell

  11,786,000.00

  3 4

  Michael Jae

  Blythe Pak

  11,162,000.00 10,514,000.00

  ---

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

• Question 136:

  You administer a Microsoft SQL Server database that includes a table named Products. The Products table has columns named ProductId, ProductName, and CreatedDateTime.

  The table contains a unique constraint on the combination of ProductName and CreatedDateTime.

  You need to modify the Products table to meet the following requirements:

  Remove all duplicates of the Products table based on the ProductName column.

  Retain only the oldest Products row.

  Which Transact-SQL query should you run?

  

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

  ---

  You use the WHERE clause to delete a set of rows Incorrect Answers:

  A: Instead use the cte.CreatedDateTime > p.CreateDateTime condition.

  C: Need to have a AND cte.CreatedDateTime > p.CreateDateTime condition.

  D: Use MAX not MIN, and we need to have a AND cte.CreatedDateTime > p.CreateDateTime condition.

  References: https://docs.microsoft.com/en-us/sql/t-sql/statements/delete-transact-sql

• Question 137:

  As part of a new HR project you're creating several stored procedures that will add logging information to the logs table. This logging information is very detailed and should contain carriage returns to make paragraphs more readable. How can you add carriage returns to text when inserting into a table?

  A. Use CASE
  B. Use CHAR
  C. Use TEXTPTR
  D. Use COS
  B. Use CHAR

  ---

  You can use CHAR(13) to add carriage returns. The CHAR function converts an int ASCII code to a character.

• Question 138:

  You work as a database developer at ABC.com. You need to store Microsoft Word documents in a SQL Server 2012 database table.

  The Word documents must not be accessed by Windows applications that do not use Transact- SQL queries.

  How would accomplish this task?

  A. You should store the documents using the VARBINARY(MAX) datatype.
  B. You should store the documents using the VARCHAR(MAX) datatype.
  C. You should store the documents using the FILESTREAM datatype.
  D. You should store the documents using the NVARCHAR(MAX) datatype.
  A. You should store the documents using the VARBINARY(MAX) datatype.

• Question 139:

  You have a view that was created by using the following code:

  

  You need to create an inline table-valued function named Sales.fn_OrdersByTerritory. Sales.fn_OrdersByTerritory must meet the following requirements:

  1. Use one-part names to reference columns.

  2. Return the columns in the same order as the order used in OrdersByTerritoryView.

  Part of the correct T-SQL statement has been provided in the answer area. Provide the complete code.

  

  Check the answer below

  CREATE FUNCTION Sales.fn_OrdersByTerritory (@T int) RETURNS TABLE AS RETURN ( SELECT OrderID, OrderDate, SalesTerritoryID, TotalDue FROM Sales.OrdersByTerritory WHERE SalesTerritoryID=@T )

• Question 140:

  DRAG DROP

  You develop a Microsoft SQL Server database that contains a table named FactoryOrder defined by the following Transact-SQL statements:

  

  The database contains the following user defined function:

  

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

  1. Reports on factory orders for a specified date.

  2. Adds 2 hours to the OrderTime when reporting the FinishedTime.

  3. Executes the most efficient execution plan each time it is called.

  4. Minimizes read operations.

  Which command or commands should you use to accomplish this objective? To answer, drag the appropriate commands to the correct positions. Answer choices 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: