PrepAway - Latest Free Exam Questions & Answers

Which Transact-SQL query should you use?

You administer a Microsoft SQL Server 2012 databasenamed ContosoDb. Tables are defined as shown in the
exhibit. (Refer to the Exhibit.)

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:
Verified answer as correct.
Reference: http://msdn.microsoft.com/en-us/library/bb510464.aspx

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

  5. reg says:

    declare @SQL_String1 nvarchar(max)
    declare @AIR int,@CPIR int,@eed int,@VLI int

    set @eed=”
    set @VLI=”

    DECLARE Inserts CURSOR FOR

    open Inserts

    FETCH NEXT FROM Inserts
    INTO @AIR
    ,@CPIR,@eed,@VLI
    WHILE @@FETCH_STATUS = 0
    BEGIN

    set @SQL_String1 = ‘Insert Into Table_I_X_C
    (AIR
    ,CPIR
    ,eED
    ,VLI
    ) values(‘+cast(@AIR as varchar(255))+cast(@CPIR as varchar(255))+cast(@eed as varchar(255))+cast(@VLI as varchar(255))+’)’
    print @sql_string1

    FETCH NEXT FROM Inserts
    INTO @AIR
    ,@CPIR,@eed,@VLI
    END

    CLOSE Inserts
    DEALLOCATE Inserts




    0



    0
  6. nabil says:

    Hello guys! I just got some 70-461 new questions:
    QUESTION 169
    You have a database that contains a table named Customer.
    The customer table contains a column named LastName that has a column definition of varchar(50).
    An application named App1 reads from the table frequently.
    You need to change the column definition to nvarchar(100).
    The solution must minimize the amount of time it takes for App1 to read the data.
    Which statement should you execute?

    A.
    B.
    C.
    D.

    Answer: C

    QUESTION 170
    You are maintaining a SQL Server database that uses the default settings.
    The database contains a table that is defined by the following Transact-SQL statement:

    You must write a query that returns the AddressLine1, AddressLine2, and Region fields separated by carriage returns.
    You must return an empty string for any null values.

    A.
    B.
    C.
    D.
    Answer: A

    QUESTION 171
    A table named Profits stores the total profit made each year within a territory.
    The Profits table has columns named Territory, Year, and Profit.
    You need to create a report that displays the profits made by each territory for each year and its previous year.
    Which Transact-SQL query should you use?

    A. SELECT Territory, Year, Profit, LEAD(Profit, 1, 0) OVER (PARTITION BY Territory ORDER BY Year) AS PreviousYearProfit FROM Profits
    B. SELECT Territory, Year, Profit, LAG(Profit, 1, 0) OVER (PARTITION BY Year ORDER BY Territory) AS PreviousYearProfit FROM Profits
    C. SELECT Territory, Year, Profit, LAG(Profit, 1, 0) OVER (PARTITION BY Territory ORDER BY Year) AS PreviousYearProfit FROM Profits
    D. SELECT Territory, Year, Profit, LEAD(Profit, 1, 0) OVER (PARTITION BY Year ORDER BY Territory) AS PreviousYearProfit FROM Profits

    Answer: C

    QUESTION 172
    Your database contains a table named Products that has columns named ProductID and Name.
    You want to write a query that retrieves data from the Products table sorted by Name listing 15 rows at a time.
    You need to view rows 31 through 45.
    Which Transact-SQL query should you create?
    A.
    B.
    C.
    D.
    Answer: C

    QUESTION 173
    A database named AdventureWorks contains two tables named Production.Product and Sales.SalesOrderDetail. The tables contain data on the available products and a detailed order history.
    The Production.Product table contains the following two columns:

    – ProductID
    – Name

    The Sales.SalesOrderDetail table contains the following three columns:

    – SalesOrderID
    – ProductID
    – OrderQty

    You need to create a query listing all of the products that were never ordered.
    Which statements should you execute?

    A.
    B.
    C.
    D.
    Answer: A

    QUESTION 174
    You plan to write a query for a new business report that will contain several nested queries.
    You need to ensure that a nested query can call a table-valued function for each row in the main query.
    Which query operator should you use in the nested query?

    A. CROSS APPLY
    B. INNER JOIN
    C. OUTER JOIN
    D. PIVOT

    Answer: A

    QUESTION 175
    You are designing a table for a SQL Server database.
    The table uses horizontal partitioning.
    You have the following requirements:

    – Each record in the table requires a unique key.
    – You must minimize table fragmentation as the table grows.

    You need to choose the appropriate data type for the key value.
    What should you do?

    A. Use the NEWID function to create a unique identifier.
    B. Use the NEWSEQUENTIALID function to create a unique identifier.
    C. Generate a random value that uses the bigint datatype.
    D. Generate a random value that uses the char(16) data type.

    Answer: B

    QUESTION 176
    You are a database developer of a Microsoft SQL Server database.
    You are designing a table that will store Customer data from different sources. The table will include a column that contains the CustomerID from the source system and a column that contains the SourceID.
    A sample of this data is as shown in the following table.

    You need to ensure that the table has no duplicate CustomerID within a SourceID.
    You also need to ensure that the data in the table is in the order of SourceID and then CustomerID.
    Which Transact- SQL statement should you use?

    A. CREATE TABLE Customer
    (SourceID int NOT NULL,
    CustomerID int NOT NULL,
    CustomerName varchar(255) NOT NULL
    CONSTRAINT UQ_Customer UNIQUE
    (SourceID, CustomerID));
    B. CREATE TABLE Customer
    (SourceID int NOT NULL UNIQUE,
    CustomerID int NOT NULL UNIQUE,
    CustomerName varchar(255) NOT NULL);
    C. CREATE TABLE Customer
    (SourceID int NOT NULL PRIMARY KEY CLUSTERED,
    CustomerID int NOT NULL UNIQUE,
    CustomerName varchar(255) NOT NULL);
    D. CREATE TABLE Customer
    (SourceID int NOT NULL,
    CustomerID int NOT NULL,
    CustomerName varchar(255) NOT NULL,
    CONSTRAINT PK_Customer PRIMARY KEY CLUSTERED
    (SourceID, CustomerID));

    Answer: D

    QUESTION 177
    Drag and Drop Question
    You maintain a SQL Server database that is used by a retailer to track sales and salespeople. The database includes two tables and two triggers that is defined by the following Transact-SQL statements:

    During days with a large sales volume, some new sales transaction fail and report the following error:

    Arithmetic overflow error converting expression to data type int.

    You need to ensure that the two triggers are applied once per sale, and that they do not interfere with each other.
    How should you complete the relevant Transact-SQL statement? To answer, drag the appropriate Transact-SQL segments to the correct location or locations. Each Transact-SQL segment may be used once, more than once, or not at all. You may need to drag the split bar between panes or scroll to view content.

    Answer:

    More details, you can visit this blog: http://www.passpmp.net/category/microsoft/70-461-dumps
    If you have any real questions, reply to me please!




    0



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