Latest 70-433 Real Exam Download 31-40

Ensurepass

QUESTION 31

You have two tables named SalesPerson and SalesTerritory. You need to create sample data by using a Cartesian product that contains the data from the SalesPerson and SalesTerritory tables. Which code segment should you use?

A. SELECT p.SalesPersonId,t.Name AS [Territory] FROM Sales.SalesPerson p

CROSS JOIN Sales.SalesTerritory t WHERE p.TerritoryId = t.TerritoryId

B. SELECT p.SalesPersonId,t.Name AS [Territory] FROM Sales.SalesPerson p

CROSS JOIN Sales.SalesTerritory t

C. SELECT p.SalesPersonId,t.Name AS [Territory] FROM Sales.SalesPerson p

INNER JOIN Sales.SalesTerritory t ON p.TerritoryId = t.TerritoryId

D. SELECT p.SalesPersonId,t.Name AS [Territory] FROM Sales.SalesPerson p

FULL JOIN Sales.SalesTerritory t ON p.TerritoryId = t.TerritoryId

Correct Answer: B


QUESTION 32

You are given a database design to evaluate. All of the tables in this database should have a clustered index. You need to determine the tables that are missing a clustered index by using the system catalog views. Which Transact-SQL statement should you use?

A. SELECT name AS table_name FROM sys.tables

WHERE OBJECTPROPERTY(object_id,’TableHasClustIndex’) = 0 ORDER BY name;

B. SELECT name AS table_name FROM sys.tables

WHERE OBJECTPROPERTY(object_id,’TableHasClustIndex’) = 0 AND OBJECTPROPERTY(object_id,’TableHasUniqueCnst’) = 1 ORDER BY name;

C. SELECT name AS table_name FROM sys.tables

WHERE OBJECTPROPERTY(object_id,’TableHasUniqueCnst’) = 0 ORDER BY name;

D. SELECT name AS table_name FROM sys.tables

WHERE OBJECTPROPERTY(object_id,’TableHasClustIndex’) = 1 AND OBJECTPROPERTY(object_id,’TableHasUniqueCnst’) = 1 ORDER BY name;

Correct Answer: A


QUESTION 33

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: 01 SELECT *02 FROM (SELECT Customer.CustomerID, SUM(TotalDue) AS TotalGiven, 03 04

FROM Customer05

JOIN SalesOrder 06

ON Customer.CustomerID = SalesOrder.CustomerID07

GROUP BY Customer.CustomerID) AS DonationsToFilter08 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. NTILE(100) OVER (ORDER BY SUM(TotalDue) DESC) AS FilterCriteria

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

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

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

Correct Answer: B


QUESTION 34

You are a database developer for your organization. You have an application hosted on Microsoft SQL Server 2008 R2.

One of the tables in the application database has the following definition: CREATE TABLE XMLData(XMLPage xml NOT NULL) No indexes, keys, or constraints are defined along with the table. The table currently contains more than one million entries. An example of the XML data is shown below: Company

<Employee Name = "Mark" = "Department Finance"/>

<Employee Name = "Peter" = "Department Sales"/>

<Employee Name = "Susan" = "Department Facilities"/>Company A stored procedure is executed in the application.

The stored procedure has the following definition:

CREATE PROCEDURE p_GetFinanceUser(@Name varchar(20))AS SELECT Name = Company.Employee.value(‘@Name’,’varchar(20)’)

,Department = Company.Employee.value(‘@Department’,’varchar(20)’)FROM XMLData cCROSS APPLY XMLPage.nodes(‘/Company/Employee’) Department(Employee)WHERE Company.Employee.exist(‘@Department[.="Finance"]’) = 1 Users report that the response time of the stored procedure has decreased. You need to ensure that the response time of the stored procedure is improved. Which three 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.)

Build List and Reorder:

clip_image001

Correct Answer:

clip_image002


QUESTION 35

You have a table named Books that has columns named BookT itle and Description. There is a full-text index on these columns. You need to return rows from the table in which the word ‘computer’ exists in either column. Which code segment should you use?

A. SELECT *FROM Books WHERE BookTitle LIKE ‘%computer%’

B. SELECT *FROM Books WHERE BookTitle = ‘%computer%’ OR Description = ‘%computer%’

