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:
<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?

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

Explanation:
http://msdn.microsoft.com/en-us/library/bb510464.aspx

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

  5. 2mec says:

    CREATE TABLE [dbo].[Customers] (
    [CustomerId] [int] NOT NULL
    ,[Name] [nvarchar](50) NOT NULL
    ,[Country] [nvarchar](50) NULL
    )
    GO
    CREATE TABLE [dbo].[Orders] (
    [OrderId] [int] NOT NULL
    ,[CustomerId] [int] NOT NULL
    ,[OrderDate] [datetime] NOT NULL
    ,[Amount] [money] NULL
    )
    GO
    INSERT INTO [dbo].[Customers] (
    [CustomerId]
    ,[Name]
    ,[Country]
    )
    VALUES (
    1
    ,’Customer A’
    ,’Australia’
    )
    GO
    USE [ContosoDB]
    GO
    INSERT INTO [dbo].[Orders] (
    [OrderId]
    ,[CustomerId]
    ,[OrderDate]
    ,[Amount]
    )
    VALUES (
    1
    ,1
    ,’2000-01-01T00:00:00′
    ,3400.00
    )
    GO
    INSERT INTO [dbo].[Orders] (
    [OrderId]
    ,[CustomerId]
    ,[OrderDate]
    ,[Amount]
    )
    VALUES (
    2
    ,1
    ,’2001-01-01T00:00:00′
    ,4300.00
    )
    GO
    SELECT OrderId
    ,OrderDate
    ,Amount
    ,NAME
    ,Country
    FROM Orders
    INNER JOIN Customers ON Orders.CustomerId = Customers.CustomerId
    WHERE Customers.CustomerId = 1
    FOR XML RAW




    0



    0

Leave a Reply