QUESTION NO: 1
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.CustomerlD = SalesOrder.CustomerlD)
B.
SELECT *
FROM Customer
WHERE 100 > SOME (SELECT OrderTotal
FROM SalesOrder
WHERE Customer.CustomerlD = SalesOrder.CustomerlD)
C.
SELECT *
FROM Customer
WHERE 100 > (SELECT MAX(OrderTotal)
FROM SalesOrder
WHERE Customer.CustomerlD = SalesOrder.CustomerlD)
D.
SELECT *
FROM Customer
WHERE EXISTS (SELECT SalesOrder.CustomerlD
FROM SalesOrder
WHERE Customer.CustomerlD = SalesOrder.CustomerlD
AND SalesOrder.OrderTotal <= 100)
Answer: A
Explanation:
QUESTION NO: 2
You need to generate the following XML document
Which query should you use?
A. SELECT Price, ProductName
FROM Products AS ProductExport
FOR XML PATH('Product')
B. SELECT Price, ProductName
FROM Products
FOR XML AUTO, ROOT('ProductExport')
C. SELECT Price [_Price]
ProductName AS [*]
FROM Products AS ProductExport
FOR XML AUTO, ELEMENTS
D. SELECT Price [_Price]
ProductName AS [*]
FROM Products FOR XML
PATHCProduct'XROOTC'ProductExport')
Answer: D
Explanation:
QUESTION NO: 31
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;
20
B.
WITH Percentages AS (
SELECT *, NTILE(S) OVER (ORDER BY UnitsSold) AS groupingColumn FROM ProductCounts)
SELECT *
FROM Percentages
WHERE groupingColumn = 5;
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(20) OVER (ORDER BY UnitsSold) AS groupingColumn FROM ProductCounts)
SELECT *
FROM Percentages
WHERE groupingColumn = 20;
Answer: D
Explanation:
About us