C. SELECT *FROM Books WHERE FREETEXT(BookTitle,’computer’)

D. SELECT *FROM Books WHERE FREETEXT(*,’computer’)

Correct Answer: D


QUESTION 36

You administer a Microsoft SQL Server 2008 database that includes a table named ProductDetails.

The Products table has the following schema: CREATE TABLE dbo.ProductDetails( ProductID nchar(4) NOT NULL,

ProductName nvarchar(50) NULL, ProductDescription nvarchar(50) NULL, UnitCost money NULL,

UnitPrice money NULL, ReOrderLevel int NOT NULL,

CONSTRAINT PK_ProductDetails PRIMARY KEY CLUSTERED (

ProductID ASC

)) GO You create a User-Defined Function (UDF) in the same database.

The UDF has the following schema: CREATE FUNCTION dbo.CalculateProductProfit(@ProductID nchar(4))RETURNS MoneyWITH SCHEMABINDINGASBEGIN

DECLARE @Profit Money;

SELECT @Profit = UnitPrice – UnitCost FROM dbo.ProductDetails WHERE ProductID =

@ProductID;

RETURN @Profit;END You need to meet the following requirements:

·Ensure that the UnitPrice column in the ProductDetails table does not accept NULL values.

·Use the CalculateProductProfit() UDF to calculate profit for each product.

·Avoid accidental drop or change of the Product table. Which three 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.)

Build List and Reorder:

clip_image003

Correct Answer:

clip_image004


QUESTION 37

A table named Locations contains 5000 locations and the number of sales made at each location.You need to display the top 5 percent of locations by sales made. Which Transact-SQL code segment should you use?

A. WITH Percentages AS

( SELECT *, NTILE(20) OVER (ORDER BY SalesMade) AS

groupingColumnFROM Locations) SELECT *

FROM Percentages

WHERE groupingColumn = 20;

B. WITH Percentages AS

(SELECT *, NTILE(5) OVER (ORDER BY SalesMade) AS

groupingColumnFROM Locations) SELECT *

FROM percentages

WHERE groupingColumn =1;

C. WITH Percentages AS

(SELECT *, NTILE(20) OVER (ORDER BY SalesMade) AS

groupingColumnFROM Locations) SELECT *

FROM Percentages

WHERE groupingColumn = 1;

D. WITH Percentages AS

(SELECT *, NTILE(5) OVER (ORDER BY SalesMade) AS

groupingColumnFROM Locations) SELECT *

FROM Percentages

WHERE groupingColumn = 5;

Correct Answer: A


QUESTION 38

Your company manufactures and distributes bowling balls. You have a full-text catalog named ftCatalog which contains the ftInventory index on the Products table. Your marketing department has just inserted a new bowling ball into the Inventory table. You notice only the new bowling ball is not being included in the results of the full-text searches. You have confirmed that the row exists in the Products table.

You need to update the full-text catalog in the least amount of time. Which Transact-SQL statement should you use?

A. ALTER FULLTEXT INDEX ON ftInventory RESUME POPULATION

B. ALTER FULLTEXT CATALOG ftCatalog REBUILD

C. ALTER FULLTEXT INDEX ON ftInventory START UPDATE POPULATION

D. ALTER FULLTEXT INDEX ON ftInventory START FULL POPULATION

Correct Answer: C


QUESTION 39

Note: This
QUESTION is part of a series of questions that use the same set of answer choices. An answer choice may be correct for more than one
QUESTION in the series. You are a developer for a Microsoft SQL Server 2008 R2 database instance used to support a customer service application. You create tables named complaint, customer, and product as follows: CREATE TABLE [dbo].[complaint] ([ComplaintID] [int],

[ProductID] [int], [CustomerID] [int],

[ComplaintDate] [datetime]); CREATE TABLE [dbo].[customer] ([CustomerID] [int], [CustomerName] [varchar](100),

[Address] [varchar](200), [City] [varchar](100),

[State] [varchar](50),

[ZipCode] [varchar](5)); CREATE TABLE [dbo].[product] ([ProductID] [int], [ProductName] [varchar](100),

[SalePrice] [money],

[ManufacturerName] [varchar](100)); You need to write a query to sum the monthly sales of each product. Which SQL query should you use?

A. SELECT p.ProductName,DATEPART(mm, com.ComplaintDate) ComplaintMonth,SUM(p.SalePrice) AS Sales

