[Free] Get all latest Microsoft 70-433 Actual Tests 61-70

Ensurepass

QUESTION 61

You have a column named TelephoneNumber that stores numbers as varchar(20). You need to write a query that returns the first three characters of a telephone number. Which expression should you use?

 

A.

LEFT(TelephoneNumber, 3)

B.

SUBSTRING(TelephoneNumber, 3, 3)

C.

SUBSTRING (TelephoneNumber, 3, 1)

D.

CHARINDEX(‘[0-9][0-9][0-9]’, TelephoneNumber, 3)

 

Correct Answer: A

 

 

QUESTION 62

You are a database developer located in Seattle. You have a client in Melbourne, which is in a different time zone from Seattle. You have been using the datetimeoffset data type and storing data by using the Seattle offset. You need to display the dates in the Melbourne offset. Which function should you use?

 

A.

CONVERT

B.

DATEADD

C.

SWITCHOFFSET

D.

TODATETIMEOFFSET

 

Correct Answer: C

 

 

 

QUESTION 63

You have a database that contains two tables named ProductCategory and ProductSubCategory.

You need to write a query that returns a list of product categories that contain more than

ten sub-categories. Which query should you use?

 

A.

SELECT [Name]

FROM ProductSubCategory

WHERE ProductCategoryID IN ( SELECT ProductCategoryID FROM ProductCategory)

GROUP BY [Name]

HAVING COUNT(*) > 10

B.

SELECT [Name]

FROM ProductSubCategory

WHERE ProductCategoryID NOT IN (SELECT ProductCategoryID FROM ProductCategory)

GROUP BY [Name]

HAVING COUNT(*) > 10

C.

SELECT [Name]

FROM Product Category c

WHERE EXISTS (SELECT ProductCategoryID

FROM ProductSubCategory

WHERE ProductCategoryID = c.ProductCategoryID

GROUP BY ProductCategoryID

HAVING COUNT(*) > 10)

D.

SELECT [Name]

FROM Product Category c

WHERE NOT EXISTS (SELECT ProductCategoryID

FROM ProductSubCategory

WHERE ProductCategoryID = c.ProductCategoryID

GROUP BY ProductCategoryID

HAVING COUNT(*) > 10)

 

Correct Answer: C

 

 

QUESTION 64

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)

B.

SalesOrderId,

C.

OrderDate,

D.

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;

E.

SELECT TOP (20)

F.

SalesOrderId,

G.

OrderDate,

H.

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 AvgUnitPrice DESC;

I.

SELECT TOP (20)

J.

SalesOrderId,

K.

OrderDate,

L.

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 Total DESC;

M.

SELECT TOP (20)

N.

SalesOrderId,

O.

OrderDate,

P.

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;

 

Correct Answer: B

 

 

QUESTION 65

Your company stores vendor and price information in a database. All items in the database have a list price. You need to increase the list price for all products of only the vendor named Fabrikam by 20.00. Which query should you use?

 

A.

UPDATE Production.Product

SET ListPrice = ListPrice + 20.00

WHERE NOT EXISTS (SELECT VendorId

FROM Purchasing.Vendor);

WHERE VendorName = ‘Fabrikam’);

B.

UPDATE Production.Product

SET ListPrice = ListPrice + 20.00

WHERE VendorId NOT IN (SELECT VendorId

FROM Purchasing.Vendor);

WHERE VendorName = ‘Fabrikam’);

C.

UPDATE Production.Product

SET ListPrice = ListPrice + 20.00

WHERE EXISTS (SELECT VendorId

FROM Purchasing.Vendor

WHERE VendorName = ‘Fabrikam’);

D.

UPDATE Production.Product

SET ListPrice = ListPrice + 20.00

WHERE VendorId IN (SELECT VendorId

FROM Purchasing.Vendor

WHERE VendorName = ‘Fabrikam’);

 

Correct Answer: D

 

 

QUESTION 66

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 > ALL (SELECT OrderTotal

FROM SalesOrder

WHERE Customer.CustomerID = SalesOrder.CustomerID)

B.

SELECT *

FROM Customer

WHERE 100 > SOME (SELECT OrderTotal

FROM SalesOrder

WHERE Customer.CustomerID = SalesOrder.CustomerID)

C.

SELECT *

