Mar 2016 updated: Pass4sure Microsoft 70-461 free practice questions 16-30

70-461 Royal Pack Testengine pdf

100% Actual & Verified — 100% PASS

Unlimited access to the world's largest Dumps library! Try it Free Today!

Get it now →

Product Description:
Exam Number/Code: 70-461
Exam name: Querying Microsoft SQL Server 2012
n questions with full explanations
Certification: Microsoft Certification
Last updated on Global synchronizing

Free Certification Real IT 70-461 Exam pdf Collection

Question No. 16

You are a database developer of a Microsoft SQL Server 2012 database. 

The database contains a table named Customers that has the following definition: 


You need to ensure that the CustomerId column in the Orders table contains only values that exist in the CustomerId column of the Customer table. 

Which Transact-SQL statement should you use? 

A. ALTER TABLE Orders 

ADD CONSTRAINT FX_Orders_CustomerID FOREIGN KEY (CustomerId) REFERENCES 

Customer (CustomerId) 

B. ALTER TABLE Customer 

ADD CONSTRAINT FK_Customer_CustomerID FOREIGN KEY {CustomerID) 

REFERENCES 

Orders (CustomerId) 

C. ALTER TABLE Orders 

ADD CONSTRAINT CK_Crders_CustomerID 

CHECK (CustomerId IN (SELECT CustomerId FROM Customer)) 

D. ALTER TABLE Customer 

ADD OrderId INT NOT NULL; 

ALTER TABLE Customer 

ADD CONSTRAINT FK_Customer_OrderID FOREIGN KEY (CrderlD) REFERENCES 

Orders 

(CrderlD); 

E. ALTER TABLE Orders 

ADD CONSTRAINT PK Orders CustomerId PRIMARY KEY (CustomerID) 

Answer:

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


Question No. 17

CORRECT TEXT 

You have a database that contains the tables as shown in the exhibit. (Click the Exhibit button.) 


You need to create a query that returns a list of products from Sales.ProductCatalog. The solution must meet the following requirements: 

UnitPrice must be returned in descending order. 

The query must use two-part names to reference the table. 

The query must use the RANK function to calculate the results. 

The query must return the ranking of rows in a column named PriceRank. 

The list must display the columns in the order that they are defined in the table. 

PriceRank must appear last. 

Which code segment should you use? 

To answer, type the correct code in the answer area. 

Answer: 


Question No. 18

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:


Question No. 19

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:


Question No. 20

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: 



Question No. 21

You develop 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 not include customers who have not placed any orders. 

Which Transact-SQL query should you use? 

A. SELECT CustomerName, OrderDate FROM Customers LEFT OUTER JOIN Orders ON Customers.CuscomerlD = Orders.CustomerId 

B. SELECT CustomerName, OrderDate FROM Customers RIGHT OUTER 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 JOIN Orders ON Customers.CustomerId = Orders.CustomerId 

Answer:


Question No. 22

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:


Question No. 23

CORRECT TEXT 

You have a database that contains the tables shown in the exhibit. (Click the Exhibit button). 


You need to create a query for a report. The query must meet the following requirements: 

NOT use object delimiters. 

Return the most recent orders first. 

Use the first initial of the table as an alias. 

Return the most recent order date for each customer. 

Retrieve the last name of the person who placed the order. 

Return the order date in a column named MostRecentOrderDate that appears as 

the last column in the report. The solution must support the ANSI SQL-99 standard. 

Which code segment should you use? 

To answer, type the correct code in the answer area. 

Answer: 


Question No. 24

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, LEAD(Profit, 1, 0) OVER (PARTITION BY Territory ORDER BY Year) AS PrevProfit FROM Profits 

B. SELECT Territory, Year, Profit, LAG(Profit, 1, 0) OVER (PARTITION BY Year ORDER BY Territory) AS PrevProfit FROM Profits 

C. SELECT Territory, Year, Profit, LAG(Profit, 1, 0) OVER (PARTITION BY Territory ORDER BY Year) AS PrevProfit FROM Profits 

D. SELECT Territory, Year, Profit, LEAD(Profit, 1, 0) OVER (PARTITION BY Year ORDER BY Territory) AS PrevProfit FROM Profits 

Answer:


Question No. 25

CORRECT TEXT 

You need to create a table named OrderDetails on a new server. OrderDetails must meet the following requirements: 

. Contain a new column named LineltemTotal that stores the product of ListPrice 

and Quantity for each row. 

. The calculation for a line item total must not be run every time the table is queried. 

. The code must NOT use any object delimiters. 

The solution must ensure that LineItemTotal is stored as the last column in the table. 

Part of the correct T-SQL statement has been provided in the answer area. Provide the complete code. 


Answer: 


Question No. 26

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: 



Question No. 27

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 preceding year. Which Transact-SQL query should you use? 

A. SELECT Territory, Year, Profit, LAG(Profit, 1, 0) OVER(PARTITION BY Year ORDER BY Territory) AS NextProfit FROM Profits 

B. SELECT Territory, Year, Profit, LAG(Profit, 1, 0) OVER(PARTITION BY Territory ORDER BY Year) AS NextProfit FROM Profits 

C. SELECT Territory, Year, Profit, LEAD(Profit, 1, 0) OVER(PARTITION BY Territory ORDER BY Year) AS NextProfit FROM Profits 

D. SELECT Territory, Year, Profit, LEAD(Profit, 1, 0) OVER(PARTITION BY Year ORDER BY Territory) AS NextProfit FROM Profits 

Answer:


Question No. 28

You are writing a set of queries against a FILESTREAM-enabled database. 

You create a stored procedure that will update multiple tables within a transaction. 

You need to ensure that if the stored procedure raises a runtime error, the entire 

transaction is terminated and rolled back. 

Which Transact-SQL statement should you include at the beginning of the stored 

procedure? 

A. SET TRANSACTION ISOLATION LEVEL SERIALIZABLE 

B. SET XACT_ABORT OFF 

C. SET TRANSACTION ISOLATION LEVEL SNAPSHOT 

D. SET IMPLICIT_TRANSACTIONS ON 

E. SET XACT_ABORT ON 

F. SET IMPLICIT TRANSACTIONS OFF 

Answer:


Question No. 29

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

Answer:

Explanation: 

Decimal is the only one in the list that can give a fixed precision and scale. Reference: http://msdn.microsoft.com/en-us/library/ms187746.aspx 


Question No. 30

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: