PrepAway - Latest Free Exam Questions & Answers

Which Transact-SQL query should you use?

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?

PrepAway - Latest Free Exam Questions & Answers

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’)

3 Comments on “Which Transact-SQL query should you use?”

  1. Slazenjer_m says:

    In SSMS, this returns syntax error for ‘@Name’, ‘@Country’:

    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’)

    Whereas, the following does not return any error:

    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’)




    0



    0
  2. Slazenjer_m says:

    Option G is actually right:

    SELECT C.contactname AS ‘@Name’, C.country AS ‘@Country’, O.OrderId, O.OrderDate, O.freight
    FROM Sales.Orders AS O
    INNER JOIN Sales.Customers AS C
    ON O.custid = C.custid
    WHERE C.custid = 1
    FOR XML PATH (‘Customers’);
    GO




    0



    0

Leave a Reply