[Free] Get all latest Microsoft 70-433 Actual Tests 71-80

Ensurepass

QUESTION 71

You need to implement a common table expression (CTE). Which code segment should you use?

 

A.

CREATE VIEW SalesByYear

AS

SELECT Year,

Region,

SUM(OrderTotal)

FROM Orders

GROUP BY Year, Region;

GO

SELECT Year,

Region,

Total

FROM SalesByYear;

B.

WITH SalesByYear(Year,Region,Total)

AS (SELECT Year,

Region,

SUM(OrderTotal)

FROM Orders

GROUP BY Year,Region)

SELECT Year,

Region,

Total

FROM SalesByYear;

C.

SELECT Year,

Region,

Total

FROM (SELECT Year,

Region,

SUM(OrderTotal) AS Total

FROM Orders

GROUP BY Year, Region) AS [SalesByYear];

D.

SELECT DISTINCT Year,

Region,

(SELECT SUM(OrderTotal)

FROM Orders SalesByYear

WHERE Orders.Year = SalesByYear.YEAR

AND Orders.Region = SalesByYear.Region) AS [Total] FROM Orders;

 

Correct Answer: B

 

 

QUESTION 72

You are tasked to analyze blocking behavior of the following query:

 

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE

 

WITH Customers AS (

 

SELECT *

 

FROM Customer ),

 

SalesTotal AS (

 

SELECT CustomerId, SUM(OrderTotal) AS AllOrderTotal

 

FROM SalesOrder)

 

SELECT CustomerId, AllOrderTotal

 

FROM SalesTotal

 

WHERE AllOrderTotal > 10000.00;

 

You need to determine if other queries that are using the Customer table will be blocked by this query. You also need to determine if this query will be blocked by other queries that are using the Customer table.

 

What behavior should you expect?

 

A.

The other queries will be blocked by this query. This query will be blocked by the other queries.

B.

The other queries will be blocked by this query. This query will not be blocked by the other queries.

C.

The other queries will not be blocked by this query. This query will be blocked by the other queries.

D.

The other queries will not be blocked by this query. This query will not be blocked by the other queries.

 

Correct Answer: D

 

 

QUESTION 73

You create and populate a table named SiteNavigation by using the following statements:

 

CREATE TABLE SiteNavigation

 

(

 

SiteNavigationId INT PRIMARY KEY,

 

Linktext VARCHAR(10),

 

LinkUrl VARCHAR(40),

 

ParentSiteNavigationId INT NULL REFERENCES SiteNavigation(SiteNavigationId)

 

)

 

INSERT INTO SiteNavigation

 

