Microsoft 70-461 Online Practice Questions and Exam Preparation

Microsoft 70-461 Online Questions & Answers

• Question 1:

  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')
  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

• Question 2:

  DRAG DROP

  You want to add a new GUID column named ProductGUID to a table named dbo.Product that already contains data.

  ProductGUID will have a constraint to ensure that it always has a value when new rows are inserted into dbo.Product.

  You need to ensure that the new column is assigned a GUID for existing rows.

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

  Select and Place:

  

  

  ---

  Box 1:

  Existing BookGuid can be null.

  Box 2:

  Box 3: new()

  Generate a new global value

  Box 4:

  Actually, in the real world, you don't have to use WITH VALUES at the end of the statement and it works just as well. But because the question specifically states which FOUR TSQL statements to use, we have to include it.

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

• Question 3:

  You need to delete a stored procedure from the SQL Server 2012 database. The stored procedure is called MyProc. Which statement below is correct?

  A. drop MyProc
  B. delete procedure MyProc
  C. delete MyProc
  D. drop procedure MyProc
  D. drop procedure MyProc

  ---

  You must drop (delete) the stored procedure using the drop procedure statement followed by the stored procedure name.

• Question 4:

  How can SQL Server estimate the cardinality of a query?

  A. SQL Server stores the cardinality information on leaf-level pages of indexes.
  B. SQL Server quickly executes the query on 10 percent of sample data.
  C. SQL Server cannot estimate the cardinality of a query if you do not provide a table hint.
  D. SQL Server uses statistics to estimate the cardinality of a query.
  D. SQL Server uses statistics to estimate the cardinality of a query.

• Question 5:

  You develop a database for a travel application. You need to design tables and other database objects. You create a stored procedure. You need to supply the stored procedure with multiple event names and their dates as parameters. What should you do?

  A. Use the CAST function.
  B. Use the DATE data type.
  C. Use the FORMAT function.
  D. Use an appropriate collation.
  E. Use a user-defined table type.
  F. Use the VARBINARY data type.
  G. Use the DATETIME data type.
  H. Use the DATETIME2 data type.
  I. Use the DATETIMEOFFSET data type.
  J. Use the TODATETIMEOFFSET function.
  E. Use a user-defined table type.

• Question 6:

  DRAG DROP

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

  

  There are thousands of rows in the Level table, with significant duplication of data in the Color column. Seventy-five percent of the rows in the table have Color="Red". Fifteen percent of the rows in the table have Color="Yellow". The remaining ten percent have Color="Green".

  You want to normalize the color information in this table. You create a table named Colors that is defined by the following statement:

  

  You populate the new Colors table by using the following Transact-SQL statement:

  INSERT Colors (ColorName) SELECT DISTINCT [Color] FROM [Level]

  You need to ensure that the following requirements are met:

  1. The Level table uses only colors that exist in the Colors table.

  2. Data redundancy in the Level table is reduced.

  3. Data integrity is enforced during the normalization process.

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

  Select and Place:

  

  

  ---

  Step 1: Update [Level] SET ColorID=C.Color.id ..

  First update the new column ColorID (not the color column), and drop the old Column Color.

  Step 2: Add a check constraint on the new ColorID column.

  Step 3: Finally add a foreign key constraint.

• Question 7:

  You administer a Microsoft SQL Server database named Sales. The database is 3 terabytes in size. The Sales database is configured as shown in the following table.

  

  You discover that Sales_2.ndf is corrupt. You need to recover the corrupted data in the minimum amount of time. What should you do?

  A. Perform a file restore.
  B. Perform a transaction log restore.
  C. Perform a restore from a full backup.
  D. Perform a file group restore.
  A. Perform a file restore.

• Question 8:

  Which database do you have to install in order to enable the Semantic Search feature?

  A. msdb
  B. distribution
  C. semanticsdb
  D. tempdb
  C. semanticsdb

• Question 9:

  What is true about the result of a query without an ORDER BY clause?

  A. It is relational as long as other relational requirements are met.
  B. It cannot have duplicates.
  C. The order of the rows in the output is guaranteed to be the same as the insertion order.
  D. The order of the rows in the output is guaranteed to be the same as that of the clustered index.
  A. It is relational as long as other relational requirements are met.

• Question 10:

  You administer a Microsoft SQL Server database named ContosoDb. Tables are defined as shown in the exhibit. (Click the Exhibit button.)

  

  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, OrderId, 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')
  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')

  ---

  Need to use FOR XML PATH ('Customers') to get the XML output oriented towards the Customers Element.

  Example: Specifying XPath-like column names

  In the following query the ProductModelID column name specified starts with '@'. Therefore, an attribute of the element that has the corresponding column value is created in the resulting XML.

  SELECT ProductModelID AS "@id",

  Name

  FROM Production.ProductModel

  WHERE ProductModelID=122 OR ProductModelID=119

  FOR XML PATH ('ProductModelData');

  This is the result:

  All-Purpose Bike Stand

  Bike Wash

  References:

  https://docs.microsoft.com/en-us/sql/relational-databases/xml/examples-using-path-mode