FROM Customer

WHERE 100 > (SELECT MAX(OrderTotal)

FROM SalesOrder

WHERE Customer.CustomerID = SalesOrder.CustomerID)

D.

SELECT *

FROM Customer

WHERE EXISTS (SELECT SalesOrder.CustomerID

FROM SalesOrder

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

 

Correct Answer: A

 

 

QUESTION 67

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 Customer

 

WHERE Customer.CustomerID IN (SELECT Customer.CustomerID

FROM SalesOrder)

 

You need to identify the results of the query.

 

Which results will the query return?

 

A.

No rows

B.

A warning message

C.

The 1000 rows in the Customer table

D.

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

 

Correct Answer: C

 

 

QUESTION 68

You have the following rows in the Customer Table:

 

clip_image001

 

You write the following query to return all customers that do not have NULL or ‘Dormant’ for their status:

 

SELECT *

 

FROM Customer

 

WHERE Status NOT IN (NULL, ‘Dormant’)

 

You need to identify the results of the query.

 

Which result should you expect?

 

clip_image002

 

A.

Option A

B.

Option B

C.

Option C

D.

Option D

 

Correct Answer: A

 

 

QUESTION 69

You have a table named Employee. You document your company’s organizational hierarchy by inserting the EmployeeID of each employee’s manager in the ReportsTo column.

 

You need to write a recursive query that produces a list of employees and their manager.

The query must also include the employee’s level in the hierarchy.

 

You write the following code segment. (Line numbers are included for reference only.)

 

01 WITH EmployeeList (EmployeeID, FullName, ManagerName, Level)

 

02 AS (

 

03 ………

 

04 )

 

05 SELECT EmployeeID, FullName, ManagerName, Level

 

06 FROM EmployeeList;

 

Which code segment should you insert at line 3?

 

A.

SELECT EmployeeID,

FullName,

” AS [ReportsTo],

1 AS [Level]

FROM Employee

WHERE ReportsTo IS NULL

UNION ALL

SELECT emp.EmployeeID,

emp.FullNName

mgr.FullName,

1 + 1 AS [Level]

FROM Employee emp

JOIN Employee mgr

ON emp.ReportsTo = mgr.EmployeeID

B.

SELECT EmployeeID,

FullName,

” AS [ReportsTo],

1 AS [Level]

FROM Employee

WHERE ReportsTo IS NULL

UNION ALL

SELECT emp.EmployeeID,

emp.FullName,

mgr.FullName,

mgr.Level + 1

FROM EmployeeList mgr

JOIN Employee emp

ON emp.ReportsTo = mgr.EmployeeId

C.

SELECT EmployeeID,

FullName,

” AS [Reports To],

1 AS [Level]

FROM Employee

UNION ALL

SELECT emp.EmployeeID,

emp.FullName,

mgr.FullName,

1 + 1 AS [Level]

FROM Employee emp

LEFT JOIN Employee mgr

ON emp.ReportsTo = mgr.EmployeeID

D.

SELECT EmployeeID,

FullName,

” AS [ReportsTo],

1 AS [Level]

FROM Employee

UNION ALL

SELECT emp.EmployeeID,

emp.FullName,

mgr.FullName,

mgr.Level + 1

FROM EmployeeList mgr

JOIN Employee emp

ON emp.ReportsTo = mgr.EmployeeID

 

Correct Answer: B

 

 

QUESTION 70

You need to determine the result of executing this code segment.

 

clip_image003

 

Which result will be returned?

 

A.

101 rows will be returned with no error.

B.

10,001 rows will be returned with no error.

C.

101 rows will be returned with a maximum recursion error.

D.

10,001 rows will be returned with a maximum recursion error.

 

Correct Answer: C

Explanation:

MAXRECURSION number specifies the maximum number of recursions allowed for this query. Number is a nonnegative integer between 0 and 32767. When 0 is specified, no limit is applied. If this option is not specified, the default limit for the server is 100. When the specified or default number for MAXRECURSION limit is reached during query execution, the query is ended and an error is returned.

 

Instant Access to Download Testing Software & PDF File for Microsoft 70-433 Real Exam

Instant Access to Try Microsoft 70-433 Free Demo

This entry was posted in 70-433 Actual Tests and tagged , , , , , . Bookmark the permalink.