VALUES (1,’First’,’http://first’,NULL)

 

,(2,’Second’,’http://second’,1)

 

,(3,’Third’,’http://third’,1)

 

,(4,’Fourth’,’http://fourth’,2)

 

,(5,’Fifth’,’http://fifth’,2)

 

,(6,’Sixth’,’http://sixth’,2)

 

,(7,’Seventh’,’http://seventh’,6)

 

,(8,’Eighth’,’http://eighth’,7)

 

You are tasked to write a query to list all site references that are more than two levels from the root node. The query should produce the following results:

 

clip_image001

 

You have written the following query:

 

WITH DisplayHierarchy

 

AS (SELECT LinkText, LinkUrl,

 

SiteNavigationId, ParentSiteNavigationId,

 

0 AS DistanceFromRoot

 

FROM SiteNavigation

 

WHERE ParentSiteNavigationId IS NULL

 

UNION ALL

 

SELECT SiteNavigation.LinkText, SiteNavigation.LinkUrl,

 

SiteNavigation.SiteNavigationId, SiteNavigation.ParentSiteNavigationId,

 

dh.DistanceFromRoot + 1 AS DistanceFromRoot

 

FROM SiteNavigation

 

INNER JOIN DisplayHierarchy dh

 

ON SiteNavigation.ParentSiteNavigationId = dh.SiteNavigationId)

 

SELECT LinkText, LinkUrl, DistanceFromRoot

 

FROM DisplayHierarchy

 

You need to append a WHERE clause to the query.

 

Which clause should you use?

 

A.

WHERE DistanceFromRoot =2

B.

WHERE DistanceFromRoot < 2

C.

WHERE DistanceFromRoot >= 2

D.

WHERE DistanceFromRoot IN (2,3)

 

Correct Answer: C

 

 

QUESTION 74

You have two views named Sales.SalesSummaryOverall and Sales.CustomerAndSalesSummary. They are defined as follows:

 

CREATE VIEW Sales.SalesSummaryOverall

 

AS

 

SELECT CustomerId, SUM(SalesTotal) AS OverallTotal

 

FROM Sales.SalesOrder

 

GROUP BY CustomerId

 

GO

 

CREATE VIEW Sales.CustomerAndSalesSummary

 

AS

 

SELECT Customer.Name,

 

SalesSummaryOverall.OverallTotal,

 

(SELECT AVG(OverallTotal)

 

FROM Sales.SalesSummaryOverall

 

WHERE SalesSummaryOverall.CustomerId =

 

Customer.CustomerId) AS avgOverallTotal,

 

(SELECT MAX(OverallTotal)

 

FROM Sales.SalesSummaryOverall

 

WHERE SalesSummaryOverall.CustomerId =

 

Customer.CustomerId) AS maxOverallTotal,

 

FROM Sales.Customer

 

LEFT OUTER JOIN Sales. Sales.SalesSummaryOverall

 

ON SalesSummaryByYear.CustomerId = Customer.CustomerId

 

GO

 

You have been tasked to modify the Sales.CustomerAndSalesSummary view to remove references to other views. You need to identify a feature to use in the modified version of the Sales.CustomerAndSalesSummary object to achieve the task. Which feature should you use?

 

A.

Table variables

B.

Temporary tables

C.

User-defined table types

D.

Common table expressions

 

Correct Answer: D

 

 

QUESTION 75

You need to write a query that allows you to rank total sales for each salesperson into four groups, where the top 25 percent of results are in group 1, the next 25 percent are in group 2, the next 25 percent are in group 3, and the lowest 25 percent are in group 4. Which Transact-SQL statement should you use?

 

A.

NTILE(1)

B.

NTILE(4)

C.

NTILE(25)

D.

NTILE(100)

 

Correct Answer: B

 

 

QUESTION 76

You need to write a query that uses a ranking function that returns the sequential number of a row within a partition of a result set, starting at 1 for the first row in each partition. Which Transact-SQL statement should you use?

 

A.

RANK

B.

NTILE(10)

C.

DENSE_RANK

D.

ROW_NUMBER

 

Correct Answer: D

Explanation:

ROW_NUMBER returns the sequential number of a row within a partition of a result set, starting at 1 for the first row in each partition.

 

 

QUESTION 77

You have a table named ProductCounts that contains 1000 products as well as the number of units that have been sold for each product. You need to write a query that displays the top 5% of products that have been sold most frequently. Which Transact-SQL code segments should you use?

 

A.

WITH Percentages AS (

SELECT *, NTILE(5) OVER (ORDER BY UnitsSold) AS groupingColumn FROM ProductCounts)

SELECT *

FROM percentages

WHERE groupingColumn =1;

B.

WITH Percentages AS (

SELECT *, NTILE(5) OVER (ORDER BY UnitsSold) AS groupingColumn FROM ProductCounts)

SELECT *

FROM Percentages

WHERE groupingColumn = 5;

C.

WITH Percentages AS (

SELECT *, NTILE(20) OVER (ORDER BY UnitsSold) AS groupingColumn FROM ProductCounts)

SELECT *

FROM Percentages

WHERE groupingColumn = 1;

D.

WITH Percentages AS (

SELECT *, NTILE(20) OVER (ORDER BY UnitsSold) AS groupingColumn FROM ProductCounts)

SELECT *

FROM Percentages

WHERE groupingColumn = 20;

 

Correct Answer: D

 

 

QUESTION 78

You work for an international charity organization. You are writing a query to list the highest 100 different amounts that were donated. You have written the following code segment (Line numbers are included for reference only):

 

01 SELECT *

 

02 FROM (SELECT Customer.CustomerID, SUM(TotalDue) AS TotalGiven,

 

03

 

04 FROM Customer

 

05 JOIN SalesOrder

 

06 ON Customer.CustomerID = SalesOrder.CustomerID

 

07 GROUP BY Customer.CustomerID) AS DonationsToFilter

 

08 WHERE FilterCriteria <= 100

 

You need to insert a Transact-SQL clause in line 03 to complete the query. Which Transact-SQL clause should you insert?

 

A.

RANK() OVER (ORDER BY SUM(TotalDue) DESC) AS FilterCriteria

B.

NTILE(100) OVER (ORDER BY SUM(TotalDue) DESC) AS FilterCriteria

C.

ROW_NUMBER() OVER (ORDER BY SUM(TotalDue) DESC) AS FilterCriteria

D.

DENSE_RANK() OVER (ORDER BY SUM(TotalDue) DESC) AS FilterCriteria

 

Correct Answer: D

 

 

QUESTION 79

You have a database server that has four quad-core processors. This database server executes complex queries that are used to generate reports. You need to force a query to use only one processor core without affecting other queries. Which option should you use?

 

A.

OPTION (FAST 1)

B.

OPTION (MAXDOP 1)

C.

OPTION (RECOMPILE)

D.

OPTION (MAXRECURSION 1)

 

Correct Answer: B

 

 

QUESTION 80

You notice that for a particular set of parameter values the following query sometimes executes quickly and other times executes slowly. You also notice that 90 percent of the rows in the Address table contain the same value for the city.

 

SELECT AddressId,

 

AddressLine1,

 

City,

 

PostalCode

 

FROM Person.Address

 

WHERE City = @city_name

 

AND PostalCode = @postal_code

 

You need to use a query hint that, for the particular set of parameter values, will result in a more consistent query execution time. Which query hint should you use?

 

A.

FAST

B.

MAXDOP

C.

OPTIMIZE FOR

D.

PARAMETERIZATION FORCED

 

Correct Answer: C

 

Instant Access to Download Testing Software & PDF File for Microsoft 70-433 Real Exam

Instant Access to Try Microsoft 70-433 Free Demo

This entry was posted in 70-433 Actual Tests and tagged , , , , , . Bookmark the permalink.