The Only Tip You Need: 70 461 exam questions pdf

70-461 Exam

The Only Tip You Need: 70 461 exam questions pdf

Master the 70 461 dumps Querying Microsoft SQL Server 2012 content and be ready for exam day success quickly with this Ucertify mcsa 70 461 simulations. We guarantee it!We make it a reality and give you real 70 461 dumps questions in our Microsoft sql server 70 461 braindumps.Latest 100% VALID Microsoft 70 461 vce Exam Questions Dumps at below page. You can use our Microsoft sql server 70 461 braindumps and pass your exam.

Q51. Your database contains a table named SalesOrders. The table includes a DATETIME column named OrderTime that stores the date and time each order is placed. There is a non-clustered index on the OrderTime column.

The business team wants a report that displays the total number of orders placed on the current day.

You need to write a query that will return the correct results in the most efficient manner.

Which Transact-SQL query should you use?

A. SELECT COUNT(*) FROM SalesOrders

WHERE OrderTime = CONVERT(DATE, GETDATE())

B. SELECT COUNT(*) FROM SalesOrders

WHERE OrderTime = GETDATE()

C. SELECT COUNT(*) FROM SalesOrders

WHERE CONVERT(VARCHAR, OrderTime, 112) = CONVERT(VARCHAR, GETDATE(I,

112))

D. SELECT COUNT(*) FROM SalesOrders

WHERE OrderTime >= CONVERT(DATE, GETDATE())

AND OrderTime < DATEADD(DAY, 1, CONVERT(DATE, GETDATE()))

Answer: D

Q52. 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 Customerld 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, 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: G

Q53. 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.

</CUSTOMERS>

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

Answer: E

Q54. 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 existing applications.

What should you create for each application?

A. table partitions

B. views

C. table-valued functions

D. stored procedures

Answer: B

Q55. DRAG DROP

You use Microsoft SQL Server 2012 to develop a database application. You create two tables by using the following table definitions.

Which six Transact-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:

Q56. You develop a database for a travel application. You need to design tables and other database objects.

You create the Airline_Schedules table.

You need to store the departure and arrival dates and times of flights along with time zone

information.

What should you do?

A. Use the CAST function.

B. Use the DATE data type.

C. Use the FORMAT function.

D. Use an appropriate collation.

E. Use a user-defined table type.

F. Use the VARBINARY data type.

G. Use the DATETIME data type.

H. Use the DATETIME2 data type.

I. Use the DATETIMEOFFSET data type.

J. Use the TODATETIMEOFFSET function.

Answer: I

Q57. You develop a Microsoft SQL Server 2012 database.

You need to create a batch process that meets the following requirements:

. Returns a result set based on supplied parameters.

. Enables the returned result set to perform a join with a table.

Which object should you use?

A. Inline user-defined function

B. Stored procedure

C. Table-valued user-defined function

D. Scalar user-defined function

Answer: C

Q58. 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 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: F

Q59. 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 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: A

Q60. You develop a Microsoft SQL Server 2012 database. You create a view from the Orders and OrderDetails tables by using the following definition.

You need to ensure that users are able to modify data by using the view.

What should you do?

A. Create an AFTER trigger on the view.

B. Modify the view to use the WITH VIEW_METADATA clause.

C. Create an INSTEAD OF trigger on the view.

D. Modify the view to an indexed view.

Answer: C