Question 48

--Query01

Select Top 10 OrderId , Convert(char(10), OrderDate,121) Last_Paris_Order , (Select Convert(char(10),max(OrderDate),121) from Orders) Last_OrderDate , DateDiff(dd,OrderDate, (Select Max(OrderDate)from Orders)) Day_Diff From Orders Where ShipCity = 'Paris' Order By OrderDate desc

Use Query01 from above.

What is the longest number of days that between the actual order date and the very last order date where the ShipCity equal Paris?

850

176

215

14

Question 49

The inner query of the following query statement is known as a _____?

Select Distinct OrderId From [Order Details] OD Where Quantity < (Select Avg(Quantity) * .1 From [Order Details] Where OD.ProductID = ProductID )

subselect

relational subquery

correlated subquery

join subselect

Question 50

Which of the following queries correctly answer the following problem statement?

Write a query that returns the three most recent orders for each client.

		SELECT CustomerID, Orders.OrderDate, Orders.OrderID FROM Orders WHERE EXISTS (Select top 3 MAX(Orders.OrderDate), [Customers].CustomerID FROM Customers Join Orders on orders.CustomerID = Customers.CustomerID GROUP BY Customers.CustomerID ) ORDER BY CustomerID, OrderDate, OrderID
		SELECT Customers.CustomerID, Orders.OrderDate, Orders.OrderID FROM Customers INNER JOIN Orders ON Customers.CustomerID = Orders.CustomerID WHERE OrderDate > 2007 ( SELECT Top 3 OrderID FROM Orders ) ORDER BY CustomerID, OrderDate, OrderID
		select CustomerID, OrderDate, OrderID from orders WHERE EXISTS (select top 3 CustomerId, orderdate from orders order by customerid, orderdate desc) order by customerID, orderdate desc
		SELECT CustomerID, OrderDate, OrderID FROM ( SELECT CustomerID, OrderDate, OrderID, ROW_NUMBER() OVER (PARTITION BY CustomerID ORDER BY OrderDate DESC) AS RecID FROM Orders ) AS d WHERE RecID BETWEEN 1 AND 3 ORDER BY CustomerID, OrderDate, OrderID
		SELECT CustomerID, OrderDate, OrderID FROM Orders as OuterOrders WHERE OrderID in ( SELECT TOP 3 OrderID FROM Orders WHERE CustomerID = OuterOrders.CustomerID order by CustomerID, OrderDate DESC ) ORDER BY CustomerID, OrderDate, OrderID
		SELECT Customers.CustomerID, OrderDate, OrderID FROM Customers JOIN Orders ON Customers.CustomerID = Orders.CustomerID WHERE OrderDate in (SELECT TOP 3 Max(OrderDate) From Orders AS iQ WHERE iQ.CustomerID = Orders.CustomerID GROUP BY OrderDate )-- End Inner Select ORDER BY Customers.CustomerID, OrderDate, OrderID
		SELECT CustomerID, OrderDate, OrderID FROM Orders a WHERE ( RequiredDate IN (SELECT TOP 3 RequiredDate FROM orders AS b WHERE a.customerID = b.customerID AND a.OrderDate = b.OrderDate AND a.OrderID = b.OrderID )--End Select )--Inner Query AND (RequiredDate BETWEEN CONVERT(DATETIME, '1997-09-22 00:00:00', 102) AND CONVERT(DATETIME, '2010-01-01 00:00:00', 102) )--End And Clause ORDER BY CustomerID, OrderDate, OrderID