Latest 70-433 Real Exam Download 51-60

Ensurepass

QUESTION 51

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. You create tables named order, customer, and product as follows: CREATE TABLE [dbo].[order] ([OrderID] [int],

[ProductID] [int],

[CustomerID] [int],

[OrderDate] [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 return all customer names and total number of orders for customers who have placed more than 10 orders. Which SQL query should you use?

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

INNER JOIN[order] o ON p.ProductID = o.ProductID INNER JOINcustomer c ON o.CustomerID = c. CustomerID

WHERE o.OrderDate > ’09/01/2011′ GROUP BY c.CustomerName HAVING AVG(p.SalePrice) >= 500

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

INNER JOIN[order] o ON p.ProductID = o.ProductID INNER JOINcustomer c ON o.CustomerID = c.CustomerID

WHERE o.OrderDate > ’09/01/2011′ AND AVG(p.SalePrice) >= 500

C. SELECT p.ProductName,DATEPART(mm, o.OrderDate) OrderMonth,SUM(p.SalePrice) AS Sales FROM product p

INNER JOIN[order] o ON p.ProductID = o.ProductID GROUP BY p.ProductName, DATEPART(mm, o.OrderDate);

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

INNER JOIN [order] o ON p.ProductID = o.ProductID INNER JOIN customer c ON o.CustomerID = c.CustomerID

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

E. SELECT p.ProductName,DATEPART(mm, o.OrderDate) OrderMonth,SUM(p.SalePrice) AS Sales FROM product p

INNER JOIN [order] o ON p.ProductID = o.ProductID GROUP BY CUBE;

F. SELECT p.ProductName,DATEPART(mm, o.OrderDate) OrderMonth,SUM(p.SalePrice) AS Sales FROM product p

INNER JOIN [order] o ON p.ProductID = o.ProductID

GROUP BY CUBE(p.ProductName, DATEPART(mm, o.OrderDate));

G. SELECT c.CustomerName,COUNT(o.OrderID) AS Orders FROM customer c

INNER JOIN [order] o ON c.CustomerID = o.CustomerID WHERE COUNT(o.OrderID) > 10

GROUP BY c.CustomerName;

H. SELECT p.ProductName,DATEPART(mm, o.OrderDate) OrderMonth,SUM(p.SalePrice) AS Sales FROM product p

INNER JOIN [order] o ON p.ProductID = o.ProductID GROUP BY p.ProductName, OrderMonth;

I. SELECT c.CustomerName,COUNT(o.OrderID) AS Orders FROM customer c

INNER JOIN [order] o ON c.CustomerID = o.CustomerID GROUP BY c.CustomerName

HAVING COUNT(o.OrderID) > 10;

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

INNER JOIN [order] o ON p.ProductID = o.ProductID INNER JOIN customer c ON o.CustomerID = c.CustomerID

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

Correct Answer: I


QUESTION 52

You are using Microsoft SQL Server 2008 Enterprise Edition. You need to maintain a history of all data modifications made to a table, including the type of modification and the values modified.

Which tracking method should you use?

A. Database Audit

B. Change Data Capture

C. Change Tracking

D. C2 Audit Tracing

Correct Answer: B


QUESTION 53

You need to build CREATE INDEX statements for all the missing indexes that SQL Server has identified. Which dynamic management view should you use?

A. sys.dm_db_missing_index_columns

B. sys.dm_db_index_usage_stats

C. sys.dm_db_missing_index_group_stats

D. sys.dm_db_missing_index_details

Correct Answer: D


QUESTION 54

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

B. DENSE_RANK

C. RANK

D. NTILE(10)

Correct Answer: A


QUESTION 55

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. You create tables named order, customer, and product as follows: CREATE TABLE [dbo].[order] ([OrderID] [int],

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

[OrderDate] [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 identify all customers who have ordered for an average amount of more than 500 or more from September 01, 2011. Which SQL query should you use?

A. SELECT p.ProductName,DATEPART(mm, o.OrderDate) OrderMonth,SUM(p.SalePrice) AS Sales FROM product p

INNER JOIN [order] o ON p.ProductID = o.ProductID GROUP BY p.ProductName, DATEPART(mm, o.OrderDate);

B. SELECT c.CustomerName,COUNT(o.OrderID) AS Orders FROM customer c

INNER JOIN [order] o ON c.CustomerID = o.CustomerID

WHERE COUNT(o.OrderID) > 10

GROUP BY c.CustomerName;

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

INNER JOIN [order] o ON p.ProductID = o.ProductID INNER JOIN customer c ON o.CustomerID = c.CustomerID WHERE o.OrderDate > ’09/01/2011′

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

D. SELECT p.ProductName,DATEPART(mm, o.OrderDate) OrderMonth,SUM(p.SalePrice) AS Sales FROM product p

INNER JOIN [order] o ON p.ProductID = o.ProductID

GROUP BY CUBE(p.ProductName, DATEPART(mm, o.OrderDate));

E. SELECT p.ProductName,DATEPART(mm, o.OrderDate) OrderMonth,SUM(p.SalePrice) AS Sales FROM product p

INNER JOIN [order] o ON p.ProductID = o.ProductID GROUP BY CUBE;

F. SELECT c.CustomerName,COUNT(o.OrderID) AS Orders FROM customer c

INNER JOIN [order] o ON c.CustomerID = o.CustomerID GROUP BY c.CustomerName

HAVING COUNT(o.OrderID) > 10;

G. SELECT p.ProductName,DATEPART(mm, o.OrderDate) OrderMonth,SUM(p.SalePrice) AS Sales FROM product p

INNER JOIN [order] o ON p.ProductID = o.ProductID GROUP BY p.ProductName, OrderMonth;

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

INNER JOIN [order] o ON p.ProductID = o.ProductID INNER JOINcustomer c ON o.CustomerID = c.CustomerID

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

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

INNER JOIN [order] o ON p.ProductID = o.ProductID INNER JOIN customer c ON o.CustomerID = c.CustomerID

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

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

INNER JOIN [order] o ON p.ProductID = o.ProductID INNER JOIN customer c ON o.CustomerID = c.CustomerID

WHERE o.OrderDate > ’09/01/2011′ AND AVG(p.SalePrice) >= 500

Correct Answer: C


QUESTION 56

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 in 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 57

You administer a Microsoft SQL Server 2008 database.

The database contains tables named Customer, Subscriptions, and Orders that have the following definitions: CREATE TABLE dbo.Customer (CustomerID int NOT NULL PRIMARY KEY, FirstName varchar(255) NOT NULL,

LastName varchar(255) NOT NULL,

CustomerAddress varchar(1024)) CREATE TABLE dbo.Subscriptions (SubscriptionID int NOT NULL PRIMARY KEY

SubscriptionName varchar(255) NOT NULL

CustomerID int FOREIGN KEY NOT NULL REFERENCES Customer(CustomerID)) CREATE TABLE dbo.Orders (OrderID int NOT NULL PRIMARY KEY

OrderText varchar(255) NOT NULL

CustomerID int FOREIGN KEY NOT NULL REFERENCES Customer(CustomerID)) Customers are considered active if they meet the following requirements:

·Placed an order for a subscription that is recorded in the Subscriptions table.

·Placed an order for an individual product that is recorded in the Orders table. You need to create a view that shows unique rows where the customer either has made an Order or has a Subscription. Which Transact-SQL statement should you use?

A. CREATE VIEW dbo.vw_ActiveCustomers AS

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

WHERE dbo.CustomerId in (SELECT CustomerId

FROM dbo.Subscriptions s UNION ALL

SELECT CustomerId FROM dbo.Orders o)

B. CREATE VIEW dbo.vw_ActiveCustomers AS

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

LEFT OUTER JOIN dbo.Subscriptions s ON c.CustomerID = s.CustomerID LEFT OUTER JOIN dbo.Orders o ON c.CustomerID = o.CustomerID

C. CREATE VIEW dbo.vw_ActiveCustomers AS

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

WHERE CustomerId in (SELECT CustomerId

FROM dbo.Subscriptions s INTERSECT

SELECT CustomerId FROM dbo.Orders o)

D. CREATE VIEW dbo.vw_ActiveCustomers AS

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

INNER JOIN dbo.Subscriptions s ON c.CustomerID = s.CustomerID EXCEPT

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

INNER JOIN dbo.Orders o ON c.CustomerID = o.CustomerID

E. CREATE VIEW dbo.vw_ActiveCustomers AS

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

LEFT OUTER JOIN dbo.Subscriptions s ON c.CustomerID = s.CustomerID LEFT OUTER JOIN dbo.Orders o ON c.CustomerID = o.CustomerID WHERE s.CustomerID IS NOT NULL

OR o.CustomerID IS NOT NULL

F. CREATE VIEW dbo.vw_ActiveCustomers AS

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

INNER OUTER JOIN dbo.Subscriptions s ON c.CustomerID = s.CustomerID INNER OUTER JOIN dbo.Orders o ON c.CustomerID = o.CustomerID

G. CREATE VIEW dbo.vw_ActiveCustomers AS

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

INNER JOIN dbo.Subscriptions s ON c.CustomerID = s.CustomerID UNION ALL

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

INNER JOIN dbo.Orders o ON c.CustomerID = o.CustomerID

H. CREATE VIEW dbo.vw_ActiveCustomers AS

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

WHERE EXISTS (SELECT *

FROM dbo.subscriptions s

WHERE c.CustomerID = s.CustomerID) OR EXISTS

(SELECT *

FROM dbo.orders o

WHERE c.CustomerID = o.CustomerID)

Correct Answer: AH


QUESTION 58

You are the database developer for an order-processing application. After a customer places an order, a confirmation message must be sent to the customer.

The following Transact-SQL batch has been run in the database: ALTER DATABASE NORTHWIND SET ENABLE_BROKER; CREATE MESSAGE TYPE EmailMessageVALIDATION = NONE; CREATE

CONTRACT EmailContract(EmailMessage SENT BY INITIATOR); CREATE QUEUE EmailSendQueue; CREATE QUEUE EMailReceiveQueue; CREATE SERVICE EmailSendServiceON QUEUE EmailSendQueue (EmailContract); CREATE SERVICE EmailReceiveServiceON QUEUE EmailReceiveQueue (EmailContract); You need to place the message in the EmailSendQueue for

the email system to process. Which Transact-SQL batch should you use?

A. DECLARE@EmailDialog UNIQUEIDENTIFIER,@Message NVARCHAR(128); BEGIN DIALOG CONVERSATION @EmailDialog

FROM SERVICE EmailSendService TO SERVICE ‘EmailReceiveService’

ON CONTRACT EmailContractWITH ENCRYPTION = OFF;

SET @Message = N’Dear Sir/Madam. Your order has been received.’;

SEND ON CONVERSATION @EmailDialogMESSAGE TYPE EmailMessage (@Message); GO

B. DECLARE@EmailDialog UNIQUEIDENTIFIER,@Message NVARCHAR(128);

BEGIN DIALOG @EmailDialog FROM SERVICE EmailSendService TO SERVICE ‘EmailReceiveService’ ON CONTRACT EmailContract;

SET @Message = N’Dear Sir/Madam. Your order has been received.’; SEND ON CONVERSATION @EmailDialog (@Message);

C. DECLARE@EmailDialog UNIQUEIDENTIFIER,@Message NVARCHAR(128);

BEGIN DIALOG @EmailDialog FROM SERVICE EmailSendService TO SERVICE EmailReceiveService ON CONTRACT EmailContract;

SET @Message = N’Dear Sir/Madam. Your order has been received.’; SEND ON CONVERSATION @EmailDialog

MESSAGE TYPE EmailMessage (@Message); GO

D. DECLARE@EmailDialog UNIQUEIDENTIFIER,@Message NVARCHAR(128);

BEGIN DIALOG @EmailDialog FROM SERVICE EmailSendService TO SERVICE ‘EmailReceiveService’

ON CONTRACT EmailContractWITH LIFETIME = 1000;

SET @Message = N’Dear Sir/Madam. Your order has been received.’; SEND ON CONVERSATION @EmailDialog

MESSAGE TYPE EmailMessage (@Message); GO

E. DECLARE@EmailDialog BIGINT,@Message NVARCHAR(128); BEGIN DIALOG @EmailDialog

TO SERVICE ‘EmailReceiveService’ ON CONTRACT EmailContract;

SET @Message = N’Dear Sir/Madam. Your order has been received.’; SEND ON CONVERSATION @EmailDialog

MESSAGE TYPE EmailMessage (@Message); GO

F. DECLARE@EmailDialog BIGINT,@Message NVARCHAR(128); BEGIN DIALOG @EmailDialog

TO SERVICE EmailReceiveService FROM SERVICE ‘EmailSendService’ ON CONTRACT EmailContract;

SET @Message = N’Dear Sir/Madam. Your order has been received.’; SEND ON CONVERSATION @EmailDialog

MESSAGE TYPE EmailMessage (@Message); GO

G. DECLARE@EmailDialog BIGINT,@Message NVARCHAR(128); BEGIN DIALOG @EmailDialog

FROM SERVICE EmailSendService TO SERVICE ‘EmailReceiveService’ ON CONTRACT EmailContract;

SET @Message = N’Dear Sir/Madam. Your order has been received.’; SEND ON CONVERSATION @EmailDialog

MESSAGE TYPE EmailMessage (@Message); GO

H. DECLARE@EmailDialog BIGINT,@Message NVARCHAR(128); BEGIN DIALOG @EmailDialog

TO SERVICE ‘EmailReceiveService’ FROM SERVICE EmailSendService ON CONTRACT EmailContract;

SET @Message = N’Dear Sir/Madam. Your order has been received.’;

SEND ON CONVERSATION @EmailDialog MESSAGE TYPE EmailMessage (@Message); GO

Correct Answer: AD


QUESTION 59

You have a table named Sales.PotentialClients. This table contains a column named EmailAddress. You are tasked to develop a report that returns valid ".com" email addresses from Sales.PotentialClients. A valid email address must have at least one character before the @ sign, and one character after the @ sign and before the ".com." You need to write a Transact-SQL statement that returns data to meet the business requirements. Which Transact-SQL statement should you use?

A. SELECT* FROM Sales.PotentialClients WHERE EmailAddress LIKE’_%@_.com’

B. SELECT * FROM Sales.PotentialClients WHERE EmailAddress LIKE ‘_%@_%.com’

C. SELECT * FROM Sales.PotentialClients WHERE EmailAddress LIKE ‘%@%.com’

D. SELECT* FROM Sales.PotentialClients WHERE EmailAddress LIKE’%@%[.]com

Correct Answer: B


QUESTION 60

Your server collation is SQL_Latin1_General_CP1_CI_AS. You have a database named Contoso that has a collation setting of SQL_Scandinavian_Cp850_CI_AS.

You create and populate a temporary table #Person from table dbo.Person in Contoso using the following statements: USE Contoso;CREATE TABLE #Person (LastName nchar(128));INSERT INTO

#Person SELECT LastName FROM dbo.Person;You then run the following command: SELECT * FROM dbo.Person a JOIN #Person b

ON a.LastName = b.LastName; This command returns the following error: Cannot resolve the collation conflict between "SQL_Latin1_General_CP1_CI_AS" and "SQL_Scandinavian_Cp850_CI_AS" in the equal to operation.

You need to resolve the collation conflict. Which Transact-SQL statement should you use?

A. CREATE TABLE #Person (LastName nvarchar(128) COLLATE database_default);

B. CREATE TABLE #Person (LastName nvarchar(128) COLLATE SQL_Latin1_General_CP1_CI_AS);

C. CREATE TABLE #Person (LastName nvarchar(128) SPARSE);

D. CREATE TABLE tmpPerson (LastName nvarchar(128) COLLATE SQL_Latin1_General_CP1_CI_AS);

Correct Answer: A

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