Question 48 --Query01 Select Top 10 OrderId , Convert(char(10), OrderDate,121) L

Question

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

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

Explanation / Answer

Solution:

Answer 48)

215

Answer 49)

Relational subquery

Answer 49)

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

Related Questions

Navigate

• Browse (All)
• Browse Q
• Subjects

---

---

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.