04_Procedures_and_Views.sql

GO
CREATE PROCEDURE sp_AddProduct
    @Name NVARCHAR(100),
    @Price DECIMAL (10,2),
    @Stock INT,
    @SupID INT
AS
BEGIN
    INSERT INTO Product (product_name, unit_price, stock_quantity, supplier_id)
    VALUES (@Name, @Price, @Stock, @SupID);
END;
GO

CREATE PROCEDURE sp_UpdateStock
    @ProductID INT,
    @Qty INT
AS
BEGIN
    UPDATE Product
    SET stock_quantity = stock_quantity - @Qty
    WHERE product_id = @ProductID;
END;
GO

CREATE PROCEDURE sp_PatientHistory
    @PatientID INT
AS
BEGIN
    SELECT S.sale_date, P.product_name, SD.quantity
    FROM Sale S
    JOIN Sale_Detail SD ON S.sale_id = SD.sale_id
    JOIN Product P ON SD.product_id = P.product_id
    WHERE S.patient_id = @PatientID;
END;
GO

CREATE PROCEDURE sp_MonthlyRevenue
    @Month INT,
    @Year INT
AS
BEGIN
    SELECT SUM(total_amount) as Total_Revenue
    FROM Sale
    WHERE MONTH(sale_date) = @Month AND YEAR(sale_date) = @Year;
END;
GO

CREATE PROCEDURE sp_CheckExpiry
    @CheckDate DATE
AS
BEGIN
    SELECT product_name, expiry_date, stock_quantity
    FROM Product
    WHERE expiry_date <= @CheckDate;
END;
GO

CREATE VIEW vw_ProductStockStatus AS
SELECT product_name, category, stock_quantity
FROM Product;
GO

CREATE VIEW vw_PatientContactList AS
SELECT first_name, last_name, phone_number, email
FROM Patient;
GO

SELECT product_name, unit_price
FROM Product
WHERE unit_price > (SELECT AVG(unit_price) FROM Product);

SELECT DISTINCT P.first_name, P.last_name
FROM Patient P
JOIN Sale S ON P.patient_id = S.patient_id
JOIN Sale_Detail SD ON S.sale_id = SD.sale_id
WHERE SD.product_id IN (SELECT product_id FROM Product WHERE category = 'Antibiotics');