Microsoft MCTS 070-461

Booking the 070-461 exam is an investment, and a failed attempt means paying the registration fee all over again. With 252 practice questions from Getcertkey, you walk into the Microsoft Querying Microsoft SQL Server 2012/2014 exam knowing exactly where you stand.

Microsoft 070-461 Exam Overview:

Certification Vendor:	Microsoft
Exam Name:	Querying Microsoft SQL Server 2012/2014
Exam Number:	70-461
Exam Format:	Active screen, Multiple select, Multiple choice, Drag and drop, Build list
Real Exam Qty:	40-60
Passing Score:	700 / 1000
Exam Price:	$80 USD
Exam Duration:	120-150
Certificate Validity Period:	Retired Jan 31, 2021; MCSA credential does not expire
Available Languages:	Chinese (Simplified), Japanese, French, English, German, Portuguese (Brazil)
Related Certifications:	MCSE: Data Platform MCSE: Business Intelligence MCSA: SQL Server 2012/2014
Recommended Training:	Microsoft Official Course 20461: Querying Microsoft SQL Server 2014
Exam Registration:	Microsoft Learn Exam Page
Sample Questions:
Exam Way:	Previously delivered via Prometric test centers and online proctoring; no longer available
Pre Condition:	Recommended 2+ years of hands-on experience with SQL Server and Transact-SQL; no mandatory prerequisites
Official Syllabus URL:	https://learn.microsoft.com/en-us/credentials/certifications/exams/70-461/

Microsoft 070-461 Exam Syllabus Topics:

Common Questions About the Microsoft 070-461 Exam

What is the Microsoft Querying Microsoft SQL Server 2012/2014 exam all about?

The 070-461 exam is the official Microsoft exam behind the MCSA: SQL Server 2012/2014 certification, validating the skills measured by the Microsoft Querying Microsoft SQL Server 2012/2014 credential. It sits at the Associate level of the Microsoft certification program. It also connects to MCSA: SQL Server 2012/2014, MCSE: Data Platform, MCSE: Business Intelligence, so the knowledge you build here carries over to those tracks as well.

How many questions are on the 070-461 exam, and how much time do I get?

The 070-461 exam contains 40-60 questions to be completed within 120-150. Before exam day, divide the available time by the question count to work out a comfortable per-question pace, and mark any item that eats into it so you can return later instead of getting stuck. Timed sessions in the Getcertkey test engines make that pacing automatic — run at least two full-length mock exams under the clock so time pressure never becomes the reason you drop points.

What score do I need to pass the 070-461 exam, and what does it cost?

The passing score for the 070-461 exam is 700 / 1000, and the official registration fee is $80 USD. Retakes are not discounted — every new attempt means paying the full fee again — so it pays to measure yourself before you book. Work through the 252 practice questions on Getcertkey, sit a timed practice test, and schedule your exam only when your scores are consistently comfortable. That simple habit is the cheapest exam strategy there is.

Are there any prerequisites for the 070-461 exam?

Recommended 2+ years of hands-on experience with SQL Server and Transact-SQL; no mandatory prerequisites Requirements can change when Microsoft revises its certification program, so confirm the current eligibility rules on the official exam page before you register.

How do I register for the 070-461 exam?

You can book the Microsoft Querying Microsoft SQL Server 2012/2014 exam through the official registration channels below:

• Microsoft Learn Exam Page

As for delivery, the exam is offered in the following format: Previously delivered via Prometric test centers and online proctoring; no longer available. Choose the option that suits you best when you book your seat.

What official training is recommended for the 070-461 exam?

Microsoft recommends the following training resources for the Microsoft Querying Microsoft SQL Server 2012/2014 exam:

• Microsoft Official Course 20461: Querying Microsoft SQL Server 2014

Official courses build the foundation; the 252 practice questions from Getcertkey then show you how that knowledge is examined, so the two work best together.

Can I try the 070-461 practice questions before I buy?

