From 06121aef5ab01d39f2d89ce902c529ec78cb0912 Mon Sep 17 00:00:00 2001
From: Andrew <saintruler@gmail.com>
Date: Mon, 13 Dec 2021 22:21:52 +0400
Subject: =?UTF-8?q?=D0=A1=D0=B4=D0=B5=D0=BB=D0=B0=D0=BB=20=D0=BF=D0=B5?=
 =?UTF-8?q?=D1=80=D0=B2=D0=BE=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 | 62 +++++++++++++++++++++++++++++++++++++++++++++++++++++++++
 1 file changed, 62 insertions(+)
 create mode 100644 task5_views.sql

(limited to 'task5_views.sql')

diff --git a/task5_views.sql b/task5_views.sql
new file mode 100644
index 0000000..87d1ea3
--- /dev/null
+++ b/task5_views.sql
@@ -0,0 +1,62 @@
+---------------
+-- Задание 1 --
+---------------
+
+CREATE VIEW PaymentNotifications AS
+SELECT 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
+GO
+
+DROP VIEW PaymentNotifications
+GO
+
+-- При получении оплаты уведомлять клиентов, о начале
+-- обработки заказа на указанную дату.
+SELECT * FROM PaymentNotifications
+GO
+
+
+CREATE VIEW DeliveryExpenses AS
+SELECT Deliveries.date, Vehicles.gas_consumption, Routes.distance
+FROM Deliveries
+INNER JOIN Vehicles ON Deliveries.vehicle_id = Vehicles.id
+INNER JOIN Routes ON Deliveries.route_id = Routes.id
+WHERE DATEPART(year, date) = DATEPART(year, GETDATE())
+GO
+
+DROP VIEW DeliveryExpenses
+GO
+
+-- Посчитать затраты на топливо по месяцам в текущем году.
+SELECT DATEPART(month, date), SUM(gas_consumption * distance)
+FROM DeliveryExpenses
+GROUP BY DATEPART(month, date)
+GO
+
+
+CREATE VIEW BoxDates AS
+SELECT Orders.id, Boxes.id, Deliveries.date
+FROM Shipments
+INNER JOIN Orders ON Shipments.order_id = Orders.id
+INNER JOIN Boxes ON Orders.id = Boxes.order_id
+INNER JOIN Deliveries ON Shipments.delivery_id = Deliveries.id
+WHERE Deliveries.date > GETDATE()
+GO
+
+DROP VIEW BoxDates
+GO
+
+-- Найти даты доставки всех недоставленных коробок.
+SELECT * FROM Boxes
+GO
+
+---------------
+-- Задание 2 --
+---------------
+
+
+---------------
+-- Задание 3 --
+---------------
\ No newline at end of file
-- 
cgit v1.2.3