From d75fa846d8c9ed26b18dabfa756b9134307d1972 Mon Sep 17 00:00:00 2001
From: Andrew <saintruler@gmail.com>
Date: Mon, 13 Dec 2021 22:54:20 +0400
Subject: =?UTF-8?q?=D0=A1=D0=B4=D0=B5=D0=BB=D0=B0=D0=BB=20=D1=82=D1=80?=
 =?UTF-8?q?=D0=B5=D1=82=D1=8C=D0=B5=20=D0=B7=D0=B0=D0=B4=D0=B0=D0=BD=D0=B8?=
 =?UTF-8?q?=D0=B5=20=D0=BF=D1=8F=D1=82=D0=BE=D0=B9=20=D0=BB=D0=B0=D0=B1?=
 =?UTF-8?q?=D1=8B.?=
MIME-Version: 1.0
Content-Type: text/plain; charset=UTF-8
Content-Transfer-Encoding: 8bit

---
 task5_views.sql | 38 +++++++++++++++++++++++++++++++++++---
 1 file changed, 35 insertions(+), 3 deletions(-)

(limited to 'task5_views.sql')

diff --git a/task5_views.sql b/task5_views.sql
index 15032de..145380e 100644
--- a/task5_views.sql
+++ b/task5_views.sql
@@ -3,7 +3,7 @@
 ---------------
 
 CREATE VIEW PaymentNotifications AS
-SELECT Customers.email, Payments.amount, Orders.date
+SELECT Payments.id, Customers.email, Payments.amount, Orders.date
 FROM Payments
 INNER JOIN Orders ON Payments.order_id = Orders.id
 INNER JOIN Customers ON Orders.customer_id = Customers.id
@@ -37,7 +37,7 @@ GO
 
 
 CREATE VIEW BoxDates AS
-SELECT Orders.id, Boxes.id, Deliveries.date
+SELECT Boxes.id, Orders.id, Deliveries.date
 FROM Shipments
 INNER JOIN Orders ON Shipments.order_id = Orders.id
 INNER JOIN Boxes ON Orders.id = Boxes.order_id
@@ -79,4 +79,36 @@ GO
 
 ---------------
 -- Задание 3 --
----------------
\ No newline at end of file
+---------------
+
+SET NUMERIC_ROUNDABORT OFF;
+SET ANSI_PADDING, ANSI_WARNINGS,
+    CONCAT_NULL_YIELDS_NULL, ARITHABORT,
+    QUOTED_IDENTIFIER, ANSI_NULLS ON;
+GO
+
+CREATE VIEW [dbo].[PaymentNotificationsInd] WITH SCHEMABINDING AS
+SELECT Payments.id, Customers.email, Payments.amount, Orders.date
+FROM [dbo].[Payments]
+INNER JOIN [dbo].[Orders] ON Payments.order_id = Orders.id
+INNER JOIN [dbo].[Customers] ON Orders.customer_id = Customers.id
+GO
+
+DROP VIEW PaymentNotificationsInd
+GO
+
+CREATE UNIQUE CLUSTERED INDEX NotifyEmail
+ON PaymentNotificationsInd (id, email)
+GO
+
+DROP INDEX NotifyEmail ON PaymentNotificationsInd
+GO
+
+SELECT * FROM PaymentNotificationsInd
+GO
+
+SELECT Payments.id, Customers.email, Payments.amount, Orders.date
+FROM [dbo].[Payments]
+INNER JOIN [dbo].[Orders] ON Payments.order_id = Orders.id
+INNER JOIN [dbo].[Customers] ON Orders.customer_id = Customers.id
+GO
\ No newline at end of file
-- 
cgit v1.2.3

