Microsoft

EXAM - 70-461
Querying Microsoft SQL Server 2012




TYPE: DEMO

http://www.examskey.com/70-461.html



Complete collection of 70-461 Exam's Question and answers.
1
http://www.ExamsKey.com

---

Question: 1

Note: This question is part of a series of questions that use the same set of answer choices. An
answer choice may be correct for more than one question in the series. 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 retrieve the students who
scored the highest marks for each subject along with the marks. Which Transact-SQL query should
you use?

A.
SELECT StudentCode as Code,
RANK() OVER (ORDER BY AVG (Marks) DESC) AS Value FROM StudentMarks GROUP BY
StudentCode
B.
SELECT Id, Name, Marks, DENSE_RANK() OVER (ORDER BY Marks DESC) AS Rank FROM
StudenMarks
C.
SELECT StudentCode as Code,
DENSE_RANK() OVER (ORDER BY AVG (Marks) DESC) AS Value FROM StudentMarks GROUP BY
StudentCode
D.
SELECT StudentCode as Code,
NTILE(2) OVER (ORDER BY AVG(Marks) DESC) AS Value FROM StudentMarks
GROUP BY StudentCode
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
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
G.
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
H.
SELECT StudentCode AS Code,Marks AS Value FROM
(SELECT StudentCode, Marks AS Marks,
RANK () OVER (PARTITION BY StudentCode ORDER BY Marks DESC) AS Rank FROM
StudentMarks)
tmp WHERE Rank = 1
Complete collection of 70-461 Exam's Question and answers.
2
http://www.ExamsKey.com

---

Answer: C



Question: 2

Note: This question is part of a series of questions that use the same set of answer choices. An
answer choice may be correct for more than one question in the series. 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 top half of the
students arranged by their average marks must be given a rank of 1 and the remaining students
must be given a rank of 2. Which Transact-SQL query should you use?

A.
SELECT StudentCode as Code,
RANK() OVER (ORDER BY AVG (Marks) DESC) AS Value FROM StudentMarks GROUP BY
StudentCode
B.
SELECT Id, Name, Marks, DENSE_RANK() OVER (ORDER BY Marks DESC) AS Rank FROM
StudenMarks
C.
SELECT StudentCode as Code,
DENSE_RANK() OVER (ORDER BY AVG (Marks) DESC) AS Value FROM StudentMarks GROUP BY
StudentCode
D.
SELECT StudentCode as Code,
NTILE(2) OVER (ORDER BY AVG(Marks) DESC) AS Value FROM StudentMarks
GROUP BY StudentCode
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
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
G.
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
H.
SELECT StudentCode AS Code,Marks AS Value FROM
(SELECT StudentCode, Marks AS Marks,
Complete collection of 70-461 Exam's Question and answers.
3
http://www.ExamsKey.com

---

RANK () OVER (PARTITION BY StudentCode ORDER BY Marks DESC) AS Rank FROM
StudentMarks)
tmp WHERE Rank = 1
Answer: A



Question: 3

Note: This question is part of a series of questions that use the same set of answer choices. An
answer choice may be correct for more than one question in the series. 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 fol owing
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?

A.
SELECT StudentCode as Code,
RANK() OVER (ORDER BY AVG (Marks) DESC) AS Value FROM StudentMarks GROUP BY
StudentCode
B.
SELECT Id, Name, Marks, DENSE_RANK() OVER (ORDER BY Marks DESC) AS Rank FROM
StudenMarks
C.
SELECT StudentCode as Code,
DENSE_RANK() OVER (ORDER BY AVG (Marks) DESC) AS Value FROM StudentMarks GROUP BY
StudentCode
D.
SELECT StudentCode as Code,
NTILE(2) OVER (ORDER BY AVG(Marks) DESC) AS Value FROM StudentMarks
GROUP BY StudentCode
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
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
G.
SELECT StudentCode AS Code,Marks AS Value FROM
(SELECT StudentCode, Marks AS Marks,
Complete collection of 70-461 Exam's Question and answers.
4
http://www.ExamsKey.com

---

RANK() OVER (PARTITION BY StudentCode ORDER BY Marks ASC) AS Rank FROM StudentMarks)
tmp WHERE Rank = 1
H.
SELECT StudentCode AS Code,Marks AS Value FROM
(SELECT StudentCode, Marks AS Marks,
RANK () OVER (PARTITION BY StudentCode ORDER BY Marks DESC) AS Rank FROM
StudentMarks)
tmp WHERE Rank = 1
Answer: D



