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
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
|
-- INNER JOIN, LOWER
SELECT LOWER(Customers.email) email, Orders.date
FROM Orders INNER JOIN Customers
ON Orders.customer_id = Customers.id;
-- LEFT JOIN, UPPER
SELECT UPPER(Customers.name) name, Orders.date
FROM Orders LEFT JOIN Customers
ON Orders.customer_id = Customers.id;
-- RIGHT JOIN
SELECT Customers.name, Orders.date
FROM Orders RIGHT JOIN Customers
ON Orders.customer_id = Customers.id;
-- FULL JOIN, IIF
-- Выбрать все коробки и ограничить цену на обёртки в 40 рублей.
SELECT Boxes.id, order_id, warehouse_id,
IIF(price > 40, 40, price) price,
volume, weight
FROM Boxes FULL JOIN Wrappings
ON Boxes.wrapping_id = Wrappings.id;
-- CROSS JOIN
SELECT gas_consumption, distance
FROM Vehicles CROSS JOIN Routes;
-- CROSS APPLY
SELECT id, city_id, volume, weight
FROM Warehouses CROSS APPLY (
SELECT Boxes.volume, Boxes.weight
FROM Boxes WHERE Boxes.warehouse_id = Warehouses.id
) ap;
-- САМОСОЕДИНЕНИЕ
SELECT R1.city1_id, R2.city2_id, R1.distance + R2.distance AS distance
FROM Routes as R1
INNER JOIN Routes AS R2 ON R1.city2_id = R2.city1_id;
--- *** ---
-- UNION
-- Все коробки, у которых обёртка должна быть мягкой или воздушно-пузырьковой.
SELECT id, volume FROM Boxes WHERE wrapping_id = 3
UNION
SELECT id, volume FROM Boxes where wrapping_id = 4;
-- UNION ALL
SELECT city_id FROM Warehouses
UNION ALL SELECT city_id FROM Drivers;
-- EXCEPT
-- Выбрать все заказы, кроме сделанных 29 февраля 2004 года.
SELECT * FROM Orders
EXCEPT SELECT * FROM Orders WHERE date='2004-02-02';
-- INTERSECT
-- ВЫбрать доставки, в которых Водитель 1 ехал на Машине 1
SELECT * FROM Deliveries WHERE driver_id=1
INTERSECT SELECT * FROM Deliveries WHERE vehicle_id=1;
--- *** ---
-- EXISTS
-- Выбрать оплаченные заказы
SELECT * FROM Orders
WHERE EXISTS(
SELECT * FROM Payments WHERE Payments.order_id = Orders.id
);
-- IN, BETWEEN
-- Выбрать доставки, исполненные водителями из Города #4
-- между 20 апреля и 30 апреля 2021 года.
SELECT * FROM Deliveries
WHERE Deliveries.driver_id IN(
SELECT id FROM Drivers WHERE city_id=4
) AND (Deliveries.date BETWEEN '2021-04-20' AND '2021-04-30');
-- LIKE, REPLACE
-- ВЫбрать всех клиентов с почтой на домене mail.ru и заменить этот домен на vk.com
SELECT id, name, REPLACE(email, '@mail.ru', '@vk.com')
FROM Customers WHERE email LIKE '%@mail.ru';
-- CASE
SELECT id,
CASE WHEN on_delivery = 1
THEN N'Водитель в данный момент на доставке'
ELSE N'Водитель свободен'
END AS delivery_status
FROM Drivers;
--- *** ---
-- CAST
-- Оценить обёртку для коробок (формула не несёт особого смысла)
SELECT Boxes.id, Boxes.volume / Boxes.weight * CAST(price AS FLOAT)
FROM Boxes FULL JOIN Wrappings on Boxes.wrapping_id = Wrappings.id;
-- CONVERT
SELECT id, CONVERT(NVARCHAR, Payments.date, 0) FROM Payments;
-- CHOOSE
SELECT id, order_id, warehouse_id,
CHOOSE( wrapping_id, N'Жёсткая',
N'Полужёсткая', N'Мягкая',
N'Воздушно-Пузырьковая', N'Рулон') wrapping,
volume, weight
FROM Boxes;
--- *** ---
-- STUFF, SUBSTRING
-- Скрыть email клиентов
SELECT id, name, STUFF(SUBSTRING(email, 1, 4), 4, 1, '***')
FROM Customers;
-- ALL
-- Выбрать наибольшую выплату.
SELECT * FROM Payments WHERE amount >= ALL (SELECT amount FROM Payments);
-- ANY
-- Выбрать машины, занятые в заданный интервал времени
SELECT * FROM Vehicles
WHERE id = ANY (
SELECT vehicle_id FROM Deliveries
WHERE (date BETWEEN '2021-04-20' AND '2021-04-30')
);
-- STR
SELECT N'Площадь склада ' + STR(Warehouses.id, 3) + N' в городе ' + Cities.name + N' равна ' + STR(area, 7)
FROM Warehouses INNER JOIN Cities
ON Warehouses.city_id = Cities.id;
--- *** ---
-- DATEADD, SYSDATETIMEOFFSET, DATEPART, GETDATE
-- Выбрать доставки следующего месяцы
SELECT * FROM Deliveries
WHERE DATEPART(year, date) = DATEPART(year, GETDATE()) AND
DATEPART(month, DATEADD(month, 1, SYSDATETIMEOFFSET())) =
DATEPART(month, date);
-- DATEDIFF
-- Время ожидания до доставки от оформления заказа.
SELECT Shipments.id,
DATEDIFF(year, Orders.date, Deliveries.date) * 12 +
DATEDIFF(month, Orders.date, Deliveries.date)
FROM Shipments INNER JOIN Orders
ON Shipments.order_id = Orders.id
FULL JOIN Deliveries
ON Shipments.delivery_id = Deliveries.id;
-- DATEPART, GETDATE
-- Вычислить количество доставок в каждом месяце этого года, если их больше 50.
SELECT COUNT(id) count, DATEPART(month, date) month FROM Deliveries
WHERE DATEPART(year, date) = DATEPART(year, GETDATE())
GROUP BY DATEPART(month, date)
HAVING COUNT(id) > 50;
|
