-- 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;