Microsoft 70-461 Online Practice Questions and Exam Preparation

Microsoft 70-461 Online Questions & Answers

• Question 121:

  When you need to operate on one row at a time, what are the alternatives to using a cursor?

  A. Using the FOR EACH looping construct.
  B. Retrieving the minimum and maximum keys, and then looping with a counter that starts with the minimum and keeps being incremented by 1 in each iteration until it reaches the maximum.
  C. Using a TOP (1) query ordered by the key to fetch the first row. Then use a loop while the last key returned is not NULL. In each iteration of the loop, process the current row and then use a TOP (1) query where the key is greater than the last,ordered by the key, to fetch the next row.
  D. Define a per-row SELECT trigger.
  C. Using a TOP (1) query ordered by the key to fetch the first row. Then use a loop while the last key returned is not NULL. In each iteration of the loop, process the current row and then use a TOP (1) query where the key is greater than the last,ordered by the key, to fetch the next row.

• Question 122:

  Which of the following is true about scalar UDFs?

  A. Scalar UDFs are both inline and multistatement.
  B. Scalar UDFs return the result of a SELECT statement.
  C. Scalar UDFs can be invoked in a SELECT list or a WHERE clause.
  D. Scalar UDFs can be invoked in the FROM clause of a SELECT statement.
  C. Scalar UDFs can be invoked in a SELECT list or a WHERE clause.

  ---

  The results of a SELECT statement would be a table, and scalar UDFs do not return tables. You can invoke a scalar UDF in a SELECT list or in the conditions of a WHERE clause, anywhere a scalar value would be valid.

• Question 123:

  You've created a standard stored procedure that's not a CLR procedure. This procedure has several parameters, some of which are output parameters. Which of the following CANNOT be passed back as an output parameter from a stored procedure?

  A. Text
  B. image
  C. Ntext
  D. Any of these
  D. Any of these

  ---

  Unless you create a CLR procedure, you can use these data types are return values.

• Question 124:

  You are designing an order entry system that uses an SQL Server database. The database includes the following tables:

  

  You need to ensure that Orders are added to the Orders table only for customers that have an account balance of zero.

  How should you complete the relevant Transact-SQL statement? To answer, select the correct Transact-SQL statement from each list in the answer area.

  Hot Area:

  

  

  ---

  The Transact SQL CREATE TRIGGER command creates a DML, DDL, or logon trigger. A trigger is a special kind of stored procedure that automatically executes when an event occurs in the database server. DML triggers execute when a

  user tries to modify data through a data manipulation language (DML) event. DML events are INSERT, UPDATE, or DELETE statements on a table or view. These triggers fire when any valid event is fired, regardless of whether or not any

  table rows are affected.

  Partial syntax is:

  CREATE TRIGGER [ schema_name . ]trigger_name

  ON { table | view }

  [ WITH [ ,...n ] ]

  { FOR | AFTER | INSTEAD OF }

  { [ INSERT ] [ , ] [ UPDATE ] [ , ] [ DELETE ] }

• Question 125:

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

• Question 126:

  You create a table that has three columns named StudentCode, SubjectCode, and Marks. The Marks column records grades for students expressed as a percentage. The table has marks obtained by 50 students for various subjects.

  You need to retrieve the StudentCode and Marks for students who scored the lowest percentage for each subject.

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

• Question 127:

  You administer a Microsoft SQL Server server. You plan to deploy new features to an application. You need to evaluate existing and potential clustered and non-clustered indexes that will improve performance.

  What should you do?

  A. Query the sys.dm_db_index_usage_stats DMV.
  B. Query the sys.dm_db_missing_index_details DMV.
  C. Use the Database Engine TuningAdvisor.
  D. Query the sys.dm_db_missing_index_columns DMV.
  C. Use the Database Engine TuningAdvisor.

• Question 128:

  Your database contains two tables named DomesticSalesOrders and InternationalSalesOrders. Both tables contain more than 100 million rows. Each table has a Primary Key column named SalesOrderId. The data in the two tables is distinct from one another. Business users want a report that includes aggregate information about the total number of global sales and total sales amounts. You need to ensure that your query executes in the minimum possible time. Which query should you use?

  A. SELECT COUNT(*) AS NumberOfSales, SUM(SalesAmount) AS TotalSalesAmount FROM (SELECT SalesOrderId, SalesAmountFROM DomesticSalesOrders UNION ALL SELECT SalesOrderId, SalesAmountFROM InternationalSalesOrder s ) AS p
  B. SELECT COUNT(*) AS NumberOfSales, SUM(SalesAmount) AS TotalSalesAmount FROM (SELECT SalesOrderId, SalesAmountFROM DomesticSalesOrders UNION SELECT SalesOrderId, SalesAmountFROM InternationalSalesOrder s ) AS p
  C. SELECT COUNT(*) AS NumberOfSales, SUM(SalesAmount) AS TotalSalesAmountFROM DomesticSalesOrders UNION SELECT COUNT(*) AS NumberOfSales, SUM(SalesAmount) AS TotalSalesAmountFROM InternationalSalesOrders
  D. SELECT COUNT(*) AS NumberOfSales, SUM(SalesAmount) AS TotalSalesAmountFROM DomesticSalesOrders UNION ALL SELECT COUNT(*) AS NumberOfSales, SUM(SalesAmount) AS TotalSalesAmountFROM InternationalSalesOrders
  A. SELECT COUNT(*) AS NumberOfSales, SUM(SalesAmount) AS TotalSalesAmount FROM (SELECT SalesOrderId, SalesAmountFROM DomesticSalesOrders UNION ALL SELECT SalesOrderId, SalesAmountFROM InternationalSalesOrder s ) AS p

  ---

  http://msdn.microsoft.com/en-us/library/ms180026.aspx http://blog.sqlauthority.com/2009/03/11/sql-server-difference-between-union-vs-union-all-optimal- performance- comparison/

• Question 129:

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

  

  You have an application named Appl. You have a parameter named @Count that uses the int data type. App1 is configured to pass @Count to a stored procedure.

  You need to create a stored procedure named usp_Customers for App1 that returns only the number of rows specified by the @Count parameter.

  The solution must NOT use BEGIN, END, or DECLARE statements.

  Part of the correct Transact-SQL statement has been provided in the answer area.

  Complete the Transact-SQL statement

  

  Check the answer below

  CREATE PROCEDURE usp_Customers @Count int SELECT TOP(@Count) Customers.LastName FROM Customers ORDER BY Customers.LastName

• Question 130:

  You develop a database application. You create four tables. Each table stores different categories of products. You create a Primary Key field on each table. You need to ensure that the following requirements are met.

  -

  The fields must use the minimum amount of space.

  -

  The fields must be an incrementing series of values.

  -

  The values must be unique among the four tables. What should you do?

  A. Create a ROWVERSION column.
  B. Create a SEQUENCE object that uses the INTEGER data type.
  C. Use the INTEGER data type along with IDENTITY.
  D. Use the UNIQUHDENTTFIER data type along with NEWSEQUENTIALID().
  E. Create a TIMESTAMP column.
  B. Create a SEQUENCE object that uses the INTEGER data type.

  ---

  It takes less space than the UNIQUEIDENTIFIER datatype (16Bytes) vs INT (4Bytes). It would be unique across whatever calls it, because a SEQUENCE is an independent object, getting its next value with the NEXT VALUE FOR SeqName,

  defining a constraint on the PrimaryKey of all tables

  as:

  ALTER TABLE tname

  ADD CONSTRAINT DFT_tname_cname

  DEFAULT(NEXT VALUE FOR SeqName) FOR cname;