70-461 Exam
A Complete Guide to 70 461 dumps
Want to know Actualtests exam 70 461 dumps free download pdf Exam practice test features? Want to lear more about Microsoft Querying Microsoft SQL Server 2012 certification experience? Study Breathing Microsoft 70 461 exam answers to Avant-garde exam 70 461 questions at Actualtests. Gat a success with an absolute guarantee to pass Microsoft 70 461 training kit (Querying Microsoft SQL Server 2012) test on your first attempt.
Q21. You develop a Microsoft SQL Server 2012 database.
You need to create and call a stored procedure that meets the following requirements:
. Accepts a single input parameter for CustomerID.
. Returns a single integer to the calling application.
Which Transact-SQL statement or statements should you use? (Each correct answer presents part of the solution. Choose all that apply.)
A. CREATE PROCEDURE dbo.GetCustomerRating @Customer INT, @CustomerRatIng INT OUTPUT AS SET NOCOUNT ON SELECT @CustomerRating = CustomerOrders/CustomerValue FROM Customers WHERE CustomerID = @CustomerID RETURN GO
B. EXECUTE dbo.GetCustomerRatIng 1745
C. DECLARE @customerRatingBycustomer INT DECLARE @Result INT EXECUTE @Result = dbo.GetCustomerRating
, @CustomerRatingSyCustomer
D. CREATE PROCEDURE dbo.GetCustomerRating @CustomerID INT, @CustomerRating INT OUTPUT AS SET NOCOUNT ON SELECT @Result = CustomerOrders/CustomerValue FROM Customers WHERE CustomerID = @CustomeriD RETURN @Result GO
E. DECLARE @CustomerRatIngByCustcmer INT EXECUTE dbo.GetCustomerRating @CustomerID = 1745, @CustomerRating = @CustomerRatingByCustomer OUTPUT
F. CREATE PROCEDURE dbo.GetCustomerRating @CustomerID INT AS DECLARE @Result INT SET NOCOUNT ON SELECT @Result = CustomerOrders/CustomerVaLue FROM Customers WHERE Customer= = @CustomerID RETURNS @Result
Answer: A,E
Q22. 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
Q23. 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
Q24. You administer a Microsoft SQL Server 2012 database. The database contains a Product table created by using the following definition:
You need to ensure that the minimum amount of disk space is used to store the data in the Product table.
What should you do?
A. Convert all indexes to Column Store indexes.
B. Implement Unicode Compression.
C. Implement row-level compression.
D. Implement page-level compression.
Answer: D
Q25. DRAG DROP
You administer a Microsoft SQL Server 2012 database. You use an OrderDetail table that has the following definition:
You need to create a non-clustered index on the SalesOrderID column in the OrderDetail table to include only rows that contain a value in the SpecialOfferID column. Which four Transact-SQL statements should you use?
(To answer, move the appropriate statements from the list of statements to the answer area and arrange them in the correct order.)
Answer:
Q26. You administer a Microsoft SQL Server 2012 database that contains a table named OrderDetail. You discover that the NCI_OrderDetail_CustomerID non-clustered index is fragmented. You need to reduce fragmentation.
You need to achieve this goal without taking the index offline. Which Transact-SQL batch should you use?
A. CREATE INDEX NCI_OrderDetail_CustomerID ON OrderDetail.CustomerID WITH DROP EXISTING
B. ALTER INDEX NCI_OrderDetail_CustomerID ON OrderDetail.CustomerID REORGANIZE
C. ALTER INDEX ALL ON OrderDetail REBUILD
D. ALTER INDEX NCI_OrderDetail_CustomerID ON OrderDetail.CustomerID REBUILD
Answer: B
Q27. CORRECT TEXT
You have an XML schema collection named Sales.InvoiceSchema. You need to declare a variable of the XML type named XML1. The solution must ensure that XML1 is validated by using Sales.InvoiceSchema.
Which code segment should you use?
To answer, type the correct code in the answer area.
Answer:
DECLARE @XML1 XML(Sales.InvoiceSchema)
Q28. You are a database developer for an application hosted on a Microsoft SQL Server 2012 server.
The database contains two tables that have the following definitions:
Global customers place orders from several countries.
You need to view the country from which each customer has placed the most orders.
Which Transact-SQL query do you use?
A. SELECT c.CustomerID, c.CustomerName, o.ShippingCountry FROM Customer c INNER JOIN (SELECT CustomerID, ShippingCountry, RANK() OVER (PARTITION BY CustomerID ORDER BY COUNT(OrderAmount) DESC) AS Rnk FROM Orders GROUP BY CustomerID, ShippingCountry) AS o ON c.CustomerID = o.CustomerID WHERE o.Rnk = 1
B. SELECT c.CustomerID, c.CustomerName, o.ShippingCountry FROM (SELECT c.CustomerID, c.CustomerName, o.ShippingCountry, RANK() OVER (PARTITION BY CustomerID ORDER BY COUNT(o.OrderAmount) ASC) AS Rnk FROM Customer c INNER JOIN Orders o ON c.CustomerID = o.CustomerID GROUP BY c.CustomerID, c.CustomerName, o.ShippingCountry) cs WHERE Rnk = 1
C. SELECT c.CustomerID, c.CustomerName, o.ShippingCountry FROM Customer c INNER JOIN (SELECT CustomerID, ShippingCountry, RANK() OVER (PARTITION BY CustomerID ORDER BY OrderAmount DESC) AS Rnk FROM Orders GROUP BY CustomerID, ShippingCountry) AS o ON c.CustomerID = o.CustomerID WHERE o.Rnk = 1
D. SELECT c.CustomerID, c.CustomerName, o.ShippingCountry FROM Customer c INNER JOIN (SELECT CustomerID, ShippingCountry, COUNT(OrderAmount) DESC) AS OrderAmount FROM Orders GROUP BY CustomerID, ShippingCountry) AS o ON c.CustomerID = o.CustomerID ORDER BY OrderAmount DESC
Answer: A
Q29. You have three tables that contain data for dentists, psychiatrists, and physicians. You create a view that is used to look up their email addresses and phone numbers.
The view has the following definition:
You need to ensure that users can update only the phone numbers and email addresses by using this view.
What should you do?
A. Alter the view. Use the EXPAND VIEWS query hint along with each SELECT statement.
B. Create an INSTEAD OF UPDATE trigger on the view.
C. Drop the view. Re-create the view by using the SCHEMABINDING clause, and then create an index on the view.
D. Create an AFTER UPDATE trigger on the view.
Answer: B
Q30. CORRECT TEXT
You need to create a query that calculates the total sales of each OrderlD from a table named Sales.Details. The table contains two columns named OrderlD and ExtendedAmount.
The solution must meet the following requirements:
Use one-part names to reference columns.
Start the order of the results from OrderlD.
NOT depend on the default schema of a user.
Use an alias of TotalSales for the calculated ExtendedAmount.
Display only the OrderlD column and the calculated TotalSales column.
Provide the correct code in the answer area.
Answer: