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

Ensurepass

QUESTION 51

You have a table named Sales. You are tasked to list products that have been sold to less

than ten customers. You need to write a query to achieve the task. Which Transact-SQL statement should you use?

 

A.

SELECT ProductID,

COUNT(*) AS CustomerCount

FROM Sales

GROUP BY ProductID, CustomerID

HAVING COUNT(*) < 10;

B.

SELECT ProductID,

COUNT(DISTINCT CustomerID) AS CustomerCount

FROM Sales

GROUP BY ProductID

HAVING COUNT(DISTINCT CustomerID) < 10;

C.

SELECT ProductID, CustomerID,

COUNT(DISTINCT CustomerID) AS CustomerCount

FROM Sales

GROUP BY ProductID, CustomerID

HAVING COUNT(DISTINCT CustomerID) < 10;

D.

SELECT *

FROM

(SELECT ProductID, RANK() OVER (ORDER BY CustomerID DESC) AS Rnk FROM Sales) s

WHERE s.Rnk <= 10;

 

Correct Answer: B

 

 

QUESTION 52

You have two tables named Customers and Orders.

 

for customers that have placed at least one order, you need to produce a list of customer names and the number of orders for each customer.

 

Which query should you use?

 

A.

SELECT c.CustomerName,

SUM(o.OrderID) AS [OrderCount]

FROM Customers c

JOIN Orders o

ON c.CustomerID = o.CustomerID

GROUP BY c.CustomerName

B.

SELECT COUNT(o.OrderId) AS [OrderCount]

FROM CUSTOMERS c

JOIN ORDERS o

ON c.CUSTOMERID = o.CUSTOMERID

C.

SELECT c.CustomerName,

COUNT(o.OrderID) AS [OrderCount]

FROM Customers c

JOIN Orders o

ON c.CustomerID = o.CustomerID

GROUP BY c.CustomerName

HAVING COUNT(o.OrderID) > 1

D.

SELECT c.CustomerName,

COUNT(o.OrderId) AS [OrderCount]

FROM Customers c

JOIN Orders o

ON c.CustomerId = o.CustomerId

GROUP BY c.CustomerName

 

Correct Answer: D

 

 

 

 

 

 

 

QUESTION 53

You have a table named Products. The table contains a column named Color. You need to write a Transact-SQL statement that calculates the percentage of products of each product color. Which Transact-SQL statement should you use?

 

A.

SELECT Color

COUNT(*) OVER(PARTITION BY Color)

/ (COUNT(*) * 1.0) AS PercentColor

FROM Products

GROUP BY Color;

B.

SELECT Color

COUNT(*) OVER() / (COUNT(*) * 1.0) AS PercentColor / (COUNT(*) * 1.0) AS PercentColor

FROM Products

GROUP BY Color;

C.

SELECT Color, (COUNT(*) * 1.0)/ COUNT(*) OVER() AS PercentColor FROM Products

GROUP BY Color;

D.

SELECT Color

COUNT(*) * 1.0) / COUNT(*) OVER(PARTITION BY Color) AS PercentColor FROM Products

GROUP BY Color;

 

Correct Answer: C

 

 

QUESTION 54

You have two tables named SalesPerson and SalesTerritory. You need to create sample data by using a Cartesian product that contains the data from the SalesPerson and SalesTerritory tables.

Which code segment should you use?

 

A.

SELECT p.SalesPersonId,

B.

Name AS [Territory]

FROM Sales.SalesPerson p

FULL JOIN Sales.SalesTerritory t

ON p.TerritoryId = t.TerritoryId

C.

SELECT p.SalesPersonId,

D.

Name AS [Territory]

FROM Sales.SalesPerson p

INNER JOIN Sales.SalesTerritory t

ON p.TerritoryId = t.TerritoryId

E.

SELECT p.SalesPersonId,

F.

Name AS [Territory]

FROM Sales.SalesPerson p

CROSS JOIN Sales.SalesTerritory t

WHERE p.TerritoryId = t.TerritoryId

G.

SELECT p.SalesPersonId,

H.

Name AS [Territory]

FROM Sales.SalesPerson p

CROSS JOIN Sales.SalesTerritory t;

 

Correct Answer: D

 

 

 

 

 

QUESTION 55

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 [SuperVisorName]

 

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.

LEFT JOIN Employees s

ON e.ReportsTo = s.EmployeeId

B.

RIGHT JOIN Employees s

