Latest 70-433 Real Exam Download 101-110

Ensurepass

QUESTION 101

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 (MAXDOP 4)

B. OPTION (MAXDOP 0)

C. OPTION (MAXDOP 16)

D. OPTION (MAXDOP 1)

Correct Answer: D


QUESTION 102

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

A. SELECT Year,Region,Total FROM

(SELECT Year,Region,SUM(OrderTotal) AS Total FROM Orders

GROUP BY Year, Region) AS [SalesByYear];

B. CREATE VIEW SalesByYear AS

SELECT Year,Region,SUM(OrderTotal) FROM Orders

GROUP BY Year, Region; GO

SELECT Year,Region,Total FROM SalesByYear;

C. WITH SalesByYear (Year,Region,Total) AS

(SELECT Year,Region,SUM(OrderTotal)

FROM Orders

GROUP BY Year,Region)

SELECT Year,Region,TotalFROM 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: C


QUESTION 103

You have a single CLR assembly in your database. The assembly only references blessed assemblies from the Microsoft .NET Framework and does not access external resources. You need to deploy

this assembly by using the minimum required permissions. You must ensure that your database remains as secure as possible. Which options should you set?

A. PERMISSION_SET = SAFE TRUSTWORTHY OFF

B. PERMISSION_SET = SAFE TRUSTWORTHY ON

C. PERMISSION_SET = UNSAFE TRUSTWORTHY ON

D. PERMISSION_SET = EXTERNAL_ACCESS TRUSTWORTHY OFF

Correct Answer: A


QUESTION 104

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(20) OVER (ORDER BY UnitsSold) AS groupingColumn FROM ProductCounts)

SELECT *

FROM Percentages

WHERE groupingColumn = 20;

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(5) OVER (ORDER BY UnitsSold) AS groupingColumn FROM ProductCounts)

SELECT *

FROM Percentages

WHERE groupingColumn = 5;

Correct Answer: B


QUESTION 105

You have two tables named MainTable and ArchiveTable. You need to move data older than 30 days from MainTable into ArchiveTable. Which code segment should you use?

A. INSERT INTO ArchiveTable SELECT *

FROM MainTable

WHERE RecordDate < DATEADD(D,-30,GETDATE())

B. DELETE FROM MainTable OUTPUT

DELETED.* INTO ArchiveTable

WHERE RecordDate < DATEADD(D,-30,GETDATE())

C. DELETE FROM MainTable OUTPUT deleted.*

WHERE RecordDate < DATEADD(D,-30,GETDATE())

D. INSERT INTO ArchiveTable SELECT *

FROM MainTable

WHERE RecordDate < DATEADD(D,-30,GETDATE())

DELETE FROM MainTable

Correct Answer: B


QUESTION 106

Your database contains sales information for millions of orders. You need to identify the orders with the highest average unit price and an order total greater than 10,000. The list should contain no more than 20 orders. Which query should you use?

A. SELECT TOP (20)o.SalesOrderId,o.OrderDate,o.Total,SUM(od.QTY * od.UnitPrice) / SUM(od.Qty) AS [AvgUnitPrice]

FROM Sales.SalesOrderHeader o

JOIN SALES.SalesOrderDetail od ON o.SalesOrderId = od.SalesOrderId WHERE o.Total> 10000

GROUP BY o.SalesOrderId, o.OrderDate, o.Total ORDER BY AvgUnitPrice;

B. SELECT TOP (20) o.SalesOrderId,o.OrderDate,o.Total,(SELECT SUM(od.Qty * od.UnitPrice) / SUM (od.Qty)

FROM Sales.SalesOrderDetail od

WHERE o.SalesOrderId = od.SalesOrderId) AS [AvgUnitPrice] FROM Sales.SalesOrderHeader o

WHERE o.Total > 10000

ORDER BY o.Total DESC,AvgUnitPrice;

C. SELECT TOP (20) o.SalesOrderId,o.OrderDate,o.Total,(SELECT SUM(od.Qty * od.UnitPrice) / SUM (od.QTY)

[AvgUnitPrice]

FROM Sales.SalesOrderHeader o WHERE o.Total> 10000

ORDER BY AvgUnitPrice DESC;

FROM Sales.SalesOrderDetail od

WHERE o.SalesOrderId = od.SalesOrderId) AS

D. SELECT TOP (20)o.SalesOrderId,o.OrderDate,o.Total,SUM(od.Qty * od.UnitPrice) / SUM(od.Qty) AS [AvgUnitPrice]

FROM Sales.SalesOrderHeader o

JOIN Sales.SalesOrderDetail od ON o.SalesOrderId = od.SalesOrderId WHERE o.Total> 10000GROUP BY o.SalesOrderId, o.OrderDate,

o.TotalORDER BY Total DESC;

Correct Answer: C


QUESTION 107

Note: This
QUESTION is part of a series of questions that use the same set of answer choices. Each answer choice may be used once, more than once, or not at all. 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 return all customer names and total number of complaints for customers who have made more than 10 complaints. Which SQL query should you use?

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

