Latest 70-433 Real Exam Download 91-100

Ensurepass

QUESTION 91

A table named Shirts includes an XML column named SizeAndColors that contains the sizes and colors of each shirt, as shown in the following code segment. <shirt size="XS" colors="Red"

/><shirt size="S" colors="Red, Blue" />…<shirt size="XL" colors="Blue, Green" /> You need to return a list of shirt colors available in a specific size. Which code segment should you use?

A. DECLARE @Size VARCHAR(10) = ‘L’

SELECT ShirtStyleName,SizeAndColors.value(‘/shirt[1]/@colors’,’color’) FROM Shirts

WHERE SizeAndColors.exist(‘/shirt[@size=sql:variable("@Size")]’) = 1

B. DECLARE @Size VARCHAR(10) = ‘L’

SELECT ShirtStyleName,SizeAndColors.query(‘data(/shirt[@size=sql:variable("@Size")]/@colors)’) FROM Shirts

C. DECLARE @Size VARCHAR(10) = ‘L’

SELECT ShirtStyleName,SizeAndColors.value(‘/shirt[1][@size =sql:variable("@Size")]/@ colors, ‘color’)

FROM Shirts D.

DECLARE @Size VARCHAR(10) = ‘L’

SELECT ShirtStyleName,SizeAndColors.value(‘/shirt[1]/@colors’,’color’) FROM Shirts

WHERE SizeAndColors.value(‘/shirt[1]/@size’,’varchar(20)’) = @Size

Correct Answer: B


QUESTION 92

You have the following XML document that contains Product information. DECLARE @prodList xml ='<ProductList xmlns="urn:Wide_World_Importers/schemas/Products">

<Product Name="Product1" Category="Food" Price="12.3" />

<Product Name="Product2" Category="Drink" Price="1.2" />

<Product Name="Product3" Category="Food" Price="5.1" />

…</ProductList>’; You need to return a list of products that contains the Product Name, Category, and Price of each product. Which query should you use?

A. SELECT prod.value(‘.[1]/@Name’,’varchar(100)’),prod.value(‘.[1]/@Category’,’varchar(20)’),prod.value (‘.[1]/@Price’,’money’)

FROM @prodList.nodes(‘/ProductList/Product’) ProdList(prod);

B. SELECT prod.value(‘@Name’,’varchar(100)’),prod.value(‘@Category’,’varchar(20)’),prod.value (‘@Price’,’money’)

FROM @prodList.nodes(‘/ProductList/Product’) ProdList(prod);

C. WITH XMLNAMESPACES

(DEFAULT ‘urn:Wide_World_Importers/schemas/Products’)

SELECT prod.value(‘./@Name’,’varchar(100)’),prod.value(‘./@Category’,’varchar(20)’),prod.value(‘./

@Price’,’money’)

FROM @prodList.nodes(‘/ProductList/Product’) ProdList(prod);

D. WITH XMLNAMESPACES

(DEFAULT ‘urn;Wide_World_Importers/schemas/Products’ as o)

SELECT prod.value(‘Name[1]’,’varchar(100)’),prod.value(‘Category[1]’,’varchar(20)’),prod.value(‘Price [1]’,’money’)

FROM @prodList.nodes(‘/o:ProductList/o:Product’) ProdList(prod);

Correct Answer: C


QUESTION 93

You need to capture and record a workload for analysis by the Database Engine Tuning Advisor (DTA). Which tool should you use?

A. Performance Monitor

B. SQL Server Profiler

C. DTA utility

D. Activity Monitor

Correct Answer: B


QUESTION 94

You have a table named Subscribers. You receive an updated list of subscribers. You need to remove subscribers that are no longer on the list. Which clause should you use to remove the subscribers from the table?

A. WHEN NOT MATCHED BY TARGET

B. WHEN MATCHED

C. WHEN NOT MATCHED BY SOURCE

D. WHEN NOT MATCHED

Correct Answer: C


QUESTION 95

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 sales of all products that have complaints by the following entries:

·The product name

·The month the product had a complaint

·The product name and the month the product had a complaint

·The grand total of all sales Which SQL query should you use?

A. 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;

B. 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 ProductName, DATEPART(mm, com.ComplaintDate);

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 ProductName, ComplaintMonth;

D. 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), ());

E. 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), ());

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

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

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

HAVING COUNT(com.ComplaintID) > 10;

H. SELECTc.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′

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

I. 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));

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: I


QUESTION 96

Your company’s database contains Customers and Orders tables. You have been tasked to write a SELECT statement that outputs customer and order data as a valid and well-formed XML document. You are required to mix attribute and element based XML within the document. You

have determined that using the FOR XML AUTO clause will not be suitable. You need to identify the correct FOR XML clause to meet the requirement. Which FOR XML statement should you use? (Each correct answer represents a complete solution. Choose two.)

A. FOR BROWSE

B. FOR XML RAW

C. FOR XML PATH

D. FOR XML EXPLICIT

Correct Answer: CD


QUESTION 97

You have two tables named Customer and SalesOrder. You need to identify all customers that have not yet made any purchases and those that have only made orders with an OrderTotal less than 100. Which query should you use?

A. SELECT *

