Pass4Test의 Microsoft 70-461 (Querying Microsoft SQL Server 2012)덤프를 구매하시면 1년동안 무료 업데이트서비스버전을 받을수 있습니다. 시험문제가 변경되면 업데이트 하도록 최선을 다하기에 Pass4Test의 Microsoft 70-461 (Querying Microsoft SQL Server 2012)덤프의 유효기간을 연장시켜드리는 셈입니다.퍼펙트한 구매후는 서비스는 Pass4Test의 Microsoft 70-461 (Querying Microsoft SQL Server 2012)덤프를 구매하시면 받을수 있습니다.
NO.1 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?
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')
Answer: A
NO.2
You develop a Microsoft SQL Server 2012 server database that supports an
application. The application
contains a table that has the following
definition:
CREATE TABLE Inventory
(ItemID int NOT NULL PRIMARY
KEY,
ItemsInStore int NOT NULL,
ItemsInWarehouse int NOT NULL)
You need
to create a computed column that returns the sum total of the ItemsInStore
and
ItemsInWarehouse values for each row.
The new column is expected to be
queried heavily, and you need to be able to index the column.
Which
Transact-SQL statement should you use?
A. ALTER TABLE Inventory
All
TotalItems AS Item3lnStore + ItemsInWarehouse
B. ALTER TABLE Inventory
ADD
TotalItems AS ItemsInStore + ItemsInWarehouse PERSISTED
C. ALTER TABLE
Inventory
ADD TotalItems AS SUM (ItemsInStore, ItemsInWarehouse)
PERSISTED
D. ALTER TABLE Inventory
All TotalItems AS SUM (ItemsInStore,
ItemsInWarehouse)
Answer: C
NO.3 You create a table that has the
StudentCode, SubjectCode, and Marks columns to record mid-year
marks for
students. The table has marks obtained by 50 students for various
subjects.
You need to retrieve the students who scored the highest marks for
each subject along with the marks.
Which Transact-SQL query should you
use?
A. SELECT StudentCode as Code,
RANK ( ) OVER (ORDER BY AVG (Marks)
DESC) AS Value FROM StudentMarks
GROUP BY StudentCode
B. SELECT Id, Name,
Marks, DENSE_RANK () OVER (ORDER BY Marks DESC) AS Rank
FROM
StudentMarks
C. SELECT StudentCode as Code,
DENSE_RANK () OVER
(ORDER BY AVG (Marks) DESC) AS Value FROM StudentMarks
GROUP BY
StudentCode
D. SELECT StudentCode as Code,
NTILE (2) OVER (ORDER BY AVG
(Marks) DESC) AS Value FROM StudentMarks
GROUP BY StudentCode
E. SELECT
StudentCode AS Code,Marks AS Value
FROM (SELECT StudentCode, Marks AS
Marks,
RANK () OVER (PARTITION BY SubjectCode ORDER BY Marks ASC) AS Rank
FROM StudentMarks)
tmp WHERE Rank = 1
F. SELECT StudentCode AS Code,Marks
AS Value FRCM (
SELECT StudentCode, Marks AS Marks,
RANK() OVER (PARTITION
BY SubjectCode ORDER 3Y Marks DESC) AS Rank FRCM StudentMarks)
tmp WHERE Rank
= 1
G. SELECT StudentCode AS Code,Marks AS Value FROM
(SELECT StudentCode,
Marks AS Marks,
RANK () OVER (PARTITION BY StudentCode ORDER BY Marks ASC) AS
Rank FROM StudentMarks)
tmp WHERE Rank = 1
H. SELECT StudentCode AS
Code,Marks AS Value FROM
(SELECT StudentCode, Marks AS Marks,
RANXO OVER
(PARTITION BY StudentCode ORDER BY Marks DESC) AS Rank FROM StudentMarks)
tmp
WHERE Rank = 1
Answer: F
NO.4 You create a table that has the
StudentCode, SubjectCode, and Marks columns to record mid-year
marks for
students. The table has marks obtained by 50 students for various
subjects.
You need to ensure that the top half of the students arranged by
their average marks must be given a rank
of 1 and the remaining students must
be given a rank of 2.
Which Transact-SQL query should you use?
A. SELECT
StudentCode as Code,
RANK ( ) OVER (ORDER BY AVG (Marks) DESC) AS Value FROM
StudentMarks
GROUP BY StudentCode
B. SELECT Id, Name, Marks, DENSE_RANK ()
OVER (ORDER BY Marks DESC) AS Rank FROM
StudentMarks
C. SELECT StudentCode
as Code,
DENSE_RANK () OVER (ORDER BY AVG (Marks) DESC) AS Value FROM
StudentMarks
GROUP BY StudentCode
D. SELECT StudentCode as Code,
NTILE
(2) OVER (ORDER BY AVG (Marks) DESC) AS Value FROM StudentMarks
GROUP BY
StudentCode
E. SELECT StudentCode AS Code,Marks AS Value
FROM (SELECT
StudentCode, Marks AS Marks,
RANK () OVER (PARTITION BY SubjectCode ORDER BY
Marks ASC) AS Rank FROM StudentMarks)
tmp WHERE Rank = 1
F. SELECT
StudentCode AS Code,Marks AS Value FRCM (
SELECT StudentCode, Marks AS
Marks,
RANK() OVER (PARTITION BY SubjectCode ORDER 3Y Marks DESC) AS Rank
FRCM StudentMarks)
tmp WHERE Rank = 1
G. SELECT StudentCode AS Code,Marks
AS Value FROM
(SELECT StudentCode, Marks AS Marks,
RANK () OVER (PARTITION
BY StudentCode ORDER BY Marks ASC) AS Rank FROM StudentMarks)
tmp WHERE Rank
= 1
H. SELECT StudentCode AS Code,Marks AS Value FROM
(SELECT StudentCode,
Marks AS Marks,
RANXO OVER (PARTITION BY StudentCode ORDER BY Marks DESC) AS
Rank FROM StudentMarks)
tmp WHERE Rank = 1
Answer: D
NO.5 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
CustomerIdvalue set to
1 in the following XML format.
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.CustomerXd -
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')
Answer: C
NO.6 You develop a Microsoft SQL Server 2012
database that contains a table named Customers.
The Customers table has the
following definition:
You need to create an audit record only when either the
MobileNumber or HomeNumber column is
updated.
Which Transact-SQL query
should you use?
A. CREATE TRIGGER TrgPhoneNumberChange
ON Customers FOR
UPDATE
AS
IF COLUMNS_UPDATED (HomeNumber, MobiieNumber)
- - Create
Audit Records
B. CREATE TRIGGER TrgPhoneNumberChange
ON Customers FOR
UPDATE
AS
IF EXISTS( SELECT HomeNumber from inserted) OR
EXISTS (SELECT
MobileNumber FROM inserted)
- - Create Audit Records
C. CREATE TRIGGER
TrgPhoneNumberChange
ON Customers FOR UPDATE
AS
IF COLUMNS_CHANGED
(HomeNumber, MobileNumber)
- - Create Audit Records
D. CREATE TRIGGER
TrgPhoneNumberChange
ON Customers FOR UPDATE
AS
IF UPDATE (HomeNumber)
OR UPDATE (MobileNumber)
- - Create Audit Records
Answer: D
NO.7
You create a table that has the StudentCode, SubjectCode, and Marks columns to
record mid-year
marks for students. The table has marks obtained by 50
students for various subjects.
You need to ensure that the following
requirements are met:
Students must be ranked based on their average
marks.
If one or more students have the same average, the same rank must be
given to these students.
Consecutive ranks must be skipped when the same rank
is assigned.
Which Transact-SQL query should you use?
A. SELECT
StudentCode as Code,
RANK ( ) OVER (ORDER BY AVG (Marks) DESC) AS Value FROM
StudentMarks
GROUP BY StudentCode
B. SELECT Id, Name, Marks, DENSE_RANK ()
OVER (ORDER BY Marks DESC) AS Rank FROM
StudentMarks
C. SELECT StudentCode
as Code,
DENSE_RANK () OVER (ORDER BY AVG (Marks) DESC) AS Value FROM
StudentMarks
GROUP BY StudentCode
D. SELECT StudentCode as Code,
NTILE
(2) OVER (ORDER BY AVG (Marks) DESC) AS Value FROM StudentMarks
GROUP BY
StudentCode
E. SELECT StudentCode AS Code,Marks AS Value
FROM (SELECT
StudentCode, Marks AS Marks,
RANK () OVER (PARTITION BY SubjectCode ORDER BY
Marks ASC) AS Rank FROM StudentMarks)
tmp WHERE Rank = 1
F. SELECT
StudentCode AS Code,Marks AS Value FRCM (
SELECT StudentCode, Marks AS
Marks,
RANK() OVER (PARTITION BY SubjectCode ORDER 3Y Marks DESC) AS Rank
FRCM StudentMarks)
tmp WHERE Rank = 1
G. SELECT StudentCode AS Code,Marks
AS Value FROM
(SELECT StudentCode, Marks AS Marks,
RANK () OVER (PARTITION
BY StudentCode ORDER BY Marks ASC) AS Rank FROM StudentMarks)
tmp WHERE Rank
= 1
H. SELECT StudentCode AS Code,Marks AS Value FROM
(SELECT StudentCode,
Marks AS Marks,
RANXO OVER (PARTITION BY StudentCode ORDER BY Marks DESC) AS
Rank FROM StudentMarks)
tmp WHERE Rank = 1
Answer: A
NO.8 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?
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.CustomerXd -
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')
Answer: E