1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
|
USE shipping
---------------
-- Задание 1 --
---------------
CREATE VIEW PaymentNotifications AS
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
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 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
INNER JOIN Deliveries ON Shipments.delivery_id = Deliveries.id
WHERE Deliveries.date > GETDATE()
GO
DROP VIEW BoxDates
GO
-- Найти даты доставки всех недоставленных коробок.
SELECT * FROM Boxes
GO
---------------
-- Задание 2 --
---------------
CREATE VIEW BoxesView AS
SELECT * FROM shipping.dbo.Boxes
WHERE weight < 25 AND volume <= 1.1
WITH CHECK OPTION
GO
DROP VIEW BoxesView
GO
INSERT INTO BoxesView (order_id, warehouse_id, wrapping_id, volume, weight)
VALUES (1, 1, 1, 0.7, 13)
GO
INSERT INTO BoxesView (order_id, warehouse_id, wrapping_id, volume, weight)
VALUES (1, 1, 1, 1.7, 13)
GO
INSERT INTO BoxesView (order_id, warehouse_id, wrapping_id, volume, weight)
VALUES (1, 1, 1, 0.5, 27)
GO
---------------
-- Задание 3 --
---------------
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
|