B. 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′

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

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

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

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

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

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

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

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;

Correct Answer: D


QUESTION 108

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 administer a Microsoft SQL Server 2008 database for an inventory management system.

The application contains a product table that has the following definition: CREATE TABLE [Production].[Product](

[ProductID] [int] IDENTITY(1,1) NOT NULL,

[Name] [nvarchar](50) NOT NULL, [ProductNumber] [nvarchar](25) NOT NULL, [Color] [nvarchar](15) NULL,

[Class] [nchar](2) NULL,

[Style] [nchar](2) NULL, [Active] [bit] NOT NULL, [ModifiedDate] [datetime] NOT NULL,

CONSTRAINT [PK_Product_ProductID] PRIMARY KEY CLUSTERED (

[ProductID] ASC

) ON [PRIMARY]) ON [PRIMARY]GO You want to add a new field to the Product table to meet the following requirements:

·Allows user-specified information that will be added to records in the Product table.

·Supports the largest storage size needed for the field.

·Uses the smallest data type necessary to support the domain of values that will be entered by users. You need to add a field named User_Data_1 to support only values that are 1 or 0. Which SQL statement should you use?

A. ALTER TABLE [Production].[Product] ADD [User_Data_1] SMALLDATETIME

B. ALTER TABLE [Production].[Product] ADD [User_Data_1] NUMERIC(11,6) C. ALTER TABLE [Production].[Product] ADD [User_Data_1] TINYINT

C. ALTER TABLE [Production].[Product] ADD [User_Data_1] NVARCHAR(100)

D. ALTER TABLE [Production].[Product] ADD [User_Data_1] BIGINT

E. ALTER TABLE [Production].[Product] ADD [User_Data_1] SMALLMONEY

F. ALTER TABLE [Production].[Product] ADD [User_Data_1] NCHAR(100)

G. ALTER TABLE [Production].[Product] ADD [User_Data_1] DATE

H. ALTER TABLE [Production].[Product] ADD [User_Data_1] VARCHAR(100)

I. ALTER TABLE [Production].[Product] ADD [User_Data_1] DATETIME

J. ALTER TABLE [Production].[Product] ADD [User_Data_1] SMALLINT

K. ALTER TABLE [Production].[Product] ADD [User_Data_1] INT

L. ALTER TABLE [Production].[Product] ADD [User_Data_1] NUMERIC(6,11)

M. ALTER TABLE [Production].[Product] ADD [User_Data_1] DATETIME2

N. ALTER TABLE [Production].[Product] ADD [User_Data_1] CHAR(100) O.

P. ALTER TABLE [Production].[Product] ADD [User_Data_1] BIT

Q. ALTER TABLE [Production].[Product] ADD [User_Data_1] NUMERIC(5,6)

R. ALTER TABLE [Production].[Product] ADD [User_Data_1] MONEY

Correct Answer: P


QUESTION 109

You are responsible for a SQL Server database.

You require the tables to be added or altered only on the first day of the month.

You need to ensure that if the tables are attempted to be modified or created on any other day, an error is received and the attempt is not successful. Which Transact-SQL statement should you use?

A. CREATE TRIGGER TRG_TABLES_ON_FIRST ON DATABASE FOR CREATE_TABLE, ALTER_TABLE

AS

IF DATEPART(day,getdate())>1 BEGIN

RAISERROR (‘Must wait til next month.’, 16, 1) END

B. CREATE TRIGGER TRG_TABLES_ON_FIRSTON DATABASE FOR CREATE_TABLE AS

IF DATEPART(day,getdate())>1 BEGIN

RAISERROR (‘Must wait til next month.’, 16, 1) END

C. CREATE TRIGGER TRG_TABLES_ON_FIRSTON ALL SERVER FOR ALTER_DATABASE AS

IF DATEPART(day,getdate())>1 BEGIN

ROLLBACK

RAISERROR (‘Must wait til next month.’, 16, 1) END

D. CREATE TRIGGER TRG_TABLES_ON_FIRSTON DATABASE FOR CREATE_TABLE, ALTER_TABLE

AS

IF DATEPART(day,getdate())>1 BEGIN

ROLLBACK

RAISERROR (‘Must wait til next month.’, 16, 1) END

Correct Answer: D


QUESTION 110

You have a table named Employees. You want to identify the supervisor to which each employee reports. You write the following query. SELECT e.EmloyeeName AS [EmployeeName], s.EmployeeName AS [SuperV isorName]FROM Employees e You need to ensure that the query returns a list of all employees and their respective supervisor. Which join clause should you use to complete the query?

A. RIGHT JOIN Employees sON e.ReportsTo = s.EmployeeId

B. INNER JOIN Employees sON e.EmployeeId = s.EmployeeId

C. LEFT JOIN Employees sON e.ReportsTo = s.EmployeeId

D. LEFT JOIN Employees sON e.EmployeeId = s.EmployeeId

Correct Answer: C

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