USE WideWorldImporters

-- Scenario 1
SELECT SupplierName, WebsiteURL, DeliveryAddressLine1, DeliveryAddressLine2, DeliveryPostalCode
FROM Purchasing.Suppliers

-- Scenario 2
SELECT StateProvinceName, SalesTerritory, CountryID
FROM Application.StateProvinces
WHERE SalesTerritory = 'Southeast'
ORDER BY StateProvinceName

-- Scenario 3
SELECT CustomerID, TransactionDate, TransactionAmount
FROM Sales.CustomerTransactions
WHERE TransactionAmount < 0 AND TransactionDate BETWEEN '1/1/2015' AND '12/31/2015'
ORDER BY TransactionDate DESC, TransactionAmount ASC

-- Scenario 4
SELECT CountryName, Continent, Region, Subregion, LatestRecordedPopulation
FROM Application.Countries
WHERE LatestRecordedPopulation > 1000000 AND Continent != 'Oceania'
ORDER BY CountryName, Continent, Region, Subregion

-- Scenario 5
SELECT TOP 10
    StockItemName, RecommendedRetailPrice, Tags
FROM Warehouse.StockItems
WHERE IsChillerStock = 1 OR StockItemName LIKE 'USB%'
ORDER BY StockItemName

-- Scenario 6
SELECT InvoiceID, InvoiceDate, CustomerName
FROM Sales.Invoices i
    JOIN Sales.Customers c ON c.CustomerID = i.CustomerID
WHERE InvoiceDate BETWEEN '1/1/2016' AND '3/1/2016' AND CustomerName NOT LIKE '%Toys%'
ORDER BY CustomerName, InvoiceDate

-- Scenario 7
SELECT c.ColorName, c.ColorID
FROM Warehouse.Colors c
WHERE c.ColorID NOT IN (SELECT DISTINCT ColorID
FROM Warehouse.StockItems
WHERE ColorID IS NOT NULL)
ORDER BY ColorName

-- Scenario 8
SELECT c.Cityname, sp.StateProvinceName, sp.StateProvinceCode
FROM Application.Cities c
    JOIN Application.StateProvinces sp ON c.StateProvinceID = sp.StateProvinceID
WHERE Cityname LIKE 'A%' AND sp.SalesTerritory = 'Southeast'
ORDER BY StateProvinceName DESC, CityName

-- Scenario 9
    SELECT 'Customer' TYPE, CustomerID Id, CustomerName Name
    FROM Sales.Customers
UNION
    SELECT 'Supplier' TYPE, SupplierID Id, SupplierName Naame
    FROM Purchasing.Suppliers
ORDER BY TYPE, Name

-- Scenario 10
SELECT ci.CityName, sp.StateProvinceName, co.CountryName, sp.SalesTerritory
FROM Application.Cities ci
    JOIN Application.StateProvinces sp ON sp.StateProvinceID = ci.StateProvinceID
    JOIN Application.Countries co ON sp.CountryID = co.CountryID
WHERE SalesTerritory IN ('Southeast', 'Southwest')
ORDER BY StateProvinceName, CityName