/*E5.1.1 Display all the records in the table REGION.Select *From region;*\
SELECT * FROM Region;

/*E5.1.2 Display the StoreID and StoreZip for all stores.*\
SELECT StoreID, StoreZip
From Store;

/*E5.1.3 Display the CustomerName and CustomerZip for all customers, sortedalphabetically by CustomerName.*\
SELECT CustomerName. CustomerZip
FROM Customers
ORDER BY CustomerName;

/*E5.1.4 Display the RegionIDs of regions where we have stores (use only tableSTORE and do not display the same information more than once).*\
SELECT DISTINCT RegionID
FROM STORES;

/*E5.1.5 Display all the information for all stores whose RegionID value is C.*\
SELECT *
FROM STORE
WHERE RegionID = 'C';

/*E5.1.6 Display CustomerID and CustomerName for all customers whoseCustomerName begins with a letter T.*\
SELECT CustomerID, CustomerName
FROM CUSTOMER
WHERE CustomerName LIKE 'T%';

/*E5.1.7 Display the ProductID, ProductName, and ProductPrice for productswith a ProductPrice of $100 or higher.*\
SELECT ProductID, ProductName, ProductPrice
FROM PRODUCT
WHERE ProductPrice >= 100;

/*E5.1.8 Display the ProductID, ProductName, ProductPrice, and VendorNamefor all products. Sort the results by ProductID.*\
SELECT ProductID, ProductName, ProductPrice
FROM PRODUCT
WHERE ProductPrice >=100;

/*E5.1.9 Display the ProductID, ProductName, ProductPrice, VendorName, andCategoryName for all products. Sort the results by ProductID.*\
SELECT ProductID, ProductName, ProductPrice, VendorName, CategoryName
FROM PRODUCT
JOIN VENDOR ON PRODUCT.VendorID = VENDOR.VendorID
JOIN CATEGORY ON PRODUCT.CategoryID = CATEGORY.CategoryID
ORDER BY ProductID;

/*E5.1.10 Display the ProductID, ProductName, and ProductPrice for productsin the category whose CategoryName value is Camping. Sort the results byProductID.*\
SELECT ProductID, ProductName, ProductPrice 
FROM PRODUCT
JOIN CATEGORY ON PRODUCT.CategoryID = CATEGORY.CategoryID
WHERE CategoryName = 'Camping'
ORDER BY ProductID;

/*E.5.1.11 Display the ProductID, ProductName, and ProductPrice for productsthat were sold in the zip code 60600. Sort the results by ProductID.*\
SELECT ProductID, ProductName, ProductPrice
FROM PRODUCT
JOIN SOLDVIA ON PRODUCT.ProductID = SOLDVIA.ProductID
JOIN SALESTRANSACTION ON SOLDVIA.TID = SALESTRANSACTION.TID
JOIN CUSTOMER ON SALESTRANSACTION.CustomerID = CUSTOMER.CustomerID
WHERE CustomerZip = 60600
ORDER BY ProductID;

/*E.5.1.12 Display the ProductID, ProductName, and ProductPrice for Productswhose VendorName is Pacifica Gear that were sold in the region whoseRegionName is Tristate. Do not display the same information more than once(i.e., do not show the same product more than once). Sort the results byProductID.*\
SELECT DISTINCT Products.ProductID, Products.ProductName, Products.ProductPrice FROM Products;
JOIN Vendors ON Products.VendorID = Vendors.VendorID ;
JOIN Sales ON Sales.ProductID = Products.ProductID ;
JOIN Regions ON Sales.RegionID = Regions.RegionID
WHERE Vendors.VendorName = 'Pacifica Gear' AND Regions.RegionName = 'Tristate' ORDER BY Products.ProductID;

/*E5.1.13 Display the TID, CustomerName, and TDate for sales transactionsinvolving a customer buying a product whose ProductName is Easy Boot.*\
SELECT Sales.TID, Customers.CustomerName, Sales.TDate 
FROM Sales ;
JOIN Customers ON Sales.CustomerID = Customers.CustomerID ;
JOIN Products ON Sales.ProductID = Products.ProductID 
WHERE Products.ProductName = 'Easy Boot';

/*E5.1.14 Display the RegionID, RegionName, and number of stores in theregion for all regions.E5.1.15 For each product category, display the CategoryID, CategoryName,and average price of a product in the category.*\
SELECT Regions.RegionID, Regions.RegionName, COUNT(*) AS NumStores FROM Regions ;
JOIN Stores ON Regions.RegionID = Stores.RegionID 
GROUP BY Regions.RegionID, Regions.RegionName;

/*E5.1.15 For each product category, display the CategoryID, CategoryName,and average price of a product in the category*\
SELECT Categories.CategoryID, Categories.CategoryName, AVG(Products.ProductPrice) AS AvgPrice 
FROM Categories;
JOIN Products ON Categories.CategoryID = Products.CategoryID GROUP BY Categories.CategoryID, Categories.CategoryName;

/*E5.1.16 For each product category, display the CategoryID and the totalnumber of items purchased in the category.*\
SELECT Categories.CategoryID, COUNT(*) AS NumItems 
FROM Categories ;
JOIN Products ON Categories.CategoryID = Products.CategoryID ;
JOIN Sales ON Products.ProductID = Sales.ProductID 
GROUP BY Categories.CategoryID;

/*E5.1.17 Display the RegionID, RegionName, and the total amount of sales (indollars) in the region for all regions. Display the total amount of sales asAmountSpent.*\
SELECT Regions.RegionID, Regions.RegionName, SUM(Sales.Quantity * Products.ProductPrice) AS AmountSpent FROM Regions ;
JOIN Stores ON Regions.RegionID = Stores.RegionID ;
JOIN Sales ON Stores.StoreID = Sales.StoreID; 
JOIN Products ON Sales.ProductID = Products.ProductID 
GROUP BY Regions.RegionID, Regions.RegionName;

/*E5.1.18 Display the TID and the total number of items (of all products) soldwithin the transaction for all sales transactions whose total number of items(of all products) sold within the transaction is greater than five.*\
SELECT TID, SUM(Quantity) AS TotalItemsSold 
FROM Sales GROUP BY TID HAVING TotalItemsSold > 5;

/*E5.1.19 For each vendor whose product sales exceed $700, display theVendorID, VendorName, and total amount of sales in dollars. Display the totalamount of sales as TotalSales.*\
SELECT Vendors.VendorID, Vendors.VendorName, SUM(Sales.Quantity * Products.ProductPrice) AS TotalSales FROM Vendors; 
JOIN Products ON Vendors.VendorID = Products.VendorID;
JOIN Sales ON Products.ProductID = Sales.ProductID ;
GROUP BY Vendors.VendorID, Vendors.VendorName 
HAVING TotalSales > 700;

/*E5.1.20 Display the ProductID, ProductName, and ProductPrice of thecheapest product.*\
SELECT ProductID, ProductName, ProductPrice 
FROM Products WHERE ProductPrice = (SELECT MIN(ProductPrice) FROM Products);

/*E5.1.21 Display the ProductID, ProductName, and VendorName for productswhose price is below the average price of all products.*\
SELECT Products.ProductID, Products.ProductName, Vendors.VendorName FROM Products ;
JOIN Vendors ON Products.VendorID = Vendors.VendorID 
WHERE Products.ProductPrice < (SELECT AVG(ProductPrice) FROM Products);

/*E5.1.22 Display the ProductID and ProductName of the product for theproducts whose total quantity sold in all transactions is greater than 2. Sortthe results by ProductID.*\
SELECT Products.ProductID, Products.ProductName 
FROM Products ;
JOIN Sales ON Products.ProductID = Sales.ProductID 
GROUP BY Products.ProductID, Products.ProductName 
HAVING SUM(Sales.Quantity) > 2 ORDER BY Products.ProductID;

/*E5.1.23 Display the ProductID for the product that has been sold the most within all transactions (i.e., that has been sold in the highest total quantityacross all transactions).*\
SELECT TOP 1 ProductID
FROM Transactions
GROUP BY ProductID
ORDER BY SUM(Quantity) DESC;

/*E5.1.24 Rewrite Query 30 using a join statement (no nested queries).*\
SELECT productid, productname, productprice
FROM product
WHERE productid IN (
    SELECT productid
    FROM includes
    GROUP BY productid
    HAVING SUM(quantity) > 3);

/*E5.1.25 Rewrite Query 31 using a join statement (no nested queries).*\
SELECT productid, productname, productprice
FROM product
WHERE productid IN (
    SELECT productid
    FROM includes
    GROUP BY productid
    HAVING COUNT(tid) > 1);