Добавил часть четвёртого задания

1 files changed, 75 insertions, 0 deletions

diff --git a/task4_requests.sql b/task4_requests.sql
new file mode 100644
index 0000000..c364b95
--- /dev/null
+++ b/task4_requests.sql
@@ -0,0 +1,75 @@
+-----------------------
+-- Кластерный индекс --
+-----------------------
+
+SELECT amount FROM Payments WHERE order_id=12412;
+
+CREATE CLUSTERED INDEX Payments_Clustered ON Payments (order_id);
+GO
+DROP INDEX Payments_Clustered ON Payments;
+GO
+
+--------------------------
+-- Некластерные индексы --
+--------------------------
+
+-- составной (средне)
+
+SELECT Boxes.id, Orders.id, date
+FROM Boxes JOIN Orders
+ON Boxes.order_id = Orders.id
+WHERE Boxes.warehouse_id = 12465
+GO
+
+CREATE INDEX Boxes_Composite ON Boxes (order_id, warehouse_id);
+GO
+DROP INDEX Boxes_Composite ON Boxes;
+GO
+
+-- покрывающий (не очень)
+
+SELECT * FROM Orders
+WHERE EXISTS(
+    SELECT * FROM Customers
+    WHERE customer_id = Customers.id
+        AND Customers.email LIKE N'%@mail.ru'
+);
+
+CREATE INDEX Orders_Index_Covering ON Orders (id)
+    INCLUDE (customer_id, city_id, date);
+GO
+DROP INDEX Orders_Index_Covering ON Orders;
+GO
+
+-- уникальный (норм)
+
+SELECT email FROM Customers WHERE name=N'customer-12451';
+
+CREATE UNIQUE INDEX Customers_Unique ON Customers (name);
+GO
+DROP INDEX Customers_Unique ON Customers;
+GO
+
+-- индекс с включеными столбцами (не очень)
+
+SELECT date FROM Orders
+WHERE EXISTS(
+    SELECT * FROM Customers
+    WHERE customer_id = Customers.id
+        AND Customers.email LIKE N'%@mail.ru'
+);
+
+CREATE INDEX Orders_Index_Columns ON Orders (id)
+    INCLUDE (customer_id, date);
+GO
+DROP INDEX Orders_Index_Columns ON Orders;
+GO
+
+-- отфильтрованный индекс (средне)
+
+SELECT SUM(amount) FROM Payments WHERE date >= '2021-12-01';
+
+CREATE INDEX Payments_Filtered ON Payments (amount) WHERE date >= '2021-12-01';
+GO
+DROP INDEX Payments_Filtered ON Payments;
+GO