dz9

9.sql

1. Написать хранимую процедуру, которая показывает общее количество проданных товаров в каждой из категорий и от каждого производителя.

CREATE PROCEDURE GetSalesAnalysis2 AS
BEGIN
    SELECT 
    c.name AS [Категория],
        p2.name AS [Производитель],
        SUM(s.quantity) AS [Кол Прод Тов]
        
    FROM Sale s
    JOIN Product p ON s.id_product = p.id
    JOIN Category c ON p.id_category = c.id
    JOIN Producer p2 ON p.id_producer = p2.id
    GROUP BY 
        c.name,
        p2.name
    ORDER BY 
        c.name,
        p2.name;
END



3. Написать хранимую процедуру, которая показывает список производителей и общее количество товаров, произведённых каждым из них.

CREATE PROCEDURE GetProducerProducts AS
BEGIN
    SELECT 
        p.name AS [Производитель],
        COUNT(pr.id) AS [Кол Товаров],
        SUM(pr.quantity) AS [Общее Кол]
    FROM Producer p
    LEFT JOIN Product pr ON p.id = pr.id_producer
    GROUP BY 
        p.id,
        p.name
    ORDER BY 
        p.name;
END

4. Создать хранимую процедуру, которая показывает название продукта, который был продан в максимальном количестве.

--CREATE PROCEDURE GetMostSoldProd AS
--BEGIN
--    SELECT TOP 1
--        p.name AS [Название Продукта],
--        SUM(s.quantity) AS [Количество Продаж]
--    FROM Product p
--    JOIN Sale s ON p.id = s.id_product
--    GROUP BY 
--        p.id,
--        p.name
--    ORDER BY 
--        SUM(s.quantity) DESC;
--END




5. Создать хранимую процедуру, которая возвращает общее количество проданных товаров и общего дохода.

--CREATE PROCEDURE GetTotalSales AS
--BEGIN
--    SELECT 
--        COUNT(id) AS [Кол Продаж],
--        SUM(quantity) AS [Общее Кол],
--        SUM(price * quantity) AS [Общая Сумма]
--    FROM Sale
--    WHERE id_product IS NOT NULL;  
--END




=============================================================================================================================

TRIGGERS:
1. При продаже товара (добавлении записи в таблицу Sale), его количество должно уменьшаться (в таблице Product).

CREATE TRIGGER UpdateQuantity ON Sale
AFTER INSERT AS
BEGIN

    UPDATE Product
    SET quantity = Product.quantity - inserted.quantity
    FROM Product
    JOIN inserted ON Product.id = inserted.id_product;

   
    IF EXISTS (SELECT 1 FROM Product WHERE quantity < 0)

    BEGIN
        ROLLBACK;
		PRINT 'Недостаточно товара на складе' 

    END

END


2. Нельзя продать товар, которого нет на складе магазина (по количеству).

CREATE TRIGGER CheckQuantity ON Sale
INSTEAD OF INSERT AS
BEGIN

    IF EXISTS (
        SELECT 1 
        FROM inserted i
        JOIN Product p ON p.id = i.id_product
        WHERE i.quantity > p.quantity
    )
    BEGIN
        PRINT 'Недостаточно товара на складе'
        
    END
    
  
END


4. При удалении товара из таблицы Product, данные о нём должны копироваться в резервную таблицу ProductArchive.

CREATE TABLE ProductArchive
(
    id int,
    name nvarchar(50),
    id_category int,
    price float,
    quantity float,
    id_producer int,
    id_measurement int,
    id_markup int,
    delete_date datetime default GETDATE()

);


CREATE TRIGGER ProductArchive ON Product
INSTEAD OF DELETE AS
BEGIN
    
    INSERT INTO ProductArchive 
	(
        id,
		name,
		id_category,
		price,
		quantity,
        id_producer,
		id_measurement,
		id_markup
    )
    SELECT 
        id,
		name,
		id_category,
		price,
		quantity,
        id_producer,
		id_measurement,
		id_markup
    FROM deleted;

    
    DELETE FROM Product
    WHERE id IN (SELECT id FROM deleted);
END



5. Если новый товар добавляется в таблицу Product, он должен быть удалён из таблицы ProductArchive (если такой товар там есть).

CREATE TRIGGER Product_DeleteFromArchive ON Product
AFTER INSERT AS
BEGIN
    
    DELETE FROM ProductArchive
    WHERE name IN 
	(
	SELECT name 
	FROM inserted
	) 
	OR id IN 
	(
	SELECT id 
	FROM inserted
	);
END