Facts about 70 461 exam dumps

Proper study guides for Rebirth Microsoft Querying Microsoft SQL Server 2012 certified begins with Microsoft 70 461 pdf preparation products which designed to deliver the Highest Quality 70 461 exam questions by making you pass the 70 461 certification test at your first time. Try the free exam 70 461 demo right now.

♥♥ 2021 NEW RECOMMEND ♥♥

Free VCE & PDF File for Microsoft 70-461 Real Exam (Full Version!)

★ Pass on Your First TRY ★ 100% Money Back Guarantee ★ Realistic Practice Exam Questions

Free Instant Download NEW 70-461 Exam Dumps (PDF & VCE):
Available on: http://www.surepassexam.com/70-461-exam-dumps.html

Q21. 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.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') 

Answer: F 

Q22. You develop a Microsoft SQL Server 2012 database. The database is used by two web 

applications that access a table named Products. 

You want to create an object that will prevent the applications from accessing the table directly while still providing access to the required data. 

You need to ensure that the following requirements are met: 

. Future modifications to the table definition will not affect the applications' ability to access data. . The new object can accommodate data retrieval and data modification. 

You need to achieve this goal by using the minimum amount of changes to the applications. 

What should you create for each application? 

A. Synonyms 

B. Common table expressions 

C. Views 

D. Temporary tables 

Answer: C 

Q23. You use Microsoft SQL Server 2012 to create a stored procedure as shown in the following code segment. (Line numbers are included for reference only.) 

The procedure can be called within other transactions. 

You need to ensure that when the DELETE statement from the HumanResourcesJobCandidate table succeeds, the modification is retained even if the insert into the Audit.Log table fails. 

Which code segment should you add to line 14? 

A. IF @@TRANCOUNT = 0 

B. IF (XACT_STATE ( ) ) = 0 

C. IF (XACT_STATE ( ) ) = 1 

D. IF @@TRANCOUNT = l 

Answer: C 

Q24. You administer a Microsoft SQL Server 2012 database. The database contains a table named Employee. Part of the Employee table is shown in the exhibit. (Click the Exhibit button.) 

Unless stated above, no columns in the Employee table reference other tables. 

Confidential information about the employees is stored in a separate table named EmployeeData. One record exists within EmployeeData for each record in the Employee table. 

You need to assign the appropriate constraints and table properties to ensure data integrity and visibility. 

On which column in the Employee table should you use an identity specification to include a seed of 1,000 and an increment of 1? 

A. DateHired 

B. DepartmentID 

C. EmployeeID 

D. EmployeeNum 

E. FirstName 

F. JobTitle 

G. LastName 

H. MiddleName 

I. ReportsToID 

Answer: C 

Q25. You are developing a database that will contain price information. You need to store the prices that include a fixed precision and a scale of six digits. Which data type should you use? 

A. Float 

B. Money 

C. Smallmoney 

D. Numeric 

Answer: D 

Explanation: 

Numeric is the only one in the list that can give a fixed precision and scale. 

Reference: http://msdn.microsoft.com/en-us/library/ms179882.aspx 

Q26. 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 FROM ( SELECT StudentCode, Marks AS Marks, RANK() OVER (PARTITION BY SubjectCode ORDER BY Marks DESC) AS Rank FROM 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 

Q27. Your database contains a table named Customer that has columns named CustomerID 

and Name. 

You want to write a query that retrieves data from the Customer table sorted by Name 

listing 20 rows at a time. 

You need to view rows 41 through 60. 

Which Transact-SQL query should you create? 

A. Option A 

B. Option B 

C. Option C 

D. Option D 

Answer: B 

Q28. You have a Microsoft SQL Server 2012 database that contains tables named Customers and Orders. 

The tables are related by a column named CustomerID. 

You need to create a query that meets the following requirements: 

. Returns the CustomerName for all customers and the OrderDate for any orders that they have placed. . Results must include customers who have not placed any orders. 

Which Transact-SQL query should you use? 

A. SELECT CustomerName, OrderDate FROM Customers RIGHT OUTER JOIN Orders ON Customers.CustomerID = Orders.CustomerID 

B. SELECT CustomerName, CrderDate FROM Customers JOIN Orders ON Customers.CustomerID = Orders.CustomerID 

C. SELECT CustomerName, OrderDate FROM Customers CROSS JOIN Orders ON Customers.CustomerID = Orders.CustomerID 

D. SELECT CustomerName, OrderDate FROM Customers LEFT OUTER JOIN Orders ON Customers.CustomerID = Orders.CustomerID 

Answer: D 

Q29. You administer a Microsoft SQL Server database that supports a shopping application. 

You need to retrieve a list of customers who live in territories that do not have a sales person. 

Which Transact- SQL query or queries should you use? (Each correct answer presents a complete solution. Choose all that apply.) 

A. SELECT CustomerID FROM Customer 

WHERE TerritoryID <> SOME(SELECT TerritoryID FROM Salesperson) 

B. SELECT CustomerID FROM Customer 

WHERE TerritoryID <> ALL(SELECT TerritoryID FROM Salesperson) 

C. SELECT CustomerID FROM Customer 

WHERE TerritoryID <> ANY(SELECT TerritoryID FROM Salesperson) 

D. SELECT CustomerID FROM Customer 

WHERE TerritoryID NOT IN(SELECT TerritoryID FROM Salesperson) 

Answer: B,D 

Q30. You administer a Microsoft SQL Server 2012 database. The database contains a table named Employee. Part of the Employee table is shown in the exhibit. (Click the Exhibit button.) 

Confidential information about the employees is stored in a separate table named EmployeeData. One record exists within EmployeeData for each record in the Employee table. You need to assign the appropriate constraints and table properties to ensure data integrity and visibility. On which column in the Employee table should you a create a unique constraint? 

A. DateHired 

B. DepartmentID 

C. EmployeelD 

D. EmployeeNum 

E. FirstName 

F. JobTitle 

G. LastName 

H. MiddleName 

I. ReportsToID 

Answer: D