USE WideWorldImporters

-- Scenario 1
SELECT MIN(Temperature) AS LowestTemperature, MAX(Temperature) AS HighestTemperature
FROM Warehouse.VehicleTemperatures

-- Scenario 2
SELECT SalesPersonPersonID, COUNT(*) AS TotalOrders
FROM Sales.Orders
GROUP BY SalesPersonPersonID
ORDER BY TotalOrders

-- Scenario 3
SELECT CustomerID, SUM(InvoiceID) AS TotalInvoices
FROM Sales.Invoices
WHERE InvoiceDate BETWEEN '1/1/2015' AND '12/31/2015'
GROUP BY CustomerID
ORDER BY TotalInvoices, CustomerID

-- Scenario 4
SELECT od.OrderID, ol.Quantity * ol.UnitPrice AS TotalOrderPrice
FROM Sales.Orders od
    JOIN Sales.OrderLines ol ON ol.OrderID = od.OrderID
    JOIN Sales.Customers cu ON od.CustomerID = cu.CustomerID
WHERE cu.CustomerName = 'Debbie Molina' AND od.OrderDate BETWEEN '1/1/2016' AND '12/31/2016'
ORDER BY TotalOrderPrice

-- Scenario 5
SELECT *
FROM (SELECT CustomerID, COUNT(OrderID) AS TotalOrders
    FROM Sales.Orders
    WHERE OrderDate BETWEEN '1/1/2016' AND '12/31/2016'
    GROUP BY CustomerID) Results
WHERE TotalOrders >= 20
ORDER BY TotalOrders DESC, CustomerID

-- Scenario 6
WITH
    Results
    AS
    (
        SELECT TOP 50
            ol.StockItemID, SUM(Quantity) TotalQuantity
        FROM Sales.OrderLines ol
            JOIN Warehouse.StockItems si ON ol.StockItemID = si.StockItemID
        GROUP BY ol.StockItemID
        ORDER BY TotalQuantity DESC
    )
SELECT Results.StockItemID, StockItemName, TotalQuantity
FROM Results JOIN Warehouse.StockItems ON WareHouse.Stockitems.StockItemID = Results.StockItemID
ORDER BY TotalQuantity DESC

-- Scenario 7
WITH
    Results
    AS
    (
        SELECT cu.CustomerID, COUNT(OrderID) OrderCount
        FROM Sales.Customers cu
            LEFT JOIN Sales.Orders od ON cu.CustomerID = od.CustomerID
        GROUP BY cu.CustomerID
    )
SELECT TOP 10
    Results.CustomerID, CustomerName, OrderCount
FROM Results
    JOIN Sales.Customers ON Results.CustomerID = Sales.Customers.CustomerID

-- Scenario 8
SELECT DeliveryMethodID
FROM Application.DeliveryMethods
WHERE DeliveryMethodID NOT IN (SELECT DeliveryMethodID
FROM Sales.Customers)
ORDER BY DeliveryMethodName