Question: 4

The following section presents questions in which you are required to input a line or lines of code as
a response.
To input your response:
Type your response into the free text entry field in the bottom half of the screen. You may input
multiple lines of code. More than one correct code response may achieve the same result. Your
response will be compared to this list of possibilities. You will receive credit if your response matches
any of these possible solutions.
To validate code syntax:
After completing your code entry, click on the Check Syntax button. This wil validate the code syntax
(such as SQL commands) and values (such as table names and variable names) used in your
response. If there are any errors, they wil appear in the window next to the Check Syntax button.
You may change your code entry and revalidate the syntax as many times as desired. Note that
Check Syntax does not validate whether you have answered the question correctly. It simply
validates the accuracy of your syntax.
To view available command keywords:
Click on the Keywords button to view a list of command keywords. This is a general list provided for
reference and is not limited to commands used in the question. You have a view that was created by
using the fol owing code:

You need to create an inline table-valued function named Sales.fn_OrdersByTerritory. Sales.fn_
OrdersByTerritory must meet the fol owing requirements:
* Accept the @T integer parameter.
* Use one-part names to reference columns.
* Filter the query results sorted by SalesTerritoryID.
* Return the columns in the same orders as the order used in OrdersByTerritory
Which code segment should you use?
To answer, type the correct code in the answer area.
Answer: PENDING
Complete collection of 70-461 Exam's Question and answers.
5
http://www.ExamsKey.com

---

Question: 5

The following section presents questions in which you are required to input a line or lines of code as
a response.
To input your response:
Type your response into the free text entry field in the bottom half of the screen. You may input
multiple lines of code. More than one correct code response may achieve the same result. Your
response will be compared to this list of possibilities. You will receive credit if your response matches
any of these possible solutions.
To validate code syntax:
After completing your code entry, click on the Check Syntax button. This wil validate the code syntax
(such as SQL commands) and values (such as table names and variable names) used in your
response. If there are any errors, they wil appear in the window next to the Check Syntax button.
You may change your code entry and revalidate the syntax as many times as desired. Note that
Check Syntax does not validate whether you have answered the question correctly. It simply
validates the accuracy of your syntax.
To view available command keywords:
Click on the Keywords button to view a list of command keywords. This is a general list provided for
reference and is not limited to commands used in the question.
You have a database that contains the tables shown in the exhibit. (Click the Exhibit button.)
You deploy a new server that has SQL Server 2012 installed.
You need to create a table named Sales.OrderDetails on the new server. Sales.OrderDetails must
meet the following requirements:
* Write the results to a disk.
* Contain a new column named LineItemTotal that stores the product of ListPrice and Quantity for
each row.
* The code must NOT use any object delimiters.
The solution must ensure that LineItemTotal is stored as the last column in the table. Which code
segment should you use?
To answer, type the correct code in the answer area.
Complete collection of 70-461 Exam's Question and answers.
6
http://www.ExamsKey.com

---

Answer:
Complete collection of 70-461 Exam's Question and answers.
7
http://www.ExamsKey.com

---

Complete collection of 70-461 Exam's Question and answers.
8
http://www.ExamsKey.com

---

Thank You for Trying Our Product

Microsoft

EXAM - 70-461
Querying Microsoft SQL Server 2012



TYPE: DEMO

http://www.examskey.com/70-461.html



Complete collection of 70-461 Exam's Question and answers.
9
http://www.ExamsKey.com

---

Document Outline

• Querying Microsoft SQL Server 2012
• Querying Microsoft SQL Server 2012

---