Chat with us

AllExam Dumps

DUMPS, FREE DUMPS, VCP5 DUMPS| VMWARE DUMPS, VCP DUMPS, VCP4 DUMPS, VCAP DUMPS, VCDX DUMPS, CISCO DUMPS, CCNA, CCNA DUMPS, CCNP DUMPS, CCIE DUMPS, ITIL, EXIN DUMPS,


DOWNLOAD & READ Free Dumps For Microsoft- 70-461



You are reading online questions and answers for Microsoft Exam 70-461 (Querying Microsoft SQL Server 2012 ) 100% free... Please share this site which will help this community to grow...

Question ID 15022

You develop a Microsoft SQL Server 2012 database that contains a table named Products.
The Products table has the following definition:

You need to create an audit record only when either the RetailPrice or WholeSalePrice
column is updated.
Which Transact-SQL query should you use?

Option A

CREATE TRIGGER TrgPriceChange ON Products FOR UPDATE AS IF CCLUMNS_CHANGED(RetailPrice, WholesalePrice) - - Create Audit Records

Option B

CREATE TRIGGER TrgPriceChange ON Products FOR UPDATE AS IF EXISTS(SELECT RetailPrice from inserted) OR EXISTS (SELECT WholeSalePnce FROM inserted) - - Create Audit Records

Option C

CREATE TRIGGER TrgPriceChange ON Products FOR UPDATE AS IF COLUMNS_UPDATED(RetailPrice, WholesalePrice) - - Create Audit Records

Option D

 CREATE TRIGGER TrgPriceChange ON Products FOR UPDATE AS IF UPDATE(RetailPrice) OR UPDATE(WholeSalePrice) - - Create Audit Records

Correct Answer D
Description Reference: http://msdn.microsoft.com/en-us/library/bb510663.aspx Reference: http://msdn.microsoft.com/en-us/library/ms186329.aspx
Update Date and Time 2017-11-18 06:24:16

Question ID 15023

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?

Option A

SELECT StudentCode as Code, RANK() OVER(ORDER BY AVG (Marks) DESC) AS Value FROM StudentMarks GROUP BY StudentCode

Option B

SELECT Id, Name, Marks, DENSE_RANK() OVER(ORDER BY Marks DESC) AS Rank FROM StudentMarks

Option C

SELECT StudentCode as Code, DENSE_RANK() OVER(ORDER BY AVG (Marks) DESC) AS Value FROM StudentMarks GROUP BY StudentCode

Option D

SELECT StudentCode as Code, NTILE(2) OVER(ORDER BY AVG (Marks) DESC) AS Value FROM StudentMarks GROUP BY StudentCode

Option E

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

Option F

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

Correct Answer A
Description Reference: http://msdn.microsoft.com/en-us/library/ms189798.aspx
Update Date and Time 2017-11-18 06:25:36