Yes. Getcertkey provides a free 070-461 PDF demo so you can review the question style and answer quality before purchasing. Every purchase also includes 365 days of free updates — if Microsoft revises the exam during that period, the updated material reaches you at no cost. Once the free-update year ends, you can extend your update service at a 50% discount.

What if I fail the 070-461 exam, and how is my order delivered?

Every Microsoft Querying Microsoft SQL Server 2012/2014 purchase on Getcertkey is covered by a 100% money-back guarantee with clear conditions: if you take the corresponding exam within 60 days of your purchase and do not pass, you can claim a full refund by submitting a scanned copy of your exam enrollment slip and your official score report as a PDF within two days of the exam date; claims are processed within seven days of submission. The guarantee does not apply to exams taken within three days of purchase, to material that was downloaded but never used in an exam attempt, or to free products and expired orders, and the candidate name must match the payer name. If you would rather not take a refund, you can instead exchange your purchase for two free exam preparation products of equal value and keep the update service on your original product.

Delivery is instant: your download is sent to your email within one minute of payment, with no limit on how many computers you may install the material on. If nothing arrives within two hours, check your spam folder and contact customer service for help.

What topics are covered in the 070-461 exam?

The Microsoft Querying Microsoft SQL Server 2012/2014 exam blueprint is organized into 4 domains. The first three are:

• Create Database Objects — 20-25% of the exam
• Modify Data — 20-25% of the exam
• Work with Data — 25-30% of the exam

For the complete domain-by-domain breakdown, scroll up to the full exam topics outline above and use it to plan how you distribute your study time.

Microsoft Querying Microsoft SQL Server 2012/2014 Sample Questions:

Question #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 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')
B. SELECT Name, Country, OrderId, OrderDate, Amount
FROM Orders INNER JOIN Customers ON Orders.CustomerId= Customers.CustomerId WHERE Customers.CustomerId = 1 FOR XML AUTO
C. SELECT OrderId, OrderDate, Amount, Name, Country
FROM Orders INNER JOIN Customers ON Orders.CustomerId = Customers.CustomerId WHERE Customers.CustomerId = 1 FOR XML RAW, ELEMENTS
D. SELECT OrderId, OrderDate, Amount, Name, Country
FROM Orders INNER JOIN Customers ON Orders.CustomerId = Customers.CustomerId WHERE Customers.CustomerId = 1 FOR XML RAW
E. 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')
F. SELECT Name, Country, OrderId, OrderDate, Amount
FROM Orders INNER JOIN Customers ON Orders.CustomerId= Customers.CustomerId WHERE Customers.CustomerId = 1 FOR XML AUTO, ELEMENTS
G. SELECT OrderId, OrderDate, Amount, Name, Country
FROM Orders INNER JOIN Customers ON Orders.CustomerId = Customers.CustomerId WHERE Customers.CustomerId = 1 FOR XML AUTO
H. SELECT OrderId, OrderDate, Amount, Name, Country
FROM Orders INNER JOIN Customers ON Orders.CustomerId - Customers.CustomerId WHERE Customers.CustomerId = 1 FOR XML AUTO, ELEMENTS

Question #2
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 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')
B. SELECT OrderId, OrderDate, Amount, Name, Country FROM Orders
INNER JOIN Customers ON Orders.CustomerId = Customers.CustomerId
WHERE Customers.CustomerId = 1
FOR XML RAW
C. SELECT Name, Country, OrderId, OrderDate, Amount FROM Orders
INNER JOIN Customers ON Orders.CustomerId = Customers.CustomerId
WHERE Customers.CustomerId = 1
FOR XML AUTO, ELEMENTS
D. SELECT Name, Country, OrderId, OrderDate, Amount FROM Orders
INNER JOIN Customers ON Orders.CustomerId = Customers.CustomerId
WHERE Customers.CustomerId = 1
FOR XML AUTO
E. SELECT OrderId, OrderDate, Amount, Name, Country FROM Orders
INNER JOIN Customers ON Orders.CustomerId = Customers.CustomerId
WHERE Customers.CustomerId = 1
FOR XML RAW, ELEMENTS
F. SELECT OrderId, OrderDate, Amount, Name, Country FROM Orders
INNER JOIN Customers ON Orders.CustomerId = Customers.CustomerId
WHERE Customers.CustomerId = 1
FOR XML AUTO
G. 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')
H. SELECT OrderId, OrderDate, Amount, Name, Country FROM Orders
INNER JOIN Customers ON Orders.CustomerId = Customers.CustomerId
WHERE Customers.CustomerId = 1
FOR XML AUTO, ELEMENTS