FROM product p

INNER JOIN complaint com ON p.ProductID = com.ProductID GROUP BY p.ProductName, ComplaintMonth;

B. B. SELECT c.CustomerName,p.ProductName,SUM(p.SalePrice) AS Sales FROM product p

INNER JOIN complaint com ON p.ProductID = com.ProductID INNER JOIN customer c ON com.CustomerID = c.CustomerID

GROUP BY GROUPING SETS ((c.CustomerName), (p.ProductName), ());

C. SELECT p.ProductName,DATEPART(mm, com.ComplaintDate) ComplaintMonth,SUM(p.SalePrice) AS Sales

FROM product p

INNER JOIN complaint com ON p.ProductID = com.ProductID GROUP BY p.ProductName, DATEPART(mm, com.ComplaintDate);

D. . SELECT c.CustomerName,AVG(p.SalePrice) AS Sales FROM product p

INNER JOIN complaint com ON p.ProductID = com.ProductID INNER JOINcustomer c ON com.CustomerID = c.CustomerID WHERE com.ComplaintDate > ’09/01/2011′

GROUP BY c.CustomerName HAVING AVG(p.SalePrice) >= 500

E. SELECT p.ProductName,DATEPART(mm, com.ComplaintDate) ComplaintMonth,SUM(p.SalePrice) AS Sales

FROM product p

INNER JOIN complaint com ON p.ProductID = com.ProductID

GROUP BY CUBE(p.ProductName, DATEPART(mm, com.ComplaintDate));

F. SELECT c.CustomerName,COUNT(com.ComplaintID) AS complaints FROM customer c

INNER JOIN complaint com ON c.CustomerID = com.CustomerID GROUP BYc.CustomerName

HAVING COUNT(com.ComplaintID) > 10;

G. SELECT c.CustomerName,AVG(p.SalePrice) AS Sales FROM product p

INNER JOIN complaint com ON p.ProductID = com.ProductID INNER JOIN customer c ON com.CustomerID = c.CustomerID WHERE com.ComplaintDate > ’09/01/2011′

AND AVG(p.SalePrice) >= 500

H. SELECT c.CustomerName,p.ProductName,SUM(p.SalePrice) AS Sales FROM product p

INNER JOIN complaint com ON p.ProductID = com.ProductID INNER JOIN customer c ON com.CustomerID = c.CustomerID

GROUP BY GROUPING SETS ((c.CustomerName, p.ProductName), ());

I. SELECT c.CustomerName,COUNT(com.ComplaintID) AS Complaints FROM customer c

INNER JOIN complaint com ON c.CustomerID = com.CustomerID WHERE COUNT(com.ComplaintID) > 10

GROUP BY c.CustomerName;

J. SELECT p.ProductName,DATEPART(mm, com.ComplaintDate) ComplaintMonth,SUM(p.SalePrice) AS Sales

FROM product p

INNER JOIN complaint com ON p.ProductID = com.ProductID GROUP BY CUBE;

Correct Answer: A


QUESTION 40

You have the following two tables. ProductsProductID ProductName VendorID1 Product1 02

Product2 13

Product3 14

Product4 0 ProductChangesProductID ProductName VendorID1 Product1 12

Product2 13 NewProduct3 25

Product5 1 You execute the following statement. MERGE Products USING ProductChanges

ON (Products.ProductID = ProductChanges.ProductID)WHEN MATCHED AND Products.VendorID = 0

THEN DELETEWHEN MATCHED

THEN UPDATE SET Products.ProductName = ProductChanges.ProductName, Products.VendorID = ProductChanges.VendorID; You need to identify the rows that will be displayed in the Products table.

Which rows will be displayed?

A. ProductID ProductName VendorID

1 Product1 1

2 Product2 1

3 NewProduct3 2

4 Product4 0

5 Product5 1

B. ProductID ProductName VendorID

1 Product1 1

2 Product2 1

3 NewProduct3 2

5 Product5 1

C. ProductID ProductName VendorID

2 Product2 1

3 NewProduct3 2

4 Product4 0

D. ProductID ProductName VendorID

2 Product2 1

3 NewProduct3 2

Correct Answer: C


 

Download Latest 70-433 Real Free Tests , help you to pass exam 100%.