Microsoft 70-461 Online Practice Questions and Exam Preparation

Microsoft 70-461 Online Questions & Answers

• Question 11:

  Your database contains a table named Products that has columns named ProductID and Name.

  You want to write a query that retrieves data from the Products table sorted by Name listing 15 rows at a time.

  You need to view rows 31 through 45.

  Which Transact-SQL query should you create?

  

  A. OptionA
  B. OptionB
  C. OptionC
  D. OptionD
  C. OptionC

  ---

  The OFFSET-FETCH clause provides you with an option to fetch only a window or page of results from the result set. OFFSET-FETCH can be used only with the ORDER BY clause.

  Example: Skip first 10 rows from the sorted resultset and return next 5 rows.

  SELECT First Name + ' ' + Last Name FROM Employees ORDER BY First Name OFFSET 10 ROWS FETCH NEXT 5 ROWS ONLY;

  References: https://technet.microsoft.com/en-us/library/gg699618(v=sql.110).aspx

• Question 12:

  The table contains duplicate records based on the combination of values in the surName, givenName, and dateOfBirth fields.

  You need to remove the duplicate records.

  How should you complete the relevant Transact-SQL statements? To answer, drag the appropriate code segment or segments to the correct location or locations in the answer area. Each code 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:

  

  You develop an SQL Server database. The database contains a table that is defined by the following T-SQL statements:

  

  

  ---

  let us write a query which will delete all duplicate data in one shot. We will use a CTE (Common Table Expression) for this purpose. We will read in future posts what a CTE is and why it is used. On a lighter note, CTE's can be imagined as equivalent to temporary result sets that can be used only in an underlying SELECT, INSERT, UPDATE, DELETE or CREATE VIEW statement. ;WITH CTE AS ( SELECT Name , City , [State] , ROW_NUMBER() OVER(PARTITION BY Name, City, [State] ORDER BY [Name]) AS Rnum FROM Persons ) DELETE FROM CTE WHERE Rnum <> 1 In the code by saying WHERE Rnum <> 1, we are asking SQL Server to keep all the records with Rank 1, which are not duplicates, and delete any other record. After executing this query in SQL Server Management Studio, you will end up with no duplicates in your table. To confirm that just run a simple query against your table.

• Question 13:

  Which of the following practices are considered bad practices? (Choose all that apply.)

  A. Aliasing columns by using the AS clause
  B. Aliasing tables by using the AS clause
  C. Not assigning column aliases when the column is a result of a computation
  D. Using * in the SELECT list
  C. Not assigning column aliases when the column is a result of a computation
  D. Using * in the SELECT list

• Question 14:

  Why is it important to use standard SQL code when possible and know what is standard and what isn't? (Choose all that apply.)

  A. It is not important to code using standard SQL.
  B. Standard SQL code is more portable between platforms.
  C. Standard SQL code is more efficient.
  D. Knowing what standard SQL code is makes your knowledge more portable.
  B. Standard SQL code is more portable between platforms.
  D. Knowing what standard SQL code is makes your knowledge more portable.

• Question 15:

  You are designing a data warehouse that uses SQL Server 2012. You are preparing to update the contents of a fact table that contains a non-clustered columnstore index. You need to run an update statement against the table. What should you do first?

  A. Pause the columnstore index.
  B. Change the recovery model of the database to Bulk-logged.
  C. Change the non-clustered columnstore index to be a clustered columnstore index.
  D. Drop the columnstore index.
  D. Drop the columnstore index.

• Question 16:

  You work as a SQL Server 2012 database developer at ABC.com. You are developing a database driven Web application.

  The application executes a store procedure based on the location of the web user.

  The location of the Web user is determined by IP Geolocation. You want to develop a process that will execute the correct stored procedure for every Web user based on the user's location.

  How would you accomplish this?

  A. You should make use of a foreach SQLCLR statement.
  B. You should make use of a scalar user-defined function.
  C. You should make use of an inline function.
  D. You should make use of a cursor.
  D. You should make use of a cursor.

• Question 17:

  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, Crderld, 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')
  F. SELECT Name, Country, Crderld, OrderDate, Amount FROM Orders INNER JOIN Customers ON Orders.CustomerId= Customers.CustomerId WHERE Customers.CustomerId= 1 FOR XML AUTO, ELEMENTS

• Question 18:

  What is the most important drawback of DMOs?

  A. You must have enough data collected from the last restart of SQL Server.
  B. DMOs are complex to use.
  C. DMOs are not available in the Standard edition of SQL Server.
  D. You have to recreate DMOs before each analysis.
  A. You must have enough data collected from the last restart of SQL Server.

• Question 19:

  You are employed as a SQL Server 2012 database developer at ABC.com. You have a stored procedure that is executed quite often. The stored procedure joins data from two tables.

  ABC.com users report that the stored procedure takes a long time to execute. You analyze the

  query plan and find that the stored procedure often makes use of table scans rather than indexes when the estimated rows do not match the actual rows on one of the tables.

  How would you optimize the performance of the stored procedure?

  A. You should make use of the KEEPIDENTITY table hint in the stored procedure.
  B. You should make use of the KEEPDEFAULTS table hint in the stored procedure.
  C. You should make use of the IGNORE_CONSTRAINTS table hint in the stored procedure.
  D. You should make use of the FORCESEEK table hint in the stored procedure.
  E. You should update statistics on the tables queried by the stored procedure.
  E. You should update statistics on the tables queried by the stored procedure.

• Question 20:

  Which operator is used when SQL Server performs a nonclustered index seek to find a row, but then also needs data from the underlying table, which is organized as a clustered index?

  A. RID Lookup
  B. Clustered Index Scan
  C. Merge Join
  D. Key Lookup
  D. Key Lookup