Microsoft 70-461 Online Practice Questions and Exam Preparation

Microsoft 70-461 Online Questions & Answers

• Question 191:

  DRAG DROP

  You are developing a Microsoft SQL Server database:

  You have the following requirements:

  1. Data should use the least storage space.

  2. Data should not lose precision.

  Which storage type or types should you use? To answer, drag the appropriate storage types to the correct uses. Each type 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: FILESTREAM

  FILESTREAM integrates the SQL Server Database Engine with an NTFS or ReFS file systems by storing varbinary(max) binary large object (BLOB) data as files on the file system.

  Box 2: NUMERIC

  Numeric data types that have fixed precision and scale.

  Box 3: BIGINT

  Need bigint for 3 billion unique values.

  

  Box 4: BIT

  The SQL Server Database Engine optimizes storage of bit columns. If there are 8 or fewer bit columns in a table, the columns are stored as 1 byte. If there are from 9 up to 16 bit columns, the columns are stored as 2 bytes, and so on.

  References:

  https://docs.microsoft.com/en-us/sql/relational-databases/blob/filestream-sql-server

  https://docs.microsoft.com/en-us/sql/t-sql/data-types/decimal-and-numeric-transact-sql https://docs.microsoft.com/en-us/sql/t-sql/data-types/int-bigint-smallint-and-tinyint-transact-sql https://docs.microsoft.com/en-us/sql/t-sql/data-types/bit-transact-sql

• Question 192:

  Complete the missing word below. A ____ is a special kind of stored procedure that executes automatically when a user attempts the specified data-modification statement on the specified table.

  A. Constraint
  B. DLL
  C. View
  D. Trigger
  D. Trigger

• Question 193:

  In an INSERT SELECT statement, how do you generate sequence values in specific order?

  A. Use the OVER clause in the NEXT VALUE FOR function.
  B. Specify an ORDER BY clause at the end of the query.
  C. Use TOP (100) PERCENT and ORDER BY in the query.
  D. Use TOP (9223372036854775807) and ORDER BY in the query.
  A. Use the OVER clause in the NEXT VALUE FOR function.

• Question 194:

  You administer a Microsoft SQL Server 2012 database named ContosoDb. Tables are defined as shown in the exhibit.

  

  You need to display rows from the Orders table for the Customers row having the CustomerId value set to 1 in the following XML format.

  

  Which Transact-SQL query should you use?

  A. SELECT OrderId, OrderDate, Amount, Name, Country FROM Orders INNER JOIN Customers ON Orders.CustomerId = Customers.CustomerId WHERE Customers.CustomerId = 1 FOR XML RAW
  B. SELECT OrderId, OrderDate, Amount, Name, Country FROM Orders INNER JOIN Customers ON Orders.CustomerId = Customers.CustomerId WHERE Customers=CustomerId = 1 FOR XML RAW, ELEMENTS
  C. SELECT OrderId, OrderDate, Amount, Name, Country FROM Orders INNER JOIN Customers ON Orders.CustomerId = Customers.CustomerId WHERE Customers.CustomerId = 1 FOR XML AUTO
  D. SELECT OrderId, OrderDate, Amount, Name, Country FROM Orders INNER JOIN Customers ON Orders.CustomerId = Customers.CustomerId WHERE Customers.CustomerId= 1 FOR XML AUTO, ELEMENTS
  E. SELECT Name, Country, OrderId, OrderDate, Amount FROM Orders INNER JOIN Customers ON Orders.CustomerId= Customers.CustomerId WHERE Customers.CustomerId= 1 FOR XML AUTO
  F. SELECT Name, Country, Orderld, OrderDate, Amount FROM Orders INNER JOIN Customers ON Orders.CustomerId= Customers.CustomerId WHERE Customers.CustomerId= 1 FOR XML AUTO, ELEMENTS
  G. SELECT Name AS '@Name', Country AS '@Country', OrderId, OrderDate, Amount FROM Orders INNER JOIN Customers ON Orders.CustomerId= Customers.CustomerId WHERE Customers.CustomerId= 1 FOR XML PATH ('Customers')
  H. SELECT Name AS 'Customers/Name', Country AS 'Customers/Country', OrderId, OrderDate, Amount FROM Orders INNER JOIN Customers ON Orders.CustomerId= Customers.CustomerId WHERE Customers.CustomerId= 1 FOR XML PATH ('Customers')
  C. SELECT OrderId, OrderDate, Amount, Name, Country FROM Orders INNER JOIN Customers ON Orders.CustomerId = Customers.CustomerId WHERE Customers.CustomerId = 1 FOR XML AUTO

  ---

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

