Microsoft 70-461 Online Practice Questions and Exam Preparation

Microsoft 70-461 Online Questions & Answers

• Question 61:

  You administer a Microsoft SQL Server 2012 database. You use an OrderDetail table that has the following definition:

  

  You need to create a non-clustered index on the SalesOrderID column in the OrderDetail table to include only rows that contain a value in the SpecialOfferID column. Which four Transact-SQL statements should you use? (To answer, move the appropriate statements from the list of statements to the answer area and arrange them in the correct order.)

  Select and Place:

  

  

• Question 62:

  Which of the following statements regarding SQL Server 2012 objects is TRUE?

  A. A user-defined data type can accept an input variable and return a table of results but cannot be used within a view.
  B. A scalar function can accept an input variable and return a table of results but cannot be used within a view.
  C. A table-valued function can accept an input variable and return a table of results but cannot be used within a view.
  D. A table-valued type can accept an input variable and return a table of results but cannot be used within a view.
  A. A user-defined data type can accept an input variable and return a table of results but cannot be used within a view.

• Question 63:

  You work as a SQL Server 2012 database developer at ABC.com. You are developing a query for a database driven Web application that allows visitors to vote for the cricket player of the week. The number of votes is stored in a table named WeeklyVotes that has columns named Week, PlayerName, Votes.

  You need to write a Transact-SQL query that returns the cricket player that received the most votes for each week, as well as the number of votes they received.

  Which of the following SELECT statement would accomplish this task?

  A. SELECT PlayerName, Votes FROM ( SELECT PlayerName, Votes, RANK () OVER (PARTITION BY PlayerName ORDER BY Votes ASC) AS Rank FROM WeeklyVotes ) AS tmp WHERE Rank = 1
  B. SELECT PlayerName, Votes FROM ( SELECT PlayerName, Votes, RANK() OVER (PARTITION BY Week ORDER BY Votes DESC) AS Rank FROM WeeklyVotes) AS tmp WHERE Rank = 1
  C. SELECT PlayerName, Votes FROM ( SELECT TOP 1 PlayerName, Votes, RANK () OVER (PARTITION BY PlayerName ORDER BY Votes ASC) AS Rank FROM WeeklyVotes ORDER BY Rank) AS tmp
  D. SELECT PlayerName, Votes FROM ( SELECT TOP 1 PlayerName, Votes, RANXO OVER (PARTITION BY PlayerName ORDER BY Votes DESC) AS Rank FROM WeeklyVotes ORDER BY Rank) AS tmp
  B. SELECT PlayerName, Votes FROM ( SELECT PlayerName, Votes, RANK() OVER (PARTITION BY Week ORDER BY Votes DESC) AS Rank FROM WeeklyVotes) AS tmp WHERE Rank = 1

• Question 64:

  What can you search for with the CONTAINS predicate? (Choose all that apply.)

  A. Inflectional forms of a word
  B. Synonyms of a searched word
  C. Translations of a word
  D. Text in which a search word is close to another search word
  E. A prefix of a word or a phrase only
  A. Inflectional forms of a word
  B. Synonyms of a searched word
  D. Text in which a search word is close to another search word
  E. A prefix of a word or a phrase only

• Question 65:

  You use a Microsoft SQL Server database that contains a table. The table has records of web requests as shown in the exhibit. (Click the Exhibit button.)

  

  Your network has three web servers that have the following IP addresses:

  10.0.0.1

  10.0.0.2

  10.0.0.3

  You need to create a query that displays the following information:

  The number of requests for each web page (UriStem) grouped by the web server (ServerIP) that served the request

  A column for each server

  Which Transact-SQL query should you use?

  

  

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

  ---

  PIVOT rotates a table-valued expression by turning the unique values from one column in the expression into multiple columns in the output, and performs aggregations where they are required on any remaining column values that are wanted in the final output.

  References: https://docs.microsoft.com/en-us/sql/t-sql/queries/from-using-pivot-and-unpivot?view=sql-server-2017

• Question 66:

  You use Microsoft SQL Server 2012 to develop a database application. You need to implement a computed column that references a lookup table by using an INNER JOIN against another table. What should you do?

  A. Reference a user-defined function within the computed column.
  B. Create a BEFORE trigger that maintains the state of the computed column.
  C. Add a default constraint to the computed column that implements hard-coded values.
  D. Add a default constraint to the computed column that implements hard-coded CASE statements.
  A. Reference a user-defined function within the computed column.

• Question 67:

  

  You create a view based on the following statement:

  You grant the Select permission to User1 for this view.

  You need to change the view so that it displays only the records that were processed in the month prior to the current month. You need to ensure that after the changes, the view functions correctly for User1.

  Which four Transact-SQL statements should you use? (To answer, move the appropriate SQL statements from the list of statements to the answer area and arrange them in the correct order.)

  Select and Place:

  

  

• Question 68:

  You have an XML schema collection named Sales.InvoiceSchema.

  You need to declare a variable of the XML type named invoice. The solution must ensure that the invoice is validated by using Sales.InvoiceSchema.

  The solution must ensure that the invoice variable is validated by using Sales.InvoiceSchema schema.

  Provide the correct code in the answer area.

  DECLARE @invoice XML(Sales.InvoiceSchema)

• Question 69:

  DRAG DROP

  You use a Microsoft Azure SQL Database instance named ContosoDb. ContosoDb contains a table named Customers that has existing records.

  The Customers table has columns named Id and Name.

  You need to create a new column in the Customer table named Status that allows null values and sets the value of the Status column to Silver for all existing records.

  Which three 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 anwer area and arrange them in the correct order.

  Select and Place:

  

  

  ---

  NULL or NOT NULL specifies whether the column can accept null values. Columns that do not allow null values can be added with ALTER TABLE only if they have a default specified or if the table is empty. NOT NULL can be specified for computed columns only if PERSISTED is also specified. If the new column allows null values and no default is specified, the new column contains a null value for each row in the table. If the new column allows null values and a default definition is added with the new column, WITH VALUES can be used to store the default value in the new column for each existing row in the table.

  References: https://docs.microsoft.com/en-us/sql/t-sql/statements/alter-table-transact-sql?view=sql-server-2017

• Question 70:

  Which of the following T-SQL statements automatically occur in the context of a transaction? (Choose all that apply.)

  A. An ALTER TABLE command
  B. A PRINT command
  C. An UPDATE command
  D. A SET command
  A. An ALTER TABLE command
  C. An UPDATE command