1 files changed, 62 insertions, 0 deletions
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