Latest 70-433 Real Exam Download 141-145

Ensurepass

QUESTION 141

You have a table named Sales.SalesOrderHeader and a table named Person.Person.

You are tasked to write a query that returns SalesOrderID and SalesPersonName that have an OrderDate greater than 20040101. SalesPersonName should be made up by concatenating the columns named FirstName and LastName from the table named Person.Person.

You need to write a query to return data, sorted in alphabetical order, by the concatenation of FirstName and LastName. Which Transact-SQL statement should you use?

A. SELECT SalesOrderID, FirstName + ‘ ‘ + LastName as SalesPersonName FROM Sales.SalesOrderHeader H

JOIN Person.Person P on P.BusinessEntityID = H.SalesPersonID WHERE OrderDate > ‘20040101’

ORDER BY SalesPersonName DESC

B. SELECT SalesOrderID, FirstName + ‘ ‘ + LastName as SalesPersonName FROM Sales.SalesOrderHeader H

JOIN Person.Person P on P.BusinessEntityID = H.SalesPersonID WHERE OrderDate > ‘20040101’

ORDER BY FirstName ASC, LastName ASC

C. SELECT SalesOrderID, FirstName +’ ‘ + LastName as SalesPersonName FROM Sales.SalesOrderHeader H

JOIN Person.Person P onP.BusinessEntityID = H.SalesPersonID WHERE OrderDate > ‘20040101’

ORDER BY SalesPersonName ASC

D. SELECT SalesOrderID, FirstName + ‘ ‘ + LastName as SalesPersonName FROM Sales.SalesOrderHeader H

JOIN Person.Person P onP.BusinessEntityID = H.SalesPersonID WHERE OrderDate > ‘20040101’

ORDER BY FirstName DESC, LastName DESC

Correct Answer: C


QUESTION 142

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 sum the sales made to each customer by the following entries:

·Each customer name

·Each product name

·The grand total of all sales 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 CUBE(p.ProductName, DATEPART(mm, o.OrderDate));

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

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

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

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

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

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, DATEPART(mm, o.OrderDate);

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 CUBE;

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

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

Correct Answer: B


QUESTION 143

You have two tables named Customer and SalesOrder. In the Customer table you have 1000 customers, of which 900 customers have orders in the SalesOrder table. You execute the following query to list all customers that have had at least one sale.

SELECT *FROM

CustomerWHERE Customer.CustomerID IN (SELECT SalesOrder.CustomerID

FROM SalesOrder) You need to identify the results of the query. Which results will the query return?

A. No rows

B. The 900 rows in the Customer table with matching rows in the SalesOrder table

C. The 1000 rows in the Customer table

D. A warning message

Correct Answer: B


QUESTION 144

You attempt to query sys.dm_db_index_usage_stats to check the status on the indexes in the Contoso database. The query fails and you receive the following error:

"The user does not have permission to perform this action."

You need to have the least amount of permissions granted to access the dynamic management views. Which permissions should be granted?

A. CREATE EXTERNAL ACCESS ASSEMBLY

B. SELECT

C. CONTROL

D. VIEW SERVER STATE

Correct Answer: D


QUESTION 145

You have created an assembly that utilizes unmanaged code to access external resources. You need to deploy the assembly with the appropriate permissions. Which permission set should you use?

A. UNSAFE

B. EXTERNAL_ACCESS

C. SAFE

D. Default permission set

Correct Answer: A

 

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