ArticleField.comInternet's Fastest Growing Free Content Article Directory

70-461 Exam

Exam Number/Code : 70-461

Exam Name : Querying Microsoft SQL Server 2012

Questions and Answers : 54 Q&As

Question: 1

You administer a Microsoft SQL Server 2012 database named ContosoD

B. 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= 1  FOR XML PATH (‘Customers’)

Answer: A

 

 

Question: 2

You administer a Microsoft SQL Server 2012 database named ContosoD

B. 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, 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: C

 

 

Question: 3

You administer a Microsoft SQL Server 2012 database named ContosoD

B. 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- 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: E

 

 

Question: 4

You administer a Microsoft SQL Server 2012 database named ContosoD

B. 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- 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

 

 

Question: 5

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 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
http://www.exam1pass.com/70-461-exam.html
http://www.exam1pass.com/Microsoft-series.html