73 lines
2.1 KiB
SQL
73 lines
2.1 KiB
SQL
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 |