Pass4Test Microsoft 70-461 (Querying Microsoft SQL Server 2012)시험덤프 구매전 구매사이트에서 무료샘플을 다운받아 PDF버전 덤프내용을 우선 체험해보실수 있습니다. 무료샘플을 보시면 Pass4Test Microsoft 70-461 (Querying Microsoft SQL Server 2012)시험대비자료에 믿음이 갈것입니다.고객님의 이익을 보장해드리기 위하여 Pass4Test는 시험불합격시 덤프비용전액환불을 무조건 약속합니다. Pass4Test의 도움으로 더욱 많은 분들이 멋진 IT전문가로 거듭나기를 바라는바입니다.
Create database objects (24%)
Work with data (27%)
Modify data (24%)
Troubleshoot and optimize (25%)
Instructor-led training
Exam prep videos
Self-paced training
Practice tests
Books
Who should take this exam?
This exam is intended for SQL Server database administrators, system engineers, and developers with two or more years of experience, who are seeking to validate their skills and knowledge in writing queries
NO.1 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.2 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.3
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
NO.4 DRAG DROP
You use a Microsoft SQL
Server 2012 database.
You need to create an indexed view within the database
for a report that displays Customer Name and the
total revenue for that
customer.
Which four T-SQL statements should you use? (To answer, move the
appropriate SQL statements from
the list of statements to the answer area and
arrange them in the correct order.)
Answer:
NO.5 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.6 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.7 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.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: D