Ensurepass.com : Ensure you pass the IT Exams
2018 May Microsoft Official New Released 70-461
100% Free Download! 100% Pass Guaranteed!

Querying Microsoft SQL Server 2012

Question No: 41 DRAG DROP

You have a SQL Server database named CUSTOMERS.

You need to sign a stored procedure named SelectCustomers in the CUSTOMERS database.

Which four statements should you execute in sequence? To answer, move the appropriate statements from the list of statements to the answer area and arrange them in the correct order.

image

Answer:

image

Explanation:

image

There are four steps involved in signing a module: References:https://msdn.microsoft.com/en-us/library/bb669102(v=vs.110).aspx

Question No: 42

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

image

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.

image

Which Transact-SQL query should you use?

  1. SELECT OrderId, OrderDate, Amount, Name, Country

    FROM Orders INNER JOIN Customers ON Orders.CustomerId = Customers.CustomerId WHERE Customers.CustomerId = 1

    FOR XML RAW

  2. SELECT OrderId, OrderDate, Amount, Name, Country

    FROM Orders INNER JOIN Customers ON Orders.CustomerId = Customers.CustomerId WHERE Customers=CustomerId = 1

    FOR XML RAW, ELEMENTS

  3. SELECT OrderId, OrderDate, Amount, Name, Country

    FROM Orders INNER JOIN Customers ON Orders.CustomerId = Customers.CustomerId WHERE Customers.CustomerId = 1

    FOR XML AUTO

  4. SELECT OrderId, OrderDate, Amount, Name, Country

    FROM Orders INNER JOIN Customers ON Orders.CustomerId = Customers.CustomerId WHERE Customers.CustomerId = 1

    FOR XML AUTO, ELEMENTS

  5. SELECT Name, Country, OrderId, OrderDate, Amount

    FROM Orders INNER JOIN Customers ON Orders.CustomerId= Customers.CustomerId WHERE Customers.CustomerId = 1

    FOR XML AUTO

  6. SELECT Name, Country, OrderId, OrderDate, Amount

    FROM Orders INNER JOIN Customers ON Orders.CustomerId= Customers.CustomerId WHERE Customers.CustomerId = 1

    FOR XML AUTO, ELEMENTS

  7. SELECT Name AS #39;@Name#39;, Country AS #39;@Country#39;, OrderId, OrderDate, Amount FROM Orders INNER JOIN Customers ON Orders.CustomerId= Customers.CustomerId WHERE Customers.CustomerId = 1

    FOR XML PATH (#39;Customers#39;)

  8. SELECT Name AS #39;Customers/Name#39;, Country AS #39;Customers/Country#39;, OrderId, OrderDate, Amount

FROM Orders INNER JOIN Customers ON Orders.CustomerId= Customers.CustomerId WHERE Customers.CustomerId = 1

FOR XML PATH (#39;Customers#39;)

Answer: C

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

Question No: 43

You administer a Microsoft SQL Server 2012 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?

  1. Query the sys.dm_db_index_usage_stats DMV.

  2. Query the sys.dm_db_missing_index_details DMV.

  3. Use the Database Engine Tuning Advisor.

  4. Query the sys.dm_db_missing_index_columns DMV.

Answer: C

Question No: 44

You create a table that has the StudentCode, SubjectCode, and Marks columns to record mid-year marks for students. The table has marks obtained by 50 students for various subjects.

You need to ensure that the following requirements are met:

-> Students must be ranked based on their average marks.

-> If one or more students have the same average, the same rank must be given to these students.

-> Consecutive ranks must be skipped when the same rank is assigned.

Which Transact-SQL query should you use?

  1. SELECT StudentCode as Code,

    RANK() OVER(ORDER BY AVG (Marks) DESC) AS Value

    FROM StudentMarks GROUP BY StudentCode

  2. SELECT Id, Name, Marks,

    DENSE_RANK() OVER(ORDER BY Marks DESC) AS Rank

    FROM StudentMarks

  3. SELECT StudentCode as Code,

    DENSE_RANK() OVER(ORDER BY AVG (Marks) DESC) AS Value

    FROM StudentMarks GROUP BY StudentCode

  4. SELECT StudentCode as Code,

    NTILE(2) OVER(ORDER BY AVG (Marks) DESC) AS Value

    FROM StudentMarks GROUP BY StudentCode

  5. SELECT StudentCode AS Code,Marks AS Value FROM ( SELECT StudentCode, Marks AS Marks,

    RANK() OVER(PARTITION BY SubjectCode ORDER BY Marks ASC) AS Rank

    FROM StudentMarks) tmp WHERE Rank = 1

  6. SELECT StudentCode AS Code,Marks AS Value FROM ( SELECT StudentCode, Marks AS Marks,

    RANK() OVER(PARTITION BY SubjectCode ORDER BY Marks DESC) AS Rank

    FROM StudentMarks) tmp WHERE Rank = 1

  7. SELECT StudentCode AS Code,Marks AS Value FROM ( SELECT StudentCode, Marks AS Marks,

    RANK() OVER(PARTITION BY StudentCode ORDER BY Marks ASC) AS Rank

    FROM StudentMarks) tmp WHERE Rank = 1

  8. SELECT StudentCode AS Code,Marks AS Value FROM ( SELECT StudentCode, Marks AS Marks,

RANXO OVER(PARTITION BY StudentCode ORDER BY Marks DESC) AS Rank

FROM StudentMarks) tmp WHERE Rank = 1

Answer: A

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

Question No: 45

You use Microsoft SQL Server 2012 database to develop a shopping cart application.

You need to rotate the unique values of the ProductName field of a table-valued expression

into multiple columns in the output.

Which Transact-SQL operator should you use?

  1. CROSS JOIN

  2. CROSS APPLY

  3. PIVOT

  4. UNPIVOT

Answer: C Explanation:

http://technet.microsoft.com/en-us/library/ms177634.aspx

Question No: 46 CORRECT TEXT

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

image

You need to create a query that meets the following requirements:

-> References columns by using one-part names only.

-> Groups aggregates only by SalesTerritoryID, and then by ProductID.

-> Orders the results in descending order by SalesTerritoryID and then by ProductID in descending order for both.

Part of the correct T-SQL statement has been provided in the answer area. Provide the complete code.

image

Answer: Please review the explanation part for this answer

Explanation:

SELECT SalesTerritoryID,

ProductID, AVG(UnitPrice), MAX(OrderQty) MAX(DiscountAmount) FROM Sales.Details

GROUP BY SalesTerritoryID, ProductID

ORDER BY SalesTerritoryID DESC, ProductID DESC

Question No: 47 DRAG DROP

You develop a SQL Server database for a large multinational company.

You must develop a stored procedure to loop through employee information and return data for one employee at a time.

How should you complete the stored procedure? To answer, drag the appropriate Transact-SQL segment to the correct locations in the answer area. Each Transact-SQL 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.

image

Answer:

image

Explanation:

image

Box 1: CURSOR VARYING OUTPUT

Transact-SQL stored procedures can use the cursor data type only forOUTPUT parameters. If the cursor data type is specified for a parameter, both the VARYING and OUTPUT parameters are required. If the VARYING keyword is specified for a parameter, the data type must be cursor and the OUTPUT keyword must be specified.

Box2: FORWARD_ONLY STATIC Box 3: CLOSE

Box 4: DEALLOCATE

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

Question No: 48

You have three tables that contain data for dentists, psychiatrists, and physicians. You create a view that is used to look up their email addresses and phone numbers.

The view has the following definition:

image

You need to ensure that users can update only the phone numbers and email addresses by using this view.

What should you do?

  1. Alter the view. Use the EXPAND VIEWS query hint along with each SELECT statement.

  2. Create an INSTEAD OF UPDATE trigger on the view.

  3. Drop the view. Re-create the view by using the SCHEMABINDING clause, and then create an index on the view.

  4. Create an AFTER UPDATE trigger on the view.

Answer: B

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

Question No: 49

You develop a Microsoft SQL Server 2012 database. You need to create a batch process that meets the following requirements:

-> Status information must be logged to a status table.

-> If the status table does not exist at the beginning of the batch, it must be created.

Which object should you use?

  1. Scalar user-defined function

  2. Inline user-defined function

  3. Table-valued user-defined function

  4. Stored procedure

Answer: D

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

Question No: 50

You develop a Microsoft SQL Server 2012 database that has two tables named SavingAccounts and LoanAccounts. Both tables have a column named AccountNumber of the nvarchar data type.

You use a third table named Transactions that has columns named TransactionId AccountNumber, Amount, and TransactionDate.

You need to ensure that when multiple records are inserted in the Transactions table, only the records that have a valid AccountNumber in the SavingAccounts or LoanAccounts are inserted.

Which Transact-SQL statement should you use?

  1. CREATE TRIGGER TrgValidateAccountNumber ON Transactions

    INSTEAD OF INSERT AS

    BEGIN

    INSERT INTO Transactions

    SELECT TransactionID,AccountNumber,Amount,TransactionDate FROM inserted WHERE AccountNumber IN

    (SELECT AccountNumber FROM LoanAccounts

    UNION SELECT AccountNumber FROM SavingAccounts) END

  2. CREATE TRIGGER TrgValidateAccountNumber ON Transactions

    FOR INSERT AS

    BEGIN

    INSERT INTO Transactions

    SELECT TransactionID,AccountNumber,Amount,TransactionDate FROM inserted WHERE AccountNumber IN

    (SELECT AccountNumber FROM LoanAccounts

    UNION SELECT AccountNumber FROM SavingAccounts) END

  3. CREATE TRIGGER TrgValidateAccountNumber ON Transactions

    INSTEAD OF INSERT AS

    BEGIN

    IF EXISTS (

    SELECT AccountNumber FROM inserted EXCEPT (SELECT AccountNumber FROM LoanAccounts

    UNION SELECT AccountNumber FROM SavingAccounts)) BEGIN

    ROLLBACK TRAN END

    END

  4. CREATE TRIGGER TrgValidateAccountNumber ON Transactions

FOR INSERT AS

BEGIN

IF EXISTS (

SELECT AccountNumber FROM inserted EXCEPT (SELECT AccountNumber FROM LoanAccounts

UNION SELECT AccountNumber FROM SavingAccounts)) BEGIN

ROLLBACK TRAN END

END

Answer: A

100% Ensurepass Free Download!
Download Free Demo:70-461 Demo PDF
100% Ensurepass Free Guaranteed!
Download 2018 EnsurePass 70-461 Full Exam PDF and VCE

EnsurePass ExamCollection Testking
Lowest Price Guarantee Yes No No
Up-to-Dated Yes No No
Real Questions Yes No No
Explanation Yes No No
PDF VCE Yes No No
Free VCE Simulator Yes No No
Instant Download Yes No No