ON e.ReportsTo = s.EmployeeId

C.

INNER JOIN Employees s

ON e.EmployeeId = s.EmployeeId

D.

LEFT JOIN Employees s

ON e.EmployeeId = s.EmployeeId

 

Correct Answer: A

 

 

QUESTION 56

You have a table named Subcategories that contains subcategories for socks, vests and helmets. You have another table named Products that contains products only from the subcategories socks and vests. You have the following query:

 

SELECT s.Name, p.Name AS ProductName

 

FROM Subcategories s

 

OUTER APPLY

 

(SELECT *

 

FROM Products pr

 

WHERE pr.SubcategoryID = s.SubcategoryID) p

 

WHERE s.Name IS NOT NULL;

 

You need to predict the results of the query.

 

What results should the query produce?

 

A.

Name ProductName

——— ——————–

Socks Mountain Bike Socks,

Socks Mountain Bike Socks,

Socks Racing Socks, M

Socks Racing Socks, L

Vests Classic Vest, S

Vests Classic Vest, M

Vests Classic Vest, L

B.

Name ProductName

——— ——————–

Socks Mountain Bike Socks,

Socks Mountain Bike Socks,

Socks Racing Socks, M

Socks Racing Socks, L

Vests Classic Vest, S

Vests Classic Vest, M

Vests Classic Vest, L

Helmets NULL

C.

Name ProductName

——— ——————–

Socks Mountain Bike Socks,

Socks Mountain Bike Socks,

Socks Racing Socks, M

Socks Racing Socks, L

Vests Classic Vest, S

Vests Classic Vest, M

Vests Classic Vest, L

Helmets NULL

NULL NULL

D.

Name ProductName

——— ——————–

Socks Mountain Bike Socks,

Socks Mountain Bike Socks,

Socks Racing Socks, M

Socks Racing Socks, L

Vests Classic Vest, S

Vests Classic Vest, M

Vests Classic Vest, L

NULL Mountain Bike Socks,

NULL Mountain Bike Socks,

NULL Racing Socks, M

NULL Racing Socks, L

NULL Classic Vest, S

NULL Classic Vest, M

NULL Classic Vest, L

Helmets NULL

NULL NULL

 

Correct Answer: B

 

 

 

 

 

 

 

 

 

QUESTION 57

You have two tables named dbo.CurrentProducts and dbo.ArchiveProducts.

 

You have the following query:

 

SELECT ProductID, Name

 

FROM dbo.CurrentProducts

 

UNION ALL

 

SELECT ProductID, Name

 

FROM dbo.ArchiveProducts;

 

You need to predict the list of products that the query will produce.

 

Which list of products should the query return?

 

A.

Products that appear in dbo.CurrentProducts or dbo.ArchiveProducts but not in both.

B.

Products that have a matching ProductID and Name in dbo.CurrentProducts or dbo.ArchiveProducts.

C.

Products that appear in dbo.CurrentProducts or dbo.ArchiveProducts. Products that appear in both tables are listed only once.

D.

Products that appear in dbo.CurrentProducts or dbo.ArchiveProducts. Products that appear in both tables are listed multiple times.

 

Correct Answer: D

 

 

QUESTION 58

You have two tables named Products and NewProducts that have identical structures. You have the following query (Line numbers are included for reference only):

 

01 SELECT Product, Description

 

02 FROM dbo.Products

 

03

 

04 SELECT Product, Description

 

05 FROM dbo.NewProducts

 

You need to choose the appropriate Transact-SQL operator to display rows that exist in both tables. Which Transact-SQL operator should you insert in line 03?

 

A.

UNION

B.

EXCEPT

C.

UNION ALL

D.

INTERSECT

 

Correct Answer: D

 

 

QUESTION 59

You are tasked to create a table that has a column that must store the current time accurate to ten microseconds. You need to use a system function in conjunction with the DEFAULT option in the column definition. Which system function should you use?

 

A.

DATEADD

B.

GETUTCDATE

C.

SYSDATETIME

D.

CURRENT_TIMESTAMP

 

Correct Answer: C

 

 

QUESTION 60

You need to round the value 1.75 to the nearest whole number. Which code segment should you use?

 

A.

Select ROUND(1.75,0)

B.

Select ROUND(1.75,2)

C.

Select ROUND(1.75,1.0)

D.

Select ROUND(1.75,2.0)

 

Correct Answer: A

 

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.