----------------------------------------------------
-- Запрос с использованием автономных подзапросов --
----------------------------------------------------

SELECT * FROM shipping.dbo.Payments
WHERE amount > (SELECT AVG(amount)
                FROM shipping.dbo.Payments
                WHERE date > '2021-04-25');

-------------------------------------------------------
-- Создание запроса с использованием коррелированных --
-- подзапросов в предложении SELECT и WHERE          --
-------------------------------------------------------

-- Выбрать минимальные в соответствующие дни платежи
SELECT * FROM shipping.dbo.Payments AS P1
WHERE amount = (
    SELECT MIN(amount)
    FROM shipping.dbo.Payments P2
    WHERE P1.date = P2.date
);

SELECT *, ( SELECT MAX(weight)
            FROM shipping.dbo.Boxes
            WHERE order_id = O.id ) max_weight
FROM shipping.dbo.Orders O;

----------------------------------------------
-- Запрос с использованием временных таблиц --
----------------------------------------------

DROP TABLE #OrderPayments

SELECT Payments.id, amount, Payments.date, customer_id
INTO #OrderPayments
FROM shipping.dbo.Orders INNER JOIN shipping.dbo.Payments
ON Orders.id = Payments.order_id
WHERE Orders.date > '2021-04-25';

SELECT *
FROM #OrderPayments
WHERE amount > (SELECT AVG(amount) FROM #OrderPayments);

------------------------------------------------------------------
-- Запрос с использованием обобщенных табличных выражений (CTE) --
------------------------------------------------------------------

WITH OrderPaymentsCTE AS (
    SELECT amount, Payments.date
    FROM shipping.dbo.Orders INNER JOIN shipping.dbo.Payments
    ON Orders.id = Payments.order_id
    WHERE Orders.date > '2021-04-25' )
SELECT *
FROM OrderPaymentsCTE
WHERE amount > (SELECT AVG(amount) FROM OrderPaymentsCTE);

----------------------------------------------------------------
-- Слияние данных (INSERT, UPDATE) c помощью инструкции MERGE --
----------------------------------------------------------------

CREATE PROCEDURE add_payment
    @order_id int,
    @amount int,
    @date datetime
AS BEGIN
    MERGE INTO shipping.dbo.Payments as tgt
    USING (SELECT @order_id, @amount, @date)
        AS src (order_id, amount, date)
    ON src.order_id = tgt.order_id
    WHEN MATCHED THEN
        UPDATE SET amount=src.amount, date=src.date
    WHEN NOT MATCHED THEN
        INSERT (order_id, amount, date)
        VALUES (src.order_id, src.amount, src.date);
END

EXEC add_payment 3, 500, '2021-04-25';

---------------------------------------------
-- Запрос с использованием оператора PIVOT --
---------------------------------------------

SELECT * FROM (
    SELECT Boxes.id, Wrappings.type
    FROM shipping.dbo.Boxes
    INNER JOIN shipping.dbo.Wrappings
    ON Boxes.wrapping_id = Wrappings.id
) t
PIVOT (
    COUNT(id)
    FOR type IN (
        [Жёсткая],
        [Полужёсткая],
        [Мягкая],
        [Воздушно-Пузырьковая],
        [Рулон]
    )
) AS PivotTable;

-----------------------------------------------
-- Запрос с использованием оператора UNPIVOT --
-----------------------------------------------

SELECT * FROM (
    SELECT Boxes.id, Wrappings.type
    FROM shipping.dbo.Boxes
    INNER JOIN shipping.dbo.Wrappings
    ON Boxes.wrapping_id = Wrappings.id
) t
PIVOT (
    COUNT(id)
    FOR type IN (
        [Жёсткая],
        [Полужёсткая],
        [Мягкая],
        [Воздушно-Пузырьковая],
        [Рулон]
    )
) pvt
UNPIVOT (
    count
    FOR type IN (
        [Жёсткая],
        [Полужёсткая],
        [Мягкая],
        [Воздушно-Пузырьковая],
        [Рулон]
    )
) AS UnpivotTable;

----------------------------------------------
-- Запрос с использованием GROUP BY с       --
-- операторами ROLLUP, CUBE и GROUPING SETS --
----------------------------------------------

SELECT COUNT(Boxes.id) count, Wrappings.type, warehouse_id
FROM shipping.dbo.Boxes
INNER JOIN shipping.dbo.Wrappings
ON Boxes.wrapping_id = Wrappings.id
GROUP BY ROLLUP (Wrappings.type, warehouse_id);

SELECT COUNT(Boxes.id) count, Wrappings.type, warehouse_id
FROM shipping.dbo.Boxes
INNER JOIN shipping.dbo.Wrappings
ON Boxes.wrapping_id = Wrappings.id
GROUP BY CUBE (Wrappings.type, warehouse_id);

SELECT COUNT(Boxes.id) count, Wrappings.type, warehouse_id
FROM shipping.dbo.Boxes
INNER JOIN shipping.dbo.Wrappings
ON Boxes.wrapping_id = Wrappings.id
GROUP BY GROUPING SETS ((Wrappings.type, warehouse_id));

---------------------------------------------------
-- Секционирование с использованием OFFSET FETCH --
---------------------------------------------------

SELECT * FROM shipping.dbo.Boxes
ORDER BY volume
OFFSET 0 ROW FETCH NEXT 20 ROWS ONLY;

--------------------------------------------------------------
-- Запросы с использованием ранжирующих оконных функций.    --
-- ROW_NUMBER() нумерация строк. Использовать для нумерации --
-- внутри групп. RANK(), DENSE_RANK(), NTILE().             --
--------------------------------------------------------------

SELECT ROW_NUMBER()
OVER (ORDER BY volume DESC) volume_number, *
FROM shipping.dbo.Boxes;

SELECT RANK()
OVER (ORDER BY DATEPART(year, date) DESC) year_rank, *
FROM shipping.dbo.Orders;

SELECT DENSE_RANK()
OVER (ORDER BY DATEPART(year, date) DESC) year_rank, *
FROM shipping.dbo.Orders;

SELECT NTILE(10)
OVER (ORDER BY volume DESC) volume_tile, *
FROM shipping.dbo.Boxes;

----------------------------------------
-- Перенаправление ошибки в TRY/CATCH --
----------------------------------------

BEGIN TRY
    INSERT INTO shipping.dbo.Boxes (order_id, warehouse_id, wrapping_id, volume, weight)
    VALUES (3, 2134, 2, 12, 50);
END TRY
BEGIN CATCH
    RAISERROR(N'Неверный warehouse_id', 10, 1);
END CATCH

--------------------------------------------------------------
-- 1. Создание процедуры обработки ошибок в блоке CATCH с   --
--    использованием функций ERROR;                         --
-- 2. Контроль транзакций с BEGIN и COMMIT;                 --
-- 3. Добавление логики обработки транзакций в блоке CATCH. --
--------------------------------------------------------------

SET XACT_ABORT ON
DROP PROCEDURE getError
GO

CREATE PROCEDURE getError
AS SELECT
     ERROR_NUMBER() AS ErrorNumber,
     ERROR_STATE() AS ErrorState,
     ERROR_MESSAGE() AS ErrorMessage;
GO

BEGIN TRY
    BEGIN TRANSACTION
        DELETE FROM shipping.dbo.Orders WHERE id=1;
    COMMIT TRANSACTION
END TRY
BEGIN CATCH
    EXECUTE getError;
    ROLLBACK TRANSACTION;
END CATCH
GO

---------------------------------------------------------------------
-- Использование THROW, чтобы передать сообщение об ошибке клиенту --
---------------------------------------------------------------------

BEGIN TRY
    INSERT INTO shipping.dbo.Boxes (order_id, warehouse_id, wrapping_id, volume, weight)
    VALUES (3, 2134, 2, 12, 50);
END TRY
BEGIN CATCH
    THROW 50001, N'Неверный warehouse_id', 1;
END CATCH

------------------------------
-- Использование XACT_ABORT --
------------------------------

SET XACT_ABORT OFF;

BEGIN TRANSACTION
    INSERT INTO shipping.dbo.Boxes (order_id, warehouse_id, wrapping_id, volume, weight)
    VALUES (3, 2134, 2, 12, 1337);

    INSERT INTO shipping.dbo.Boxes (order_id, warehouse_id, wrapping_id, volume, weight)
    VALUES (3, 401, 2, 12, 1337);

    INSERT INTO shipping.dbo.Boxes (order_id, warehouse_id, wrapping_id, volume, weight)
    VALUES (3, 400, 2, 12, 1337);

    INSERT INTO shipping.dbo.Boxes (order_id, warehouse_id, wrapping_id, volume, weight)
    VALUES (3, 123, 2, 12, 1337);
COMMIT TRANSACTION