-- Section I: Simple Queries, Subqueries and Joins [Marks: 20]

-- a) Products with price higher than average
SELECT * 
FROM Products 
WHERE Price > (SELECT AVG(Price) FROM Products);

-- b) Customers who have placed at least one order
SELECT DISTINCT C.CustomerID, C.FirstName, C.LastName
FROM Customers C
JOIN Orders O ON C.CustomerID = O.CustomerID;

-- c) Supplier(s) who supply the most expensive product(s)
SELECT S.*
FROM Suppliers S
JOIN Products P ON S.SupplierID = P.SupplierID
WHERE P.Price = (SELECT MAX(Price) FROM Products);

-- d) Products that have never been ordered
SELECT ProductName 
FROM Products 
WHERE ProductID NOT IN (
    SELECT DISTINCT ProductID FROM OrderDetails
);


-- Section II: Views, Stored Procedures and Triggers [Marks: 30]

-- a) View showing product name in UPPERCASE and category in lowercase
CREATE VIEW ProductCategoryView AS
SELECT 
    UPPER(ProductName) AS ProductName_Upper,
    LOWER(Category) AS Category_Lower
FROM Products;

-- b) View showing OrderID, ProductName, Quantity, Price * Quantity, first 5 letters of ProductName
CREATE VIEW OrderSummaryView AS
SELECT 
    OD.OrderID,
    P.ProductName,
    OD.Quantity,
    (P.Price * OD.Quantity) AS TotalPrice,
    LEFT(P.ProductName, 5) AS ShortName
FROM OrderDetails OD
JOIN Products P ON OD.ProductID = P.ProductID;

-- c) Stored procedure: input CustomerID, returns full name and total amount spent
CREATE PROCEDURE GetCustomerTotalSpent
    @CustomerID INT
AS
BEGIN
    SELECT 
        C.FirstName + ' ' + C.LastName AS FullName,
        ROUND(SUM(O.TotalAmount), 2) AS TotalSpent
    FROM Customers C
    JOIN Orders O ON C.CustomerID = O.CustomerID
    WHERE C.CustomerID = @CustomerID
    GROUP BY C.FirstName, C.LastName;
END;

-- d) Stored procedure: input MinAmount, returns orders where TotalAmount > MinAmount and name length
CREATE PROCEDURE GetOrdersAboveMinAmount
    @MinAmount INT
AS
BEGIN
    SELECT 
        O.OrderID,
        O.TotalAmount,
        C.FirstName + ' ' + C.LastName AS FullName,
        LEN(C.FirstName + ' ' + C.LastName) AS NameLength
    FROM Orders O
    JOIN Customers C ON O.CustomerID = C.CustomerID
    WHERE O.TotalAmount > @MinAmount;
END;

-- e) AFTER INSERT Trigger: sets Price = 100 if inserted with NULL or zero
CREATE TRIGGER trg_SetDefaultPrice
ON Products
AFTER INSERT
AS
BEGIN
    UPDATE Products
    SET Price = 100
    WHERE ProductID IN (
        SELECT ProductID FROM inserted WHERE Price IS NULL OR Price = 0
    );
END;

-- f) INSTEAD OF DELETE trigger on ProductSupplierView to prevent deletion
CREATE VIEW ProductSupplierView AS
SELECT P.ProductID, P.ProductName, S.SupplierName
FROM Products P
JOIN Suppliers S ON P.SupplierID = S.SupplierID;

CREATE TRIGGER trg_PreventDelete
ON ProductSupplierView
INSTEAD OF DELETE
AS
BEGIN
    PRINT 'Deletion not allowed from this view.';
END;