FROM Customer

WHERE 100 > SOME (SELECT OrderTotal

FROM SalesOrder

WHERE Customer.CustomerID = SalesOrder.CustomerID)

B. SELECT *

FROM Customer

WHERE EXISTS (SELECT SalesOrder.CustomerID FROM SalesOrder

WHERE Customer.CustomerID = SalesOrder.CustomerID AND SalesOrder.OrderTotal <= 100)

C. SELECT *

FROM Customer

WHERE 100 > ALL (SELECT OrderTotal

FROM SalesOrder

WHERE Customer.CustomerID = SalesOrder.CustomerID)

D. SELECT *

FROM Customer

WHERE 100 > (SELECT MAX(OrderTotal)

FROM SalesOrder

WHERE Customer.CustomerID = SalesOrder.CustomerID)

Correct Answer: C


QUESTION 98

You administer a Microsoft SQL Server 2008R2 database that hosts a customer relationship management (CRM) application.

The application supports the following two types of customers as shown in the exhibit. (Click the Exhibit button.)

·Business customers who have shipments sent to their office locations

·Residential customers who have shipments sent to their home address You need to generate a list of residential customers who live outside North America. 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.)

Exhibit:

clip_image001

Build List and Reorder:

clip_image002

Correct Answer:

clip_image003


QUESTION 99

You are tasked to analyze blocking behavior of the following query: SET TRANSACTION ISOLATION LEVEL SERIALIZABLEWITH Customers AS (

SELECT *

FROM Customer ),SalesTotal AS (

SELECT CustomerId, SUM(OrderTotal) AS AllOrderTotal FROM SalesOrder)SELECT CustomerId, AllOrderTotalFROM

SalesTotalWHERE 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 not be blocked by this query.This query will not 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 be blocked by this query.This query will be blocked by the other queries.

Correct Answer: A


QUESTION 100

You administer a Microsoft SQL Server 2008 R2 database that has a table named Customer.

The table has the following definition: CREATE TABLE Customer

(CustomerID int NOT NULL PRIMARY KEY, FirstName varchar(255) NOT NULL, LastName varchar(255) NOT NULL,

CustomerAddress varchar(1024)) The database also has a table named PreferredCustomerList. Data will be added to the PreferredCustomerList table regularly.

The PreferredCustomerList table has the following definition: CREATE TABLE PreferredCustomerList

(FirstName varchar(255) NOT NULL,

LastName varchar(255) NOT NULL) You need to create a view that returns all records and columns of the Customer table that are also present in the PreferredCustomerList table. Which

Transact-SQL statement should you use?

A. CREATE VIEW vw_ValidCustomer AS

SELECT FirstName,LastName FROM Customer c

EXCEPT

SELECT FirstName,LastName FROM PreferredCustomerList

B. CREATE VIEW vw_ValidCustomer AS

SELECT c.CustomerID,c.FirstName,c.LastName,c.CustomerAddress FROM Customer c

INNER JOIN PreferredCustomerList cel ON c.Firstname = cel.FirstName AND c.LastName = cel. LastName

C. CREATE VIEW vw_ValidCustomer AS

SELECT c.CustomerID,c.FirstName,c.LastName,c.CustomerAddress FROM Customer c

LEFT OUTER JOIN PreferredCustomerList cel ON c.Firstname = cel.FirstName AND c.LastName = cel.LastName

WHERE cel.FirstName IS NULL

D. CREATE VIEW vw_ValidCustomer AS

SELECT c.CustomerID,c.FirstName,c.LastName,c.CustomerAddress FROM Customer c

INNER JOIN PreferredCustomerList cel ON c.Firstname = cel.FirstName INNER JOIN PreferredCustomerList cel ON c.LastName = cel.LastName

E. CREATE VIEW vw_ValidCustomer AS

SELECT c.CustomerID,c.FirstName,c.LastName,c.CustomerAddress FROM Customer c

EXCEPT

SELECT c.CustomerID,c.FirstName,c.LastName,c.CustomerAddress FROM Customer c

INNER JOIN PreferredCustomerList cel ON c.Firstname = cel.FirstName AND c.LastName = cel.

LastName

F. CREATE VIEW vw_ValidCustomer AS

SELECT c.CustomerID,c.FirstName,c.LastName,c.CustomerAddress FROM Customer c

INTERSECT

SELECT c.CustomerID,c.FirstName,c.LastName,c.CustomerAddress FROM Customer c

INNER JOIN PreferredCustomerList cel ON c.Firstname = cel.FirstName AND c.LastName = cel. LastName

G. CREATE VIEW vw_ValidCustomer AS

SELECT c.CustomerID,c.FirstName,c.LastName,c.CustomerAddress FROM Customer c

LEFT OUTER JOIN PreferredCustomerList cel ON c.Firstname = cel.FirstName AND c.LastName = cel.LastName

WHERE cel.LastName IS NULL

H. CREATE VIEW vw_ValidCustomer AS

SELECT CustomerID,FirstName,LastName,CustomerAddress FROM Customer c

EXCEPT

SELECT CustomerID,FirstName,LastName,CustomerAddress FROM PreferredCustomerList

Correct Answer: BF


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