Free Download Pass4sure Microsoft MCTS SQL Server 70-461 Dumps

MICROSOFT

Querying Microsoft SQL Server 2012: 70-461 Exam
70-461 Questions & Answers
Exam Code: 70-461
Exam Name: Querying Microsoft SQL Server 2012
Q & A: 100 Q&As

QUESTION 1
You administer a Microsoft SQL Server 2012 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.
<row OrderId= "1" orderDate="2000-01-01T00:00:00", Amount="3400.00" Name="Customer A"
Country="Australia" />
<row OrderId="2" OrderDate="2001-01-01T00:00:00" Amount="4300.00" Name="Customer A" Country-
"Australia" />
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, Crderld, 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= 1FOR XML PATH (‘Customers’)
Answer: A

QUESTION 2
You administer a Microsoft SQL Server 2012 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
CustomerIdvalue 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.CustomerXd – 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, Crderld, OrderDate, Amount FROM Orders INNER JOIN Customers
ON Orders.CustomerId= Customers.CustomerId WHERE Customers.CustomerId= 1
FOR XML AUTO, ELEMENTSG. 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’)
Answer: C

QUESTION 3
You administer a Microsoft SQL Server 2012 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.CustomerXd – 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- 1FOR XML AUTO
F. SELECT Name, Country, Crderld, 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’)
Answer: E

QUESTION 4
You create a table that has the StudentCode, SubjectCode, and Marks columns to record midyear 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 StudentMarks
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 FRCM (
SELECT StudentCode, Marks AS Marks,
RANK() OVER (PARTITION BY SubjectCode ORDER 3Y Marks DESC) AS Rank FRCM 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,
RANXO OVER (PARTITION BY StudentCode ORDER BY Marks DESC) AS Rank FROM StudentMarks) tmp
WHERE Rank = 1
Answer: D

QUESTION 5
You administer a Microsoft SQL Server 2012 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, ELEMENTSC. 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.CustomerXd – 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, Crderld, 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’)
Answer: D

…you can go to http://www.lead2pass.com/70-461.html to download the full version.

try_now

, , ,