Question #3
You develop a Microsoft SQL Server database that contains a table named Products. The Products table has columns named ProductId, CategoryId, Name, and Price.
Product prices are often updated as follows:
* For a single product by a specific value by using the ProductId column
* For an entire category by a percentage by using the CategoryId column You need to log the change price to a new table named PriceChange by using the ProductId' ChangedValue, and ChangedDate columns. You also need to ensure that increases in price are recorded as positive values and decreases in price as negative values.
Which Transact-SQL query should you use?

A. Option C
B. Option B
C. Option A
D. Option D

Question #4
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, LAG(Profit, 1, 0) OVER (PARTITION BY Territory ORDER BY Year) AS PreviousYearProfit FROM Profits
B. SELECT Territory, Year, Profit, LEAD(Profit, 1, 0) OVER (PARTITION BY Territory ORDER BY Year) AS PreviousYearProfit FROM Profits
C. SELECT Territory, Year, Profit, LAG(Profit, 1, 0) OVER (PARTITION BY Year ORDER BY Territory) AS PreviousYearProfit FROM Profits
D. SELECT Territory, Year, Profit, LEAD(Profit, 1, 0) OVER (PARTITION BY Year ORDER BY Territory) AS PreviousYearProfit FROM Profits

Question #5
You develop a Microsoft SQL Server 2012 database that contains tables named Employee and Person.
The tables have the following definitions:

Users are able to use single INSERT statements or INSERT...SELECT statements into this view.
You need to ensure that users are able to use a single statement to insert records into both Employee and Person tables by using the VwEmployee view.
Which Transact-SQL statement should you use?

A. CREATE TRIGGER TrgVwEmployee
ON VwEmployee
INSTEAD OF INSERT
AS
BEGIN
INSERT INTO Person(Id, FirstName, LastName)
SELECT Id, FirstName, LastName, FROM inserted
INSERT INTO Employee(PersonId, EmployeeNumber)
SELECT Id, EmployeeNumber FROM inserted
END
B. CREATE TRIGGER TrgVwEmployee
ON VwEmployee
INSTEAD OF INSERT
AS
BEGIN
INSERT INTO Person(Id, FirstName, LastName)
SELECT Id, FirstName, LastName FROM VwEmployee
INSERT INTO Employee(PersonID, EmployeeNumber)
SELECT Id, EmployeeNumber FROM VwEmployee
End
C. CREATE TRIGGER TrgVwEmployee
ON VwEmployee
FOR INSERT
AS
BEGIN
INSERT INTO Person(Id, FirstName, LastName)
SELECT Id, FirstName, LastName, FROM inserted
INSERT INTO Employee(PersonId, EmployeeNumber)
SELECT Id, EmployeeNumber FROM inserted
END
D. CREATE TRIGGER TrgVwEmployee
ON VwEmployee
INSTEAD OF INSERT
AS
BEGIN
DECLARE @ID INT, @FirstName NVARCHAR(25), @LastName NVARCHAR(25), @PersonID INT, @EmployeeNumber NVARCHAR(15) SELECT @ID = ID, @FirstName = FirstName, @LastName = LastName, @EmployeeNumber
= EmployeeNumber
FROM inserted
INSERT INTO Person(Id, FirstName, LastName)
VALUES(@ID, @FirstName, @LastName)
INSERT INTO Employee(PersonID, EmployeeNumber)
VALUES(@PersonID, @EmployeeNumber
End

Solutions: