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?
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
A
For A you wil have:
first read difference between XML RAW and XML AUTO than u will get correct answer
Ans: C
I tested, it is C.
I tested, it is A.
C
yeah its A
Is it okay to put part of this on my webpage if I post a link back to this webpage?
http://www.freephpscripts.co
no, its A
You actually make it seem so easy with your presentation but I find this matter to be really something that I think I would never understand. It seems too complex and very broad for me. I’m looking forward for your next post, I’ll try to get the hang of it!
http://www.threadsmagazine.com/profile/louise3t
It’s A
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
Check below, are some entirely unrelated websites to ours, even so, they’re most trustworthy sources that we use.
the time to read or stop by the material or web pages we have linked to beneath the
Wonderful story, reckoned we could combine a few unrelated data, nevertheless genuinely worth taking a appear, whoa did a single understand about Mid East has got additional problerms at the same time
here are some hyperlinks to internet sites that we link to for the reason that we believe they are really worth visiting
i tried it, actually answer is C
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!
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