-- Write a SQL statement to ensure that customerID in the invoice is a foreign key referencing ID in the customer table. 

-- If a customer is deleted we want to the invvoice to also be deleted 


ALTER TABLE Invoice 
ADD CONSTRAINT fk_CustomerID 
FOREIGN KEY (CustomerID) REFERENCES Customer(ID)
ON DELETE CASCADE; 


-- Insert the following products into the stock table 
INSERT INTO Stock( ProductName, QuantityAvailable, CurrentPrice) VALUES 

('Blue Jeans', 50, 39.99), 
('Hat', 30, 19.99), 
('Sunglasses', 20, 24.99);


-- Write a SQL Statment to create an index called idx_PN on the ProductName of the stock table to improve 
-- Search performance 

CREATE INDEX idx_PN ON Stock(ProductName); 


-- Write a SQL Statement to display CustomerName and loyaltyYears, ordering the results from the highest to 
-- Lowest LoyaltyYears 

SELECT CustomerName, LoyaltyYears 
FROM Customer 
ORDER BY LoyaltyYears DESC; 

-- Write a SQL statement to find the average loyaltyYears of all customers 
-- Use alias AverageLoyaltyYears 



SELECT AVG(LoyaltyYears) AS AverageLoyaltyYears 
FROM customer; 


-- Write a SQL Statement to delete all oders from the invoice table that were placed between January 31, 2025,
-- And Feburary 2nd 2025 

DELETE FROM Invoice 
WHERE OrderDate BETWEEN '2025-01-01' AND '2025-02-02';

-- Write a SQL Statement to create a view named Low_Quantity that displays ProductName and CurrentPrice for Products 
-- with QuantityAvailable of less then 20 

CREATE VIEW Low_Quantity AS 
SELECT ProductName, CurrentPrice 
FROM Stock 
WHERE QuantityAvilable < 20; 

-- Write a SQL code to add 30 to any product with quantity less then 20 
UPDATE Stock 
SET QuantityAvailable = QuantityAvailable + 30
WHERE QuantityAvailable < 20; 


-- Delete the transaction table 
DROP TABLE Transaction; 


-- Add a new column to customer called emailaddress - variable length string with up to 200 characters 
ALTER TABLE Customer 
ADD COLUMN EmailAddress VARCHAR(200); 


-- Add Constraint called Quantity_Check which ensures that no product will have a quantity greater then 1000 in 
-- the stock table 
ALTER TABLE Stock 
ADD CONSTRAINT Quantity_Check 
CHECK(QuantityAvailable <= 1000); 


-- Write a SQL query to display the number of unique products per order ids 
SELECT DISTINCT OrderID,Count(ProductID)
FROM Transaction 
ORDER BY OrderID; 


-- Find the unique regions and their average loyalty year 

SELECT DISTINCT Region, AVG(LoyaltyYear)
FROM Customer 
GROUP BY Region; 

-- Write a SQL query to display the total quantity for each product in the transaction table
-- Use the alias 'Total Quantity'

SELECT SUM(Quantity) AS TotalQuantity
FROM Tranasction 
GROUP BY ProductID;

-- Find the unique Order IDs in the transaction table 
SELECT DISTINCT OrderID 
FROM Transaction;

-- Find all customers names along with the orders IDs they placed. Customers with no orders
-- Should still appear in the results. Order by Customer Name 

SELECT Customer.CustomerName, Invoice.OrderID
FROM Customer 
LEFT JOIN Invoice ON Customer.ID = Invoice.CustomerID
ORDER BY CustomerName; 

-- Find the date of each transaction. Only transactions with valid orders should appear in the results.
-- Return the OrderID, ProductID and OrderDate. Order by date 


SELECT Invoice.OrderID , Transaction.ProductID 
FROM Inovice 
LEFT JOIN Transaction ON Invoice.OrderID = Transaction.OrderID 
ORDER BY Invoice.OrderDate; 


-- Write a SQL query to display unique regions and their total number of orders. Display all regions, regardless of if they have any orders. 
-- Use the alias as Total orders 
SELECT Customer.Region, COUNT(Invoice.OrderID) AS TotalOrders 
FROM Customer 
LEFT JOIN Invoice ON Customer.ID  = Invoice.CustomerID 
GROUP BY Customer.Region; 


-- Find customers who have between 5 and 15 loyalty years.Return the customer name only 
SELECT CustomerName, Region
FROM Customer 
WHERE LoyaltyYears BETWEEN 5 AND 15 
ORDER BY CustomerName; 

-- Delete the region column 
ALTER TABLE Customer 
DROP COLUMN Region;


-- Delete any orders with AmountSpent less then $100 
DELETE FROM Transaction 
WHERE AmountSpent < 100;