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

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

  1. Yommy O. says:

    SELECT C.contactname, C.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’);

    RESULT:

    Allen, Michael
    Germany
    10643
    2007-08-25T00:00:00
    29.4600

    Allen, Michael
    Germany
    10692
    2007-10-03T00:00:00
    61.0200




    0



    0
  2. Yommy O. says:

    What about the dynamic variables @Name, @Country?! Option G doesn’t seem right to me though. I’d simply go for Option H; it returns the exact result set, as specified above.




    0



    0
  3. Yommy O. says:

    –With Option H:

    SELECT C.contactname AS ‘Customers/Name’, C.country AS ‘Customers/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’);

    Sample Result Set:

    Allen, Michael
    Germany

    10643
    2007-08-25T00:00:00
    29.4600

    Allen, Michael
    Germany

    10692
    2007-10-03T00:00:00
    61.0200

    Allen, Michael
    Germany

    10702
    2007-10-13T00:00:00
    23.9400

    Allen, Michael
    Germany

    10835
    2008-01-15T00:00:00
    69.5300

    Allen, Michael
    Germany

    10952
    2008-03-16T00:00:00
    40.4200

    Allen, Michael
    Germany

    11011
    2008-04-09T00:00:00
    1.2100




    0



    0

Leave a Reply