Добавил девятое задание

1 files changed, 255 insertions, 0 deletions

diff --git a/task9_requests.sql b/task9_requests.sql
new file mode 100644
index 0000000..0beece0
--- /dev/null
+++ b/task9_requests.sql
@@ -0,0 +1,255 @@
+----------------------------------------------------
+-- Запрос с использованием автономных подзапросов --
+----------------------------------------------------
+
+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