• Question 195:

  You administer a Microsoft SQL Server 2012 database that includes a table named Products. The Products table has columns named Productld, 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:

  1.

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

  2.

  Retain only the newest Products row.

  Which Transact-SQL query should you use?

  A. WITH CTEDupRecords AS ( SELECT MAX(CreatedDateTime) AS CreatedDateTime, ProductName FROM Products GROUP BY ProductName HAVING COUNT(*) > 1 ) DELETE p FROM Products p JOIN CTEDupRecords cte ON ProductName = cte.ProductName AND p.CreatedDateTime > cte.CreatedDateTime
  B. WITH CTEDupRecords AS ( SELECT MAX(CreatedDateTime) AS CreatedDateTime, ProductName FROM Products GROUP BY ProductName HAVING COUNT(*) > 1 ) DELETE p FROM Products p JOIN CTEDupRecords cte ON cte.ProductName = p.ProductName AND cte.CreatedDateTime > p.CreatedDateTime
  C. WITH CTEDupRecords AS ( SELECT MIN(CreatedDateTime) AS CreatedDateTime, ProductName FROM Products GROUP BY ProductName ) DELETE p FROM Products p JOIN CTEDupRecords cte ON ProductName = cte.ProductName
  D. WITH CTEDupRecords AS ( SELECT MAX(CreatedDateTime) AS CreatedDateTime, ProductName FROM Products GROUP BY ProductName HAVING COUNT(*) > 1 ) DELETE p FROM Products p JOIN CTEDupRecords cte ON ProductName = cte.ProductName
  B. WITH CTEDupRecords AS ( SELECT MAX(CreatedDateTime) AS CreatedDateTime, ProductName FROM Products GROUP BY ProductName HAVING COUNT(*) > 1 ) DELETE p FROM Products p JOIN CTEDupRecords cte ON cte.ProductName = p.ProductName AND cte.CreatedDateTime > p.CreatedDateTime

• Question 196:

  You have a database that contains the tables as shown in the exhibit. (Click the Exhibit button.)

  

  You have the following query:

  You need to recreate the query to meet the following requirements:

  Reference columns by using one-part names only.

  Sort aggregates by SalesTerritoryID, and then by ProductID.

  Order the results in descending order from SalesTerritoryID to ProductID.

  The solution must use the existing SELECT clause and FROM clause.

  Which code segment should you use?

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

  Check the answer below

  SELECT SalesTerritoryID,

  ProductID,

  AVG(UnitPrice),

  MAX(OrderQty),

  MAX(DiscountAmount)

  FROM Sales.Details

  GROUP BY SalesTerritoryID , ProductID

  ORDER BY SalesTerritoryID DESC, ProductID DESC

• Question 197:

  You are writing a set of queries against a FILESTREAM-enabled database. You create a stored procedure that will update multiple tables within a transaction. You need to ensure that if the stored procedure raises a run-time error, the entire transaction is terminated and rolled back. Which Transact-SQL statement should you include at the beginning of the stored procedure?

  A. SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
  B. SET XACT_ABORT OFF
  C. SET TRANSACTION ISOLATION LEVEL SNAPSHOT
  D. SET IMPLICIT_TRANSACTIONS ON
  E. SET XACT_ABORT ON
  F. SET IMPLICIT TRANSACTIONS OFF
  E. SET XACT_ABORT ON

  ---

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

• Question 198:

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

  The application allows ABC.com employees from different regions to execute a store procedure based on their location.

  The location of the ABC.com employees are determined by an input parameter named @location. You want to develop a process that will execute the correct stored procedure for every ABC.com employee based on their location.

  How would you accomplish this?

  A. You should make use of a client cursor.
  B. You should make use of a static cursor.
  C. You should make use of a forward-only cursor.
  D. You should make use of a dynamic cursor.
  E. You should make use of a keyset cursor.
  E. You should make use of a keyset cursor.

  ---

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

• Question 199:

  Which full-text search elements can you use to prevent indexing noisy words? (Choose all that apply.)

  A. Stopwords
  B. Thesaurus
  C. Stemmer
  D. Stoplists
  A. Stopwords
  D. Stoplists

  ---

  Stopwords include noisy words. You group stopwords in stoplists.

• Question 200:

  You work as a database developer at ABC.com. You want to create a Transact-SQL query will call a table-valued function for every row the query returns.

  How would you accomplish this task?

  A. You should make use of a UNION.
  B. You should make use of a CONVERT function.
  C. You should make use of an INNER JOIN.
  D. You should make use of a Trigger.
  E. You should make use of a CAST function.
  F. You should make use of an OUTER JOIN.
  G. You should make use of a CROSS APPLY.
  H. You should make use of the FORMAT function.
  G. You should make use of a CROSS APPLY.